作者：京東零售 肖勇
一、 前言導讀
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. 在開始實際案例分析前，我們先看下執行計劃中每列的含義：
引自：https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain和https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain-analyze
| 屬性名 | 含義 |
| 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執行情況，以便針對性優化，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/550385.html
標籤：其他