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 users 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 the relationship between indexes and keys.
OceanBase Database uses a clustered index table model. It automatically generates a primary key index for the primary key specified by the user. 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 data records.
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
The data is stored in the employee table in order of the specified id. When searching for data, it can quickly locate the specific data based on the id using binary search. If you want to quickly search based on the name field, you can create a secondary index on the name field, as shown in the following example:
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
The data is stored in the name_index index table in order of the name field. When you specify the name field for querying, it can quickly locate the specific data using binary search.
Advantages and disadvantages of indexes
The advantages of indexes are as follows:
You can accelerate queries without modifying SQL statements. It only needs to scan the required data.
Indexes store fewer columns, which saves query I/O.
The disadvantages of indexes are as follows:
You need to have a deep understanding of your business and data model to decide which fields to create indexes on.
When your business changes, you need to re-evaluate whether the existing indexes still meet your needs.
Writing data requires maintaining the data in the index table, which consumes a certain amount of performance.
Index tables consume memory, disk space, and other resources.
Availability and visibility of indexes
Index availability
In the case of dropping a partition, if the rebuild index field is not specified, the index will be 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 it.
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, you can set it to invisible to observe the impact on your business. If there is no impact, you can then delete the index.
Relationship between indexes and keys
A key is a set of columns or expressions on which you can create an index. However, indexes and keys are different. An index is an object stored in the data, while a key is a logical concept.