如何通过DDL语句动态创建和删除列存索引 - 云原生数据库 PolarDB

本文介绍了如何在建表之后，通过DDL语句动态创建和删除列存索引。

前提条件

在添加完只读列存节点，并配置好对应的集群地址后，您可以通过集群地址连接到集群，使用SQL语句创建并管理列存索引。

添加只读列存节点。
配置集群地址。为了实现行存和列存的分流，目前有手动和自动两种分流方案。您可以根据业务情况选择一种分流方案概述，并配置集群地址。
通过集群地址连接到数据库集群。

创建列存索引

语法：
- 可在ALTER TABLE语句后增加COMMENT 'COLUMNAR=1'字段，为表创建对全表生效的列存索引。
- 可在ALTER TABLE ... MODIFY COLUMN ...语句后增加COMMENT 'COLUMNAR=1'字段，为指定列创建列存索引。
  说明
  - 修改COMMENT字段比较轻量，使用DMS连接集群时，不建议使用无锁结构变更流程。
  - 8.0.1.1.25版本及之后的版本，列存索引支持BLOB和TEXT类型。
  - 8.0.1.1.28版本及之后的版本，列存索引支持ENUM类型。
  - 8.0.1.1.29版本及之后的版本，支持在分区表上创建列存索引。
  - 8.0.1.1.30版本及之后的版本，列存索引支持BIT、JSON和地理信息类型。
  - 列存索引暂不支持SET数据类型。
  - 如果表上或列上已存在注释内容，您可以在现有注释内容中添加COLUMNAR=1，为了直观性，建议将COLUMNAR=1添加到注释的前方。如：原有注释内容为COMMENT 'abc'，添加COLUMNAR=1后的内容为COMMENT 'COLUMNAR=1abc'。

示例：

