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刪除已經成功建立的列存索引。