TiDB 与 MySQL 的 SQL 差异及执行计划简析

作者：京东零售 肖勇

一、 前言导读

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