AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB MySQL版 用户指南 列存索引（IMCI） 列存索引语法说明 库表级列存索引DDL语法

库表级列存索引DDL语法

更新时间：
产品详情
我的收藏

本文将介绍如何为库表批量创建与删除列存索引。

前提条件

数据库引擎版本要求如下所示：

  • PolarDB MySQL版8.0.1版本，且修订版本为8.0.1.1.45及以上。

  • PolarDB MySQL版8.0.2版本，且修订版本为8.0.2.2.27及以上。

单表简化命令

添加表级列存索引

您可以使用CREATE COLUMNAR INDEX ON语句来创建列存索引。基本的语法如下：

CREATE COLUMNAR INDEX ON <db_name>.<table_name>;
CREATE COLUMNAR INDEX ON <table_name>;

列存索引的创建语法与创建普通（非列存）二级索引语法类似，但列存索引不支持指定索引名和覆盖列。此时db_name为当前会话中设置的数据库。

说明

  • 不支持自定义列存索引，如果您希望启用自定义列存索引，更多信息，请参见使用扩展属性定制列存索引。

  • 在库表名<db_name>.<table_name>上执行添加表级列存索引操作时，若索引不存在，将执行添加表级列存索引操作。若您已经存在列存索引，您可以在ON关键字前添加IF NOT EXISTS来避免添加表级列存索引时的提示报错信息。

  • 默认情况下，CREATE COLUMNAR INDEX ON命令等价于ALTER TABLE指令。

    ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=1 <OLD_COMMENT>';

  • 在allow_implicit_imci_alter_comment参数关闭的情况下，将不会修改表上的备注信息。

删除表级列存索引

DROP COLUMNAR INDEX ON语法：

DROP COLUMNAR INDEX ON <db_name>.<table_name>;
DROP COLUMNAR INDEX ON <table_name>;

列存索引的删除语法与删除普通（非列存）二级索引语法类似，但不同之处在于不需要指定索引名。此时使用当前会话中设置的数据库作为<db_name>。

说明

  • 在库表名<db_name>.<table_name>上执行删除表级列存索引操作时，若索引存在，将执行删除表级列存索引操作。如果您已经执行过删除表级列存索引的操作，再次执行时，您可以在ON关键字前添加IF EXISTS来避免删除表级列存索引时的提示报错信息。

  • DROP COLUMNAR INDEX ON命令等价于以下2条ALTER TABLE指令的组合。

    ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=0 <OLD_COMMENT>';
ALTER TABLE <db_name>.<table_name> COMMENT '<OLD_COMMENT>';

批量添加列存索引

CREATE COLUMNAR FOR TABLES IN语法：

CREATE COLUMNAR INDEX FOR TABLES IN <db_name>;

CREATE COLUMNAR FOR TABLES FROM语法：

CREATE COLUMNAR INDEX FOR TABLES FROM <db_name>;

CALL语法：

-- dbms_imci：这是一个数据库包（package）的名称，通常用于管理和操作数据库中的特定任务。在这里，dbms_imci是一个专门用于处理列存索引相关操作的包。
-- add_columnar_index：这是要调用的存储过程的名称。在此上下文中，它的功能是为指定的数据库创建列存索引。
CALL dbms_imci.add_columnar_index('<db_name>');

该存储过程第二个参数为可选项。当您将第二个参数设置为1时，系统将为数据库添加implicit_imci标记。此后，在该数据库下新增表时，数据库内核将自动为这些表创建列存索引。

说明

  • 在已有数据库（库名为<db_name>）下，对于所有现存表，如果表级列存索引不存在，则添加该索引。

  • 不支持自定义列存索引，如果您希望启用自定义列存索引，更多信息，请参见使用扩展属性定制列存索引。

  • 列存索引的创建语法与创建普通（非列存）二级索引语法类似，但列存索引不支持指定索引名和覆盖列。

示例：

mysql> SHOW tables IN tpch;
+--------------------+
| Tables_in_tpch |
+--------------------+
| customer           |
| lineitem           |
| nation             |
| orders             |
| part               |
| partsupp           |
| region             |
| revenue0           |
| supplier           |
+--------------------+
9 rows in set (0.01 sec)

mysql> SHOW imci indexes;
Empty set (0.02 sec)

mysql> CREATE COLUMNAR INDEX FOR TABLES IN tpch;
+------------+--------+
| Table_Name | Result |
+------------+--------+
| customer   | Ok     |
| lineitem   | Ok     |
| nation     | Ok     |
| orders     | Ok     |
| part       | Ok     |
| partsupp   | Ok     |
| region     | Ok     |
| supplier   | Ok     |
+------------+--------+
8 rows in set (0.56 sec)

-- 若STATE为COMMITTED时，则表示列存索引已创建完成。为其他值时，则表示列存索引正在创建中。
mysql> SHOW imci indexes;
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID  | STATE            | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536   | COMMITTED        | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (0.07 sec)

批量删除列存索引

DROP COLUMNAR INDEX FOR TABLES IN语法：

DROP COLUMNAR INDEX FOR TABLES IN <db_name>;

