設定最佳化器的Join Reorder - PolarDB

為了提高列存索引（IMCI）處理複雜查詢的能力，列存索引最佳化器通過結合變換規則與表中各個列的統計資訊，基於代價產生高效的執行計畫，以此來增強自身的最佳化器能力。本文介紹列存索引查詢最佳化功能的工作原理、使用方法以及使用限制等內容。

工作原理

SQL是聲明式查詢語言，不會具體的描述SQL語句的查詢計劃，擷取一條SQL語句的正確結果時，可能存在若干個可行的查詢計劃。樣本如下：

SELECT * FROM t0, t1, t2, t3 WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

對於上述SQL語句，通過以下兩種查詢計劃均可以擷取到正確的查詢結果。執行方式 Plan A與Plan B稱之為等價查詢計劃。查詢最佳化工具作為一個搜尋方塊架，其會通過從一個查詢計劃到另一個等價查詢計劃的變換，來搜尋與當前SQL語句對應的等價查詢計劃。例如：t1 INNER JOIN t2與t2 INNER JOIN t1為一對等價查詢計劃，t1 INNER JOIN t2可以通過等價查詢變換產生t2 INNER JOIN t1。最佳化器的這類變換稱之為查詢變換規則。

查詢最佳化工具工作流程如下：

輸入由資料庫解析SQL語句獲得的初始查詢計劃。
通過應用查詢變換規則，由初始狀態的查詢計劃產生等價查詢計劃。
最佳化器通過結合統計資訊與代價模型，從等價查詢計劃中選擇出執行代價最小的查詢計劃，作為最終的執行計畫交由執行層執行。

查詢最佳化功能需要依靠統計資訊進行基數估算和代價計算以判別查詢計劃的優劣，在列存索引中，表的統計資訊包括以下內容：

長條圖。描述對應列的值的分布範圍，主要用於估算單張表上的取值範圍及等值謂詞的選擇率。
對應列的特殊值的個數。主要用於估算Group By中的分組個數，也可用於輔助估計等值謂詞的選擇率。
其他約束。如該列是否存在唯一索引，該列是否與其他列存在外鍵約束等。

查詢最佳化工具通過以下兩方面來計算查詢計劃中各個節點操作符的代價：

查詢計劃中操作符處理的總行數，可以通過統計資訊進行估算。
查詢計劃中各個操作符使用的演算法複雜度。

其中，查詢計劃中操作符處理的總行數為演算法複雜度函數的參數。各個節點的操作符代價和就是整個查詢計劃的執行代價。如上圖中的兩個查詢計劃，假設採用hash join作為join的執行演算法，其代價公式為：

Cost_join=Card_inner+Card_outer

兩個執行計畫的代價分別為：

Cost_A==10000+1+1000+100+10000+10=21111

Cost_B==10000+1+100+10+1000+10=11121

由執行計畫的代價可以看出，Plan B的執行代價更低。因此，最佳化器將選擇Plan B作為最終的執行計畫。

前提條件

PolarDB叢集版本需滿足以下條件之一：

PolarDB MySQL版8.0.1版本，且修訂版本為8.0.1.1.31及以上。
PolarDB MySQL版8.0.2版本，且修訂版本為8.0.2.2.12及以上。

您可以通過查詢版本號碼來確認叢集版本。

使用限制

以下情形可能會導致基數估算出現較大誤差，從而導致最佳化器選擇次優查詢計劃。您可以通過HINT文法指導最佳化器產生理想的查詢計劃。

帶謂詞的查詢，查詢同一張表的不同列時使用了比較子。如t1.c1>t1.c2。
帶謂詞的查詢，查詢語句中使用的運算子不能使用統計資訊進行估算。如t1.c1 MOD 2=1、t1.c2 LIKE '%ABC%'。
帶謂詞的查詢，查詢語句中存在運算式，且無法在使用最佳化功能時進行計算。如t1.c1+t1.c3>100。
查詢語句中運算子所涉及的列沒有用於估算謂詞選擇率的統計資訊。如SELECT a, SUM(b) FROM t1 HAVING SUM(b) > 10。
多個謂詞之間使用AND操作符串連。如t1.c1>10 AND t1.c3<5。
查詢語句的查詢層次太深。
查詢語句中join的表太多。您可以通過修改參數loose_imci_max_enum_join_pairs的值，來調整列存索引最佳化器搜尋的join數量。

