All Products
Search

This Product

    PolarDB:Create and use a CCI

    Document Center

    PolarDB:Create and use a CCI

    Last Updated:Dec 16, 2024

    This topic describes how to create and use a clustered columnar index (CCI).

    Supported versions

    The version of the PolarDB-X instance must be 5.4.19-16989811 or later. For more information about the versions of PolarDB-X instances, see Release notes and View and update the version of an instance.

    Usage notes

    • You can create a CCI only on a PolarDB-X 2.0 Enterprise Edition primary instance. For more information, see Guidelines for creating CCIs.

    • 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 of the primary table and automatically tracks and captures all changes made to the columns of the primary table. You cannot manually modify the columns in a CCI.

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

      • The LENGTH parameter for the sort key in the definition for a CCI does not take effect.

    • Primary instances, read-only instances, and columnar read-only instances support query statements, such as SHOW COLUMNAR INDEX, SHOW COLUMNAR OFFSET, and SHOW COLUMNAR STATUS.

    • For information about the limits on performing DDL operations on a table in which a CCI is created, see the "Limits" section of this topic.

    Syntax

    PolarDB-X extends the syntax of DDL statements in MySQL. The following syntax used to define a CCI is added. You can use the syntax in the same manner as you create an index in MySQL.

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

# Syntax that defines partitioning options.
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

# Syntax that defines partitioning functions.
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# Syntax that defines partitioning methods.
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

# Syntax that defines the hash/key partitioning method.
hash_partition_list:
    PARTITIONS partition_count

# Syntax that defines the range/range columns partitioning method.
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
    
