Lindorm:Secondary indexes

Background information

Features

Lindorm secondary indexes allow you to create multiple indexes for a single table. Each index is physically mapped to a data table that is independent of the primary table. Each index can have different storage policies, such as different compression algorithms or hot and cold data separation policies. When you write to the primary table, Lindorm automatically updates all index tables and ensures data consistency between the primary table and index tables. When you read data, you only need to query the primary table. Lindorm automatically selects a suitable index, which can be the primary table itself, based on the WHERE condition and schema. Hints are supported to influence the optimizer's behavior. The basic features of Lindorm secondary indexes are as follows.

• Support for multiple indexes on a single primary table.

• Support for compound indexes that use one or more columns.

• Redundant indexes are supported. A fully redundant index automatically replicates new columns from the primary table.

• Query optimization: Automatically selects an index based on the WHERE clause. Supports hints to influence the optimizer's choice.

• Online Schema Change: Index changes do not affect normal read and write operations on the primary table. You can add, delete, or update indexes at any time.

• Support for Time to Live (TTL): Index tables automatically inherit the TTL settings of the primary table. Data in the primary table and index tables expires at the same time.

• Support for dynamic columns: You can write to and include dynamic columns in an index.

• Support for custom data versions: Data is automatically written with a specified custom timestamp.

Prerequisites

• Server: A Lindorm instance.

• Client: For more information, see Use LindormTable with Lindorm SQL.

• Lindorm-cli: For more information, see Connect to and use LindormTable with Lindorm-cli.

Terms

• Strong consistency: This refers to data consistency between a primary table and its index tables. To meet data consistency requirements, minimize the overhead of secondary indexes, and improve write throughput, Lindorm secondary indexes have the following constraints for strong consistency.

  • Snapshot isolation is not supported. During a data write, visibility is not guaranteed. However, after the client receives a success response, the data is visible in both the primary table and index tables.

  • If a client timeout or an I/O error occurs, the data is not guaranteed to be visible in the primary table and index tables. However, the data in the primary table and index tables will eventually be consistent.

• Mutability options: If a primary table has one index, a write operation involves four steps: reading data from the primary table, deleting the old data from the index, writing data to the index, and writing data to the primary table. This process significantly increases the cost of index maintenance. However, write amplification does not occur in all scenarios. For example, in logging scenarios, data is only inserted and not updated. In this case, no old data exists in the index table, so the system only needs to write to the index and the primary table. For this reason, Lindorm introduces the concept of mutability. Mutability classifies the write patterns for primary tables and organizes index data accordingly. This minimizes index organization costs for different requirements. The following table describes the mutability types. You must set the Mutability attribute using the Table_Options parameter when you create or modify a table. For more information, see CREATE TABLE syntax.

  Mutability classification	Constraint	Cost	Description
  No index	None.	1	If no index exists, the system writes directly to the primary table. This is a single operation.
  IMMUTABLE	Data is written by row. Data cannot be updated or deleted.	2	Writes to the primary table and the index table. This type has the lowest cost and best performance in all scenarios. Important You must ensure that no data is updated or deleted. This type is not recommended.
  IMMUTABLE_ROWS	Data is written and deleted by row. Data cannot be updated.	2 to 3	For normal writes: writes to the primary table and the index table. In deletion scenarios, an additional read from the primary table is required. Performance is second only to IMMUTABLE. Important You must ensure that no data is updated. This type is not recommended.
  MUTABLE_LATEST (Recommended)	Data can be updated and deleted. You cannot write data with custom timestamps.	4	Reads from the primary table, deletes from the index, writes to the index, and writes to the primary table. Important For instances with a database engine version of 2.7.9 or later, this attribute is the default attribute. If the version is earlier than 2.7.9, we recommend that you manually set the attribute to MUTABLE_LATEST.
  MUTABLE_ALL	No limits. You can use custom timestamps to write data.	4	Reads from the primary table, deletes from the index, writes to the index, and writes to the primary table.
  MUTABLE_UDT	No limits. You can use custom timestamps to write data.	4	Reads from the primary table, deletes from the index, writes to the index, and writes to the primary table. Note This attribute is an optimized version of MUTABLE_ALL and provides better performance. The database engine version must be 2.6.7 or later.

  The recommended attribute is MUTABLE_LATEST. It provides better performance but does not allow writing data with a specified timestamp. For instances with a database engine version of 2.7.9 or later, MUTABLE_LATEST is the default attribute. If your instance uses a database engine version earlier than 2.7.9, you must manually set the attribute to MUTABLE_LATEST.

  To write data with custom timestamps, we recommend that you change the attribute to MUTABLE_UDT. This requires the database engine version to be 2.6.7 or later. For usage notes, see points 4 and 5 in the following note.

  Note

  • The IMMUTABLE and IMMUTABLE_ROWS types do not involve data updates. This means there is no write amplification, and the cost is minimized. These types are suitable for high-throughput write scenarios, such as logging and monitoring. If you set the type to IMMUTABLE or IMMUTABLE_ROWS but still perform data updates, the server does not report an error. However, this may cause data inconsistency between the primary table and the index table.

  • The IMMUTABLE type does not involve deletions. This lets you fully use Lindorm's multi-IDC deployment to achieve active-active data access.

  • Choosing one of the two IMMUTABLE types can effectively reduce write latency and improve overall throughput in indexing scenarios. If your business scenario does not meet the IMMUTABLE requirements, you can use data redundancy to adapt it to an IMMUTABLE scenario.

  • The Mutability attribute takes effect when you create an index table. For database engine versions 2.7.9 and later, the default value is MUTABLE_LATEST. This attribute does not support writing data with a specified timestamp. Therefore, plan this attribute in advance based on whether you need to write data with custom timestamps.

  • If you use custom timestamps to write data but do not set the Mutability attribute to MUTABLE_UDT or MUTABLE_ALL before you create an index, the service is immediately affected and an error is reported after the index is created. For solutions, see FAQ.

