Overview

An index is an optional structure that you can create for one or more fields based on your business needs to speed up queries on these fields. This topic describes the advantages and disadvantages of using indexes, as well as the availability and visibility of indexes.

Advantages and disadvantages of indexes

The advantages of indexes are as follows:

• You can accelerate queries without modifying SQL statements by scanning only the required data.

• Indexes typically store fewer columns, which can reduce query I/O.

The disadvantages of indexes are as follows:

• You need a deep understanding of your business and data model to decide which columns to index.

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

• Maintaining indexes during write operations consumes some performance.

• Indexes occupy resources such as memory and disk space.

Index availability and visibility

Index availability

In a DROP PARTITION operation, if the rebuild index field is not specified, the index is marked as UNUSABLE, indicating that it is unavailable. In this case, the optimizer does not maintain the index during DML operations and ignores the index.

Index visibility

Index visibility refers to whether the optimizer ignores the index. If an index is invisible, the optimizer ignores it, but the index still needs to be maintained during DML operations. Before deleting an index, you can set it to invisible to observe its impact on your business. If no impact is observed, 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 a database object, while a key is a logical concept.

Index types

Global and local indexes

Global indexes

A global index is created across the entire table and contains data from all partitions of the table. Global indexes are suitable for scenarios where queries are performed across partitions.

Local indexes

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

Unique and non-unique indexes

Unique indexes

A unique index requires that the values in the indexed column are unique within the index. Each index value corresponds to only one record. Unique indexes are used to ensure the uniqueness of a field in the table.

Non-unique indexes

A non-unique index allows duplicate values in the indexed column. Multiple index values can correspond to one or more records. Non-unique indexes are used to accelerate queries on the indexed column, but they do not guarantee the uniqueness of the indexed column values.

Spatial indexes

A spatial index is a special index type used to accelerate queries on spatial data. It is primarily applied in geographic information systems (GIS) and spatial data processing. Spatial indexes support efficient queries and spatial relationship analysis on spatial objects such as points, lines, and areas.

Function-based indexes

A function-based index is an index created based on the computed values of one or more columns in the table. Function-based indexes are an optimization technique that allows quick location of matching function values during queries, thereby avoiding redundant calculations and improving query efficiency.

Columnar indexes

A columnar index stores data in the index table in a columnar format. Starting from OceanBase Database V4.3.0, you can specify the storage format of a table to be columnar when creating the table. In this case, the index table, like the data table, is a single table, and the data in the index table can also be stored in a columnar format.

For more information about columnar storage, see Columnar storage.

Multi-valued indexes

The MySQL mode of OceanBase Database supports the multi-valued index feature, which is particularly useful for JSON documents and other collection data types. This feature allows you to create indexes on arrays or collections, thereby improving the efficiency of queries that search for JSON array elements.

In the current version, you can create multi-valued indexes using the CREATE TABLE statement. This involves applying the CAST(... AS ... ARRAY) statement in the index definition to convert scalar values of the same type within the JSON array into the SQL array type. This process creates a virtual column that automatically populates SQL array values; subsequently, a function index (also known as a virtual index) is created on this virtual column. The multi-valued index is established on the virtual column, which extracts values from the SQL array.

Features of multi-valued indexes

• DML operations that affect multi-valued indexes are handled in the same way as those that affect regular indexes, with the exception that a single clustered index record may be inserted or updated multiple times.

• Nullability and multi-valued indexes:

  • If a multi-valued key contains an empty array, no entries are added to the index, and the data record cannot be accessed through index scanning.
  • If a typed array column is set to NULL, the storage engine stores a single entry containing NULL and pointing to the data record.

• Since multi-valued indexes are virtual indexes on virtual columns, they must follow the same rules as auxiliary indexes on virtual columns.

Full-text indexes

A full-text index is a technique used to quickly search for text data. It provides comprehensive indexing and retrieval of text content. Full-text indexes can index entire documents or large text segments and are primarily designed for full-text search, which means quickly finding matching text in the database based on user input keywords. They are capable of handling large volumes of text data, including articles, reports, web pages, and emails.

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