參數說明

您可以在控制台上設定以下參數，來開啟和使用列存索引最佳化功能。在控制台上設定參數的操作步驟請參見設定叢集參數和節點參數。

參數	說明
loose_imci_optimizer_switch	列存索引查詢最佳化功能控制開關。取值如下： use_imci_card_est：是否啟用列存索引的基數估計和代價計算功能。取值如下： OFF（預設）：不啟用列存索引的基數估計和代價計算功能。 ON：啟用列存索引的基數估計和代價計算功能。 use_imci_join_reorder：是否啟用列存索引的join reorder功能。取值如下： OFF（預設）：不啟用列存索引的join reorder功能。 ON：啟用列存索引的join reorder功能。說明如果查詢語句中涉及的表沒有統計資訊，或未啟用列存索引的基數估計和代價計算功能，即使該參數設定為ON，列存索引的join reorder功能也不會啟用。
loose_imci_auto_update_statistic	當統計資訊過舊時，列存索引（IMCI）最佳化器是否重新收集統計資訊。取值如下： ASYNC（預設）：當統計資訊過舊時，列存索引（IMCI）最佳化器會非同步採樣並重新收集統計資訊。 SYNC：當統計資訊過舊時，列存索引（IMCI）最佳化器會同步採樣並重新收集統計資訊。 OFF：當統計資訊過舊時，列存索引（IMCI）最佳化器不會重新收集統計資訊。
loose_imci_max_enum_join_pairs	在使用列存索引功能並開啟串連重排序時，允許列存索引最佳化器搜尋的等價執行計畫數量。取值範圍：0~4294967295。預設值為2000。

使用說明

使用列存索引最佳化功能，您需要先根據選擇的資訊採集策略採集統計資訊，資訊採集完成後，再開啟列存索引查詢最佳化功能，並執行查詢語句。

採集統計資訊。
您可以根據以下兩種資訊採集策略來採集統計資訊。
- 在資料庫中定期對需要使用列存索引最佳化功能的表執行ANALYZE TABLE命令，來構建最新的統計資訊。
- （推薦）對於新添加列存索引的表，在唯讀節點上執行ANALYZE TABLE命令構建初始的統計資訊，再將參數loose_imci_auto_update_statistic的值設定為ASYNC來自動更新統計資訊。
開啟列存索引查詢最佳化功能。
您可以在控制台上設定參數loose_imci_optimizer_switch的值來開啟列存索引查詢最佳化功能。
執行查詢語句。

查詢效果對比

以TPCH-Q8為例，該查詢語句涉及多張表，且在查詢語句中使用了彙總函式。

SELECT
  o_year,
  SUM(
    CASE
      WHEN nation = 'BRAZIL' THEN volume
      ELSE 0
    END
  ) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(
        year
        FROM
          o_orderdate
      ) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      lineitem,
      orders,
      part,
      supplier,
      customer,
      nation n1,
      nation n2,
      region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01'
      AND DATE '1996-12-31'
      AND p_type = 'ECONOMY ANODIZED STEEL'
  ) AS all_nations
GROUP By
  o_year
ORDER BY
  o_year;

未開啟列存索引最佳化功能的查詢計劃如下：該查詢計劃中大量的join查詢產生了較大的結果集，導致後續由操作符處理的資料與處理成本隨之增加，查詢時延延長。該查詢計劃在TPCH SF100規模的資料下，使用32C機器進行測試，該語句的查詢耗時為7017ms。
開啟列存索引最佳化功能的查詢計劃如下：列存索引最佳化器通過對join查詢進行重排序，使得幾乎所有join操作符的輸出結果資料量降到百萬層級，有效地減少了後續各個操作符的處理成本。該查詢計劃在TPCH SF100規模的資料下，使用32C機器進行測試，該語句的查詢耗時為1900ms。較未開啟查詢最佳化功能的查詢耗時縮短了73%。