列存索引統計資訊如何進行查詢最佳化 - PolarDB

執行查詢語句時，您可以通過列存索引的pruner功能，過濾掉資料庫中不需要訪問的資料區塊，以提高SQL語句的查詢速度。本文介紹pruner的適用情境、注意事項、文法和相關參數等內容。

簡介

列存索引資料以單列資料區塊（預設包含64K行，可以通過表的總行數/64K來估算資料區塊數量）粒度儲存，掃描資料時需要遍曆指定列的所有資料區塊，並根據過濾條件來擷取滿足條件的資料。查詢資料量比較大的表時，掃描代價很大，如果表中的資料不能全部放在記憶體中，掃描代價會進一步加大。事實上，您可以通過訪問統計資訊，再結合特定的過濾條件來過濾掉不需要訪問的資料區塊以加快查詢速度。在PolarDB列存索引中，這種方法稱之為pruner。目前，Pruner有以下四種類型：

bloom filter
利用BIT數組表示一個集合，並且可以判斷某個元素是否屬於該集合。
minmax indexes
用於統計資料區塊的最值。利用過濾條件與最值進行比較，從而判斷是否需要掃描資料區塊。
token bloom filter
用於過濾字串，按照非字母和非數字字元進行分割。如“I am IMCI”，將被分割為I |am|IMCI來儲存，適用於LIKE模糊查詢。
ngram bloom filter
用於過濾字串，按照指定長度進行分割。如“我是列存資料庫”，在指定ngram大小為3時將被分割為我是列|是列存|列存數|存資料|資料庫來儲存，適用於LIKE模糊查詢。

適用情境

Bloom filter：適用於等值條件以及IN條件，對於過濾性較強的等值條件，具有較好的過濾效果。如使用字串ID進行等值過濾。
Minmax indexes：適用於對列資料分布有較好的局部性的情境，對於範圍過濾條件和等值過濾條件具有較好的過濾效果。如WHERE條件中帶有日期或排序欄位。
Token bloom filter和ngram bloom filter適用於LIKE模糊查詢，來快速過濾未命中的資料區塊。

儲存開銷

對於字串類型的資料，開啟列存索引pruner查詢最佳化功能後會帶來一定的儲存開銷和佔用一定的記憶體空間，您可以根據使用情境，選擇為指定的列構建bloom filter、minmax indexes、token bloom filter或ngram bloom filter。記憶體佔用計算公式如下：

bloom filter/token bloom filter/ngram bloom filter
- 預設資料區塊為64K，distinct值佔總行數的比例大於3%時，計算公式如下：
  佔用記憶體=1.2*構建bloom filter的列數*錶行數（單位：Byte）
- 預設資料區塊為64K，distinct值佔總行數的比例小於等於3%時，計算公式如下：
  佔用記憶體=1.2*構建bloom filter的列數*distinct值個數（單位：Byte）
  該情境下，bloom filter過濾效果依賴資料的局部性，對於資料均勻分布的情境效果較差。
minmax indexes
minmax indexes記憶體佔用計算公式如下：
佔用記憶體=2*構建minmax indexes的列數*（錶行數/資料區塊大小）*前置長度*字元集編碼長度
例如，錶行數為20億，對其中10列構建minmax indexes，前置長度為20，資料區塊大小為64K，採用預設字元集utf8mb4（編碼長度為4），則佔用的記憶體約為46 MB。

注意事項

叢集版本為PolarDB MySQL版8.0.1.1.32及以下或8.0.2.2.13及以下的版本時，對包含NULL值的資料區塊不會構建任何類型的pruner，並且不支援IS NULL或IS NOT NULL過濾條件。
叢集版本為PolarDB MySQL版8.0.1.1.35及以上或8.0.2.2.16及以上的版本時，在建立列存索引時字串類型的欄位會預設構建pruner，且bloom filter使用LRU Cache（Least Recently Used Cache）進行記憶體管理。對於從低版本升級到8.0.1.1.35及以上或8.0.2.2.16及以上版本的叢集，需要重建列存索引才會為字串列構建pruner。
叢集版本為PolarDB MySQL版8.0.1.1.34及以下或8.0.2.2.15及以下的版本時，構建的pruner會常駐記憶體。且在建立列存索引時字串類型的欄位不會預設構建pruner。
如果需要為字串類型的欄位構建pruner，則需要確保字串中不包含'\0'，如'polar\0db'。
系統會預設為int、decimal和datetime等數實值型別的資料構建minmax indexes。
不支援為JSON類型以及GEOMETRY類型的欄位構建minmax。
不支援為數實值型別欄位（例如INT、DECIMAL、DATETIME等）、JSON、BLOB、TEXT類型的欄位構建bloomfilter。

文法說明

您可以在建立表的同時構建pruner，也可以在已建立的表上構建或刪除pruner。在已建立的表上構建或刪除pruner時，需要先刪除列索引，然後重建列索引。文法如下：

說明

您可以通過DDL語句修改表的Schema中的COMMENT來為表中字串類型的欄位構建或刪除pruner。
所有的列層級的COMMENT屬性優先順序均大於表層級的COMMENT屬性。

