Indexes are a key component for improving query performance. OceanBase AI Database supports multiple index types to accelerate equality/range queries, full-text search, JSON set queries, vector similarity search, and analytical scans.
Index classification
Indexes can be classified from three dimensions: data distribution, storage format, and index structure.
Classification by data distribution
The data distribution of an index determines the correspondence between its partitions and those of the base table:
- Local index: The index partitions and base table partitions have a one-to-one correspondence and are stored together.
- Global index: The index is independent of the base table partitions and can be organized across partitions.
Classification by storage format
The storage format of an index determines how the index table's own data is organized:
- Rowstore index: The index is organized by rows, which is beneficial for point queries and random access.
- Columnstore index: The index is organized by columns (specified using syntax like
WITH COLUMN GROUP (each column)), which is beneficial for analytical scans and aggregations. It is suitable for wide-table scans and analytical queries in HTAP scenarios, significantly improving retrieval and analytical performance on large-scale data.
For more information, Create an index.
Classification by index structure
Based on the preceding two dimensions, indexes can be classified into the following categories:
- B-tree index (normal index): The default index structure, including unique index, non-unique index, and function-based index, suitable for equality queries, range queries, sorting, and point queries. B-tree indexes can be local or global, and can use rowstore or columnstore formats—the columnstore format B-tree index is commonly referred to as a columnstore index and is suitable for analytical scans in HTAP scenarios. For more information, see Index overview, Create an index, and Columnstore.
- Full-text search: Based on inverted indexes and word segmentation, it establishes keyword mappings for
CHAR/VARCHAR/TEXTcolumns, suitable for log analysis, document retrieval, and relevance ranking. Only supports local indexes. For more information, see Full-text index. - JSON multi-valued index: Can be created on JSON arrays or sets to improve query efficiency for searching elements in JSON arrays, suitable for tag classification, many-to-many relationships, and predicates such as
MEMBER OF(),JSON_CONTAINS(), andJSON_OVERLAPS()(only in MySQL-compatible mode). Only supports local indexes. For more information, see Multi-valued index and Create an index. - Spatial index: Used for geospatial data and GIS scenarios, allowing quick retrieval of data within a specified coordinate range. Only supports local indexes.
- Vector index: Created on Vector-type columns, including dense vector indexes (such as HNSW and IVF) and sparse vector indexes, supporting distance calculations like L2, inner product, and cosine, suitable for vector similarity retrieval. It has an independent index structure and only supports local indexes. For more information, see Overview of vector indexes.