京東雲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 沖突導緻。