PolarDB-X supports clustered indexes. PolarDB-X automatically manages the covering columns in global secondary indexes and synchronizes data from base tables to clustered indexes. This way, requested data can be queried from the index table without the need to scan the base table. This can help reduce resource overheads. This topic describes how to create and use a clustered index.

Prerequisites

The kernel version of your PolarDB-X instance is V5.4.9 or later.

Note

  • A clustered index is a special type of global secondary index. For more information about the operations and limits on clustered indexes, see GSI.
  • By default, the covering columns in the clustered index of a table include all columns in the table. After you update the columns in the table, the modification is automatically synchronized to the clustered index table. This ensures that data is consistent between the base table and the clustered index table.
  • Local indexes of the base table are also synchronized to the clustered index table.

Syntax

When you create a table or an index for a table, you can add the CLUSTERED keyword to the clause that is used to create an index. This way, the created index is the clustered index of the table.

  • The following sample code shows how to use the CREATE TABLE statement to create a table and a clustered index for the table:
    CREATE [SHADOW] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [drds_partition_options]

create_definition:
    [UNIQUE] CLUSTERED INDEX index_name [index_type] (index_col_name,...)
      [drds_partition_options] 
      [index_option] ...
    Note If the table that you want to create uses the primary key as the shard key, you do not need to specify a sharding rule in the [drds_partition_options] clause.
  • The following sample code shows how to use the CREATE INDEX statement to create a clustered index for a table:
    CREATE [UNIQUE]
    CLUSTERED INDEX index_name [index_type]
    ON tbl_name (index_col_name,...)
    [drds_partition_options]
    [index_option] ...
    Note If the table for which you want to create an index uses the primary key as the shard key, you do not need to specify a sharding rule in the [drds_partition_options] clause.
  • The following sample code shows how to use the ALTER TABLE statement to create a clustered index for a table:
    ALTER TABLE tbl_name
    alter_specification
    The following sample code shows how to specify a alter_specification clause:
    alter_specification:
  | ADD [UNIQUE] CLUSTERED {INDEX|KEY} index_name 
      [index_type] (index_col_name,...)
      [drds_partition_options] 
      [index_option] ...
    Note
    • You can specify only one sharding rule in the alter_specification clause to modify the clustered index of a table.
    • You must specify a name for the clustered index.
    • If the table that you want to modify uses the primary key as the shard key, you do not need to specify a sharding rule in the [drds_partition_options] clause.

Examples

You can use the following statement to create a table named t_order in a PolarDB-X database: 

CREATE PARTITION TABLE `t_order` (
    ->   `t` timestamp null default CURRENT_TIMESTAMP,
    ->   `x` int default 3,
    ->   `order_id` varchar(20) DEFAULT NULL,
    ->   `seller_id` varchar(20) DEFAULT NULL
    -> );

You can use the following statement to create a clustered index for the t_order table: 

CREATE CLUSTERED INDEX `c_i` ON `t_order` (seller_id, x)

After the clustered index is created, you can use the following statement to query the schema of the base table and check the information about the clustered index: 

SHOW CREATE TABLE t_order;

In this example, the following information is returned.

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE PARTITION TABLE `t_order` (
  `t` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `x` int(11) DEFAULT '3',
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  LOCAL KEY `_local_c_i` (`seller_id`, `x`),
  CLUSTERED INDEX `c_i`(`seller_id`, `x`) DBPARTITION BY HASH(`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

You can use the following statement to query the schema of the clustered index:

SHOW CREATE TABLE c_i;

The returned information shows that the clustered index contains all columns of the base table. 

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| c_i   | CREATE TABLE `c_i` (
  `t` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `x` int(11) DEFAULT '3',
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  KEY `auto_shard_key_seller_id` USING BTREE (`seller_id`),
  KEY `i_seller_id_x` USING BTREE (`seller_id`, `x`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`seller_id`) |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)