本文介紹了執行計畫管理的工作流程和執行計畫調優執行個體。
對於每一條SQL，最佳化器都會產生相應執行計畫。很多情況下，應用請求的SQL都是重複的（僅參數不同），參數化之後的SQL完全相同。這時，可以按照參數化之後的SQL構造一個緩衝，將除了參數以外的各種資訊（比如執行計畫）緩衝起來，稱為執行計畫緩衝（Plan Cache）。
對於較複雜的查詢（例如涉及到多個表的Join），為了使其執行計畫能保持相對穩定，不因為版本升級等原因發生變化，PolarDB-X會為每個SQL記錄一組執行計畫，該執行計畫會被持久化地儲存，即使版本升級也會保留，此過程稱為執行計畫管理（Plan Management）。
工作流程
當PolarDB-X收到一條查詢SQL時，會經歷以下流程：
對查詢SQL進行參數化處理，將所有參數替換為預留位置
?。以參數化的SQL作為Key，尋找執行計畫緩衝中是否存在此SQL的緩衝，如果沒有，則調用最佳化器進行最佳化。
如果該SQL是簡單查詢，則直接執行，跳過執行計畫管理相關步驟。
如果該SQL是複雜查詢，則使用基準（Baseline）中固化的執行計畫，如果有多個，則選擇代價最低的那個。
執行計畫緩衝
PolarDB-X預設開啟執行計畫緩衝功能。EXPLAIN結果中的HitCache表示當前SQL是否命中執行計畫緩衝。開啟執行計畫緩衝後，PolarDB-X會對SQL做參數化處理，參數化會將SQL中的常量用預留位置?替換，並構建出相應的參數列表。在執行計畫中也可以看到LogicalView運算元的SQL中含有?。
執行計畫管理
對於複雜SQL，經過執行計畫緩衝之後，還會經過執行計畫管理流程。
執行計畫緩衝和執行計畫管理都是採用參數化後的SQL作為Key來尋找執行計畫，執行計畫緩衝中會緩衝所有SQL的執行計畫，而執行計畫管理僅對複雜查詢SQL進行處理。由於受到具體參數的影響，SQL模版和最優的執行計畫並非一一對應的。
在執行計畫管理中，每一條SQL對應一個基準，每個基準中包含一個或多個執行計畫。實際使用中，會根據當時的參數選擇其中代價最小的執行計畫來執行。當執行計畫緩衝中的執行計畫進入執行計畫管理時，SPM會操作一個流程判斷該執行計畫是否是已知的，是已知的話，是否代價是最小的；不是已知的話，是否需要執行以判斷該執行計畫的最佳化程度。
營運命令
PolarDB-X提供了豐富的指令集用於管理執行計畫，文法如下：
BASELINE (LOAD|PERSIST|CLEAR|VALIDATE|LIST|DELETE) [Signed Integer,Signed Integer....]
BASELINE (ADD|FIX) SQL (HINT Select Statemtnt)BASELINE (ADD|FIX) SQL <HINT> <Select Statement>：將SQL以HINT修複過後的執行計畫記錄固定下來。
BASELINE LOAD：將系統資料表中指定的基準資訊重新整理到記憶體並使其生效。
BASELINE LOAD_PLAN：將系統資料表中指定的執行計畫資訊重新整理到記憶體並使其生效。
BASELINE LIST：列出當前所有的基準資訊。
BASELINE PERSIST：將指定的基準落盤。
BASELINE PERSIST_PLAN：將指定的執行計畫落盤。
BASELINE CLEAR：清理記憶體中某個基準。
BASELINE CLEAR_PLAN：清理記憶體中某個執行計畫。
BASELINE DELETE：刪除磁碟中某個基準。
BASELINE DELETE_PLAN：刪除磁碟中某個執行計畫。
執行計畫調優實踐
資料發生變化或PolarDB-X最佳化器引擎升級後，針對同一條SQL，有可能會出現更好的執行計畫。SPM在自動演化時會將查詢計劃枚舉過程中自動探索的更優執行計畫加入到SQL的基準中。除此以外，您也可以通過SPM的指令主動最佳化執行計畫。
執行
EXPLAIN命令，發現一條SQL的執行計畫中使用的是Hash Join，並且在Baseline List的基準中，該SQL僅有這一個執行計畫。EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';返回執行計畫資訊如下：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | | HitCache:true | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.06 sec)執行以下命令，查看基準：
BASELINE LIST;返回資訊如下：
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED | +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -935671684 | Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | 0 | 1 | +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ 1 row in set (0.02 sec)假如這個SQL在某些條件下採用BKA Join（Lookup Join）會有更好的效能，那麼首先需要想辦法利用HINT引導PolarDB-X產生符合預期的執行計畫。BKA Join的HINT為
/*+TDDL:BKA_JOIN(lineitem, part)*/。通過EXPLAIN [HINT] [SQL]觀察出來的執行計畫是否符合預期：EXPLAIN /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';返回的執行計畫資訊如下：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | Gather(concurrent=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | | HitCache:false | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.14 sec)此時由於Hint的幹預，Join的演算法已修正為BKA Join。但是這並不會對基準造成變動，如果想以後每次遇到這條SQL都使用上面的計劃，還需要將其加入到基準中。
採用執行計畫管理的
Baseline Add指令為該SQL增加一個執行計畫。此時會同時有兩套執行計畫存在於該SQL的基準中，查詢計劃枚舉最佳化器會根據代價選擇一個執行計畫執行。BASELINE ADD SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';返回資訊如下：
+-------------+--------+ | BASELINE_ID | STATUS | +-------------+--------+ | -399023558 | OK | +-------------+--------+ 1 row in set (0.09 sec)執行以下命令，查看基準：
BASELINE LIST;返回資訊如下：
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED | +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -1024543942 | Gather(parallel=true) ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) Gather(concurrent=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | 0 | 1 | | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -935671684 | Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | 0 | 1 | +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ 2 rows in set (0.03 sec)通過以上
Baseline List指令展示出來的結果，可以看到基於BKA_JOIN的執行計畫已增加到該SQL的基準中。此時對這條SQL執行EXPLAIN命令，發現隨SQL中p_name LIKE ?條件變化，PolarDB-X會選擇不同的執行計畫。如果需要PolarDB-X固定使用上述的執行計畫（而非在兩個中挑選一個），可以採用
Baseline Fix指令強制PolarDB-X使用指定的執行計畫。BASELINE FIX SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';返回資訊如下：
+-------------+--------+ | BASELINE_ID | STATUS | +-------------+--------+ | -399023558 | OK | +-------------+--------+ 1 row in set (0.07 sec) mysql> baseline list\G *************************** 1. row *************************** BASELINE_ID: -399023558 PARAMETERIZED_SQL: SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? PLAN_ID: -1024543942 EXTERNALIZED_PLAN: Gather(parallel=true) ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) Gather(concurrent=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") FIXED: 1 ACCEPTED: 1 *************************** 2. row *************************** BASELINE_ID: -399023558 PARAMETERIZED_SQL: SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? PLAN_ID: -935671684 EXTERNALIZED_PLAN: Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) FIXED: 0 ACCEPTED: 1 2 rows in set (0.01 sec)再次執行
EXPLAIN命令，查看執行計畫。EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';返回執行計畫資訊如下：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | Gather(concurrent=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | | HitCache:true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.01 sec)Baseline Fix指令執行完後，可以看到BKA Join執行計畫的Fix狀態位已被置為1。此時即使不加HINT，任意條件下對這條SQL執行EXPLAIN命令，都一定會採用這個執行計畫。