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

库表级列存索引DDL语法

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

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

前提条件

数据库引擎版本PolarDB MySQL版8.0.1版本，且修订版本为8.0.1.1.45及以上。

单表简化命令

添加表级列存索引

您可以使用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)

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

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

参数

级别

说明

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语法下一篇：查看列存索引构建的执行进度
文档推荐