# Syntax that defines the list or list columns partitioning method.
list_partition_list:
    list_partition [, list_partition ...]

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

    Example

    Execute the following statements to create a table named t_order and create a CCI named cc_i_seller in the t_order 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 CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16;

    The syntax used to create the cc_i_seller CCI in the t_order table:

    image
    Note

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

    • Primary table: the name of the primary table. In this example, the t_order table is the primary table. The table is partitioned by using the hash algorithm based on the order_id column.

    • CCI name: the name of the CCI. You can specify a CCI by its name in SQL statements.

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

    • Partitioning clause: the CCI partitioning clause whose syntax is the same as the syntax of the partitioning clause in the CREATE TABLE statement. The order_id column is a hash column.

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

    Use a CCI in queries

    After a CCI is created, you can specify the CCI for queries.

    Specify a CCI by using a hint

    Syntax:

    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";

    Ignore and use a specific CCI

    Note

    For queries on multiple tables in which CCIs are created, PolarDB-X automatically selects the CCI of the table with the lowest cost. Only columnar read-only instances support CCI-based queries.

    Ignore a specific CCI (IGNORE INDEX)

    Syntax:

    IGNORE INDEX({index_name},...)

    The following statement specifies that the cc_i_seller CCI is not used.

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

    Use a specific CCI (USE INDEX)

    Syntax:

    USE INDEX({index_name},...)

    The following statement specifies that the cc_i_seller CCI is used.

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

    Limits

    Note

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

    • To create a CCI in a table, 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 in 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 in a table in which a CCI is created, make sure that you change the column to a supported data type. For more information, see the "Limits on DDL statements" section of this topic.

    Limits on data types

    Data type

    Primary key

    Sort key

    Partition key

    Integer

    BIT (UNSIGNED)

    Yes

    Yes

    No

    TINYINT (UNSIGNED)

    Yes

    Yes

    Yes

    SMALLINT (UNSIGNED)

    Yes

    Yes

    Yes

    MEDIUMINT (UNSIGNED)

    Yes

    Yes

    Yes

    INT (UNSIGNED)

    Yes

    Yes

    Yes

    BIGINT (UNSIGNED)

    Yes

    Yes

    Yes

    Date and time

    DATE

    Yes

    Yes

    Yes

    DATETIME

    Yes

    Yes

    Yes

    TIMESTAMP

    Yes

    Yes

    Yes

    TIME

    Yes

    Yes

    No

    YEAR

    Yes

    Yes

    No

    String

    CHAR

    Yes

    Yes

    Yes

    VARCHAR

    Yes

    Yes

    Yes

    TEXT

    Yes

    Yes

    No

    BINARY

    Yes

    Yes

    Yes

    VARBINARY

    Yes

    Yes

    Yes

    BLOB

    Yes

    Yes

    No

    Floating-point number

    FLOAT

    No

    No

    No

    DOUBLE

    No

    No

    No

    DECIMAL

    No

    No

    No

    NUMERIC

    No

    No

    No

    Special type

    JSON

    No

    No

    No

    ENUM

    No

    No

    No

    SET

    No

    No

    No

    POINT

    No

    No

    No

    GEOMETRY

    No

    No

    No

    Note

    Partitioning algorithms support different data types. For more information, see the "Supported data types" section of the CREATE TABLE (Auto mode) topic.

    Limits on DDL statements

    Note

    You can execute the following statement to specify whether to allow the execution of DDL statements on a table in which a CCI is created:

    SET [GLOBAL] forbid_ddl_with_cci = [true | false];

    • The following table describes whether DDL statements are supported for tables in which a CCI is created.

      Category

      Operation

      Sample SQL statements

      Supported

      Table operation

      Delete a table

      DROP TABLE tbl_name;

      Yes

      Clear a table

      TRUNCATE TABLE tbl_name;

      Yes

      Rename a table

      • ALTER TABLE old_tbl_name RENAME TO new_tbl_name;

      • RENAME TABLE old_tbl_name TO new_tbl_name;

      Yes

      Rename multiple tables

      RENAME TABLE tbl_name_a to tbl_name_b, tbl_name_c to tbl_name_d;

      Yes

      Add a column to a table

      ALTER TABLE tbl_name ADD col_name TYPE;

      Yes

      Delete a column

      ALTER TABLE tbl_name DROP COLUMN col_name;

      Yes

      Change the data type of a column

      ALTER TABLE tbl_name MODIFY col_name TYPE;

      Yes

      Rename (modify) a column

      ALTER TABLE tbl_name CHANGE old_col new_col TYPE;

      Yes

      Change the default value of a column

      • ALTER TABLE tbl_name ALTER COLUMN col_name SET DEFAULT default_value;

      • ALTER TABLE tbl_name ALTER COLUMN col_name DROP DEFAULT;

      Yes

      Change the data type of a column without locking tables

      ALTER TABLE tbl_name MODIFY col_name TYPE, ALGORITHM = omc;

      Yes

      Perform multiple operations by using the ALTER TABLE statement

      ALTER TABLE tbl_name MODIFY col_name_a, DROP COLUMN col_name_b;

      Yes

      Create columns

      -

      No

      Modify partitions

      -

      No

      CCI operation

      Create a CCI

      • CREATE CLUSTERED COLUMNAR INDEX cci_name;

      • ALTER TABLE tbl_name ADD CLUSTERED COLUMNAR INDEX cci_name;

      Yes

      Delete a CCI

      • DROP INDEX cci_name ON TABLE tbl_name;

      • ALTER TABLE tbl_name DROP INDEX cci_name;

      Yes

      Rename a CCI

      ALTER TABLE tbl_name RENAME INDEX cci_name_a TO cci_name_b;

      Yes

      Add a partition to a CCI

      ALTER TABLE `tbl_name`.`cci_name` ADD PARTITION;

      Yes

      Other changes to CCI partitions

      -

      No

    • When you use the ALTER TABLE statement to change columns in a table in which a CCI is created, take note of the limits. The following table describes the limits.

      Clause

      Change the primary key

      Change the partition key of the CCI

      Change the sort key

      ADD COLUMN

      No

      N/A

      N/A

      ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULT

      No

      No

      No

      CHANGE COLUMN

      No

      No

      No

      DROP COLUMN

      No

      No

      No

      MODIFY COLUMN

      No

      No

      No

      Note

      You can use the ADD COLUMN, ALTER COLUMN SET DEFAULT, ALTER COLUMN DROP DEFAULT, CHANGE COLUMN, DROP COLUMN, and MODIFY COLUMN statements to manage all columns except the primary key column, the partition key column of the primary table or CCI, and the sort key column in a table in which a CCI is created.

    • To use the MODIFY/CHANGE COLUMN statement to change the data type of a column in a table in which a CCI is created, make sure you change the column to a supported data type.

      Supported data type

      Unsupported data type

      • Integer: BIT (UNSIGNED), TINYINT (UNSIGNED), SMALLINT (UNSIGNED), MEDIUMINT (UNSIGNED), INT (UNSIGNED), and BIGINT (UNSIGNED)

      • Date and time: DATETIME

      • Floating-point number: FLOAT, DOUBLE, DECIMAL, and NUMERIC

      • String: CHAR and VARCHR

      • String: TEXT, BINARY, VARBINARY, and BLOB

      • Date and time: TIMESTAMP, TIME, and YEAR

      • Specify type: JSON, ENUM, SET, POINT, and GEOMETRY

      Note

      If you want to change a column to an unsupported data type, you can use the DROP INDEX statement to delete the CCI, change the data type of the column, and then recreate the CCI.

    • The following table describes whether ALTER TABLE statements can be used to change indexes in a table in which a CCI is created.

      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 RENAME INDEX

      Can modify the name of CCIs.

      ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}

      Cannot modify CCIs.

      ALTER TABLE {DISABLE | ENABLE} KEYS

      Cannot modify CCIs.

    FAQ

    • Can I create a CCI without specifying the sort key?

      No. You must explicitly specify the sort key when you use the CREATE CLUSTERED COLUMNAR INDEX statement to create a CCI. The sort key and partition key can be different columns. Example: When you create a CCI in the t_order table, you can specify the seller_id column as the sort key and the order_id column as the partition key.

    • Can I create a CCI without specifying the partition key?

      Yes. If you do not specify the partition key, the primary key functions as the partition key and the hash partitioning method is used.

    • How do I view the CCI creation progress?

      You can use DDL statements to view the current status of a CCI and the progress of a DDL task. For more information, see SHOW COLUMNAR INDEX and SHOW DDL.

    • How do I delete an existing CCI?

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