PolarDB：列存索引常見問題

如何使用PolarDB MySQL版的列存索引功能？

如何查看列存索引狀態？

如何確認SQL使用了列存索引/如何查看列存索引的SQL執行計畫？

*************************** 1. row ***************************
IMCI Execution Plan (max_dop = 8, max_query_mem = 3435134976):
Project | Exprs: temp_table3.lineitem.L_ORDERKEY, temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT), temp_table3.orders.O_ORDERDATE, temp_table3.orders.O_SHIPPRIORITY
  Sort | Exprs: temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT) DESC,temp_table3.orders.O_ORDERDATE ASC
    HashGroupby | OutputTable(3): temp_table3 | Grouping: lineitem.L_ORDERKEY orders.O_ORDERDATE orders.O_SHIPPRIORITY | Output Grouping: lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY | Aggrs: SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT)
      HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_ORDERKEY = lineitem.L_ORDERKEY
        HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_CUSTKEY = customer.C_CUSTKEY
          CTableScan | InputTable(0): orders | Pred: (orders.O_ORDERDATE < 03/24/1995 00:00:00.000000)
          CTableScan | InputTable(1): customer | Pred: (customer.C_MKTSEGMENT = "BUILDING")
        CTableScan | InputTable(2): lineitem | Pred: (lineitem.L_SHIPDATE > 03/24/1995 00:00:00.000000)
1 row in set (0.04 sec)

如何解決SQL語句的執行計畫不使用列索引/查詢不走列存/加了列存節點後SQL執行計畫沒有變化/列索引是否支援某條SQL/“行存/列存自動引流”功能不生效的問題？

新增列存節點後，需要為SQL語句所查詢的表增加列存索引，且SQL語句的查詢代價超過一定閾值，該SQL語句才會使用列索引進行查詢。另外，SQL語句需要被轉寄到列存節點，才可以使用列存索引進行查詢加速。一般來說，如果一條SQL語句無法使用列存索引進行查詢，可以按照以下步驟進行問題排查：

1. 確認SQL是否被轉寄到列存節點。

   通過SQL洞察功能，可以確認SQL是否被轉寄到了列存節點。

   如果使用了叢集地址並且開啟了“行存/列存自動引流”功能，則資料庫代理會自動將預估查詢閾值超過imci_ap_threshold的SQL轉寄到列存節點。您也可以在SQL語句的SELECT字樣前增加/*FORCE_IMCI_NODES*/強制指定將SQL轉寄到列存節點。樣本如下：

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

   更多資訊請參見配置行列自動分流。

   說明

   建立一個新的endpoint直接連接列存節點，可以保證SQL語句一定會被轉寄到列存節點上執行。

2. SQL的查詢代價是否高於一定閾值。

   在列存節點上，最佳化器會對SQL進行代價預估，如果預估代價高於設定的閾值cost_threshold_for_imci，則使用列存索引進行查詢，否則使用原有的行索引進行查詢。

   在確認SQL被轉寄到列存節點後，如果通過EXPLAIN查看執行計畫依然沒有使用列存索引，可以通過比較預估的執行代價與預設的閾值，判斷是否是預估代價過小而沒有使用列存索引。您可以通過查詢Last_query_cost變數擷取“上一條SQL的預估執行代價”：

   EXPLAIN SELECT * FROM t1;
   SHOW STATUS LIKE 'Last_query_cost';

   如果SQL預估的執行代價小於預設的cost_threshold_for_imci，可以考慮調整cost_threshold_for_imci 。例如，使用Hint調整單條SQL的預設閾值：

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3. SQL所需的列是否被列索引完全覆蓋。

   可以通過內建的預存程序dbms_imci.check_columnar_index()檢查SQL語句中的表是否已建立列索引，樣本如下：

   CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

   如果SQL沒有完全被列索引覆蓋，調用該預存程序會返回沒有被覆蓋的表和列；如果已經被列索引完全覆蓋，調用該預存程序會返回空結果集。

