Index Introduction
Indexes serve as a crucial capability for accelerating database queries. To comprehensively address diverse user query requirements and enhance overall database performance, the read-only analytical engine fully supports columnar-based secondary indexing capabilities. Currently, the read-only analysis engine supports three types of indexes: Zonemap index, Bloom filter index, and Bitmap index.
Generally speaking, creating an index can significantly reduce the data query volume for low selectivity predicate queries on high-cardinality columns, significantly optimizing the query speed.
Note:
The capability to execute index creation statements is currently supported only in version 2.2410.4.0 and later. Additionally, creating indexes requires users to have INDEX privileges. To utilize this feature, please ensure the account is granted this permission beforehand.
The columnar secondary index functionality is exclusively effective for the "read-only analytical engine" and is not supported by analytical cluster.
Zonemap Index
Zonemap index is a built-in index that does not need special attention from users. It automatically maintains statistics for each column and records information such as the maximum value, the minimum value, and whether there is NULL for each data block.
For scenarios such as equality queries, range queries, and IS NULL, you can determine whether the data file and data block contain data that meets the conditions with information such as the maximum value and minimum value. If not, skip reading the corresponding file or data block. In this way, unnecessary I/O operations can be reduced, effectively accelerating the query process.
Bloom Filter Index
A Bloom filter index is a skip index based on Bloom filter, which uses Bloom filter to skip data blocks that do not meet the specified conditions of equality queries, so as to reduce I/O operations and achieve query acceleration.
A Bloom filter is a fast search algorithm for multi-hash function maps proposed by Bloom in 1970. It is usually applicable for scenarios where it is necessary to quickly determine whether an element is part of a collection, but there is no strict requirement for 100% accuracy. Bloom Filter has the following features:
A high space efficiency probabilistic data structure used to check whether an element is in a collection.
For a call to detect the existence of an element, the Bloom filter will tell the caller either of the results: it may exist or it definitely does not exist.
Use Cases
The Bloom filter index can accelerate equality queries (including = and IN), and it works well for high-cardinality fields.
Limitations
The Bloom filter index has no effect on queries other than = and IN, such as !=, NOT INT, >, and <.
The Bloom filter index only supports the INT type with a maximum length of 256 bits, String type, Decimal type with a maximum length of 256 bits, Time type, Date type, and DateTime type.
Index creation for expressions is not supported, nor is multi-column join index.
The first column of a single primary key column or a multi-field join primary key does not support the creation of a Bloom filter index.
Index Usage
When you are executing SQL, if a Bloom filter index is created on the fields in the equality predicate or IN predicate within the where clause, the index will be automatically applied for query acceleration during querying.
Related Commands
Index creation statement
CREATE INDEX IF NOT EXISTS index name USING BLOOM_FILTER ON table_name(column_name);
Index deletion statement
DROP INDEX index_name ON table_name;
Index query statement
SHOW CREATE TABLE table_name;
Bitmap Index
Bitmap index is an index represented by a bitmap, which is created for every key value of the column. Compared with other indexes, the advantage of Bitmap index is that it occupies very little storage and is very fast to create and use. However, the lock granularity of modifications is large, and Bitmap index does not apply to scenarios with frequent updates.
Use Cases
It is suitable to be created on columns with high repetition, 100 to 100,000 recommended, such as Occupation and Prefecture-Level City columns. If the repetition is too high, there is no obvious advantage compared to other types of indexes, and if too low, the spatial efficiency and performance will be greatly reduced.
Specific types of queries, such as logical operations like count, or, and, only require bit operations. For example, query with a combination of multiple conditions: select count(*) from table where city = 'Nanjing' and job = 'doctor' and Type = 'iphone' and gender ='male'. For such scenarios, if a Bitmap index is created on each query condition column, the database can perform efficient bit operations to precisely locate the required data and reduce disk I/O operations. The smaller the filtered result set, the more pronounced the advantage of Bitmap index becomes.
It is suitable for analysis scenarios such as ad-hoc query and multi-dimensional analysis. If there is a table with 100 columns and users use 20 columns as query conditions (using any columns among these 20 columns) to create 20 Bitmap indexes on these columns, then all queries can be applied to the indexes.
Scenarios Not Applicable
Columns with low repetition, for example, Identity Number and Mobile Number columns.
Columns with high repetition, for example, Gender column. For such columns, you can create a Bitmap index, but it is recommended to use it to filter in conjunction with other conditions rather than using it as the sole query condition.
Columns that often need to be updated or modified.
Limitations
The Bitmap index supports expressions such as =, !=, >, <, >=, <=, in, is null, is not null, but multiple predicates can only be connected by "and".
The Bitmap index only supports INT type with a maximum length of 256 bits, String type, Decimal type with a maximum length of 256 bits, Time type, Date type, and DateTime type.
Index creation for expressions is not supported, nor is multi-column join index.
The first column of a single primary key column or a multi-field join primary key does not support the creation of a Bitmap index.
Related Commands
Index creation statement
CREATE INDEX IF NOT EXISTS index name USING BITMAP ON table_name(column_name);
Index deletion statement
DROP INDEX index_name ON table_name;
Index query statement
SHOW CREATE TABLE table_name;