子查詢的去關聯化最佳化與執行機制-雲原生資料庫 PolarDB-阿里雲

子查詢是指在父查詢的WHERE子句或HAVING子句中嵌套另一個SELECT語句的查詢，本文主要介紹PolarDB-X如何最佳化和執行子查詢。

基本概念

根據是否存在關聯項，子查詢可以分為非關聯子查詢和關聯子查詢。非關聯子查詢是指該子查詢的執行不依賴外部查詢的變數，這種子查詢一般只需要計算一次；而關聯子查詢中存在引用自外層查詢的變數，邏輯上，這種子查詢需要每次帶入相應的變數、計算多次。

/* 非關聯子查詢 */
SELECT * FROM lineitem WHERE l_partkey IN (SELECT p_partkey FROM part);

/* 關聯子查詢（l_suppkey 是關聯項） */
SELECT * FROM lineitem WHERE l_partkey IN (SELECT ps_partkey FROM partsupp WHERE ps_suppkey = l_suppkey);

PolarDB-X子查詢支援絕大多數的子查詢寫法，具體參見SQL使用限制。

子查詢最佳化

對於多數常見的子查詢形式，PolarDB-X可以將其改寫為高效的SemiJoin或類似的基於JOIN的計算方式。這樣做的好處是顯而易見的。當資料量較大時，無需真正帶入不同參數迴圈迭代，大大降低了執行代價。這種查詢改寫技術稱為子查詢的去關聯化（Unnesting）。

從以下子查詢去關聯化的樣本中，可以看到執行計畫使用JOIN代替了子查詢。

EXPLAIN SELECT p_partkey, (
      SELECT COUNT(ps_partkey) FROM partsupp WHERE ps_suppkey = p_partkey
      ) supplier_count FROM part;

返回執行計畫資訊如下：

Project(p_partkey="p_partkey", supplier_count="CASE(IS NULL($10), 0, $9)", cor=[$cor0])
  HashJoin(condition="p_partkey = ps_suppkey", type="left")
    Gather(concurrent=true)
      LogicalView(tables="part_[0-7]", shardCount=8, sql="SELECT * FROM `part` AS `part`")
    Project(count(ps_partkey)="count(ps_partkey)", ps_suppkey="ps_suppkey", count(ps_partkey)2="count(ps_partkey)")
      HashAgg(group="ps_suppkey", count(ps_partkey)="SUM(count(ps_partkey))")
        Gather(concurrent=true)
          LogicalView(tables="partsupp_[0-7]", shardCount=8, sql="SELECT `ps_suppkey`, COUNT(`ps_partkey`) AS `count(ps_partkey)` FROM `partsupp` AS `partsupp` GROUP BY `ps_suppkey`")

某些情境下，PolarDB-X無法將子查詢進行去關聯化，這時會採用迭代執行的方式。如果外層查詢資料量很大，迭代執行可能會非常慢。

如下樣本由於OR l_partkey < 50的存在，導致子查詢無法被去關聯化，因而採用了迭代執行，這種情形下，建議改寫SQL去掉子查詢的OR條件。

EXPLAIN SELECT * FROM lineitem WHERE l_partkey IN (SELECT ps_partkey FROM partsupp WHERE ps_suppkey = l_suppkey) OR l_partkey IS NOT

Filter(condition="IS(in,[$1])[29612489] OR l_partkey < ?0")
  Gather(concurrent=true)
    LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.lineitem_[0-7]", shardCount=8, sql="SELECT * FROM `lineitem` AS `lineitem`")

>> individual correlate subquery : 29612489
Gather(concurrent=true)
  LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.partsupp_[0-7]", shardCount=8, sql="SELECT * FROM (SELECT `ps_partkey` FROM `partsupp` AS `partsupp` WHERE (`ps_suppkey` = `l_suppkey`)) AS `t0` WHERE (((`l_partkey` = `ps_partkey`) OR (`l_partkey` IS NULL)) OR (`ps_partkey` IS NULL))")