使用下推計算增加索引和最佳化執行計畫調優SQL-雲原生資料庫 PolarDB-阿里雲

找出需調優的慢SQL後，先通過EXPLAIN查看執行計畫，然後通過如下方法最佳化SQL：下推更多計算至儲存層MySQL，適當增加索引，最佳化執行計畫。

下推更多的計算

PolarDB-X 1.0會儘可能將更多的計算下推到儲存層MySQL。下推計算能夠減少資料轉送，減少網路層和PolarDB-X 1.0層的開銷，提升SQL語句的執行效率。PolarDB-X 1.0支援下推幾乎所有運算元，包括：

過濾條件，如WHERE或HAVING中的條件。
彙總運算元，如COUNT，GROUP BY等，會分成兩個階段進行彙總計算。
排序運算元，如ORDER BY。
JOIN和子查詢，兩邊JOIN Key的分區方式必須一樣，或其中一邊為廣播表。

如下樣本講解如何將更多的計算下推到MySQL來加速執行。

> EXPLAIN SELECT * FROM customer, nation WHERE c_nationkey = n_nationkey AND n_regionkey = 3;
Project(c_custkey="c_custkey", c_name="c_name", c_address="c_address", c_nationkey="c_nationkey", c_phone="c_phone", c_acctbal="c_acctbal", c_mktsegment="c_mktsegment", c_comment="c_comment", n_nationkey="n_nationkey", n_name="n_name", n_regionkey="n_regionkey", n_comment="n_comment")
  BKAJoin(condition="c_nationkey = n_nationkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="nation", shardCount=2, sql="SELECT * FROM `nation` AS `nation` WHERE (`n_regionkey` = ?)")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT * FROM `customer` AS `customer` WHERE (`c_nationkey` IN ('?'))")

上述執行計畫中出現了BKAJOIN，BKAJOIN的含義是每次從左表擷取一批資料，就會拼成一個IN查詢取出右表相關聯的行，並在最終執行JOIN操作。由於左表的資料量較大，需要進行多次資料提取，導致執行速度較慢。

無法下推JOIN的原因是：當前情況下，customer表是按照主鍵c_custkey進行切分的，而nation表是按照主鍵n_nationkey進行切分的。在本查詢中，JOIN Key為c_nationkey和n_nationkey，由於兩側的JOIN Key分區方式不同，因此導致下推失敗。

考慮到nation表資料量並不大且幾乎沒有修改操作，可以將其重建成如下廣播表：

CREATE TABLE `nation` (
  `n_nationkey` int(11) NOT NULL,
  `n_name` varchar(25) NOT NULL,
  `n_regionkey` int(11) NOT NULL,
  `n_comment` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`n_nationkey`)
) BROADCAST;  --- 聲明為廣播表

修改後，可以看到執行計畫中不再出現JOIN，幾乎所有計算都被下推到儲存層MySQL執行（LogicalView中），而上層僅僅是將結果收集並返回給使用者（Gather運算元），執行效能大大增強。

> EXPLAIN SELECT * FROM customer, nation WHERE c_nationkey = n_nationkey AND n_regionkey = 3;

Gather(concurrent=true)
  LogicalView(tables="customer_[0-7],nation", shardCount=8, sql="SELECT * FROM `customer` AS `customer` INNER JOIN `nation` AS `nation` ON ((`nation`.`n_regionkey` = ?) AND (`customer`.`c_nationkey` = `nation`.`n_nationkey`))")

更多關於下推的原理和最佳化，請參見查詢改寫與下推。

增加索引

如果下推SQL中出現（物理）慢SQL，可以給分表增加索引來解決，這裡不再詳述。

PolarDB-X 1.0自5.4.1版本開始支援全域二級索引，可以通過增加GSI的方式使邏輯表擁有多個拆分維度。

下面以一個慢SQL作為樣本來講解如何通過GSI下推更多運算元。

> EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders, customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-11' AND o_totalprice > 100;

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  HashJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` > ?))")

執行計畫中，orders按照o_orderkey拆分而customer按照c_custkey拆分，由於拆分維度不同JOIN運算元不能下推。

考慮到2019-11-11當天總價高於100的訂單非常多，跨分區JOIN耗時很高，需要在orders表上建立一個GSI來使得JOIN運算元可以下推。

查詢中使用到了orders表的o_orderkey，o_custkey，o_orderdate，o_totalprice四列，其中o_orderkey，o_custkey分別是主表和索引表的拆分鍵，o_orderdate，o_totalprice作為覆蓋列包含在索引中用於避免回表。

> CREATE GLOBAL INDEX i_o_custkey ON orders(`o_custkey`) covering(`o_orderdate`, `o_totalprice`)
        DBPARTITION BY HASH(`o_custkey`) TBPARTITION BY HASH(`o_custkey`) TBPARTITIONS 4;

增加GSI並通過force index(i_o_custkey)強制使用索引後，跨分區JOIN變為MySQL上的局部JOIN （IndexScan中），並且通過覆蓋列避免了回表操作，查詢效能得到提升。

> EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders FORCE INDEX(i_o_custkey), customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-11' AND o_totalprice > 100;

Gather(concurrent=true)
  IndexScan(tables="i_o_custkey_[0-7],customer_[0-7]", shardCount=8, sql="SELECT `i_o_custkey`.`o_orderkey`, `customer`.`c_custkey`, `customer`.`c_name` FROM `i_o_custkey` AS `i_o_custkey` INNER JOIN `customer` AS `customer` ON (((`i_o_custkey`.`o_orderdate` = ?) AND (`i_o_custkey`.`o_custkey` = `customer`.`c_custkey`)) AND (`i_o_custkey`.`o_totalprice` > ?))")

更多關於全域二級索引的使用細節，請參見使用全域二級索引。

執行計畫調優

說明

以下內容適用於PolarDB-X 1.0 5.3.12及以上版本。

大多數情況下，PolarDB-X 1.0的查詢最佳化工具可以自動產生最佳的執行計畫。但少數情況下，可能因為統計資訊存在缺失、誤差等，導致產生的執行計畫不夠好，這時可以通過Hint來幹預最佳化器行為，使之產生更好的執行計畫。

如下樣本將講解執行計畫的調優。

下面的查詢，PolarDB-X 1.0查詢最佳化工具綜合了JOIN兩邊的代價。

> EXPLAIN SELECT o_orderkey, c_custkey, c_name FROM orders, customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  HashJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer`")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")

但是，實際上2019-11-15這一天總價低於10美元的訂單數量很小，只有幾條，這時候用BKAJOIN是比Hash JOIN更好的選擇（關於BKAJOIN和Hash JOIN的介紹，請參見JOIN與子查詢的最佳化和執行）。

通過如下/*+TDDL:BKA_JOIN(orders, customer)*/Hint強制最佳化器使用BKAJOIN（LookupJOIN）：

> EXPLAIN /*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer
          WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

Project(o_orderkey="o_orderkey", c_custkey="c_custkey", c_name="c_name")
  BKAJoin(condition="o_custkey = c_custkey", type="inner")
    Gather(concurrent=true)
      LogicalView(tables="orders_[0-7]", shardCount=8, sql="SELECT `o_orderkey`, `o_custkey` FROM `orders` AS `orders` WHERE ((`o_orderdate` = ?) AND (`o_totalprice` < ?))")
    Gather(concurrent=true)
      LogicalView(tables="customer_[0-7]", shardCount=8, sql="SELECT `c_custkey`, `c_name` FROM `customer` AS `customer` WHERE (`c_custkey` IN ('?'))")

可以選擇執行加如下Hint的查詢：

/*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15' AND o_totalprice < 10;

以上操作加快了SQL查詢速度。為了讓Hint發揮作用，可以將應用中的SQL加上Hint，或者更方便的方式是使用執行計畫管理（Plan Management）功能對該SQL固定執行計畫。具體操作如下：

BASELINE FIX SQL /*+TDDL:BKA_JOIN(orders, customer)*/ SELECT o_orderkey, c_custkey, c_name FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate = '2019-11-15';

這樣一來，對於這條SQL（參數可以不同），PolarDB-X 1.0都會採用如上固定的執行計畫。

更多關於執行計畫管理的資訊，請參見執行計畫管理。