京東雲 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 taskdON 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 協議，使遷移使用成本降到極低。

作者：趙玉龍