PolarDB:Create and use a CCI

Usage notes

• The version of your PolarDB-X instance is 5.4.19-16989811 or later.

• The CREATE syntax provided by the CCI feature is supported only by the primary instances of Enterprise Edition. Query statements such as SHOW INDEX can be executed only on read-only instances.

• When you create CCIs, take note of the following limits:

  • CCIs cannot be created on a single table or broadcast table.

  • Prefix indexes cannot be used.

  • An index name must be specified when the CCI is created.

  • By default, all columns in the primary table are involved in the CCI when the CCI is created and cannot be modified. You do not need to specify these columns.

  • No local indexes are created when the CCI is created.

  • In the index definition, the length parameter of the sort key is ignored.

• The following table describes limits related to the ALTER TABLE syntax.

  Statement	Change the partition key of the primary table	Change the primary key	Change the partition key of the CCI	Change index columns
  ADD COLUMN	N/A	Not supported	N/A	N/A
  ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULT	Not supported	Not supported	Not supported	Not supported
  CHANGE COLUMN	Not supported	Not supported	Not supported	Not supported
  DROP COLUMN	Not supported	Not supported	Not supported	Not supported
  MODIFY COLUMN	Not supported	Not supported	Not supported	Not supported

  Note

  To ensure the stability and performance of the CCI, you cannot execute the ALTER TABLE statement on the table for which the CCI is created to change columns in the table. To change a CCI, you can execute the DROP INDEX statement to delete the existing CCI and then create a new CCI.

• The following table describes the ALTER TABLE statement that you can execute to change the CCI.

  Statement	Change the CCI
  ALTER TABLE ADD PRIMARY KEY	Supported
  ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	Supported
  ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}	Not supported
  ALTER TABLE {DISABLE | ENABLE} KEYS	Not supported
  ALTER TABLE DROP PRIMARY KEY	Not supported
  ALTER TABLE DROP INDEX	Supported
  ALTER TABLE DROP FOREIGN KEY fk_symbol	Supported
  ALTER TABLE RENAME INDEX	Not supported

  Note

  You cannot execute the RENAME INDEX statement to rename the CCI. If you want to modify the name of the CCI, you can execute the DROP INDEX statement to delete the CCI and then create a CCI.

• The following table describes DDL statements that can be executed on the table for which the CCI is created.

  Statement	Execute the statement
  DROP TABLE	Supported
  DROP INDEX	Supported
  TRUNCATE TABLE	Not supported
  RENAME TABLE	Not supported
  ALTER TABLE RENAME	Not supported

  Note

  • To ensure data consistency between the primary table and the CCI, you cannot execute the TRUNCATE TABLE statement. You can execute the DELETE statement to delete data from the primary table and the CCI.

  • To ensure the stability and performance of the CCI, you cannot execute the RENAME TABLE or ALTER TABLE RENAME statement to rename the table for which the CCI is created. If you want to modify the name of the table, you can execute the DROP INDEX statement to delete the CCI. After the table name is modified, you can re-create a CCL.

Syntax

PolarDB-X extends the syntax of DDL statements in MySQL. In PolarDB-X, the following syntax defining CCIs is added. You can use the syntax in the same manner as you create an index in MySQL.

• CLUSTERED COLUMNAR: the keyword used to specify that the type of the index to be added is CCI.

• Index name: the name of the index table used to specify the index in the SQL statement.

• Sort key: the sort key of the index on which data is sorted in the index file.

• Index partitioning clause: the partitioning algorithm of the index whose syntax is the same as that of the partitioning clause in the CREATE TABLE statement.

CREATE
    CLUSTERED COLUMNAR INDEX index_name    
    ON tbl_name (index_sort_key_name,...)    
    [partition_options]

# Specify the partitioning strategy.
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE({column_name | partition_func(column_name)})
      	| RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)} }
    partition_list_spec

# Specify the partitioning function.
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# Specify the type of the partition list.
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

# Specify the number of partitions that you want to generate by hash partitioning or key partitioning.
hash_partition_list:
    PARTITIONS partition_count

# Specify the ranges by which data is partitioned in range partitioning or range columns partitioning.
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
    
# Specify the lists by which data is partitioned in list partitioning or list columns partitioning.
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

Example

# Create a table.
CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(`order_id`) partitions 16;

# Create a CCI.
CREATE CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16;

• Primary table: In the preceding example, the base table t_order is a partitioned table. The data is partitioned by using the hash algorithm. The order_id column is specified as the hashed partition key.

• CCI: The CCI cc_i_seller is sorted by the seller_id column. The order_id column is specified as the hashed partition key.

• Index definition clause: CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16.

Use the CCI

After the CCI is created, you can use one of the following methods to specify the index table available for a query:

• Use a hint to specify an index

  You can use the following hint to specify the index that you want to use for a query:

  FORCE INDEX({index_name})

  Examples

  SELECT a.*, b.order_id 
   FROM t_seller a 
     JOIN t_order b FORCE INDEX(cc_i_seller) ON a.seller_id = b.seller_id 
   WHERE a.seller_nick="abc";

• Select an index

  For a query on the primary table that includes the CCI, PolarDB-X automatically selects the index table with the lowest costs determined by the optimizer. Queries for CCIs can be run only on read-only nodes.

• Use the IGNORE INDEX or USE INDEX statement

• Use the following hint to specify the indexes that are not used by the optimizer.

  IGNORE INDEX({index_name},...)

  Examples

  SELECT t_order.id,t_order.order_snapshot FROM t_order IGNORE INDEX(cc_i_seller) WHERE t_order.seller_id = 's1';

  Use the following hint to specify the indexes that are used:

  USE INDEX({index_name},...)

  Examples

  SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(cc_i_seller) WHERE t_order.seller_id = 's1';

FAQ

1. Can I create a CCI without specifying the sort key (index_sort_key_name)?

   No. You must explicitly specify the sort key in the CREATE CLUSTERED COLUMNAR INDEX statement. The sort key and partition key can be different columns. For example, you can specify the seller_id column as the sort key and the order_id column as the partition key when you create the CCI.

2. Can I create a CCI without specifying the partition key?

   Yes. If you do not specify the partition key, the primary key serves as the partition key, and the hash partitioning strategy is selected.

3. How do I view the progress of creating a CCI?

   You can execute the SHOW COLUMNAR INDEX statement to view the current index status and the SHOW DDL statement to view the execution progress of the DDL task.

4. How do I delete an existing CCI?

   You can execute the DROP INDEX statement to delete an existing CCI.