Introduction to indexes

An index is also known as a secondary index. It is an optional structure that users can choose to create on specific fields based on their business needs. This helps accelerate queries on those fields. This chapter introduces the advantages and disadvantages of using indexes, their availability and visibility, and the relationship between indexes and keys.

In OceanBase Database, the clustered index table model is used. The primary key index is automatically generated based on the user-specified primary key. Other indexes created by the user are considered secondary indexes.

For example, 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 specified id. When querying data, you can quickly locate the specific data by using binary search based on the id. If you need to quickly query 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

In the index table name_index, data is stored in order based on the name field. When querying data based on the name field, you can quickly locate the specific data by using binary search.

Advantages and disadvantages of indexes

The advantages of indexes are as follows:

• You can accelerate queries without modifying SQL statements. You only need to scan the required data.
• Indexes typically store fewer columns, which can save query I/O.

The disadvantages of indexes are as follows:

• You need to understand the business and data model to determine which fields to create indexes on.
• When the business changes, you need to reevaluate whether the previously created indexes still meet the requirements.
• When writing data, you need to maintain the data in the index table, which consumes some performance.
• Index tables consume resources such as memory and disk space.

Index visibility

Index visibility refers to whether the optimizer ignores the index. If an 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 first set it to invisible to observe its impact on the business. If there is no impact, you can then delete the index.

Relationship between indexes and keys

A key refers to a set of columns or expressions. You can create indexes on keys. However, indexes and keys are different. An index is an object stored in the data, while a key is a logical concept.