TiDB 查詢優化及調優系列（四）查詢執行計劃的調整及優化原理

本章節會介紹在優化器產生的查詢執行計劃和預期不符時，如何通過 TiDB 提供的調優手段來調整及穩定查詢計劃。本篇文章為查詢執行計劃的調整及優化原理解析，主要會介紹如何通過使用 HINT 來調整查詢的執行計劃，以及如何利用 TiDB SPM 來繫結查詢語句的查詢執行計劃；最後將介紹一些規劃中的功能。

相關閱讀：

TiDB 查詢優化及調優系列（一）TiDB 優化器簡介

TiDB 查詢優化及調優系列（二）TiDB 查詢計劃簡介

TiDB 查詢優化及調優系列（三）慢查詢診斷監控及排查

使用 HINT 調整查詢執行計劃
當優化器選擇了非預期或不優的執行計劃，使用者需要使用 Hint 進行執行計劃的調整。TiDB 相容了 MySQL 的 USE INDEX，FORCE INDEX，IGNORE INDEX 語法，同時開發了 TiDB 自身的 Optimizer Hints 語法，它基於 MySQL 5.7 中介紹的類似 comment 的語法，例如 /+ TIDB_XX(t1, t2) / 。下面是 TiDB 目前支援的 Hint 語法列表：

使用 USE INDEX, FORCE INDEX, IGNORE INDEX
與 MySQL 類似, 沒有使用預期索引的查詢計劃是慢查詢的常見原因，這時就要用 USE INDEX 指定查詢用的索引，例如下面例子 USE/FORCE INDEX 使得原本全表掃描的 SQL 變成了通過索引掃描。

2 rows in set (0.00 sec)

3 rows in set (0.00 sec)

3 rows in set (0.00 sec)
下面的例子 IGNORE INDEX 使得原本走索引的 SQL 變成了全表掃描

2 rows in set (0.00 sec)

3 rows in set (0.00 sec)
和 MySQL 不同的是, 目前 TiDB 並沒有對 USE INDEX 和 FORCE INDEX 做區分。當表上有多個索引時，建議使用 USE INDEX 。TiDB 的表都比較大，analyze table會對叢集效能造成較大影響，因此無法頻繁更新統計資訊。這時就要用 USE INDEX 保證查詢計劃的正確性

使用 JOIN HINT
TiDB 目前表 Join 的方式有 Sort Merge Join，Index Nested Loop Join，Hash Join，具體的每個 join 方式的實現細節可以參考 TiDB原始碼閱讀系列 語法：

TIDB_SMJ(t1, t2)
SELECT /+ TIDB_SMJ(t1, t2) / * from t1，t2 where t1.id = t2.id;
提示優化器使用 Sort Merge Join 演算法，簡單來說，就是將 Join 的兩個表，首先根據連線屬性進行排序，然後進行一次掃描歸併, 進而就可以得出最後的結果，這個演算法通常會佔用更少的記憶體，但執行時間會更久。 當資料量太大，或系統記憶體不足時，建議嘗試使用。

TIDB_INLJ(t1, t2)
SELECT /+ TIDB_INLJ(t1, t2) / * from t1，t2 where t1.id = t2.id;
提示優化器使用 Index Nested Loop Join 演算法，Index Look Up Join 會讀取外表的資料，並對內表進行主鍵或索引鍵查詢，這個演算法可能會在某些場景更快，消耗更少系統資源，有的場景會更慢，消耗更多系統資源。對於外表經過 WHERE 條件過濾後結果集較小（小於 1 萬行）的場景，可以嘗試使用。TIDB_INLJ() 中的引數是建立查詢計劃時，內表的候選表。即 TIDB_INLJ(t1) 只會考慮使用 t1 作為內表構建查詢計劃

TIDB_HJ(t1, t2)
SELECT /+ TIDB_HJ(t1, t2) / * from t1，t2 where t1.id = t2.id;
提示優化器使用 Hash Join 演算法，簡單來說，t1 表和 t2 表的 Hash Join 需要我們選擇一個 Inner 表來構造雜湊表，然後對 Outer 表的每一行資料都去這個雜湊表中查詢是否有匹配的資料這個演算法多執行緒併發執行，執行速度較快，但會消耗較多記憶體。

另外其他的 hint 語法也在開發中如 /+ TIDB_STREAMAGG() / ，/+ TIDB_HASHAGG() / 等。

使用 Hint 通常是在執行計劃發生變化的時候，通過修改 SQL 語句調整執行計劃行為，但有的時候需要在不修改 SQL 語句的情況下干預執行計劃的選擇。 執行計劃繫結 提供了一系列功能使得可以在不修改 SQL 語句的情況下選擇指定的執行計劃。

使用 MAX_EXECUTION_TIME(N)
在 SELECT 等語句中可以使用 MAX_EXECUTION_TIME(N)，它會限制語句的執行時間不能超過 N 毫秒，否則伺服器會終止這條語句的執行。 例如，下面例子設定了 1 秒超時

SELECT /+ MAX_EXECUTION_TIME(1000) / * FROM t1
此外，環境變數 MAX_EXECUTION_TIME也會對語句執行時間進行限制。 對於高可用和時間敏感的業務， 建議使用 MAX_EXECUTION_TIME，免錯誤的查詢計劃或 bug 影響整個 TiDB 叢集的效能甚至穩定性。 OLTP 業務查詢超時一般不超過 5 秒。 需要注意的是，MySQL jdbc 的查詢超時設定對 TiDB 不起作用。現實客戶端感知超時時，向資料庫傳送一個 KILL 命令， 但是由於 tidb-server 是負載均衡的， 為防止在錯誤的 tidb-server 上終止連線， tidb-server 不會執行這個 KILL。這時就要用 MAX_EXECUTION_TIME保證查詢超時的效果。