建立表的同時構建pruner

構建pruner（bloom filter）

為表中所有支援的欄位構建bloom filter。樣本如下：

CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_BLOOM=1"; /*comment中帶pruner_bloom屬性*/

為表中的某列構建bloom filter。樣本如下：

CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_BLOOM=1", /*comment中帶pruner_bloom屬性*/
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";

說明

叢集版本為PolarDB MySQL版8.0版本且修訂版本為8.0.1.1.32及以上或8.0.2.2.13及以上的版本時，支援使用PRUNER_BLOOM屬性。

構建pruner（minmax indexes）
由於字串類型的欄位長度可能會非常長，為了減少minmax佔用的記憶體空間，系統預設截取字串類型欄位的前20個字元，最多255個字元與最值進行比較。您可以通過PRUNER_MINMAX屬性控制是否構建字串minmax，通過PREFIX_LEN控制前置長度。
說明
字元個數與編碼長度無關，例如：“阿里雲PolarDB”的前2個字元為”阿里“，前5個字元為”阿里雲Po"。
- 為表中所有字串類型的欄位構建minmax indexes。樣本如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_MINMAX=1 PREFIX_LEN=30"; /*comment中帶pruner_minmax屬性，並指定前置長度為30個字元*/
```
- 為表中的某列構建minmax indexes。樣本如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_MINMAX=1 PREFIX_LEN=30", /*comment中帶pruner_minmax屬性，並指定前置長度為30*/
    str_col2 varchar(10) "PRUNER_MINMAX=1 PREFIX_LEN=10" /*comment中帶pruner_minmax屬性，並指定前置長度為10*/
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";
```
說明
叢集版本為PolarDB MySQL版8.0版本且修訂版本為8.0.1.1.32及以上或8.0.2.2.13及以上的版本時，支援使用PRUNER_MINMAX和PREFIX_LEN屬性。
構建token bloom filter
- 為表中所有支援的欄位構建token bloom filter。樣本如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_TOKEN_BLOOM=1";
```
- 為表中的某列構建token bloom filter。樣本如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_TOKEN_BLOOM=1",
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";
```
說明
當叢集版本滿足以下條件時，支援使用PRUNER_TOKEN_BLOOM屬性：
- 叢集版本為PolarDB MySQL版8.0.1版本，且修訂版本為8.0.1.1.39及以上。
- 叢集版本為PolarDB MySQL版8.0.2版本，且修訂版本為8.0.2.2.20及以上。
構建ngram bloom filter
- 為表中所有支援的欄位構建ngram bloom filter。樣本如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_NGRAM_BLOOM=2";
```
- 為表中的某列構建ngram bloom filter。樣本如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_NGRAM_BLOOM=3",
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";
```
說明
- 當叢集版本滿足以下條件時，支援使用PRUNER_TOKEN_BLOOM屬性：
  - 叢集版本為PolarDB MySQL版8.0.1版本，且修訂版本為8.0.1.1.39及以上。
  - 叢集版本為PolarDB MySQL版8.0.2版本，且修訂版本為8.0.2.2.20及以上。
- PRUNER_NGRAM_BLOOM=N，當N大於等於2時，為分割長度。N一般推薦小於或等於LIKE "%字串%" 中的字元長度。當LIKE "%字串%" 中的字元長度小於N時，則無法使用ngram bloom filter。

在已建立的表上構建或刪除pruner

在已建立的表上構建或刪除pruner，都需要重新構建列索引，即先刪除列索引，再重新構建列索引。在構建列索引之前，需要先增加或刪除COMMENT中的pruner屬性，此處以PRUNER_MINMAX屬性為例進行說明。

構建pruner

假設原表結構如下：

       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

為表t1中所有字串類型的欄位構建minmax pruner，操作步驟如下：

執行以下命令，刪除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```
執行以下命令，為表t1中所有字串類型的欄位構建minmax pruner。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=1"; 
```

（可選）執行以下命令，查看構建pruner後的表結構。

SHOW CREATE TABLE t1 FULL \G

查詢結果如下：

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER_MINMAX=1'

由以上表結構可以看出，表t1中已添加PRUNER_MINMAX屬性。

為表t1中的str_col1列構建minmax pruner，操作步驟如下：

執行以下命令，為str_col1列構建pruner。

ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) COMMENT 'PRUNER_MINMAX=1';

按順序執行以下命令，為表t1重建列索引。

ALTER TABLE t1 COMMENT = "COLUMNAR=0";
ALTER TABLE t1 COMMENT = "COLUMNAR=1";

（可選）執行以下命令，查看重建列索引後的表結構。

SHOW CREATE TABLE t1 FULL \G

查詢結果如下：

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER_MINMAX=1',
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

由以上表結構可以看出，已為str_col1列添加PRUNER_MINMAX屬性。

刪除pruner

刪除表上的PRUNER_MINMAX屬性。

假設表t1的表結構如下：

SHOW CREATE TABLE t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER_MINMAX=1'

刪除表t1中的PRUNER_MINMAX屬性，操作步驟如下：

