An index, also known as a secondary index, is an optional structure that users can choose to create based on their business needs. This allows them to accelerate queries on specific fields by creating indexes on those fields. This topic describes the advantages and disadvantages of using indexes, their availability and visibility, and their relationship with keys.
In OceanBase Database, a clustered index table model is used. This means that a primary key index is automatically created for the user-specified primary key. For other indexes created by the user, they are secondary indexes.
The following example shows how to create a table named employee and insert three sets of data.
obclient> CREATE TABLE employee(id INT, name VARCHAR(20), PRIMARY KEY(id));
Query OK, 0 rows affected
obclient> INSERT INTO employee VALUES(1,'John'),(2,'Alice'),(3,'Bob');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM employee;
+----+-------+
| ID | NAME |
+----+-------+
| 1 | John |
| 2 | Alice |
| 3 | Bob |
+----+-------+
3 rows in set
In the employee table, data is stored in order based on the user-specified id. When searching for data, it can be quickly located based on the id using a binary search method. If you need to quickly find data based on the name field, you can create a secondary index on the name field. The following example shows how to do this:
CREATE INDEX name_index ON employee(name);
The data in the index table is as follows:
name: Alice, id: 2
name: Bob, id :3
name: John, id: 1
In the name_index index table, data is stored in order based on the name field. When the user specifies the name field for querying, data can be quickly located based on the name field using a binary search method.
Advantages and disadvantages of indexes
The advantages of indexes are as follows:
Users can speed up queries without modifying SQL statements. Only the required data needs to be scanned.
Indexes typically store fewer columns, which can save query I/O.
The disadvantages of indexes are as follows:
Choosing which fields to create indexes on requires a deep understanding of the business and data model.
When the business changes, it is necessary to reassess whether the previously created indexes still meet the requirements.
When writing data, indexes must be maintained, which consumes a certain amount of performance.
Indexes occupy resources such as memory and disk space.
Availability and visibility of indexes
Index availability
In the case of dropping a partition, if the rebuild index field is not specified, the index is marked as UNUSABLE, indicating that it is unavailable. At this point, the index does not need to be maintained during DML operations, and the optimizer will ignore this index.
Index visibility
Index visibility refers to whether the optimizer ignores the index. If the index is invisible, the optimizer will ignore it, but the index still needs to be maintained during DML operations. Generally, before deleting an index, it is common practice to first set the index to invisible to observe its impact on the business. If no impact is observed, the index can then be safely deleted.
Relationship between indexes and keys
A key is a set of columns or expressions on which an index can be created. However, indexes and keys are different concepts. Indexes are stored in the data, while keys are logical concepts.
Function-based indexes
A function-based index is an index created based on the values of one or more columns in a table. Function-based indexes are an optimization technique that allows the database to quickly locate matching function values during queries, thereby avoiding redundant calculations and improving query efficiency.
Columnar indexes
A columnar index stores data in the index table in a columnar format. Starting from OceanBase Database V4.3.0, you can specify the storage format of a table as columnar when creating the table. In this model, an index table is just like a data table, and the data in the index table can also be stored in a columnar format.
For more information about columnar storage, see Columnar storage.