京東雲TiDB SQL層的背景介紹 從總體上概括 TiDB 和 MySQL 相容策略，如下表： SQL層的架構 用戶的 SQL 請求會直接或者通過 Load Balancer 發送到 京東雲TiDB Server，TiDB Server 會解析 MySQL Protocol Packet，獲取請求內 ...
京東雲TiDB SQL層的背景介紹
從總體上概括 TiDB 和 MySQL 相容策略，如下表：
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 協議，使遷移使用成本降到極低。
作者：趙玉龍
