如何通過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查詢，則會通過列存索引執行。