PolarDB:Create and use a CCI

Usage notes

• You can create and use a CCI only for a PolarDB-X instance that runs 5.4.19-16989811 or later.

• You can use the CREATE statement to create a CCI only on the primary instance of Enterprise Edition. You can execute only query statements such as SHOW INDEX on read-only instances.

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

  • You cannot use prefix indexes.

  • You must specify a name for the CCI.

  • By default, a CCI includes all columns in the primary table. You do not need to specify the columns. You cannot modify the columns.

  • Local indexes are not created when you create a CCI.

  • The length parameter specified for the sort key in the definition for the CCI does not take effect.

• Take note of the limits on data types and DDL operations. For more information, see the "Limits" section of this topic.

Syntax

PolarDB-X extends the syntax of DDL statements 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 based on which data is sorted in the index file.

• Index partitioning clause: the partitioning algorithm of the index whose syntax is the same as the syntax 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 a 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 a 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 create by using hash partitioning or key partitioning.
hash_partition_list:
    PARTITIONS partition_count

# Specify the ranges based on 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 based on 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]

Examples

# 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 t_order table is a partitioned table. 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 a CCI

After a 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})

  Example

  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},...)

  Example

  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},...)

  Example

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

Limits

Note

To create a CCI, make sure that the data types of the primary key, sort key, and partition key meet the requirements. For more information, see the "Limits on data types" section of this topic.

Only common DDL statements can be executed on a table on which a CCI is created. For more information, see the "Limits on DDL statements" section of this topic.

To change the data type of a column by using a DDL statement on a table on which a CCI is created, make sure that the new data type meets the requirements. For more information, see the "Limits on DDL statements" section of this topic.

DDL operation switch

You can use the following statement to specify whether to allow DDL operations on a table on which a CCI is created.

SET [GLOBAL] forbid_ddl_with_cci = [true | false]

Limits on data types

The following table describes the support of a CCI for the data types of the primary key, sort key, and partition key.

Limits on DDL statements

• ALTER TABLE

  Clause	Change the partition key of the primary table	Change the primary key	Change the index partition key	Change the sort key
  ADD COLUMN	N/A	No	N/A	N/A
  ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULT	No	No	No	No
  CHANGE COLUMN	No	No	No	No
  DROP COLUMN	No	No	No	No
  MODIFY COLUMN	No	No	No	No

  Note

  You cannot use the ALTER TABLE statement on a table on which a CCI is created to change the primary key, partition key of the primary table, index partition key, or sort key. You can use the statement to change other columns. You cannot change multiple columns in an ALTER TABLE statement, but you can separately execute multiple ALTER TABLE clauses by splitting clauses into individual statements.

• MODIFY/CHANGE COLUMN

  Supported data type	Unsupported data type
  Numeric: BIT (UNSIGNED), TINYINT (UNSIGNED), SMALLINT (UNSIGNED), MEDIUMINT (UNSIGNED), INT (UNSIGNED), and BIGINT (UNSIGNED) Date and time: DATETIME Floating-point: FLOAT,DOUBLE,DECIMAL, and NUMERIC String: CHAR and VARCHR	String: TEXT, BINARY, VARBINARY, and BLOB Date and time: TIMESTAMP, TIME, and YEAR Special: JSON, ENUM, SET, POINT, and GEOMETRY

  Note

  To use the ALTER TABLE CHANGE or MODIFY COLUMN statement to change the data type of a column to an unsupported data type, execute the DROP INDEX statement to delete the CCI, change the data type of the column, and then recreate the CCI.

• ALTER TABLE statements for changing indexes

  Statement	Supported
  ALTER TABLE ADD PRIMARY KEY	Yes
  ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	Yes
  ALTER TABLE DROP INDEX	Yes
  ALTER TABLE DROP FOREIGN KEY fk_symbol	Yes
  ALTER TABLE DROP PRIMARY KEY	No
  ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}	The CCI cannot be modified.
  ALTER TABLE {DISABLE | ENABLE} KEYS	The CCI cannot be modified.
  ALTER TABLE RENAME INDEX	The CCI cannot be modified.

  Note

  You cannot use the ENAME INDEX statement to rename the CCI. To rename a CCI, execute the DROP INDEX statement to delete the CCI and then recreate the CCL and specify a new name.

• Other DDL statements

  Statement	Supported
  DROP TABLE	Yes
  DROP INDEX	Yes
  RENAME TABLE	Yes
  ALTER TABLE RENAME	Yes
  TRUNCATE TABLE	No

  Note

  To ensure data consistency between the primary table and a CCI, the system does not allow you to remove all rows from the table by executing the TRUNCATE TABLE statement. To remove primary table data and CCI data from the table, use the DELETE statement.

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 CCI creation progress?

   You can execute the SHOW COLUMNAR INDEX statement to view the current CCI 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.