一、 前言導讀

 

TiDB作為NewSQL，其在對MySQL（SQL92協(xié)議）的兼容上做了很多，MySQL作為當下使用較廣的事務型數(shù)據(jù)庫，在IT界尤其是互聯(lián)網(wǎng)間使用廣泛，那么對于開發(fā)人員來說，1）兩個數(shù)據(jù)庫產(chǎn)品在SQL開發(fā)及調(diào)優(yōu)的過程中，都有哪些差異？在系統(tǒng)遷移前需要提前做哪些準備？ 2）TiDB的執(zhí)行計劃如何查看，如何SQL調(diào)優(yōu)？ 本文做了一個簡要歸納，歡迎查閱交流。

二、 建表SQL語法差異&優(yōu)化建議

分類

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.新建表時，盡量提前規(guī)劃好相應字段

建表

create table A(`id` bigint(20) NOT NULL AUTO_INCREMEN)

create table A(`id` bigint(20) NOT NULL AUTO_INCREMEN)

TiDB自增主鍵全局唯一，但不嚴格遞增（僅各Server內(nèi)部連續(xù)） 需要嚴格連續(xù)自增主鍵時，業(yè)務系統(tǒng)自己生成寫入

建表

create table A as select * from B

不支持

建表

create temporary table A

不支持

不支持臨時表

SQL DML提交前，建議結(jié)合explain和explain analyze命令和業(yè)務場景，確認執(zhí)行計劃

三、 查詢SQL語法差異&優(yōu)化建議

分類

MySQL寫法

TiDB寫法

注意事項

查詢 （結(jié)果條數(shù)統(tǒng)計）

select * from A select count(*) from A

select name,age,address from A select count(age) from A

1.避免全量字段查詢，節(jié)省網(wǎng)絡帶寬 2.當開啟TiFlash統(tǒng)計行數(shù)據(jù)時，TiDB會使用列模式提升查詢性能

查詢 （閉區(qū)間查詢）

select name,age from A where age>10

select name,age from A where age>10 and id<99

TiDB針對限定數(shù)據(jù)范圍的閉區(qū)間查詢，能減少全表掃描概率

查詢 （時間排序）

select name,age from A order by id（主鍵）

select name,age from A order by create_time（時間索引）

分布式數(shù)據(jù)庫主鍵不再連續(xù)，需要時間順序排序時，可新增時間字段

查詢 （結(jié)果字段分堆）

select name,age from A group by name

select name,age from A group by name,age

需要分堆的所有字段，在SQL中必須顯示標識

查詢 （結(jié)果字段排序）

select name,age from A order by name

select name,age from A order by name,age

需要排序的所有字段，在SQL中必須顯示標識

查詢 （索引優(yōu)化）

select name,age from A where name=‘張三’ and age>110 and cityName!='北京'

盡可能的將使用頻率高的，經(jīng)常被點查使用的列排在前面，將經(jīng)常進行范圍查詢的列排在后面

查詢 （顯示優(yōu)化規(guī)則） DBA不建議

select name,age from A where name='張三'

select name,age from A where name='張三' use index（name_age）

顯示通知TiDB優(yōu)化器，使用name_age索引

查詢 （覆蓋索引）

select name,age from A where name='張三' order by age

ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面，形成覆蓋索引

查詢 （顯示優(yōu)化規(guī)則） DBA不建議

select name,age from A where name='張三'

select /*+ read_from_storage(tiflash[A]) */ name,age from A where name='張三'

顯示通知TiDB優(yōu)化器，使用TiFlash提升性能

MySQL常見SQL優(yōu)化規(guī)則（如not in，like ‘abc%’，減少查詢返回列，避免在索引列使用函數(shù)），對于TiDB同樣適用

四、 SQL執(zhí)行計劃差異&優(yōu)化建議

分類

MySQL寫法

TiDB寫法

注意事項

執(zhí)行計劃

explain select count(*) from A

explain select count(*) from A explain analyze select count(*) from A

1.TiDB提供explain和explain analyze兩種查詢計劃分析，前者不會執(zhí)行，后者會實際執(zhí)行 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/

查詢 （結(jié)果分析優(yōu)化）

operator中包含stats:pseudo

SQL對應表統(tǒng)計信息已失真，執(zhí)行analyze tableName修復即可（注：關(guān)注數(shù)據(jù)期間卡表修復對業(yè)務的影響）

查詢 （類型優(yōu)化）

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集群的影響較大，執(zhí)行前千萬與DBA做好溝通評估，臨時情況可通過顯示指定索引（USE INDEX）繞開流量高峰期

五、 TiDB執(zhí)行計劃分析簡介

1. 在開始實際案例分析前，我們先看下執(zhí)行計劃中每列的含義：

引自：

https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain和https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain-analyze

屬性名

含義

id

算子的 ID，是算子在整個執(zhí)行計劃中唯一的標識。在 TiDB 2.1 中，ID 會格式化地顯示算子的樹狀結(jié)構(gòu)。數(shù)據(jù)從孩子結(jié)點流向父親結(jié)點，每個算子的父親結(jié)點有且僅有一個。

estRows

算子預計將會輸出的數(shù)據(jù)條數(shù)，基于統(tǒng)計信息以及算子的執(zhí)行邏輯估算而來。

actRows

算子實際輸出的數(shù)據(jù)條數(shù)

task

