Lindorm:Best practices for wide table design

Primary key columns

Columns in a row can be categorized into primary key columns and non-primary key columns. The primary key of a table can contain multiple columns and has the following characteristics:

• The schema of the primary key cannot be modified: The primary key of a Lindorm wide table is determined when you create the table. After the table is created, you cannot add, delete, or change the data types and order of the primary key columns. Therefore, design the primary key of a table carefully before you create the table. The primary key of a wide table significantly affects the performance of the table for request handling.

• The primary key is unique in a table: All primary key columns of a table form a unique row key in the table. Therefore, you can identify a unique row in a table based on a complete primary key.

• The primary key is used as a clustered index: In a Lindorm wide table, data is clustered based on the order of the primary key columns. For example, in the sample table orders, rows with the same value in the channel column are clustered. These rows are further clustered based on their values in the id column. Then, rows with the same value in the id column are clustered based on their values in the ts column.

  This way, the primary key is used as a clustered index for higher query efficiency. Lindorm preferentially matches a query to data from the leftmost primary key column, which is similar to MySQL. You can specify more equivalent conditions that match the primary key in a query to reduce the amount of queried data. You can specify the primary key column that most matches query conditions as the leftmost primary key column. If a range condition is specified for a primary key column in a query, the leftmost primary key is not preferentially matched even if other equivalent conditions are specified for other primary key columns. In this case, LindormTable needs to scan and filter a large amount of data to find the queried data.

  For example, you can execute the following statement to query data in the sample table orders:

  -- LindormTable scans all rows whose value in the channel column is alipay and whose value in the id column is larger than a0089 to locate the row whose value in the ts column equals to 1705786502068. 
  SELECT * FROM orders WHERE channel=="alipay" AND id > 'a0089' AND ts = 1705786502068;  

  If the leftmost primary key column is not specified in the query condition, LindormTable scans all rows in the table even if other primary key columns are specified in the condition. The following statement provides an example:

  -- LindormTable scans all rows in the table.
  SELECT * FROM orders WHERE id = 'a0089';

  In the preceding statement, a query condition is specified for the primary key column id, which is not the leftmost primary key column. Therefore, LindormTable scans all rows to locate the row whose value in the id column equals to a0089, which is inefficient. If most of the queries in your business contain conditions for the id column, we recommend that you specify the id column as the leftmost primary key column or create an individual index table for the id column to accelerate the queries.

Non-primary key columns

Lindorm allows you to dynamically define non-primary key columns for a table rather than defining them in the table schema. You can directly write data to a non-primary key column whether it exists in the table, which is similar to the usage of non-primary key columns in HBase. For more information, see Dynamic columns. In addition, you can use wildcards to define non-primary key columns. For example, if you execute a statement to write data to the *_str column whose data type is STRING, the data can be written to all STRING columns whose name ends with _str. For more information, see Wildcard columns.

Lindorm allows you to update data in non-primary key columns. When you write data to a table, you do not need to specify values for all non-primary key columns. However, you must specify a value for at least one non-primary key column because you cannot write a row of data that contains only the primary key. Data in a Lindorm wide table is stored based on the order of the primary key. If you specify a query condition for a non-primary key column on which no index is created, all data in the table is scanned to locate the queried data. By default, this kind of queries are rejected by Lindorm. Therefore, to query data in non-primary key columns more efficiently, you can specify a range condition for the primary key or create indexes for the non-primary key columns.

-- Specify conditions only for non-primary key columns. In this case, all data in the table is scanned.
SELECT * FROM table WHERE location = 'shanghai'; 

-- Specify conditions for primary key columns. In this case, only rows whose value in the channel column is alipay are scanned to locate the row whose value in the location column is shanghai.
SELECT * FROM table WHERE location = 'shanghai' and channel='alipay'; 

Pre-split regions

Lindorm supports automatic region splitting. Therefore, you do not need to specify the range of regions when you create a Lindorm wide table. LindormTable automatically splits regions for a table when data is written to the table. If you need to distribute data to specific regions in a table, you can specify the number of pre-split regions when you create the table. LindormTable stores the specified regions across multiple nodes to distribute read and write requests. Note that the number of regions that you specify when you create a table is only the number of initial regions. LindormTable further splits the initial regions when data is written to the table. For more information about how to specify pre-split regions when you create a table, see CREATE TABLE.

If you want to write large amounts of data or use Bulkload to import data to a table immediately after it is created, we recommend that you specify pre-split regions based on the required data distribution to prevent a single node from being overloaded.

• If you plan to use SQL or the HBase API to write data, you can set the number of pre-split regions to Number of nodes x 4 when you create a table. The optimized number of initial regions varies with scenarios. We recommend that you set the number of pre-split regions based on your business requirements.

• If you use Bulkload to import data to a table, we recommend that you set the number of pre-split regions to Size of the data to import / 8. This way, the imported data can be distributed to each region and does not trigger the splitting of these regions.

In addition, make sure that the ranges of pre-split regions conforms to the data writing pattern. Otherwise, the written data may be clustered only in specific regions. In this case, the performance of the table may be degraded even if you specify a large number of pre-split regions.

Hotspot data

