庫表級列存索引DDL文法 - PolarDB

本文將介紹如何為庫表大量建立與刪除列存索引。

前提條件

資料庫引擎版本要求如下所示：

PolarDB MySQL版8.0.1版本，且修訂版本為8.0.1.1.45及以上。
PolarDB MySQL版8.0.2版本，且修訂版本為8.0.2.2.27及以上。

單表簡化命令

添加表級列存索引

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

大量操作結果說明

上述大量新增列存索引和大量刪除索引操作的結果以表格的形式展現，其中結果列（Result）用於說明對應庫表操作的結果，可能出現的狀態代碼（Code）含義說明如下：

Result Code	含義
Ok	操作完成，例如列存索引已添加或已刪除。
Skip by unsupported	相關操作無法支援。
Skip by no change	目標對象已存在或狀態已匹配，例如列存索引已存在時跳過添加操作。
Skip by concurrent operation	存在並行作業衝突，例如表上存在其DDL操作無法擷取MDL鎖。
Skip by not found	目標對象不存在。
Skip by ACL deny	當前串連帳號沒有相應的執行許可權。
Failed	操作失敗。

不修改表備忘資訊添加表級列存索引

您可以通過設定如下參數實現隱式列存索引功能。

參數

層級

說明

allow_implicit_imci_alter_comment

Session

是否允許修改表級備忘資訊。取值範圍如下：

ON（預設值）：允許修改備忘。
OFF：以不修改備忘的方式建立列存索引。

說明

當allow_implicit_imci_alter_comment設定為OFF時，可以實現隱式列存索引的功能，即在添加列存索引時不修改表原有的備忘資訊。適用於以下情境：

loose_polar_enable_implicit_imci_with_create_table開啟的情況下執行CREATE TABLE語句。
CREATE COLUMNAR INDEX ON <db>.<table>動態添加列存索引。
大量新增列存索引操作過程中建立的列存索引。

樣本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同步的情境，由於列存索引相關的備忘資訊缺失，從庫同步後預設無法構建列存索引。如果從庫需要構建列存索引，必須確保其參數設定與主庫保持一致。
不支援自訂欄存索引，如果您希望啟用自訂欄存索引，更多資訊，請參見使用擴充屬性定製列存索引。