An index, also known as a secondary index, is an optional structure. You can create indexes for some fields based on your business requirements to speed up queries for the fields. This topic describes the advantages and disadvantages of indexes, the availability and visibility of indexes, and the relationship between indexes and keys.
OceanBase Database adopts the clustered index table model. The system automatically generates a primary key index for the specified primary key, and other indexes that you create are secondary indexes.
In the following example, the employee table is created, and three sets of data are inserted.
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 by id. You can quickly find specific data by id by performing a binary query. If you want to quickly find data by name, you can create a secondary index for name, as shown in the following example:
CREATE INDEX name_index ON employee(name);
The index table contains the following data:
name: Alice, id: 2
name: Bob, id :3
name: John, id: 1
In the name_index table, data is stored in order by name. You can quickly find specific data by name by performing a binary query.
Advantages and disadvantages of indexes
Advantages of indexes:
An index allows you to scan only part of the required data in a query without SQL statement modifications, which speeds up the query.
An index generally stores a small number of columns, which reduces the query I/O.
Disadvantages of indexes:
You must have an in-depth understanding of your business and data models to select the fields to be indexed.
When your business changes, you must re-evaluate whether the existing indexes meet your business requirements.
When data is written to the database, the system must maintain the data in the corresponding index table, which results in performance costs.
Index tables occupy memory and disk resources.
Availability and visibility of indexes
Availability of indexes
In a DROP PARTITION operation, if the rebuild index field is not specified, the index is marked as UNUSABLE, which indicates that the index is unavailable. In this case, the index does not need to be maintained in Data Manipulation Language (DML) operations, and is ignored by the optimizer.
Visibility of indexes
The visibility of an index indicates whether the index is ignored by the optimizer. If an index is invisible, the optimizer ignores the index. However, the index needs to be maintained in DML operations. Before you delete an index, you can set it to invisible, and then observe the impact on your business. If your business is not affected, you can delete the index.
Relationship between indexes and keys
A key is a set of columns or expressions. You can create an index on a key. However, indexes differ from keys. An index is an object stored in data, while a key is a logical concept.