This topic describes the built-in intelligent indexes of ApsaraDB for SelectDB and how to use the intelligent indexes.
Background information
Indexes are used to quickly filter or query data. ApsaraDB for SelectDB supports the following types of indexes:
Built-in intelligent indexes, including zone map indexes and prefix indexes.
Custom secondary indexes, including the inverted indexes, bitmap indexes, Bloom filter indexes, and NGram Bloom filter indexes. For more information, see Inverted indexes, Bitmap indexes, Bloom filter indexes, and NGram Bloom filter indexes.
Zone map index
The zone map index is the index information that is automatically maintained for each column in the column-oriented storage format, including Min, Max, and the number of NULL values. Zone map indexes are automatically created and maintained by ApsaraDB for SelectDB.
Prefix index
ApsaraDB for SelectDB is different from traditional databases. ApsaraDB for SelectDB is an online analytical processing (OLAP) database that uses a massively parallel processing (MPP) architecture to handle large amounts of data by increasing concurrency. ApsaraDB for SelectDB can work together with index schemas to accelerate queries.
The data in ApsaraDB for SelectDB is stored in a data structure that is similar to a sorted string table (SSTable). The data structure is an ordered data structure that can sort and store data based on specified columns. In the data structure, you can efficiently query data based on sort columns.
In the Aggregate, Unique, and Duplicate key models, the underlying data storage sorts and stores data based on the columns specified in the AGGREGATE KEY, UNIQUE KEY, and DUPLICATE KEY statements that are used to create tables. The prefix index is used to quickly query data based on the specified prefix columns after data is sorted.
Examples
The following example uses the first 36 bytes of a data row as the prefix index of the data row. Prefix indexes are truncated if the data is of the VARCHAR type. Example:
The prefix index of the following schema is
user_id(8 Bytes)+age(4 Bytes)+message(prefix 20 Bytes).ColumnName
Type
user_id
BIGINT
age
INT
message
VARCHAR(100)
max_dwell_time
DATETIME
min_dwell_time
DATETIME
If the prefix of the prefix index is specified as the query condition, the query can be accelerated. Execute the following query statements:
Example 1:
SELECT * FROM table WHERE user_id=1829239 and age=20;Example 2:
SELECT * FROM table WHERE age=20;The query speed in Example 1 is much faster than that in Example 2. When you create a table, you can sort columns in the correct order to improve the query efficiency.
The prefix index of the following schema is
user_name(20 Bytes). The prefix index does not contain 36 bytes and is truncated because the data is of the VARCHAR type.ColumnName
Type
user_name
VARCHAR(20)
age
INT
message
VARCHAR(100)
max_dwell_time
DATETIME
min_dwell_time
DATETIME
Modify a prefix index by using a materialized view
Columns of a table are sorted in the specified order when the table is created. Therefore, a table has only one prefix index. If you query data based on the columns that cannot hit the prefix index, the query efficiency may fail to meet your business requirements. You can create a materialized view to modify the column order. For more information, see Materialized views.