创建和使用CCI_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文将介绍如何创建并使用列存索引功能。

注意事项

实例版本需为5.4.19-16989811及以上。
仅企业版的主实例支持列存索引的CREATE语法，在只读实例上仅支持SHOW INDEX等查询命令。

创建CCI相关的约束
- 不支持通过任何方式使用前缀索引。
- 创建CCI时必须指定索引名。
- 创建CCI默认包含主表上的所有列，不需要额外指定，不支持调整。
- 创建CCI不会额外创建任何局部索引。
- 索引定义中，排序键的length参数将被忽略。

ALTER TABLE相关的约束

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

说明

考虑到列存索引的稳定性和性能情况，目前不支持在包含CCI的表上执行列变更相关的ALTER TABLE语句。您可以先使用DROP INDEX删除对应的列存索引，完成DDL变更后，再重新创建一个新的列存索引。

下表汇总了使用ALTER TABLE语句变更索引的支持情况

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

说明

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

在包含CCI的表上使用其他DDL时的约束
语句
是否支持
DROP TABLE
支持
DROP INDEX
支持
TRUNCATE TABLE
不支持
RENAME TABLE
不支持
ALTER TABLE RENAME
不支持
说明
- 考虑主表与列存索引的数据一致性，目前禁止执行TRUNCATE TABLE语句。如需清空主表与列存索引数据，您可以使用DELETE语句删除对应的数据。
- 考虑到列存索引的稳定性和性能情况，目前禁止直接使用RENAME TABLE或ALTER TABLE RENAME命令重命名包含CCI的表。如需修改包含CCI表的表名，您可以先使用DROP INDEX删除列存索引，修改表名后再重新创建新的列存索引。

语法

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

常见问题

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

创建列存索引时能否不指定分区键？
答：可以。如果没有指定分区键，则默认选择主键作为分区键，选择HASH作为分区策略。
如何查看创建列存索引的进度？
答：可以通过SHOW COLUMNAR INDEX查看列存索引当前的状态，通过SHOW DDL查看DDL任务的执行进度。
如何删除列存索引？
答：可以通过DROP INDEX删除已经成功创建的列存索引。

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