About indexes

An index is an optional structure that users can create on some fields based on their business needs. Its purpose is to accelerate the query speed on those fields. This topic mainly introduces the advantages and disadvantages of using indexes, as well as their availability and visibility.

Advantages and disadvantages

Indexes have the following advantages:

• Indexes enable faster SQL queries without the need to modify SQL statements, as only the necessary data is scanned.

• Indexes typically store a minimal number of columns, reducing query I/O overhead.

Indexes have the following disadvantages:

• Creating indexes requires a deep understanding of the business and its data model to choose the appropriate columns.

• If any changes occur in the business, a reassessment of existing indexes is necessary to ensure they still meet the requirements.

• Maintaining index table data during incremental data writes can impact database performance.

• Index tables consume resources, including 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.

Index types

Local and global indexes

Global indexes

A global index is an index that is created on the entire table and can contain data from all partitions of the table. Global indexes are suitable for cross-partition queries.

Local indexes

A local index is an index that is created in each partition and contains only data of that partition. Local indexes are suitable for scenarios where queries are performed within a specific partition.

Unique and non-unique indexes

Unique indexes

A unique index ensures the uniqueness of values of an indexed column in the index. Each index value can correspond to only one record. Unique indexes ensure that no two rows of a table have duplicate values in an indexed column.

Non-unique indexes

Non-unique indexes allow duplicate values in an indexed column. That is, multiple index values can correspond to one or more records. Non-unique indexes are used to accelerate queries on indexed columns. However, non-unique indexes cannot guarantee the uniqueness of indexed columns.

Spatial indexes

A spatial index accelerates the query of spatial data, which is mainly used to process geographic information system (GIS) and spatial data. Spatial indexes can support efficient query and spatial relationship analysis of spatial objects, such as points, lines, and planes.

Columnstore indexes

A columnstore index stores data in an index table in columnar format. In OceanBase Database V4.3.0 and later, you can specify to create a columnstore table. An index is also a table, and therefore supports columnar storage.

For more information about columnar storage, see Columnar storage.

Multi-valued index

The MySQL mode of OceanBase Database supports the multi-valued index feature, which is very useful for JSON documents and other set data types. By using the multi-valued index feature, you can create indexes on an array or a set to improve the query efficiency based on JSON array elements.

In the current version of OceanBase Database, you can use the CREATE TABLE statement to create a multi-valued index. To do so, you need to use the CAST(... AS ... ARRAY) clause in the index definition to convert the same type of scalar values in the JSON array into an SQL array. This operation creates a virtual column, into which SQL array values are automatically populated. Then, a function-based index, also known as a virtual index, is created on this virtual column. A multi-valued index is created on the virtual column, which extracts values from the SQL array.

Features

• DML operations on multi-valued indexes are processed in the same way as those on normal indexes. The only difference is that a single clustered index record may be inserted or updated multiple times.

• Take note of the following points about emptiness and multi-valued index:

  • If the key of a multi-valued index has an empty array, no entry is added to the index and the data record cannot be accessed by scanning the index.
  • If a typed-array column is set to NULL, the storage engine stores a single entry that contains NULL values and points to the data record.

• Multi-valued indexes are virtual indexes on virtual columns. Therefore, you must follow the same rules as those for auxiliary indexes on virtual generated columns.

Full-text index

Full-text index is a technique used to quickly search for and retrieve text data in an all-round manner. You can create a full-text index on a whole text file or a large segment of content in a text file. A full-text index aims to implement a full-text search. In other words, it aims to quickly search for matched text in the database based on the entered keyword. It can process a large amount of text data, including articles, reports, web pages, and emails.

For more information about full-text indexes, see Full-text index.