京東雲TiDB SQL優化的最佳實踐

京東雲TiDB SQL層的背景介紹

從總體上概括 TiDB 和 MySQL 相容策略，如下表：

通訊協議	SQL語法	功能和行為
完全相容	相容絕大多數	相容大多數

SQL 層的架構

使用者的 SQL 請求會直接或者通過 Load Balancer 傳送到 京東雲TiDB Server，TiDB Server 會解析 MySQL Protocol Packet，獲取請求內容，對 SQL 進行語法解析和語義分析，制定和優化查詢計劃，執行查詢計劃並獲取和處理資料。資料全部儲存在 TiKV 叢集中，所以在這個過程中 TiDB Server 需要和 TiKV 互動，獲取資料。最後 TiDB Server 需要將查詢結果返回給使用者。

一條SQL的生命週期圖

● SQL優化流程的概覽

在 TiDB 中，從輸入的查詢文字到最終的執行計劃執行結果的過程可以見下圖：

在經過了 parser 對原始查詢文字的解析以及一些簡單的合法性驗證後，TiDB 首先會對查詢做一些邏輯上的等價變化，通過這些等價變化，使得這個查詢在邏輯執行計劃上可以變得更易於處理。在等價變化結束之後，TiDB 會得到一個與原始查詢等價的查詢計劃結構，之後根據資料分佈、以及一個運算元具體的執行開銷，來獲得一個最終的執行計劃，同時，TiDB 在執行 PREPARE 語句時，可以選擇開啟快取來降低 TiDB 生成執行計劃的開銷。

● 使用 EXPLAIN 語句檢視執行計劃

執行計劃由一系列的運算元構成。和其他資料庫一樣，在 TiDB 中可通過 EXPLAIN 語句返回的結果檢視某條 SQL 的執行計劃。

目前 TiDB 的 EXPLAIN 會輸出 5 列，分別是：id，estRows，task，access object， operator info。執行計劃中每個運算元都由這 5 列屬性來描述，EXPLAIN結果中每一行描述一個運算元。每個屬性的具體含義如下：

● EXPLAIN ANALYZE 輸出格式

和 EXPLAIN 不同，EXPLAIN ANALYZE 會執行對應的 SQL 語句，記錄其執行時資訊，和執行計劃一併返回出來，可以視 為 EXPLAIN 語句的擴充套件。EXPLAIN ANALYZE 語句的返回結果中增加了 actRows, execution info,memory,disk 這幾列資訊：

舉個例子如下：

從上述例子中可以看出，優化器估算的 estRows 和實際執行中統計得到的 actRows 幾乎是相等的，說明優化器估算的行數與實際行數的誤差很小。同時 IndexLookUp_10 運算元在實際執行過程中使用了約 9 KB 的記憶體，該 SQL 在執行過程中，沒有觸發過任何運算元的落盤操作。

SQL優化案例最佳實踐

案例一：

索引的錯誤選擇導致SQL變慢的優化實踐

場景：資料庫遷移到TiDB，SQL在MySQL執行不到1S，在TiDB執行超過30S

SQL執行計劃如下：

execution info列，有該執行計劃的時間，這個SQL的表的連線順序，要從最裡面的迴圈開始看，如下圖，m,d是最先開始進行連線的：

關注下圖的time變化，執行計劃由毫秒級變成了秒級的地方，由71ms變成了33s，所以瓶頸卡在（（m join d） join taskm）join taskd 這個地方，對應的SQL片段如下：

INNER JOIN taskd ON taskd.no = d.no  AND taskd.o_no = d.o_no  AND taskd.d_no = d.d_no  AND taskd.w_no = d.w_no  AND taskd.g_no = d.g_no  AND IF(NULL = d.MD5_VALUE, 1, d.MD5_VALUE) = IF(NULL = d.MD5_VALUE, 1, taskd.MD5_VALUE)  AND taskd.yn = 0

● 優化思路

1、首先觀察 explain analyze 結果，看到慢在最內 3 層的 join 上 ，(m join d) join taskd；

2、對比 MySQL 的執行計劃，發現 MySQL 最內的 3 層的 join 是 (m join d) join taskm, 所以把相關的3張表提取出來，修改其join順序；

3、修改順序後，join 的時間能減少但是和 MySQL差距還是很大，再次觀察，發現 taskd 上TiDB和MySQL使用的索引不一樣，所以使用了 use index 來強制TIDB走和MySQL相同的索引。

案例二：

表關聯的錯誤選擇導致SQL變慢的優化實踐

場景：在MySQL執行時間毫秒級別，在TiDB執行時間18S

在TiDB的執行時間及執行計劃

優化前後的執行計劃

優化後加了hint的SQL

●  優化思路：

1. TiDB執行耗時 10+s 的原因是對 wps 表的估算不準確，導致優化器認為 w表 和 p表 走 hash join 效率更高，然後我們看到的執行計劃的主要耗時在 pri 表回表獲取資料的耗時較長 ；

2. w 表估算不準確的原因為TiDB 會把 w 的條件 有range scan 轉換點查，然後利用這個索引的統計資訊去估算；

3. 點查估算是會利用對應的 CMSketch 去進行估算，結合 p 表資料量很大，根據經驗推測可能是 CMSketch 內部 hash 衝突導致。

● 案例一、二的延伸擴充套件：

在SQL優化的工作中，經常會通過加hint的方式改變SQL的執行計劃，從而達到了優化的目的，但是缺點是對SQL進行了硬編碼，如果業務程式使用了ORM框架，SQL的改造難度會增加。SQL Binding（SPM）則很好的解決了硬編碼的問題，通過SQL Binding，DBA可以在不改變SQL文字的情況下，優化sql的執行計劃，從而達到優化的目標，從而使SQL優化變得更加優雅。

京東雲聯合 PingCAP 基於國內開源 NewSQL 資料庫 TiDB 打造的一款同時支援 OLTP 和 OLAP 兩種場景的分散式雲資料庫產品，實現了自動的水平伸縮，強一致性的分散式事務，部署簡單，線上非同步表結構變更不影響業務，同時相容 MySQL 協議，使遷移使用成本降到極低。

瞭解詳情可點選底部“ 閱讀原文 ”。

- End -