• Update indexes with custom timestamps: Lindorm supports writing data with custom timestamps. You can update data at any timestamp, and the system ensures that only the data with the latest timestamp takes effect. The custom timestamp feature is important for controlling data TTL and handling out-of-order and idempotent scenarios. It is widely used in HBase. Lindorm supports column-level timestamps and allows writing data to primary tables with custom timestamps. However, in NoSQL systems that support both secondary indexes and timestamps, support for updating indexes with custom timestamps is rare. This is because out-of-order writes make it difficult to effectively maintain index updates and deletions. Lindorm global secondary indexes solve this problem by supporting column-level updates with custom timestamps. The following are two business scenarios that use custom timestamps.

  • Concurrent import and real-time operations: In scenarios that require both real-time updates and historical data imports, you can use the current time for real-time updates and a time such as 23:59:59 of the previous day for historical data imports. This way, data that was not updated on the current day can be updated by the import operation, and data that has already been updated will not be overwritten by the import.

  • Process backlogged messages: A business system uses messages to trigger a series of processing logic. When message backlogs occur, the system can skip the backlogged messages and process the current message directly. It can then process the backlogged tasks later. Alternatively, if there is a problem with the business logic, the system can skip some messages to avoid the problem and reprocess them after the business logic is fixed. In this case, the business can use the timestamp of the message itself to write data. This ensures the correct overwrite relationship between backlogged messages and normal messages.

• Full covering indexes: To avoid scanning the primary table after an index scan, you typically include some primary table columns in the index table. This is known as a covering index. A full covering index is a common redundancy solution. Lindorm supports three redundancy modes that simplify the implementation of full covering indexes when the primary table schema changes or in dynamic column scenarios.

  • Include specified columns: Explicitly specify which columns from the primary table to include.

  • Include all columns in the primary table schema: When you need a full covering index, you do not need to explicitly add every column from the primary table in the CREATE INDEX statement. Instead, you can use a constant that represents all columns. When a new column is added to the primary table, the full covering index table automatically includes this new column. You do not need to reindex. You also do not need to worry that queries on the new column will cause a scan of the primary table.

  • Include dynamic columns: Lindorm supports both fixed schemas and loose schemas (dynamic columns). Using the DYNAMIC redundancy mode, an index table can automatically include all dynamic columns from the primary table. It will also include all columns from the primary table schema.

