如何在建表的时候创建列存索引 - 云原生数据库 PolarDB

本文介绍了如何在建表的时候创建列存索引。

前提条件

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

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

创建列存索引

CREATE TABLE语法：
- 在建表时，您只需要在CREATE TABLE语句的COMMENT字段里增加COLUMNAR=1字符串，即可创建列存索引。其余语法均不变，且不受影响。
- COLUMNAR=1可以单独加在列的COMMENT字段中，单独对该列生效；也可以加在CREATE TABLE语句末尾的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数据类型。
示例：
```
CREATE TABLE t1(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

CREATE TABLE t2(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';
```
从以上示例中可以看到：
- 表t1的建表语句中，为指定列（col1和col2）创建了列存索引。
- 表t2的建表语句中，为全表创建了列存索引，即覆盖了表中的所有数据类型能够支持的列，即col1、col2、col3。
CREATE TABLE LIKE语法：若使用CREATE TABLE LIKE命令创建表，如果源表包含列存索引，则目标表也将包含相同的列存索引。
CREATE TABLE ... SELECT语法：若使用CREATE TABLE ... SELECT命令创建表，则可在表的COMMENT字段里增加COLUMNAR=1字符串，为全表的列创建列存索引。但使用CREATE TABLE ... SELECT命令创建表时，无法为指定的列单独创建列存索引。
示例：
```
CREATE TABLE t3(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB;

CREATE TABLE t4 COMMENT 'COLUMNAR=1' SELECT col1, col2 FROM t3;
```
从以上示例中可以看到：
表t4的COMMENT 'COLUMNAR=1'是对全表生效的，即对t4的所有列（col1, col2，以及后续新增的列）创建列存索引。

说明

在COMMENT字段中添加COLUMNAR=1时，不区分大小写，与COMMENT原来的内容之间不需要使用分隔符，添加COLUMNAR=1后也不会影响原来的内容。

查看表的列存索引结构信息

语法：可以使用SHOW CREATE TABLE <tablename> [FULL]语句查看表的列存索引的结构信息。

示例：

SHOW CREATE TABLE test.t2;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='COLUMNAR=1'

SHOW CREATE TABLE test.t2 FULL;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` varchar(200) DEFAULT NULL,
  COLUMNAR INDEX (`col1`,`col2`,`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='COLUMNAR=1'

从以上示例中可以看到：

SHOW CREATE TABLE <tablename>命令默认不输出COLUMNAR INDEX的定义，只会展示COMMENT信息。
SHOW CREATE TABLE <tablename> FULL命令可以输出COLUMNAR INDEX的列存索引的结构信息。