云原生数据库 PolarDB：创建和使用CCI

版本限制

实例版本需为5.4.19-16989811及以上。关于版本号的更多信息，请参见版本发布说明和查看实例版本。

注意事项

• 仅PolarDB-X 2.0企业版的主实例上支持创建列存索引。更多信息，请参见列存索引创建指南。

• 创建CCI时的相关约束：

  • 不支持通过任何方式使用前缀索引。

  • 创建CCI时必须指定索引名。

  • 创建CCI默认包含主表的所有列，且创建后会随着主表的列调整而自动调整，不支持手动调整列。

  • 创建CCI不会额外创建任何局部索引。

  • 索引定义中，排序键的LENGTH参数将被忽略。

• 主实例、只读实例、列存只读实例都支持SHOW INDEX等查询命令。更多信息，请参见SHOW COLUMNAR INDEX、SHOW COLUMNAR OFFSET、SHOW COLUMNAR STATUS。

• DDL相关限制，请参见使用限制。

语法

PolarDB-X对MySQL DDL的语法进行了扩展，增加定义CCI的语法，使用方式与在MySQL上创建索引一致，其语法如下所示：

CREATE
    CLUSTERED COLUMNAR INDEX index_name    
    ON tbl_name (index_sort_key_name,...)    
    [partition_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

# 分区函数定义
partition_func：
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# 分区列表定义
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

# Hash/Key分区表列定义
hash_partition_list:
    PARTITIONS partition_count

# Range/Range Columns分区表列定义
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
    
# List/List Columns分区表列定义
list_partition_list:
    list_partition [, list_partition ...]

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

示例

执行如下代码创建表t_order，再基于t_order创建列存索引cc_i_seller：

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;

创建列存索引代码详解如下：

如何在查询中使用CCI

CCI创建完成后，可以通过如下方式指定查询使用的索引表：

通过HINT指定索引的方式

语法：

FORCE INDEX({index_name})

示例：

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

语法：

IGNORE INDEX({index_name},...)

以下代码可以指定优化器不使用索引cc_i_seller：

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

使用指定索引（USE INDEX）

语法：

USE INDEX({index_name},...)

以下代码可以指定优化器使用索引cc_i_seller：

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

使用限制

数据类型限制

DDL语句限制

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

• 含CCI的主表及CCI本身的DDL支持情况如下：

  操作类别	操作名称	示例SQL	支持情况
  主表	删除表	DROP TABLE tbl_name;	支持
  	清空表	TRUNCATE TABLE tbl_name;	支持
  	重命名表	ALTER TABLE old_tbl_name RENAME TO new_tbl_name; RENAME TABLE old_tbl_name TO new_tbl_name;	支持
  	重命名多个表	RENAME TABLE tbl_name_a to tbl_name_b, tbl_name_c to tbl_name_d;	支持
  	添加列	ALTER TABLE tbl_name ADD col_name TYPE;	支持
  	删除列	ALTER TABLE tbl_name DROP COLUMN col_name;	支持
  	修改列类型	ALTER TABLE tbl_name MODIFY col_name TYPE;	支持
  	重命名（修改）列	ALTER TABLE tbl_name CHANGE old_col new_col TYPE;	支持
  	修改列默认值	ALTER TABLE tbl_name ALTER COLUMN col_name SET DEFAULT default_value; ALTER TABLE tbl_name ALTER COLUMN col_name DROP DEFAULT;	支持
  	无锁变更列类型	ALTER TABLE tbl_name MODIFY col_name TYPE, ALGORITHM = omc;	支持
  	ALTER TABLE多重操作	ALTER TABLE tbl_name MODIFY col_name_a, DROP COLUMN col_name_b;	支持
  	生成列	-	不支持
  	分区变更	-	不支持
  列存索引（CCI）	新建CCI	CREATE CLUSTERED COLUMNAR INDEX cci_name; ALTER TABLE tbl_name ADD CLUSTERED COLUMNAR INDEX cci_name;	支持
  	删除CCI	DROP INDEX cci_name ON TABLE tbl_name; ALTER TABLE tbl_name DROP INDEX cci_name;	支持
  	更改CCI名称	ALTER TABLE tbl_name RENAME INDEX cci_name_a TO cci_name_b;	支持
  	增加CCI Range分区	ALTER TABLE `tbl_name`.`cci_name` ADD PARTITION;	支持
  	其他CCI分区变更	-	不支持

• ALTER TABLE变更列时，相关约束如下：

  语句	是否支持变更主键	是否支持变更索引分区键	是否支持变更排序键
  ADD COLUMN	不支持	不涉及	不涉及
  ALTER COLUMN SET DEFAULT、ALTER COLUMN DROP DEFAULT	不支持	不支持	不支持
  CHANGE COLUMN	不支持	不支持	不支持
  DROP COLUMN	不支持	不支持	不支持
  MODIFY COLUMN	不支持	不支持	不支持

  说明

  除了主键列、主表/索引分区键列、排序键列这些列之外，其他列都支持ADD COLUMN、ALTER COLUMN SET DEFAULT、ALTER COLUMN DROP DEFAULT、CHANGE COLUMN、DROP COLUMN、MODIFY COLUMN操作。

• MODIFY/CHANGE COLUMN语句的类型限制如下：

  支持类型	不支持类型
  数值类型：BIT (UNSIGNED)，TINYINT (UNSIGNED)，SMALLINT (UNSIGNED)，MEDIUMINT (UNSIGNED)，INT (UNSIGNED)，BIGINT (UNSIGNED) 时间类型：DATETIME 浮点数类型：FLOAT，DOUBLE，DECIMAL、NUMERIC 字符串类型：CHAR，VARCHAR	字符串类型：TEXT，BINARY，VARBINARY，BLOB 时间类型：TIMESTAMP、TIME、YEAR 特殊类型：JSON、ENUM、SET、POINT、GEOMETRY

  说明

  如果遇到不支持的类型，您可以先使用DROP INDEX删除列存索引，变更列类型后再重新创建列存索引。

• ALTER TABLE语句变更索引的支持情况如下：

  语句	是否支持
  ALTER TABLE ADD PRIMARY KEY	支持
  ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	支持
  ALTER TABLE DROP INDEX	支持
  ALTER TABLE DROP FOREIGN KEY fk_symbol	支持
  ALTER TABLE DROP PRIMARY KEY	禁止
  ALTER TABLE RENAME INDEX	支持修改CCI名称
  ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}	不支持修改CCI
  ALTER TABLE {DISABLE | ENABLE} KEYS	不支持修改CCI

常见问题

• 创建列存索引时能否不指定排序键？

  答：不能。必须在CREATE CLUSTERED COLUMNAR INDEX语句中显式指定排序键。排序键与分区键可以是完全不同的列。例如在t_order表上创建CCI时，可以指定seller_id作为排序键，order_id作为分区键。

• 创建列存索引时能否不指定分区键？

  答：可以。如果没有指定分区键，则默认选择主键作为分区键，并选择HASH作为分区策略。

• 如何查看创建列存索引的进度？

  答：可以通过DDL管理语句查看列存索引当前的状态和DDL任务的执行进度。详细信息，请参见SHOW COLUMNAR INDEX和SHOW DDL。

• 如何删除列存索引？

  答：可以通过DROP INDEX删除已经成功创建的列存索引。