Lindorm:Partition index

Before you use the partition index feature, you must execute the following statement to create a test table:

CREATE TABLE IF NOT EXISTS search_table (user_id bigint, storeId varchar, goodsId varchar, goodsPrice smallint, orderTime bigint, info varchar, constraint primary key (user_id asc));

The following sample code provides examples on how to perform hash partitioning:

• Create a search index and perform hash partitioning. By default, the hash partitioning is performed based on the primary key of the wide table and the number of partitions is two times the number of search nodes.

  CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice);

• Create a search index, set the number of partitions to 64, and perform hash partitioning based on the storeId column of the wide table. In this case, first-level hash partitioning is performed.

  CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice) partition by hash(storeId) partitions 64;

  Important

  • If a column is used as a condition in most of your queries, you can specify the column as a custom partition key.

  • In scenarios where custom partition keys are used, you can set partitions to a larger value based on your requirements. If you are not sure about how to configure the number of partitions, you can set it to 64 for a test.

  • If you perform hash partitioning based on one column of a wide table and the column that is specified as the partition key has frequently accessed data, a large volume of data can be written to a partition. For example, 10% of data in the table can be queried based on the storeId column. This reduces the query performance and the write performance. In this case, first-level hash partitioning is performed. We recommend that you perform multi-level hash partitioning based on two or three columns in a wide table. In this case, second-level hash partitioning or third-level hash partitioning is performed. For more information, see Multi-level hash partitioning (advanced usage).

  • Limits on custom partition keys:
    • Partition key values cannot be changed.
    • Partition key values cannot be empty.

The following sample code provides examples on how to perform time range partitioning on time series data:

• Create an index, specify that the first partition is created based on 30 days before the point in time when you create the index, set the interval at which a new partition is created to 7 days, set the retention period of your data in a partition to 90 days, and perform time range partitioning by the orderTime column.

  CREATE INDEX idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice, orderTime)
  partition by range time(orderTime) partitions 4
  with (indexState=ACTIVE, RANGE_TIME_PARTITION_START='30', RANGE_TIME_PARTITION_INTERVAL='7', RANGE_TIME_PARTITION_TTL='90');

• Create an index, specify that the first partition is created based on 180 days before the point in time when you create the index, set the interval at which a new partition is created to 30 days, set the retention period of your data in a partition to 180 days, set the unit of values in a partition key column to seconds, and perform time range partitioning by the orderTime column.

  CREATE INDEX idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice, orderTime)
  partition by range time(orderTime) partitions 4 
  with (indexState=ACTIVE, RANGE_TIME_PARTITION_START='180', RANGE_TIME_PARTITION_INTERVAL='30', RANGE_TIME_PARTITION_TTL='180', RANGE_TIME_PARTITION_FIELD_TIMEUNIT='s');

The following table describes the parameters that are used in the preceding code.