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
Indexes have the following advantages:
Indexes allow you to speed up SQL queries without the need to modify SQL statements, because only the data that you require is scanned.
Indexes usually store a small number of columns. This reduces the query I/O overheads.
Indexes have the following disadvantages:
To create indexes on proper columns, you must have a deep understanding of your business and the business data model.
If any change occurs in your business, you must evaluate whether the existing indexes can still meet your business requirements.
Data stored in index tables must be maintained when incremental data is written to the table. This affects the database performance.
Index tables consume resources, such as memory and disk resources.
Usability and visibility
Usability 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 determines whether the index is ignored by the optimizer. If an index is invisible, it is ignored by the optimizer but needs to be maintained in DML operations. Before you drop an index, you can make it invisible to test the effect on your business. If your business is not affected, you can drop the index.
Relationships between indexes and keys
A key is one or more columns or expressions on which you can create indexes. A key is a logical concept, whereas an index is an object stored in a database.