作者：京東零售 肖勇
一、 前言導讀
TiDB作為NewSQL，其在對MySQL（SQL92協議）的相容上做了很多，MySQL作為當下使用較廣的事務型資料庫，在IT界尤其是網際網路間使用廣泛，那麼對於開發人員來說，1）兩個資料庫產品在SQL開發及調優的過程中，都有哪些差異？在系統遷移前需要提前做哪些準備？ 2）TiDB的執行計劃如何檢視，如何SQL調優？ 本文做了一個簡要歸納，歡迎查閱交流。
二、 建表SQL語法差異&最佳化建議
三、 查詢SQL語法差異&最佳化建議
四、 SQL執行計劃差異&最佳化建議
五、 TiDB執行計劃分析簡介
1. 在開始實際案例分析前，我們先看下執行計劃中每列的含義：
2. 執行計劃最佳化的幾個關鍵點：
1) 重點觀察運算元型別，儘量控制最佳化器選擇效能較優的運算元，讀取磁碟記錄的幾個運算元效能：TableFullScan>TableRangeScan>TableRowIDScan，IndexFullScan>IndexRangeScan
2) 儘量減小root層執行動作，下放至tikv或tiflash執行，執行計劃中task屬性包括root task和cop task，其中root標識動作由tidb聚合層執行（此操作除了需要等待各分片結果外，一般部署結構中tidb資源也較tikv或tiflash少），cop標識動作下放至tikv或tiflash各分片單獨執行
3) 保證表分析資料完整性，避免大批次資料短時間內新增/刪除，estRows為執行引擎根據情況返回的預估記錄條數，特別注意：若operator info出現stats:pseudo，則標識表基本資訊不完善（無法提供準確執行計劃評估），後續可透過analyze表重新收集分析資料，或顯示use index對sql顯示最佳化
4) 根據實際業務（如：列模式資料統計），增加tiflash模組，透過空間換時間，提升結構化查詢和實時分析能力
3. 實際場景分析
下面我們透過2個實際SQL說說TiDB的執行計劃：
l SQL1
1：IndexLookUp運算元：根據索引獲取結果記錄
2 & 3：Build運算元總是優先於Probe運算元執行，*2 運算元根據條件從索引中獲取資料，*3運算元在結果中匹配結果
4：TableRowIdScan：透過 *3 運算元結果中的表主鍵id從TiKV獲取行記錄
5：cop【tikv】標識將計算邏輯從tidb下放到tikv執行，同理還會有cop【tiflash】
6：tikv透過範圍索引掃描出對應記錄
7：根據id獲取行記錄後直接返回上層，無需排序
------------------------------------------------------------------------------------------------------------------------------
l SQL2
最佳化前，兩表直接join：
explain analyze SELECT m.id AS id, m.order\_id AS orderId, s.status AS status,m.sendpay\_map as sendPayMap FROM tableA m LEFT JOIN tableB s on m.order\_id = s.order\_id WHERE m.id >= 100 AND m.id <= 100000000 and m.warehouse\_id in (111,222) and s.status in (100, 200, 300, 400) and m.is\_valid = 1 order by m.id desc limit 20,20;
1：IndexJoin運算元：根據表s索引，與表m關聯起來
2 & 3：Build運算元總是優先於Probe運算元執行，*2 運算元從表m匹配相關記錄，*3運算元透過表s索引獲取join管理資料
4 & 5：基於*3運算元join後的結果，篩選匹配s表條件的記錄
6 & 7：可以看到此處表記錄查詢使用了TableReader，耗時6.41s（其中cop\_task共424個，且使用了大量索引proc\_keys），Selection_98根據索引回表查詢更是讀取了3.03GB記錄
總結：整體sql因為是先join在limit，tidb無法將limit操作下推，導致主表大量回表查詢，影響效能
最佳化後，先子查詢再join：
explain analyze select * from (SELECT m.id AS id, m.order\_id AS orderId,m.sendpay\_map as sendPayMap FROM tableA m WHERE m.id >= 100 AND m.id <= 100000000 and m.warehouse\_id in (111 ,222) and m.is\_valid = 1 order by m.id desc limit 20,20) t LEFT JOIN tableB s on t.orderId = s.order_id WHERE s.status in (100 ,200, 300, 400)
1：IndexJoin運算元：根據表s索引，與表m關聯起來
2：從m表結果中獲取前20條記錄
3：透過表s索引獲取join管理資料
4：根據條件，從表m的索引中獲取記錄
5：從*4運算元結果中獲取40條記錄（tikv3副本，從2個分片各獲取20條，共40條）
6 & 7：基於*3運算元join後的結果，篩選匹配s表條件的記錄
9：可以看到，此處是直接從IndexLookUp\_57索引中查詢資料，cop\_task=1，且rocksdb中命中了快取cache\_hit\_count=11
總結：整體sql因為是先limit再join，tidb將limit下推至tikv，大大較少了主表的回表查詢資料量，提升效能
六、 小結
本文旨在透過TiDB和MySQl在SQL層面的差異性講解，幫助讀者在DB遷移和評估前，清楚瞭解雙方的差異，避免遺漏。同時，針對TiDB的執行計劃，透過簡介和2個案例，幫助大家快速分析SQL執行情況，以便針對性最佳化。