查詢最佳化（QueryOptimizer）串連條件下推 - PolarDB

PolarDB支援串連條件下推功能。通過將derived table外層的串連條件下推到derived table內部，使derived table內部的執行計畫可以更加高效的利用索引，從而可以大幅的提升複雜查詢的執行效能。

前提條件

叢集版本需為PolarDB MySQL版8.0版本且修訂版本需為8.0.2.2.10或以上。

背景資訊

Derived table（inline view）在複雜分析查詢中被廣泛使用，通過它可以簡化SQL編寫的複雜度，以更容易讓使用者理解的方式描述SQL所要表達的查詢語義。在原生MySQL中，如果derived table無法被展開到上層查詢（其中包括group by+ 聚集合函式等運算）中，則只能通過物化的方式執行。如果SQL內部涉及的計算量很大，例如需要掃描大量表資料，那麼執行的效率會非常低。通過將derived table外層的串連條件下推到derived table內部，使derived table內部的執行計畫可以更加高效的利用索引，從而可以大幅的提升複雜查詢的執行效能。

適用情境

在複雜查詢中存在derived table和外層表的join，並且採用了nested loop join的方式。此外join列在derived table的內層，可以使用索引來加速對內層表的物化計算。同時需要有準確的統計資訊支撐，確保下推後可以在內層過濾大量的資料。
當內層查詢在串連條件下推後，如果可以有效利用索引，並過濾掉大量資料，則可能產生更高效的執行計畫。串連條件下推功能會依賴於最佳化器的代價計算能力，來智能選擇是否下推外層串連條件。

使用方法

您可以通過loose_polar_optimizer_switch參數來開啟串連條件下推功能，並通過loose_join_predicate_pushdown_opt_mode參數來控制在哪些節點上開啟串連條件下推功能。具體操作請參見設定叢集參數和節點參數。

參數名稱

層級

描述

loose_polar_optimizer_switch

Global、Session

查詢最佳化控制開關。取值範圍如下：

'join_predicate_pushdown=ON'（預設）：開啟串連條件下推功能。
'join_predicate_pushdown=OFF'：關閉串連條件下推功能。

loose_join_predicate_pushdown_opt_mode

Global

用於控制在何種節點上開啟串連條件下推功能。取值範圍如下：

REPLICA_ON（預設值）：僅在唯讀節點開啟串連條件下推功能。
ON：在所有節點都開啟串連條件下推功能。
OFF：在所有節點都關閉串連條件下推功能。

樣本

原查詢：

原查詢中，由於對os這個derived table需要全量物化且沒有高選擇率的過濾條件，因此os表的物化要花費大量的時間，該查詢的執行時間大約為65秒。

SELECT *
FROM (
  SELECT *
  FROM sample_table.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM sample_table.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN (
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM sample_table.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
  ) os
  ON od.id = os.detail_id;

變換後的查詢：

在完成串連條件下推後，原本od表與os表的串連條件od.id = os.detail_id，被下推到os表內部。此時，os表可以更高效的利用detail_id的索引過濾大量的資料，達到更高的執行效率，該查詢的執行時間大約為0.5秒。

SELECT *
FROM (
  SELECT *
  FROM db_order.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM db_order.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN LATERAL((
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM db_order.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
      AND od.id = detail_id
  )) os;