執行以下命令，刪除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```
執行以下命令，將COMMENT中的PRUNER_MINMAX設定為0，並重建列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=0";
```

（可選）執行以下命令，查看重建列索引後的表結構。

SHOW CREATE TABLE t1 FULL \G

查詢結果如下：

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

由以上表結構可以看出，表t1中的PRUNER_MINMAX屬性已被刪除。

刪除列上的PRUNER屬性。

假設表t1中的str_col1列上存在PRUNER_MINMAX屬性。表結構如下：

       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER_MINMAX=1',
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

刪除str_col1列上的PRUNER_MINMAX屬性，操作步驟如下：

執行以下命令，刪除str_col1列上的PRUNER_MINMAX屬性。

ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) COMMENT 'PRUNER_MINMAX=0';

按順序執行以下命令，重建列索引。

ALTER TABLE t1 COMMENT = "COLUMNAR=0";
ALTER TABLE t1 COMMENT = "COLUMNAR=1";

（可選）執行以下命令，查看重建列索引後的表結構。

SHOW CREATE TABLE t1 FULL \G

查詢結果如下：

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

由以上表結構可以看出，str_col1列上的PRUNER_MINMAX屬性已被刪除。

查看pruner是否生效

查看錶上的字串類型的欄位是否構建了pruner

您可以通過imci_secondary_indexes表中的STR_BLOOM_PRUNER和STR_MINMAX_PRUNER欄位的狀態值來判斷表中的字串列是否構建了pruner。當狀態值為1時，表示建立了對應的pruner。樣本如下：

SELECT * FROM information_schema.imci_secondary_indexes WHERE schema_name='test_tmp' AND table_name='t1'\G

查詢結果如下：

*************************** 1. row ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       TABLE_NAME: t1
      COLUMN_NAME: str_col1
 STR_BLOOM_PRUNER: 1 --str_col1 建了bloom filter
STR_MINMAX_PRUNER: 1 --str_col1 建了minmax
    SINDEX_SWITCH: 0
*************************** 2. row ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       TABLE_NAME: t1
      COLUMN_NAME: str_col2
 STR_BLOOM_PRUNER: 1 --str_col2 建了bloom filter
STR_MINMAX_PRUNER: 1 --str_col2 建了minmax
    SINDEX_SWITCH: 0
2 rows in set (0.00 sec)

由以上查詢結果可以看出：STR_BLOOM_PRUNER欄位的狀態值為1，表示對str_col1和str_col2構建了bloom filter。STR_MINMAX_PRUNER狀態值為1，表示對str_col1和str_col2構建了minmax indexes。

查看構建的pruner對查詢語句是否生效
您可以在執行查詢語句前後，執行SHOW STATUS LIKE 'imci_pruner%'命令查看資料區塊過濾情況，以此來判斷pruner對該查詢是否生效。查詢結果中的狀態值說明如下：
- imci_pruner_accepted：滿足過濾條件的資料區塊數量。
- imci_pruner_rejected：不滿足過濾條件的資料區塊數量。
通過列存索引pruner功能跳過掃描的資料區塊數量=accepted數量+rejected數量
被accept的資料區塊，無需在每條記錄上使用過濾條件進行過濾，如果部分列需要被物化，則仍然需要訪問該資料區塊；被reject的資料區塊，直接跳過掃描，不會產生任何IO。
樣本
以表t1為例，判斷pruner對查詢語句是否生效。表t1的表結構如下：
```
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER=1'
```
假設表中包含10個資料區塊，符合條件str_col1='polardb'的記錄集中在其中一個資料區塊中，執行以下步驟，查看pruner是否對SELECT COUNT(1) FROM t1 WHERE str_col1='polardb'查詢語句生效。
1. 執行以下命令，查看當前的pruner狀態資訊。
```
SHOW STATUS LIKE  'imci_pruner%';
```
  查詢結果如下：
```
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)
```
2. 執行以下命令，查詢符合條件str_col1='polardb'的數量。
```
SELECT COUNT(1) FROM t1 WHERE str_col1='polardb';
```
  查詢結果如下：
```
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)
```
3. 執行以下命令，再次查看pruner狀態資訊。
```
SHOW STATUS LIKE  'imci_pruner%';
```
  查詢結果如下：
```
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 9     |
+----------------------+-------+
2 rows in set (0.00 sec)
```
  由以上查詢結果可以看出，imci_pruner_accepted結果為0，imci_pruner_rejected為9，查詢過程中跳過了9個資料區塊，以此判斷出pruner對該查詢生效。

效能測試

以包含1.2億行資料的表為例，表中包含約1800個資料區塊，且叢集記憶體為2核4 GB，表中字串類型的欄位col的distinct值為8000萬，分別測試在該列構建bloom filter和不構建bloom filter兩種情境下的查詢效能。查詢語句如下：

SELECT COUNT(1) FROM t1 WHERE col='xxx'

查詢時間見下表：

構建bloom filter	不構建bloom filter
0.15s	18.6s

SQL語句中的col='xxx'條件結合bloom filter過濾掉了絕大部分的資料區塊，實際的查詢過程中僅需要掃描幾個資料區塊，從而提升了查詢效能。