A distributed system is designed to evenly distribute requests to each region. This way, the system can be horizontally scaled to handle more requests with more data. When only a piece of data in a system is frequently accessed, the data becomes hotspot data. In this case, the performance of the node on which the hotspot data is stored bottlenecks the performance of the entire distributed system.

• Single-row hotspot data: When a single row of data is frequently read or written, this row of data becomes single-row hotspot data. Requests for the same row are always distributed to the same node. Therefore, the performance of this node bottlenecks the entire system. In this case, you can only resolve this issue by scaling up the specification of the node but not scale out the number of nodes in the system. To avoid single-row hotspot data in a wide table, you must design an appropriate schema or primary key for the table when you create the table.

• Ranged hotspot data: When data within a small range is frequently read or written, the data becomes ranged hotspot data. A Lindorm wide table is partitioned based on the ranges of its primary key. Therefore, data within a small range may be stored in the same region. Therefore, requests for the data within the range are distributed to the same node, which bottlenecks the performance of the system. Lindorm can identify hotspot data within a small range and automatically distribute the hotspot data to different nodes by splitting regions. However, we recommend that you design a more distributed primary key for the table to avoid ranged hotspot data. For example, in the sample table orders, you can use the HASH function to convert the values in the id column to hashes and use this column as the primary key to avoid ranged hotspot data.

• Hotspot written data caused by incremental primary key: Hotspot written data may exist if the primary key of a table is incremental. A Lindorm wide table is partitioned based on the ranges of its primary key. Therefore, even if a region is split into sub-regions, the data that is subsequently written to the region is stored only in the lower part of the sub-region. For example, in the sample table orders, the primary key column id is incremental, and a large number of data records that meet the channel=alipay condition is written to the table. In this case, even if Region_3 is split into Region_3_a and Region_3_b, data is only written to Region_3_b. Hotspot written data caused by incremental primary key cannot be completely resolved in Lindorm due to the system architecture. Therefore, we recommend that you do not specify an incremental column as the first primary key column and avoid including incremental columns in the primary key.

  Important

  Some databases or systems, such as Cassandra, use a column that contains the hash values of the incremental primary key column as the partition key. This way, the values of the table can be evenly distributed to different partitions. However, if you migrate data from one of the databases or systems to Lindorm, the incremental primary key column incurs hotspot written data.

Single-value query and range query

Two types of queries are commonly performed on Lindorm wide tables: single-value queries and range queries.

SELECT * FROM orders WHERE channel='alipay' AND id='a0001' AND ts=1705786502000;
SELECT * FROM orders WHERE channel='alipay' AND id='a0001' AND ts IN (1705786502000, 1705786502222, 1705786502333);
SELECT * FROM orders WHERE channel='alipay' AND id IN ('a0001', 'a0002', 'a0003') AND ts IN (1705786502000, 1705786502222, 1705786502333);
SELECT * FROM orders WHERE channel IN ('alipay', 'wechat', 'unionpay') AND id IN ('a0001', 'a0002', 'a0003') AND ts IN (1705786502000, 1705786502222, 1705786502333);

SELECT * FROM orders;
SELECT * FROM orders WHERE channel='alipay';
SELECT * FROM orders WHERE channel='alipay' AND id='1705786502001';
SELECT * FROM orders WHERE channel='alipay' AND id IN ('a0001', 'a0002', 'a0003');
SELECT * FROM orders WHERE channel IN ('alipay', 'wechat', 'unionpay') AND id IN ('a0001', 'a0002', 'a0003');

ORDER BY query

Data in a Lindorm wide table is sorted by the primary key. Therefore, the query results are returned in the order of the primary key even if you do not specify an ORDER BY clause in the SELECT statement. However, if the conditions in a query hit an index table, the results are returned in the order of the index table, which is the order of column for which the index table is created.

If you specify multiple columns in the ORDER BY clause, the result is sorted by the columns in a left-to-right order. To efficiently use the index, you must include the leftmost primary key column in the ORDER BY clause. If you specify only non-leftmost primary key columns or non-primary key columns in the ORDER BY clause, a large number of calculation operations are performed to sort the returned results. Therefore, we recommend that you use the leftmost primary key column to sort the result or create a secondary index for the non-primary key column that you want to use to sort the result. In addition, if you want to sort the query results in descending order (ORDER BY DESC), we recommend that you add the DESC keyword to the statement used to create the table. This way, data is stored in descending order and provides optimized query performance. For more information about how to use the ORDER BY clause, see Use ORDER BY in large result sets.

COUNT query

Lindorm is a NoSQL database service built based on the LSM-Tree storage structure. In this structure, multiple versions of data and delete markers are stored because data may be modified or deleted for multiple times. Therefore, the underlying storage of Lindorm does not store the number of rows in each table in the table metadata. To accurately query the number of rows in a table, you must scan all data in the table. Therefore, it takes a long period of time to query the number of rows in a table that contains large amounts of data. If you need to obtain only the estimated number of rows in a table, see Count the number of rows in a table.

If conditions are specified in a COUNT query, the duration of the query depends on the amount of data that needs to be scanned under the specified conditions. For example, if you execute the SELECT count(*) FROM table WHERE channel = 'alipay'; statement, the number of rows whose value in the channel column is alipay are counted.