作者：京东批发 肖勇

一、 前言导读

TiDB作为NewSQL，其在对MySQL（SQL92协定）的兼容上做了很多，MySQL作为当下应用较广的事务型数据库，在IT界尤其是互联网间应用宽泛，那么对于开发人员来说，1）两个数据库产品在SQL开发及调优的过程中，都有哪些差别？在零碎迁徙前须要提前做哪些筹备？ 2）TiDB的执行打算如何查看，如何SQL调优？ 本文做了一个简要演绎，欢送查阅交换。

二、 建表SQL语法差别&优化倡议

三、 查问SQL语法差别&优化倡议

四、 SQL执行打算差别&优化倡议

五、 TiDB执行打算剖析简介

1. 在开始理论案例剖析前，咱们先看下执行打算中每列的含意：

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

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执行状况，以便针对性优化。