算子屬于的 task 種類。目前的執(zhí)行計劃分成為兩種 task，一種叫 root task，在 tidb-server 上執(zhí)行，一種叫 cop task，在 TiKV 或者 TiFlash 上并行執(zhí)行。當前的執(zhí)行計劃在 task 級別的拓撲關(guān)系是一個 root task 后面可以跟許多 cop task，root task 使用 cop task 的輸出結(jié)果作為輸入。cop task 中執(zhí)行的也即是 TiDB 下推到 TiKV 或者 TiFlash 上的任務，每個 cop task 分散在 TiKV 或者 TiFlash 集群中，由多個進程共同執(zhí)行。

access object

算子所訪問的數(shù)據(jù)項信息。包括表 table，表分區(qū) partition 以及使用的索引 index（如果有）。只有直接訪問數(shù)據(jù)的算子才擁有這些信息。

execution info

算子的實際執(zhí)行信息。time 表示從進入算子到離開算子的全部 wall time，包括所有子算子操作的全部執(zhí)行時間。如果該算子被父算子多次調(diào)用 (loops)，這個時間就是累積的時間。loops 是當前算子被父算子調(diào)用的次數(shù)。

operator info

算子的其它信息。各個算子的 operator info 各有不同，可參考下面的示例解讀。

memory

算子占用內(nèi)存空間的大小

disk

算子占用磁盤空間的大小

2. 執(zhí)行計劃優(yōu)化的幾個關(guān)鍵點：

1) 重點觀察算子類型，盡量控制優(yōu)化器選擇性能較優(yōu)的算子，讀取磁盤記錄的幾個算子性能：TableFullScan>TableRangeScan>TableRowIDScan，IndexFullScan>IndexRangeScan

2) 盡量減小root層執(zhí)行動作，下放至tikv或tiflash執(zhí)行，執(zhí)行計劃中task屬性包括root task和cop task，其中root標識動作由tidb聚合層執(zhí)行（此操作除了需要等待各分片結(jié)果外，一般部署結(jié)構(gòu)中tidb資源也較tikv或tiflash少），cop標識動作下放至tikv或tiflash各分片單獨執(zhí)行

3) 保證表分析數(shù)據(jù)完整性，避免大批量數(shù)據(jù)短時間內(nèi)新增/刪除，estRows為執(zhí)行引擎根據(jù)情況返回的預估記錄條數(shù)，特別注意：若operator info出現(xiàn)stats:pseudo，則標識表基本信息不完善（無法提供準確執(zhí)行計劃評估），后續(xù)可通過analyze表重新收集分析數(shù)據(jù)，或顯示use index對sql顯示優(yōu)化

4) 根據(jù)實際業(yè)務（如：列模式數(shù)據(jù)統(tǒng)計），增加tiflash模塊，通過空間換時間，提升結(jié)構(gòu)化查詢和實時分析能力

3. 實際場景分析

下面我們通過2個實際SQL說說TiDB的執(zhí)行計劃：

l SQL1

*1：IndexLookUp算子：根據(jù)索引獲取結(jié)果記錄

*2 & *3：Build算子總是優(yōu)先于Probe算子執(zhí)行，*2 算子根據(jù)條件從索引中獲取數(shù)據(jù)，*3算子在結(jié)果中匹配結(jié)果

*4：TableRowIdScan：通過 *3 算子結(jié)果中的表主鍵id從TiKV獲取行記錄

*5：cop【tikv】標識將計算邏輯從tidb下放到tikv執(zhí)行，同理還會有cop【tiflash】

*6：tikv通過范圍索引掃描出對應記錄

*7：根據(jù)id獲取行記錄后直接返回上層，無需排序

------------------------------------------------------------------------------------------------------------------------------

l SQL2

優(yōu)化前，兩表直接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算子：根據(jù)表s索引，與表m關(guān)聯(lián)起來

*2 & *3：Build算子總是優(yōu)先于Probe算子執(zhí)行，*2 算子從表m匹配相關(guān)記錄，*3算子通過表s索引獲取join管理數(shù)據(jù)

*4 & *5：基于*3算子join后的結(jié)果，篩選匹配s表條件的記錄

*6 & *7：可以看到此處表記錄查詢使用了TableReader，耗時6.41s（其中cop_task共424個，且使用了大量索引proc_keys），Selection_98根據(jù)索引回表查詢更是讀取了3.03GB記錄

總結(jié)：整體sql因為是先join在limit，tidb無法將limit操作下推，導致主表大量回表查詢，影響性能

優(yōu)化后，先子查詢再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算子：根據(jù)表s索引，與表m關(guān)聯(lián)起來

*2：從m表結(jié)果中獲取前20條記錄

*3：通過表s索引獲取join管理數(shù)據(jù)

*4：根據(jù)條件，從表m的索引中獲取記錄

*5：從*4算子結(jié)果中獲取40條記錄（tikv3副本，從2個分片各獲取20條，共40條）

*6 & *7：基于*3算子join后的結(jié)果，篩選匹配s表條件的記錄

*9：可以看到，此處是直接從IndexLookUp_57索引中查詢數(shù)據(jù)，cop_task=1，且rocksdb中命中了緩存cache_hit_count=11

總結(jié)：整體sql因為是先limit再join，tidb將limit下推至tikv，大大較少了主表的回表查詢數(shù)據(jù)量，提升性能

六、 小結(jié)

本文旨在通過TiDB和MySQl在SQL層面的差異性講解，幫助讀者在DB遷移和評估前，清楚了解雙方的差異，避免遺漏。同時，針對TiDB的執(zhí)行計劃，通過簡介和2個案例，幫助大家快速分析SQL執(zhí)行情況，以便針對性優(yōu)化。