Create a secondary index (CREATE INDEX)

After you create a Lindorm primary table, you can create a secondary index for the columns in the table. The following example shows how to create a secondary index.

-- Create the primary table.
CREATE TABLE test (
  p1 VARCHAR NOT NULL,
  p2 INTEGER NOT NULL,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR,
  c5 GEOMETRY(POINT),
  PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');

-- Create a secondary index on the c3 column and include all columns.
CREATE INDEX idx1 ON test(c3 desc) WITH (INDEX_COVERED_TYPE ='COVERED_ALL_COLUMNS_IN_SCHEMA');

-- Query data from the index table. Because an index is built on the c3 column, the query hits the index table when c3 is specified.
SELECT * FROM test WHERE c3 = 'data'; 

View secondary indexes (SHOW INDEX)

You can use Lindorm SQL to view the status of a created secondary index. The following example shows how to view a secondary index.

SHOW INDEX FROM test;

Change the status of a secondary index (ALTER INDEX)

After you create a secondary index, if the primary table contains historical data, you must manually rebuild the index. For more information about the syntax, see BUILD INDEX. If the primary table has no historical data, you can use the ALTER INDEX syntax to change the status of the secondary index table. The following example shows how to change the status of a secondary index:

ALTER INDEX IF EXISTS idx1 ON test ACTIVE;
ALTER INDEX idx1 ON test DISABLED;

Delete a secondary index (DROP INDEX)

The following example shows how to delete a secondary index from a primary table.

DROP INDEX IF EXISTS idx1 ON test;

Query optimization

Lindorm selects a secondary index based on the Rule Based Optimization (RBO) policy. It matches the prefix of the index table based on the query conditions and selects the index table with the longest matching prefix as the index for the query. The following example helps you better understand this process.

-- The primary table and index tables are as follows:
CREATE TABLE dt (rowkey varchar, c1 varchar, c2 varchar, c3 varchar, c4 varchar, c5 varchar, PRIMARY KEY(rowkey));
CREATE INDEX idx1 ON dt (c1);
CREATE INDEX idx2 ON dt(c2,c3,c4);
CREATE INDEX idx3 ON dt(c3) INCLUDE(c1,c2,c4);
CREATE INDEX idx4 ON dt(c5 desc) WITH (INDEX_COVERED_TYPE ='COVERED_ALL_COLUMNS_IN_SCHEMA');

-- The query optimizations are as follows:
SELECT rowkey FROM dt WHERE c1 = 'a';
SELECT rowkey FROM dt WHERE c2 = 'b' AND c4 = 'd';
SELECT * FROM dt WHERE c2 = 'b' AND c3 >= 'c' AND c3 < 'f';
SELECT * FROM dt WHERE c5 = 'c';

Limits

• You can create indexes with the same name for different primary tables. For example, you can create an index named Idx1 for both the dt table and the foo table. However, you cannot create indexes with the same name for the same primary table.

• You can create indexes only for tables that store data of a single version. You cannot create indexes for multi-version tables.

• Secondary indexes do not support cell-level TTL.

• If you create an index for a primary table that has a table-level TTL, you cannot set a separate TTL for the index table. The index table automatically inherits the TTL of the primary table.

• An index can contain a maximum of three index columns.

• The total length of the index columns and the primary key of the primary table cannot exceed 30 KB. We recommend that you do not use a column larger than 100 bytes as an index column.

• You can create a maximum of five index tables for a single primary table. Too many indexes can lead to high storage costs and long write latencies.

• A query can hit a maximum of one index. Index merge queries are not supported.

• When you create an index, the data from the primary table is synchronized to the index. Creating an index for a table with a large amount of data can cause the CREATE INDEX command to take a long time.

• Secondary indexes do not support the batch increment feature.

• The sorting of a secondary index that is hit by a query is different from that of the primary table.

• You can create secondary indexes only for data that is written using SQL or an API. You cannot create secondary indexes for data that is imported into Lindorm using BulkLoad.

FAQ

For other issues about indexes, you can contact Lindorm technical support on DingTalk or submit a ticket. For more information, see Technical support.