TiDB作為NewSQL，其在對MySQL（SQL92協議）的相容上做了很多，MySQL作為當下使用較廣的事務型資料庫，在IT界尤其是互聯網間使用廣泛，那麼對於開發人員來說，1）兩個資料庫產品在SQL開發及調優的過程中，都有哪些差異？在系統遷移前需要提前做哪些準備？ 2）TiDB的執行計劃如何查看，... ...
作者：京東零售 肖勇
一、 前言導讀
TiDB作為NewSQL，其在對MySQL（SQL92協議）的相容上做了很多，MySQL作為當下使用較廣的事務型資料庫，在IT界尤其是互聯網間使用廣泛，那麼對於開發人員來說，1）兩個資料庫產品在SQL開發及調優的過程中，都有哪些差異？在系統遷移前需要提前做哪些準備？ 2）TiDB的執行計劃如何查看，如何SQL調優？ 本文做了一個簡要歸納，歡迎查閱交流。
二、 建表SQL語法差異&優化建議
| 分類 | MySQL寫法 | TiDB寫法 | 註意事項 |
| 建表 | alter table A add column phone bigint(20),add column address varchar(100)； | alter table A add column phone bigint(20)； alter table A add column address varchar(100)； | 1.一個DDL腳本僅支持一個欄位修改 2.新建表時，儘量提前規劃好相應欄位 |
| 建表 | create table A(`id` bigint(20) NOT NULL AUTO_INCREMEN) | create table A(`id` bigint(20) NOT NULL AUTO_INCREMEN) | TiDB自增主鍵全局唯一，但不嚴格遞增（僅各Server內部連續） 需要嚴格連續自增主鍵時，業務系統自己生成寫入 |
| 建表 | create table A as select * from B | 不支持 | |
| 建表 | create temporary table A | 不支持 | 不支持臨時表 |
| SQL DML提交前，建議結合explain和explain analyze命令和業務場景，確認執行計劃 |
三、 查詢SQL語法差異&優化建議
| 分類 | MySQL寫法 | TiDB寫法 | 註意事項 |
| 查詢 （結果條數統計） | select * from A select count() from A | select name,age,address from A select count(age) from A | 1.避免全量欄位查詢，節省網路帶寬 2.當開啟TiFlash統計行數據時，TiDB會使用列模式提升查詢性能 |
| 查詢 （閉區間查詢） | select name,age from A where age>10 | select name,age from A where age>10 and id<99 | TiDB針對限定數據範圍的閉區間查詢，能減少全表掃描概率 |
| 查詢 （時間排序） | select name,age from A order by id（主鍵） | select name,age from A order by create_time（時間索引） | 分散式資料庫主鍵不再連續，需要時間順序排序時，可新增時間欄位 |
| 查詢 （結果欄位分堆） | select name,age from A group by name | select name,age from A group by name,age | 需要分堆的所有欄位，在SQL中必須顯示標識 |
| 查詢 （結果欄位排序） | select name,age from A order by name | select name,age from A order by name,age | 需要排序的所有欄位，在SQL中必須顯示標識 |
| 查詢 （索引優化） | select name,age from A where name=‘張三’ and age>110 and cityName!='北京' | 儘可能的將使用頻率高的，經常被點查使用的列排在前面，將經常進行範圍查詢的列排在後面 |
| 查詢 （顯示優化規則） DBA不建議 | select name,age from A where name='張三' | select name,age from A where name='張三' use index（name_age） | 顯示通知TiDB優化器，使用name_age索引 |
| 查詢 （覆蓋索引） | select name,age from A where name='張三' order by age | ORDER BY，GROUP BY，DISTINCT 的欄位需要添加在索引的後面，形成覆蓋索引 |
| 查詢 （顯示優化規則） DBA不建議 | select name,age from A where name='張三' | select /+ read_from_storage(tiflash[A]) */ name,age from A where name='張三' | 顯示通知TiDB優化器，使用TiFlash提升性能 |
| MySQL常見SQL優化規則（如not in，like ‘abc%’，減少查詢返回列，避免在索引列使用函數），對於TiDB同樣適用 |
四、 SQL執行計劃差異&優化建議
| 分類 | MySQL寫法 | TiDB寫法 | 註意事項 |
| 執行計劃 | explain select count() from A | explain select count() from A explain analyze select count(*) from A | 1.TiDB提供explain和explain analyze兩種查詢計劃分析，前者不會執行，後者會實際執行 2.explain參考：https://docs.pingcap.com/zh/tidb/stable/explain-walkthrough 3.explain analyze參考：https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain-analyze/ |
| 查詢 （結果分析優化） | operator中包含stats:pseudo | SQL對應表統計信息已失真，執行analyze tableName修複即可（註：關註數據期間卡表修複對業務的影響） |
| 查詢 （類型優化） | select name,age from A where zip=0 （其中zip為bit類型） | select name,age from A where zip=0 （修改zip為int類型） | TiDB欄位儘量使用常見mysql類型 |
| 註意：analyze tableName對TiDB集群的影響較大，執行前千萬與DBA做好溝通評估，臨時情況可通過顯示指定索引（USE INDEX）繞開流量高峰期 |
五、 TiDB執行計劃分析簡介
1. 在開始實際案例分析前，我們先看下執行計劃中每列的含義：
| 屬性名 | 含義 |
| id | 運算元的 ID，是運算元在整個執行計劃中唯一的標識。在 TiDB 2.1 中，ID 會格式化地顯示運算元的樹狀結構。數據從孩子結點流向父親結點，每個運算元的父親結點有且僅有一個。 |
| estRows | 運算元預計將會輸出的數據條數，基於統計信息以及運算元的執行邏輯估算而來。 |
| actRows | 運算元實際輸出的數據條數 |
| task | 運算元屬於的 task 種類。目前的執行計劃分成為兩種 task，一種叫 root task，在 tidb-server 上執行，一種叫 cop task，在 TiKV 或者 TiFlash 上並行執行。當前的執行計劃在 task 級別的拓撲關係是一個 root task 後面可以跟許多 cop task，root task 使用 cop task 的輸出結果作為輸入。cop task 中執行的也即是 TiDB 下推到 TiKV 或者 TiFlash 上的任務，每個 cop task 分散在 TiKV 或者 TiFlash 集群中，由多個進程共同執行。 |
| access object | 運算元所訪問的數據項信息。包括表 table，表分區 partition 以及使用的索引 index（如果有）。只有直接訪問數據的運算元才擁有這些信息。 |
| execution info | 運算元的實際執行信息。time 表示從進入運算元到離開運算元的全部 wall time，包括所有子運算元操作的全部執行時間。如果該運算元被父運算元多次調用 (loops)，這個時間就是累積的時間。loops 是當前運算元被父運算元調用的次數。 |
| operator info | 運算元的其它信息。各個運算元的 operator info 各有不同，可參考下麵的示例解讀。 |
| memory | 運算元占用記憶體空間的大小 |
| disk | 運算元占用磁碟空間的大小 |
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執行情況，以便針對性優化。
