文档

创建和使用CCI

更新时间:

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

注意事项

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

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

  • 创建CCI相关的约束

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

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

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

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

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

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

语法

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

image.png

  • 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中不同列数据类型的支持情况。

数据类型

主键

排序键

分区键

数值类型

BIT (UNSIGNED)

支持

支持

不支持

TINYINT (UNSIGNED)

支持

支持

支持

SMALLINT (UNSIGNED)

支持

支持

支持

MEDIUMINT (UNSIGNED)

支持

支持

支持

INT (UNSIGNED)

支持

支持

支持

BIGINT (UNSIGNED)

支持

支持

支持

时间类型

DATE

支持

支持

支持

DATETIME

支持

支持

支持

TIMESTAMP

支持

支持

支持

TIME

支持

支持

不支持

YEAR

支持

支持

不支持

字符串类型

CHAR

支持

支持

支持

VARCHAR

支持

支持

支持

TEXT

支持

支持

不支持

BINARY

支持

支持

支持

VARBINARY

支持

支持

支持

BLOB

支持

支持

不支持

浮点数类型

FLOAT

不支持

不支持

不支持

DOUBLE

不支持

不支持

不支持

DECIMAL

不支持

不支持

不支持

NUMERIC

不支持

不支持

不支持

特殊类型

JSON

不支持

不支持

不支持

ENUM

不支持

不支持

不支持

SET

不支持

不支持

不支持

POINT

不支持

不支持

不支持

GEOMETRY

不支持

不支持

不支持

说明

CCI分区键类型支持与CN基本一致,详见数据类型说明

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" 作为分区键。

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

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

  2. 如何查看创建列存索引的进度?

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

  3. 如何删除列存索引?

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

  • 本页导读 (0)