DROP COLUMNAR INDEX FOR TABLES FROM语法：

DROP COLUMNAR INDEX FOR TABLES FROM <db_name>;

CALL语法：

-- dbms_imci：这是一个数据库包（package）的名称，通常用于执行一些与数据库管理相关的操作。在此上下文中，dbms_imci是一个处理列存索引（columnar index）相关任务的包。
-- drop_columnar_index：这是要调用的存储过程的名称。它的功能是删除指定数据库中的列存索引。
CALL dbms_imci.drop_columnar_index('<db_name>');

该存储过程的第二个参数为可选项。当您将第二个参数设置为1时，系统将移除数据库上的implicit_imci标记，从而禁用在该数据库下新增表时自动创建列存索引的功能。

说明

在已有数据库（数据库名为<db_name>）下，删除所有现存表中存在的列存索引。

mysql> SHOW imci indexes;
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID    | STATE     | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 306       | 65536     | 20054016  | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 16        | 65536     | 1048576   | COMMITTED | 2024-09-24 18:50:34 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 1221      | 65536     | 80019456  | COMMITTED | 2024-09-24 18:51:15 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 229       | 65536     | 15007744  | COMMITTED | 2024-09-24 18:50:28 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 2289      | 65536     | 150011904 | COMMITTED | 2024-09-24 18:51:23 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 9156      | 65536     | 600047616 | COMMITTED | 2024-09-24 18:54:16 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (1.09 sec)

mysql> CALL dbms_imci.drop_columnar_index('tpch');
+-------------+------------+--------+
| Object_Name | Operation  | Result |
+-------------+------------+--------+
| customer    | drop_index | Ok     |
| lineitem    | drop_index | Ok     |
| nation      | drop_index | Ok     |
| orders      | drop_index | Ok     |
| part        | drop_index | Ok     |
| partsupp    | drop_index | Ok     |
| region      | drop_index | Ok     |
| supplier    | drop_index | Ok     |
+-------------+------------+--------+
8 rows in set (0.33 sec)

mysql> SHOW imci indexes;
Empty set (0.27 sec)

批量操作结果说明

上述批量添加列存索引和批量删除索引操作的结果以表格的形式展现，其中结果列（Result）用于说明对应库表操作的结果，可能出现的状态码（Code）含义说明如下：

Result Code

含义

Ok

操作完成，例如列存索引已添加或已删除。

Skip by unsupported

相关操作无法支持。

Skip by no change

目标对象已存在或状态已匹配，例如列存索引已存在时跳过添加操作。

Skip by concurrent operation

存在并发操作冲突，例如表上存在其DDL操作无法获取MDL锁。

Skip by not found

目标对象不存在。

Skip by ACL deny

当前连接账号没有相应的执行权限。

Failed

操作失败。

不修改表备注信息添加表级列存索引

您可以通过设置如下参数实现隐式列存索引功能。

参数

级别

说明

allow_implicit_imci_alter_comment

Session

是否允许修改表级备注信息。取值范围如下：

  • ON（默认值）：允许修改备注。

  • OFF：以不修改备注的方式创建列存索引。

说明

当allow_implicit_imci_alter_comment设置为OFF时，可以实现隐式列存索引的功能，即在添加列存索引时不修改表原有的备注信息。适用于以下场景：

示例1：

SET allow_implicit_imci_alter_comment = OFF;
-- 创建表操作创建隐式列存索引
SET GLOBAL polar_enable_implicit_imci_with_create_table = ON;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));

SHOW CREATE TABLE t1 FULL;
/*
Table Create Table t1	
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

示例2：

CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));

SET allow_implicit_imci_alter_comment = OFF;
-- CREATE COLUMNAR INDEX ON table;
CREATE COLUMNAR INDEX ON test.t1;

SHOW CREATE TABLE t1 FULL;
/*
Table Create Table t1	
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

示例3：

*/
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE t2 (id INT PRIMARY KEY, code DOUBLE);

SET allow_implicit_imci_alter_comment = OFF;

-- CREATE COLUMNAR INDEX FOR TABLES IN db;
CREATE COLUMNAR INDEX FOR TABLES IN test;
/*
Table_Name	Result
t1	Ok
t2	Ok
*/

SHOW CREATE TABLE t1 FULL;
/*
Table Create Table t1	
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

SHOW CREATE TABLE t2 FULL;
/*
Table Create Table t2	
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `code` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

优点

用户表的备注信息无需修改。例如在SaaS业务使用场景中，大租户（添加列存索引）与小租户（不添加列存索引）的表信息保持一致，以便周边生态工具进行校验。

缺点

  • 对于使用Binlog同步的场景，由于列存索引相关的备注信息缺失，从库同步后默认无法构建列存索引。如果从库需要构建列存索引，必须确保其参数设置与主库保持一致。

  • 不支持自定义列存索引，如果您希望启用自定义列存索引，更多信息，请参见使用扩展属性定制列存索引。

上一篇：动态增加或删除列存索引的DDL语法下一篇：查看列存索引构建的执行进度