4. 是否有不支援的SQL特性。

   通過查看使用限制列表，確認某個SQL特性是否支援列存索引。

如果上述檢查都沒問題，則該SQL一般都應該會使用列存索引進行查詢。

列存節點是否能使用行索引？

列存索引唯讀節點可以看作普通的唯讀節點上新增了列索引的功能，因此列存節點上既可以像普通唯讀節點一樣使用行索引，也可以使用列存索引，最佳化器根據cost_threshold_for_imci的閾值做索引選擇。

SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;

如何為SQL增加合適的列存索引？

一條SQL語句中使用的所有列都需要被列存索引覆蓋，這條語句才能使用列存索引進行查詢。如果SQL語句中涉及到的列沒有被列存索引覆蓋，可以通過CREATE TABLE或者ALTER TABLE語句增加列索引。PolarDB MySQL版提供一系列的內建預存程序以輔助此操作。

dbms_imci.columnar_advise('<query_string>');

PolarDB MySQL版列存索引IMCI是否支援單機並行查詢？如果支援，如何自訂某個SQL的並行度？

預設啟用單機並行查詢。您可以通過 EXPLAIN 查看執行計畫，其中 max_dop 欄位表示實際使用的並行度。若需自訂某條 SQL 的並行度，可以會話層級設定 imci_max_dop 參數後，再執行SQL，例如：

set imci_max_dop=8; explain select xxxx

列存節點的CPU使用率高/記憶體使用量率高/如何配置列存節點的CPU和記憶體監控？

• 列存索引的預設配置是單條SQL並發執行，並且在執行時可以用滿所有CPU。當存在多條SQL同時執行時，資料庫內部的調度器會對SQL進行調度，動態降低各個SQL的CPU上限以及記憶體使用量上限。因此，相對於其他節點，列存節點的平均CPU使用率和記憶體都相對較高。您可以通過調整imci_max_dop參數，控制單條SQL的最大並發度（單條SQL最多使用多少核的CPU）。

• 一般建議CPU的監控閾值配置為CPU使用率的70%，記憶體監控閾值配置為記憶體使用量率的90%。

• PolarDB MySQL版支援同一個叢集內不同節點的規格異構，可以單獨為列存節點升級/降低配置。一般建議列存節點的規格至少在8核16 GB及以上。

PolarDB MySQL版 5.6/5.7版本是否支援列存索引？

PolarDB MySQL版5.6/5.7版本不支援使用列存索引功能。PolarDB MySQL版8.0版本支援使用列存索引功能。

列存索引IMCI的使用限制是什麼/列存索引是否支援全文索引/使用列存索引的查詢是否相容MySQL？

列存索引的使用方式完全相容MySQL，但是一些不常見的查詢特性，比如某些時空類型的運算式、全文索引以及某些形式的關聯子查詢，目前尚未完全支援，使用了這些查詢特性的SQL語句無法使用列索引（會預設使用行索引進行查詢）。有關列索引的詳細使用限制請參見使用限制。

INSERT INTO SELECT/CREATE TABLE AS SELECT語句是否可以使用列存索引？

列存索引只能在單獨的唯讀節點上查詢，而INSERT/CREATE語句只能在主節點（RW節點）上執行，因此如果需要使用列存索引進行INSERT INTO SELECT/CREATE TABLE AS SELECT語句的查詢，需要使用列存索引的ETL功能。更多詳細資料請參見使用列索引加速ETL。

列存索引功能是否收費/普通唯讀節點是否支援列存索引？

列存索引功能不單獨收費，但是使用列存索引需要單獨增加一個唯讀節點並且在這個唯讀節點上開啟列存索引。這個新增的唯讀節點，以及後續為某些表新增的列存索引所帶來的額外儲存，會帶來額外的費用。

普通唯讀節點不支援列存索引。

