创建和使用CCI

注意事项

• 实例版本需为5.4.19-16989811及以上。

• 仅企业版的主实例支持列存索引的CREATE语法，在只读实例上仅支持SHOW INDEX等查询命令。

• 创建CCI相关的约束

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

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

  • 创建CCI默认包含主表上的所有列，不需要额外指定，不支持调整。

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

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

• 目前的版本中，CCI数据类型和主表的DDL具有一定限制，具体可参见使用限制。

语法

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

• CLUSTERED COLUMNAR：关键字，用于指定添加的索引类型为CCI。

• 索引名：索引表的名称，用于在SQL语句中指定该索引。

• 排序键：索引的排序键，即数据在索引文件中按照该列有序存储。

• 索引分区子句：索引的分区算法，与CREATE TABLE中分区子句的语法一致。

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]

示例

# 先创建表
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;

• 主表："t_order" 是分区表，分区的拆分方式为按照 "order_id" 列进行哈希。

• 列存索引："cc_i_seller" 按照 "seller_id" 列进行排序，按照 "order_id" 列进行哈希。

• 索引定义子句：CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16。

使用CCI

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

• 通过HINT指定索引

  您可以选择以下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";

• 索引选择

  对于带有列存索引的主表查询，PolarDB-X会自动选择出优化器认为代价最低的索引表（目前仅支持在只读实例上查询列存索引）。

• IGNORE INDEX与USE INDEX

• 通过以下HINT可以指定优化器不使用某些索引。

  IGNORE INDEX({index_name},...)

  示例：

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

  通过以下HINT可以指定使用某索引。

  USE INDEX({index_name},...)

  示例：

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

使用限制

说明

创建CCI时，主键、排序键和分区键的数据类型具有一定限制。

在包含CCI的表中，执行DDL具有一定限制，目前仅支持常见的DDL语句。

执行DDL变更列数据类型时，支持有限的数据类型。

开关

您可以通过下面的参数控制是否允许在包含 CCI 的表中执行 DDL，参数默认关闭。

SET [GLOBAL] forbid_ddl_with_cci = [true | false]

数据类型限制

CCI中不同列数据类型的支持情况。

DDL语句限制

• ALTER TABLE 相关约束

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

  说明

  目前支持在包含CCI的表上执行列变更相关的ALTER TABLE语句，不支持对主键、主表/索引分区键、排序键的变更，支持对其他列的变更。暂不支持在ALTER TABLE中变更多个列，您可以将多个ALTER TABLE子句拆分后执行。

• 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。

  说明

  通过ALTER TABLE CHANGE/MODIFY COLUMN 变更列类型时，具有一定的限制。如果遇到不支持的类型，您可以先使用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 ALTER INDEX index_name {VISIBLE | INVISIBLE}	不支持修改CCI
  ALTER TABLE {DISABLE | ENABLE} KEYS	不支持修改CCI
  ALTER TABLE RENAME INDEX	不支持修改CCI

  说明

  目前禁止直接使用RENAME INDEX命令重命名列存索引。如需修改列存索引名，您可以先使用DROP INDEX删除对应的列存索引，再重新创建一个新的列存索引。

• 包含CCI的表上执行其他DDL时的约束

  语句	是否支持
  DROP TABLE	支持
  DROP INDEX	支持
  RENAME TABLE	支持
  ALTER TABLE RENAME	支持
  TRUNCATE TABLE	不支持

  说明

  考虑主表与列存索引的数据一致性，目前禁止执行TRUNCATE TABLE语句。如需清空主表与列存索引数据，您可以使用DELETE语句删除对应的数据。

常见问题

1. 创建列存索引时能否不指定排序键（index_sort_key_name）？

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

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

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

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

   答：可以通过SHOW COLUMNAR INDEX查看列存索引当前的状态，通过SHOW DDL查看DDL任务的执行进度。

4. 如何删除列存索引？

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