Index overview

An index is also called a secondary index. It is an optional structure that users can create on specific fields based on their business needs to 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.

OceanBase Database uses a clustered index table model. It automatically generates a primary key index for the user-specified primary key, and other indexes created by the user are 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 user-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 your business and data model to decide which fields to create indexes on.

• When your business changes, you need to reassess whether the previously created indexes still meet your needs.

• When writing data, you need to maintain the data in the index table, which consumes some performance.

• Index tables consume memory, disk space, and other resources.

Index visibility

Index visibility refers to whether the optimizer ignores the index. If an index is invisible, the optimizer ignores it, but the index is still maintained during DML operations. Generally, before deleting an index, you can first set it to invisible to observe its 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.