Lindorm:Secondary indexes

Background information

Features

Lindorm secondary indexes let you create multiple indexes for a single table. Each index maps to an independent physical data table, separate from the primary table. Indexes can have different properties, such as storage policies using distinct compression algorithms or hot and cold data separation strategies. When you write data to the primary table, Lindorm automatically updates all associated index tables and maintains data consistency between them. When you query data, you only need to query the primary table. Lindorm automatically selects the most suitable index—including the primary table—based on the WHERE clause and schema. You can also use hints to influence the optimizer’s choice. Key features include the following.

• Supports creating multiple indexes for a single primary table.

• Supports compound indexes built on one or more columns.

• Supports covering indexes. A full covering index automatically includes new columns added to 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 modifications do not affect normal read or write operations on the primary table. You can add, delete, or update indexes at any time.

• Supports Time to Live (TTL): Index tables inherit the TTL setting of the primary table. Data in both the primary and index tables expires simultaneously.

• Dynamic columns: Support for writing to dynamic columns and redundant dynamic columns.

• Supports custom data versions: Writes data with a user-specified timestamp.

Requirements

• Server: A Lindorm instance.

• Client: For more information, see Using the LindormTable Engine with Lindorm Wide-Table SQL.

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

Terms

• Strong consistency: Refers to data consistency between a primary table and its index tables. To meet data consistency requirements while minimizing secondary index overhead and maximizing write throughput, Lindorm secondary indexes impose the following constraints on strong consistency.

  • Snapshot isolation is not supported. While data is being written, it may not be immediately available for queries. After the system returns a success response to the client, the data becomes visible in both the primary table and index tables.

  • If a client timeout or I/O error occurs, the data may not appear in the primary table or index tables. However, eventual consistency between the primary table and index tables is guaranteed.

• Mutability options: If a primary table has one index, a write operation involves four steps: reading the primary table, deleting old data from the index, writing to the index, and writing to the primary table. This process significantly increases index maintenance cost. However, write amplification does not occur in all scenarios. For example, in logging scenarios, data is inserted only and never updated. In such cases, the index table contains no old data, so the system writes only to the index and primary table. To address this, Lindorm introduces mutability. Mutability classifies primary table write patterns and organizes index data accordingly to minimize index organization cost for different use cases. The following table describes mutability classifications. Set the mutability property using the Table_Options parameter when you create or modify a table. For more information, see CREATE TABLE syntax.

  Mutability classification	Constraint	Operation cost	Operation description
  No index	None.	1	If no index exists, the system writes directly to the primary table. This is a single operation.
  IMMUTABLE	Writes data by row. Data cannot be updated or deleted.	2	Writes to the primary table and the index table. This has the lowest cost and best performance of all scenarios. Important Make sure that no data is updated or deleted. This type is not recommended.
  IMMUTABLE_ROWS	Writes and deletes data 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 Make sure that no data is updated. This type is not recommended.
  MUTABLE_LATEST (Recommended)	Data can be updated and deleted. You cannot write data using a custom timestamp.	4	Reads from the primary table, deletes from the index, writes to the index, and writes to the primary table. Important For instances with LindormTable 2.7.9 or later, this is the default property. If your instance version is earlier than 2.7.9, set the property to MUTABLE_LATEST.
  MUTABLE_ALL	No restrictions. You can write data using a custom timestamp.	4	Reads from the primary table, deletes from the index, writes to the index, and writes to the primary table.
  MUTABLE_UDT	No restrictions. You can write data using a custom timestamp.	4	Reads from the primary table, deletes from the index, writes to the index, and writes to the primary table. Note This property is an optimized version of MUTABLE_ALL and provides better performance. It requires LindormTable 2.6.7 or later.

  The recommended setting is MUTABLE_LATEST. It delivers better performance but does not support writing data with a specified timestamp. For instances running LindormTable 2.7.9 or later, MUTABLE_LATEST is the default property. If your instance uses a LindormTable version earlier than 2.7.9, set the property to MUTABLE_LATEST.

  To write data with a custom timestamp, change the property to MUTABLE_UDT. This requires LindormTable 2.6.7 or later. For usage notes, see points four and five in the following note.

  Note

  • The IMMUTABLE and IMMUTABLE_ROWS types do not involve data updates, so there is no write amplification and the cost is lowest. They are suitable for high-throughput write scenarios, such as logging and monitoring. If you set the property to IMMUTABLE or IMMUTABLE_ROWS but still perform data updates, the server-side will not report an error, but this may cause data inconsistency between the primary data table and the index table.

  • The IMMUTABLE type does not involve deletions, so you can fully leverage Lindorm’s multi-IDC deployment to achieve active-active data access.

  • Choosing either IMMUTABLE property effectively reduces write latency and improves overall throughput in indexing scenarios. In practice, if your workload does not match the IMMUTABLE scenario, you can use data redundancy to adapt it to an IMMUTABLE scenario.

  • The mutability property takes effect when an index table is created. For LindormTable 2.7.9 or later, the default value is MUTABLE_LATEST. This property does not support writing data with a specified timestamp. Therefore, plan this property in advance based on whether you need to write data using a custom timestamp.

  • If you use a custom timestamp to write data but do not set the mutability property to MUTABLE_UDT or MUTABLE_ALL before creating an index, the service will be immediately affected and an error will occur after the index is created. For a solution, see FAQ.