CREATE TABLE t5(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- 创建默认列存索引
ALTER TABLE t5 COMMENT 'COLUMNAR=1';

-- 创建指定列的列存索引
ALTER TABLE t5 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=1',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=1';

删除列存索引

语法：
- 可在ALTER TABLE语句后增加COMMENT 'COLUMNAR=0'字段，删除对全表生效的列存索引。
- 可在ALTER TABLE ... MODIFY COLUMN ...语句后增加COMMENT 'COLUMNAR=0'字段，删除指定列的列存索引。
  说明
  - 8.0.1.1.25版本及之后的版本，列存索引支持BLOB和TEXT类型。
  - 8.0.1.1.28版本及之后的版本，列存索引支持ENUM类型。
  - 8.0.1.1.29版本及之后的版本，支持在分区表上创建列存索引。
  - 8.0.1.1.30版本及之后的版本，列存索引支持BIT、JSON和地理信息类型。
  - 列存索引暂不支持SET数据类型。
  - 如果表上或列上已存在注释内容，您可以在现有注释内容中添加COLUMNAR=0，为了直观性，建议将COLUMNAR=0添加到注释的前方。如：原有注释内容为COMMENT 'abc'，添加COLUMNAR=0后的内容为COMMENT 'COLUMNAR=0abc'。

示例：

-- 创建指定列的列存索引
CREATE TABLE t6(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- 删除指定列的列存索引
ALTER TABLE t6 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=0',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

-- 创建默认列存索引
CREATE TABLE t7(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

-- 删除默认列存索引
ALTER TABLE t7 COMMENT 'COLUMNAR=0';

修改列存索引定义

语法：
- 可在ALTER TABLE ... MODIFY COLUMN ...语句后增加COMMENT 'COLUMNAR=1'字段，为列存索引增加一列。
- 可在ALTER TABLE ... MODIFY COLUMN ...语句后增加COMMENT 'COLUMNAR=0'字段，从列存索引删除一列。
说明
- 8.0.1.1.25版本及之后的版本，列存索引支持BLOB和TEXT类型。
- 8.0.1.1.28版本及之后的版本，列存索引支持ENUM类型。
- 8.0.1.1.29版本及之后的版本，支持在分区表上创建列存索引。
- 8.0.1.1.30版本及之后的版本，列存索引支持BIT、JSON和地理信息类型。
- 列存索引暂不支持SET数据类型。
- 如果列上已存在注释内容，您可以在现有注释内容中添加COLUMNAR=1或COLUMNAR=0，为了直观性，建议将COLUMNAR=1或COLUMNAR=0添加到注释的前方。如：原有注释内容为COMMENT 'abc'，添加COLUMNAR=1后的内容为COMMENT 'COLUMNAR=1abc'。

示例：

CREATE TABLE t8(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- 为列存索引新增一列
ALTER TABLE t8 MODIFY COLUMN col3 VARCHAR(200) COMMENT 'COLUMNAR=1';

-- 从列存索引删除一列
ALTER TABLE t8 MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

创建覆盖多列的列存索引

在OLAP应用中，涉及到的表大多数为大宽表。您可以通过表的COMMENT来简化大宽表创建列存索引的方式，即默认为宽表在所有支持的数据类型的列上创建列存索引，同时可以只指定少量的无需有列存索引覆盖的列。

说明

8.0.1.1.25版本及之后的版本，列存索引支持BLOB和TEXT类型。
8.0.1.1.28版本及之后的版本，列存索引支持ENUM类型。
8.0.1.1.29版本及之后的版本，支持在分区表上创建列存索引。
8.0.1.1.30版本及之后的版本，列存索引支持BIT、JSON和地理信息类型。
列存索引暂不支持SET数据类型。
如果表上或列上已存在注释内容，您可以在现有注释内容中添加COLUMNAR=1，为了直观性，建议将COLUMNAR=1添加到注释的前方。如：原有注释内容为COMMENT 'abc'，添加COLUMNAR=1后的内容为COMMENT 'COLUMNAR=1abc'。

例如，若使用如下语句建表：

CREATE TABLE t9(
  col1 INT, col2 INT, col3 INT,
  col4 DATETIME, col5 TIMESTAMP,
  col6 CHAR(100), col7 VARCHAR(200),
  col8 TEXT, col9 BLOB
) ENGINE InnoDB;

可使用如下命令为表创建列存索引：

ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

结果如下：

SHOW CREATE TABLE t9 FULL\G
*************************** 1. row ***************************
      Table: t9
Create Table: CREATE TABLE `t9` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` datetime DEFAULT NULL,
  `col5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col6` char(100) DEFAULT NULL,
  `col7` varchar(200) DEFAULT NULL COMMENT 'COLUMNAR=0',
  `col8` text,
  `col9` blob,
  COLUMNAR INDEX  (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col8`,`col9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'COLUMNAR=1'

从以上示例可以看到：col7列被指定过滤掉，没有被列存索引覆盖。

但受限于InnoDB Online DDL的实现方式，上述示例中的DDL语句ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';会以online rebuild的方式实现，从而性能较差。您可以尝试调整为以下方式：

-- 先过滤掉无需覆盖的列，只修改列的COMMENT属性
ALTER TABLE t9 MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

-- 修改表COMMENT属性并且添加默认列存索引
ALTER TABLE t9 COMMENT 'COLUMNAR=1';

增加列的同时添加列存索引

您可以在使用ALTER TABLE ADD COLUMN时，在COMMENT中指定COLUMNAR属性，从而达到在列上创建列存索引的目的。

说明

8.0.1.1.25版本及之后的版本，列存索引支持BLOB和TEXT类型。
8.0.1.1.28版本及之后的版本，列存索引支持ENUM类型。
8.0.1.1.29版本及之后的版本，支持在分区表上创建列存索引。
8.0.1.1.30版本及之后的版本，列存索引支持BIT、JSON和地理信息类型。
列存索引暂不支持SET数据类型。
如果列上已存在注释内容，您可以在现有注释内容中添加COLUMNAR=1，为了直观性，建议将COLUMNAR=1添加到注释的前方。如：原有注释内容为COMMENT 'abc'，添加COLUMNAR=1后的内容为COMMENT 'COLUMNAR=1abc'。

例如，若使用如下语句建表，建表时创建列存索引，并覆盖col1和col2两列：

CREATE TABLE t10(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

可执行如下命令，为表t10新加一列col4，并且被列存索引覆盖：

ALTER TABLE t10 ADD col4 DATETIME DEFAULT NOW() COMMENT 'COLUMNAR=1';

由于涉及到列存索引的变更，因此不再是INSTANT DDL。该DDL语句在加列的同时会删除旧的列存索引，并且新建覆盖col1、col2、col4的列存索引。

列存索引与INSTANT DDL秒级加列功能

PolarDB MySQL版8.0.1.1.42之前，以及8.0.2.2.23之前的版本。
在创建了表级列存索引的表上执行增加列的操作时，默认不会使用INSTANT DDL。原因在于涉及到列存索引结构的变更，以及重建索引数据。在重建列存索引期间，列存索引可以正常使用。
如果您需要使用INSTANT DDL，您可以通过以下两种方式中的任意一种来开启INSTANT DDL，开启INSTANT DDL不会影响行存执行DDL的速度：
- 在数据库中执行以下命令来开启INSTANT DDL：
```
SET imci_enable_add_column_instant_ddl = ON
```
- 登录PolarDB控制台，在参数配置页面将参数loose_imci_enable_add_column_instant_ddl的值设置为ON来开启INSTANT DDL。
开启INSTANT DDL后，在列存节点上执行增加操作时，后台会异步构建列存索引，在构建索引期间，该表的列存索引暂时无法使用，直至列存索引构建完成。
PolarDB MySQL版8.0.1.1.42及以上，以及8.0.2.2.23及以上的版本。
在创建了表级列存索引的表上执行增加列的操作时，默认使用INSTANT DDL。该功能与旧版的重建模式不兼容，即需要将参数imci_enable_add_column_instant_ddl的值设置为OFF。且需要保证表上有主键。

查看索引状态

在启用列存索引特性后，当前OLAP查询请求会发给只读列存节点，而不会发给主节点，从而保证OLAP和OLTP的计算资源隔离。基于这种隔离限制，将新建列存索引或者修改列存索引定义的Online DDL优化为异步DDL，其逻辑是在主节点上完成表及索引的元数据修改操作，然后通过Redo日志同步给只读列存节点。只读列存节点会在数据字典修改生效后启动后台线程并发构建列存索引。

异步DDL的逻辑效果是在RW节点上成功执行DDL语句并且在数据字典上已生效。但列存索引在构建完成前无法提供查询服务。您会发现，在执行完相应DDL语句后，若立即进行OLAP查询，依旧是通过行存的方式执行。需要等待列存索引构建完成后，再进行OLAP查询，列存索引才会生效。

您可以在只读列存节点上查询INFORMATION_SCHEMA.IMCI_INDEXES获取列存索引的创建状态。

例如，若使用如下语句建表：

CREATE TABLE t11(
  col1 INT, col2 DATETIME, col3 VARCHAR(200)
) ENGINE InnoDB;

然后执行如下DDL语句创建列存索引：

ALTER TABLE t11 COMMENT 'COLUMNAR=1';

该DDL语句的效果类似于INSTANT DDL，会在主节点上很快执行完成。但此时查询：

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME = 't11';

说明

如果是分区表，则需要使用模糊查询。示例如下：

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME LIKE '%t1%';

可发现返回结果的STATE字段并不是COMMITTED（如RECOVERING），则表示列存索引依旧在创建中，查看列存索引创建进度详情请参见查看列存索引构建的执行进度。

+--------+-----------+----------+--------+---------+------+----------+--------+
|TABLE_ID|SCHEMA_NAME|TABLE_NAME|NUM_COLS|PACK_SIZE|ROW_ID|STATE     |MEM_SIZE|
+--------+-----------+----------+--------+---------+------+----------+--------+
|    xxxx| test      | t11      |       3|    65536|     0|RECOVERING|    0   |
+--------+-----------+----------+--------+---------+------+----------+--------+

若STATE为COMMITTED，则表示列存索引已创建完成。此时进行OLAP查询，则会通过列存索引执行。