如何在建表的時候建立列存索引 - 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的列存索引的結構資訊。