• Update indexes with custom timestamps: Lindorm supports writing data with custom timestamps. You can update data at any timestamp, and the system applies only the data with the largest timestamp. This feature is critical for controlling data TTL and handling out-of-order or idempotent scenarios. It is widely used in HBase. Lindorm supports column-level timestamps, and primary tables support writing data with custom timestamps. However, among NoSQL systems that support both secondary indexes and timestamps, few support updating indexes with custom timestamps. This is because out-of-order timestamp writes make it difficult to maintain index data updates and deletions reliably. Lindorm global secondary indexes solve this problem and support column-level custom timestamp updates. The following are two real-world business scenarios that use custom timestamps.

  • Concurrent import and real-time updates: In scenarios requiring both real-time updates and historical data imports, use the current time for real-time updates and a time such as 23:59:59 of the previous day for historical imports. This ensures that data not yet updated on the current day can be updated by the import, while already-updated data remains unaffected.

  • Catching up on messages: A business system uses messages to trigger processing logic. When messages accumulate, the system can skip accumulated messages and process the current message directly. Later, it can catch up on previously accumulated tasks. Alternatively, if a bug affects business logic, the system can skip problematic messages and reprocess them after the fix. In such cases, the business can use each message’s own timestamp to write data. This ensures correct overwrite behavior between caught-up and normal messages.

• Full covering indexes: To avoid looking up the primary table after querying an index, 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 strategy. Lindorm supports three redundancy modes, enabling easy implementation of full covering indexes—even when the primary table schema changes or includes dynamic columns.

  • Columns for redundancy: Select the columns from the (primary table) to replicate.

  • Include all columns in the primary table schema: When you need a full covering index, you do not need to explicitly list each primary table column in the CREATE INDEX statement. Instead, use a constant to indicate inclusion of all columns. When a new column is added to the primary table, the full covering index table automatically includes it without requiring reindexing. You also do not need to worry about lookups to the primary table when querying the new column.

  • Include dynamic columns: Lindorm supports both fixed schemas and loose schemas (dynamic columns). With the DYNAMIC redundancy mode, the index table automatically includes all dynamic columns from the primary table, along with all columns defined in the primary table schema.

Create a secondary index (CREATE INDEX)

After you create a Lindorm primary table, you can create a secondary index on its columns. 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 based on the index table. Because an index is built on c3, specifying c3 in the query will hit the index table.
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 run a Rebuild operation on 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 state of the index table directly. The following example shows how to change the state of a secondary index:

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

Delete a secondary index (DROP INDEX)

Use the following example to delete a secondary index from its corresponding primary table.

DROP INDEX IF EXISTS idx1 ON test;

Query optimization

Lindorm selects a secondary index using Rule Based Optimization (RBO). It matches the prefix of the index table against the query conditions and selects the index table with the highest degree of match. The following example helps clarify 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 optimization is 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, each index created for the same primary table must have a unique name.

• You can create indexes only for tables that store a single version of data. Multi-version tables do not support indexes.

• Secondary indexes do not support cell-level TTL.

• If you create an index for a primary table with 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.

• For LindormTable 2.8.6 or later, an index can contain up to eight index columns. For earlier versions, an index can contain up to three index columns.

• The combined length of the index columns and the primary key of the primary table must not exceed 30 KB. Avoid using columns larger than 100 bytes as index columns.

• For LindormTable 2.8.6 or later, you can create up to 10 indexes for a single primary table. For earlier versions, you can create up to five indexes. Too many indexes increase storage costs and extend write times.

• A query can use at most one index. Index merge queries are not supported.

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

• Secondary indexes do not support the batch increase feature.

• The sorting order of a secondary index that is used in a query differs from that of the primary table.

• You can build secondary indexes only for data written using SQL or an API. You cannot build secondary indexes for data imported into Lindorm using Bulkload.

FAQ

For other issues related to indexes, join the Lindorm group in DingTalk or submit a ticket. For more information, see Technical support.