列存索引會新增多少額外儲存空間？

列存索引資料按列組織，因此壓縮比更高。相比行存一般有3~10倍的壓縮比，會額外增加10%~30%左右的空間。

如何查看列存索引佔用的儲存空間容量？

• 叢集版本為PolarDB MySQL版8.0.1.1.32版本及之前的版本時，您可以通過information_schema中的系統資料表imci_columns來查看列索引所在的表佔用的儲存空間容量和列壓縮比。如查看test庫中含有列存索引的test表佔用的儲存空間容量和壓縮比，SQL語句如下：

  SELECT
    SCHEMA_NAME, TABLE_NAME,
    SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
    SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
    SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE,
    SUM(RAW_DATA_SIZE) / SUM(FILE_SIZE) AS COMPRESS
  FROM
    information_schema.imci_columns
  WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

• 叢集版本為PolarDB MySQL版8.0.1.1.33版本及之後的版本時，您可以通過information_schema中的系統資料表imci_data_files查看儲存空間容量，通過系統資料表imci_columns查看列壓縮比。如查看test庫中含有列存索引的test表佔用的儲存空間容量和列壓縮比，SQL語句如下：

  • 查看含有列存索引的表test佔用的儲存空間容量。SQL語句如下：

    SELECT
        SCHEMA_NAME, TABLE_NAME,
        SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
        SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
        SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE
    FROM
        INFORMATION_SCHEMA.IMCI_DATA_FILES
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

  • 查看列壓縮比的SQL語句如下：

    SELECT
        SCHEMA_NAME, TABLE_NAME,
         SUM(RAW_DATA_SIZE) / SUM(CMP_DATA_SIZE) AS COMPRESS_RATIO
    FROM
        INFORMATION_SCHEMA.IMCI_COLUMNS
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

上述SQL語句中對應的參數說明如下：

增加列存索引後INSTANT DDL不生效是什麼原因？

• PolarDB MySQL版8.0.1.1.42之前，以及8.0.2.2.23之前的版本，對於建立了表級列存索引的表上執行的加列操作，不再適用於秒級加列邏輯，原因在於涉及到列存索引結構的變更與索引資料重建。

• PolarDB MySQL版8.0.1.1.42及以上，以及8.0.2.2.23及以上的版本，已支援在建立了表級列存索引的表上使用INSTANT DDL。該功能與舊版的重建模式不相容，即需要將參數imci_enable_add_column_instant_ddl的值設定為OFF。且需要保證表上有主鍵。

如何查看/刪除Autoindex添加列存索引？

SELECT * FROM  information_schema.imci_autoindex_executed;

刪除Autoindex自動添加的列存索引和刪除手動添加的列存索引方式相同：

ALTER TABLE t1 comment 'columnar=0';

增加列存索引後AlTER TABLE加減列時間變長是什麼原因？

一般來說執行加減列變更操作時，都會涉及到表資料重建。當原表上包含列存索引時，表資料重建的同時也需要重建列存索引的資料，而列存索引資料重建的過程需要寫入Redo log，通常列存索引會覆蓋比較多的列，從而列存索引資料重建Redo log與原表資料大小成正比。相比沒有列存索引的表重建過程中會增加IO資料量，從而表現為執行時間會變長。

增加列存索引是否會影響寫入效能？

增加列存索引對寫入效能的影響基本在5%以內，使用Sysbench測試集測試oltp_insert workload，增加列存索引後寫入效能下降約為3%。

列存索引支援哪些交易隔離等級？

列存索引支援READ_COMMITTED和REPEATABLE_READ兩種交易隔離等級。

列存索引是否對模糊搜尋情境有加速效果？

適合。列存索引在模糊搜尋情境加速作用表現優異，支援LIKE PRUMER、NGRAM LIKE與SMID LIKE等。同時，列存全文索引功能同樣支援在模糊搜尋情境提供加速效果。