This topic describes the terms and the features of secondary indexes and the differences between secondary index types. This topic also describes the considerations when you use secondary indexes.

Background information

Secondary indexes allow you to create one or more index tables for a data table. Then, you can query data from the primary key columns of the index tables instead of the data table. This improves query performance.

Tablestore provides global secondary indexes and local secondary indexes to meet your requirements, such as strong query consistency.

Differences between secondary index types

Secondary indexes can be classified into global secondary indexes and local secondary indexes. You can use secondary indexes based on your requirements.

Name Difference
Global secondary index
  • Tablestore automatically synchronizes the data in indexed columns and primary key columns from a data table to an index table in asynchronous mode. In most cases, the synchronization is completed within milliseconds.
  • The first primary key column of the index table can be a primary key column or a predefined column of the data table.
Local secondary index
  • Tablestore automatically synchronizes the data in indexed columns and primary key columns from a data table to an index table in synchronous mode. You can immediately query the data from the index table after the data is synchronized.
  • The first primary key column of the index table must be the first primary key column of the data table.

Terms

Term Description
index table The table created based on the indexed columns of a data table.

The data in the index table is read-only.

predefined column The non-primary column predefined when you create a data table. A predefined column is used as an attribute column of an index table. You can also specify the data type for the non-primary key column.
Note Tablestore uses a schema-free model. You can write the data of different attribute columns to a row. You do not need to specify attribute columns for the table schema.
single-column index The index that is created on a single column.
compound index The index that is created on multiple columns. For example, a compound index can have indexed columns 1 and 2.
indexed attribute column The predefined column mapped to an index table.
autocomplete The feature that allows the system to automatically add the primary key columns that are not specified as indexed columns to an index table.

Features

  • Single-column index and compound index

    You can create an index on one or more columns in a data table.

  • Index synchronization
    Global secondary indexes and local secondary indexes synchronize data in different modes.
    • If you use global secondary indexes, Tablestore automatically synchronizes the data in indexed columns and primary key columns from a data table to an index table in asynchronous mode. In most cases, the synchronization is completed within milliseconds.
    • If you use local secondary indexes, Tablestore automatically synchronizes the data in indexed columns and primary key columns from a data table to an index table in synchronous mode. You can immediately query the data from the index table after the data is synchronized.
  • Covered indexes

    Index tables can contain attribute columns. You can create predefined columns when you create a data table. Then, you can create an index table based on predefined columns and primary key columns of the data table. You can specify predefined columns as indexed attribute columns. You can also specify no indexed attribute columns.

    If you specify predefined columns of a data table as indexed attribute columns, you can query the values in the predefined columns from the index table. You do not need to query the data table.

  • Index that contains existing data of a data table

    You can create an index table that contains the existing data of a data table.

  • Sparse index

    You can specify a predefined column of a data table as an indexed attribute column. If a row in the data table does not contain the predefined column but contains all indexed columns, an index is created on the row. However, an index cannot be created on a row if the row does not contain all indexed columns.

    For example, a data table contains primary key columns PK0, PK1, and PK2, and predefined columns Defined0, Defined1, and Defined2. You create an index table that contains primary key columns PK0, Defined0, and Defined1, and an indexed attribute column Defined2.
    • If a row of the data table contains Defined0 and Defined1 but does not contain Defined2, an index is created on the row.
    • If a row of the data table contains Defined0 and Defined2 but does not contain Defined1, an index cannot be created on the row.

Limits

For more information, see Secondary index limits.

Considerations

When you configure indexed columns and attribute columns for an index table, take note of the following items:
  • The system automatically adds the primary key columns that are not specified as indexed columns to an index table. When you scan data in an index table, you must specify the values of primary key columns. The values range from negative infinity to positive infinity.

    When you create an index table, you need only to specify the columns to be indexed. The system automatically adds all primary key columns of the data table to the index table. For example, a data table contains primary key columns PK0 and PK1 and a predefined column Defined0.

    If you use global secondary indexes, you can create an index on columns as needed.
    • If you create an index on Defined0, Tablestore generates the index table whose primary key columns are Defined0, PK0, and PK1.
    • If you create an index on Defined0 and PK1, Tablestore generates the index table whose primary key columns are Defined0, PK1, and PK0.
    • If you create an index on PK1, Tablestore generates the index table whose primary key columns are PK1 and PK0.
    If you use local secondary indexes, the first primary key column of an index table must be the same as the first primary key column of the data table.
    • If you create an index on PK0 and Defined0, Tablestore generates the index table whose primary key columns are PK0, Defined0, and PK1.
    • If you create an index on PK0, PK1, and Defined0, Tablestore generates an index table whose primary key columns are PK0, PK1, and Defined0.
    • If you create an index on PK0 and PK1, Tablestore generates the index table whose primary key columns are PK0 and PK1.
  • You can specify predefined columns of a data table as indexed attribute columns based on your query modes and costs.

    After you specify a predefined column of a data table as an indexed attribute column, you can query the value in the predefined column from the index table. You do not need to query the data table. However, this increases storage costs. If the predefined column of the data table is not specified as an indexed attribute column, you must query the column value from the data table.

  • When you use global secondary indexes, specify a column of a data table as the first primary key column of an index table based on your requirements.
    • If the column whose value is time or a date is the first primary key column of an index table, the update speed of the index table may decrease. Therefore, we recommend that you do not specify this type of column as the first primary key column of an index table.

      We recommend that you hash the column whose value is time or a date and create an index on the hashed column. If you have such a requirement, use DingTalk to contact Tablestore technical support.

    • We recommend that you do not specify a column of low cardinality or a column that contains enumerated values as the first primary key column of an index table. For example, if you specify the gender column as the first primary key column of an index table, the horizontal scalability of the index table is limited. As a result, the write performance is compromised.
When you use an index table, take note of the following items:
  • You must comply with the following rules when you write data to a data table that is associated with an index table. Otherwise, the data cannot be written to the data table.
    • You cannot customize the version number for the data that you write to an index table.
    • An index table cannot contain a row that has the same primary keys during a batch write operation.

Pricing

For more information, see Billable items of secondary indexes.