使用 SPM 繫結查詢執行計劃
執行計劃是影響 SQL 執行效能的一個非常關鍵的因素，SQL 執行計劃的穩定性也對整個叢集的效率有著非常大的影響。然而，當出現類似統計資訊過時、新增或者刪除了索引等情況時，優化器並不能確保一定生成一個很好的執行計劃。此時執行計劃可能發生預期外的改變，導致執行時間過長。因此 TiDB 提供了 SQL Plan Management 功能，用於為某些型別的 SQL 繫結執行計劃（SQL Bind），並且被繫結的執行計劃會根據資料的變化而不斷地演進（注：演進功能尚未 GA）。

SQL Bind 是 SQL Plan Management 的第一步。使用它，使用者可以為某一型別的 SQL 繫結執行計劃。當出現執行計劃不優時，可以使用 SQL Bind 在不更改業務的情況下快速地對執行計劃進行修復。 建立繫結可以使用如下的 SQL：

CREATE [GLOBAL | SESSION] BINDING FOR SelectStmt USING SelectStmt;
該語句可以在 GLOBAL 或者 SESSION 作用域內為 SQL 繫結執行計劃。在不指定作用域時，預設作用域為 SESSION。被繫結的 SQL 會被引數化，然後儲存到系統表中。在處理 SQL 查詢時，只要引數化後的 SQL 和系統表中某個被繫結的 SQL 匹配即可使用相應的優化器 Hint。

“引數化” 指的是把 SQL 中的常量用 "?" 替代，統一語句中的大小寫，清理掉多餘的空格、換行符等操作。 建立一個繫結的例子：

TiDB(root@127.0.0.1:test) > create binding for select from t where a = 1 using select from t use index(idx_a) where a = 1;
Query OK, 0 rows affected (0.00 sec)
檢視剛才建立的 binding，下面輸出結果中 Original_sql 即為引數化後的 SQL：

1 row in set (0.00 sec)
如果要刪除建立的 binding 可通過如下語句：

TiDB(root@127.0.0.1:test) > drop binding for select * from t where a = 1;
Query OK, 0 rows affected (0.00 sec)

TiDB(root@127.0.0.1:test) > show bindings;
Empty set (0.00 sec)
為了解決只能手動建立 Binding 的問題，4.0 版本中 TiDB 提供了自動建立 Binding 功能，通過將 tidb_capture_plan_baselines 變數的值設定為 on，就可以自動為某一段時間內出現多次的 SQL 去建立繫結。TiDB 會為那些出現了至少兩次的 SQL 建立繫結，統計 SQL 的出現次數依賴 TiDB 4.0 版本中提供的 Statements Summary 功能。可通過如下方法開啟自動為出現了兩次以上的 SQL 建立繫結的開關：

set tidb_enable_stmt_summary = 1; -- 開啟 statement summary
set tidb_capture_plan_baselines = 1; -- 開啟自動繫結功能
接著連續跑兩遍如下查詢即可自動為其建立一條繫結：

TiDB(root@127.0.0.1:test) > select * from t;
Empty set (0.01 sec)

TiDB(root@127.0.0.1:test) > select * from t;
Empty set (0.00 sec)
再檢視 global bindings 即可發現自動建立的 binding：

1 row in set (0.00 sec)
其它優化器開發或規劃中的診斷調優功能
針對查詢執行計劃的監控，診斷，排查，和調優，除了上述章節介紹的方法和功能外，TiDB 優化器開發了一些內部使用功能，同時目前還在開發或規劃開發更多的相關功能，將在後續版本中釋出。這些功能包括但不限於：

Plan Change Capture: 用於驗證在升級中是否會引起查詢執行計劃迴歸/變更；

Plan Replayer: 用於一鍵收集使用者問題查詢的相關資訊，並一鍵匯入 TiDB 用於問題復現以及查詢計劃的迴歸看護；

Optimizer Trace: 用於收集和監控優化器內部優化邏輯流程，提升使用者現場的問題診斷能力和效率，併為後續的基於診斷監控的反饋優化提供資料輸入；

Visual Explain: 圖形化展示查詢計劃，特別是對於複雜查詢的執行計劃檢視可以提升效率，並可在後續整合更多診斷資訊；

Optimizer Diagnosis and Advisor: 優化器自診斷和優化建議功能；並與 TiDB Dashboard, Auto Pilot 等整合；

SPM 擴充套件：增加多基線計劃版本繫結，改進完善繫結計劃演進；

Plan Hint：完善並提供更豐富的 Plan Hint；

本文為「TiDB 查詢優化及調優」系列文章的第四篇，詳細介紹瞭如何通過 TiDB HINT 和 SPM 對查詢執行計劃進行調整和優化，簡要列舉了其他優化器開發或規劃中的診斷調優功能等。下篇文章為系列文章的最後一篇，將通過幾個具體的案例介紹 TiDB 查詢優化的實踐。

如果您對 TiDB 的產品有任何建議，歡迎來到 internals.tidb.io 與我們交流。