京东云TiDB SQL优化的最优实践

作者：赵玉龙

京东云TiDB SQL层的背景介绍

从总体上概括 TiDB 和 MySQL 兼容策略，如下表：

通讯协议	SQL语法	功能和行为
完全兼容	兼容绝大多数	兼容大多数

SQL层的架构

用户的 SQL 请求会直接或者通过 Load Balancer 发送到 京东云TiDB Server，TiDB Server 会解析 MySQL Protocol Packet，获取请求内容，对 SQL 进行语法解析和语义分析，制定和优化查询计划，执行查询计划并获取和处理数据。数据全部存储在 TiKV 集群中，所以在这个过程中 TiDB Server 需要和 TiKV 交互，获取数据。最后 TiDB Server 需要将查询结果返回给用户。

一条SQL的生命周期图

SQL优化流程的概览

在 TiDB 中，从输入的查询文本到最终的执行计划执行结果的过程可以见下图：

在经过了 parser 对原始查询文本的解析以及一些简单的合法性验证后，TiDB 首先会对查询做一些逻辑上的等价变化，通过这些等价变化，使得这个查询在逻辑执行计划上可以变得更易于处理。在等价变化结束之后，TiDB 会得到一个与原始查询等价的查询计划结构，之后根据数据分布、以及一个算子具体的执行开销，来获得一个最终的执行计划，同时，TiDB 在执行 PREPARE 语句时，可以选择开启缓存来降低 TiDB 生成执行计划的开销。

使用 EXPLAIN 语句查看执行计划

执行计划由一系列的算子构成。和其他数据库一样，在 TiDB 中可通过 EXPLAIN 语句返回的结果查看某条 SQL 的执行计划。

目前 TiDB 的 EXPLAIN 会输出 5 列，分别是：id，estRows，task，access object， operator info。执行计划中每个算子都由这 5 列属性来描述，EXPLAIN结果中每一行描述一个算子。每个属性的具体含义如下：

属性名	含义
id	算子的 ID，在整个执行计划中唯一的标识一个算子。在 TiDB 2.1 中，ID 会格式化的显示算子的树状结构。数据从孩子结点流向父亲结点，每个算子的父亲结点有且仅有一个
operator info	算子的其它信息。各个算子的 operator info 各有不同，可参考下面的示例解读。
estRows	算子预计将会输出的数据条数，基于统计信息以及算子的执行逻辑估算而来。在 4.0 之前叫 count。
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（如果有）。只有直接访问数据的算子才拥有这些信息。

EXPLAIN ANALYZE 输出格式

和 EXPLAIN 不同，EXPLAIN ANALYZE 会执行对应的 SQL 语句，记录其运行时信息，和执行计划一并返回出来，可以视为 EXPLAIN 语句的扩展。EXPLAIN ANALYZE 语句的返回结果中增加了 actRows, execution info,memory,disk 这几列信息：

属性名	含义
actRows	当前算子实际输出的数据条数。
execution info time	显示从进入算子到离开算子的全部时间，包括所有子算子操作的全部执行时间。如果该算子被父算子多次调用 (loops)，这个时间就是累积的时间。loops 是当前算子被父算子调用的次数。 rows 是当前算子返回的行的总数。
memory	当前算子占用内存大小
disk	当前算子占用磁盘大小

举个例子如下：

从上述例子中可以看出，优化器估算的 estRows 和实际执行中统计得到的 actRows 几乎是相等的，说明优化器估算的行数与实际行数的误差很小。同时 IndexLookUp_10 算子在实际执行过程中使用了约 9 KB 的内存，该 SQL 在执行过程中，没有触发过任何算子的落盘操作。

SQL优化案例最佳实践

案例一：索引的错误选择导致SQL变慢的优化实践

场景：数据库迁移到TiDB，SQL在MySQL运行不到1S，在TiDB运行超过30S

SQL执行计划如下：

execution info列，有该执行计划的时间，这个SQL的表的连接顺序，要从最里面的循环开始看，如下图，m,d是最先开始进行连接的：

关注下图的time变化，执行计划由毫秒级变成了秒级的地方，由71ms变成了33s，所以瓶颈卡在（（m join d） join taskm）join taskd 这个地方，对应的SQL片段如下：

INNER JOIN taskd
ON taskd.no = d.no
 AND taskd.o_no = d.o_no
 AND taskd.d_no = d.d_no
 AND taskd.w_no = d.w_no
 AND taskd.g_no = d.g_no
 AND IF(NULL = d.MD5_VALUE, 1, d.MD5_VALUE) = IF(NULL = d.MD5_VALUE, 1, taskd.MD5_VALUE)
 AND taskd.yn = 0

优化思路：

1、首先观察 explain analyze 结果，看到慢在最内 3 层的 join 上 ，(m join d) join taskd；

2、对比 MySQL 的执行计划，发现 MySQL 最内的 3 层的 join 是 (m join d) join taskm, 所以把相关的3张表提取出来，修改其join顺序；

3、修改顺序后，join 的时间能减少但是和 MySQL差距还是很大，再次观察，发现 taskd 上TiDB和MySQL使用的索引不一样，所以使用了 use index 来强制TIDB走和MySQL相同的索引。

案例二：表关联的错误选择导致SQL变慢的优化实践

场景：在MySQL运行时间毫秒级别，在TiDB运行时间18S

在TiDB的运行时间及执行计划

优化前后的执行计划

优化后加了hint的SQL

优化思路：

1. TiDB执行耗时 10+s 的原因是对 wps 表的估算不准确，导致优化器认为 w表 和 p表 走 hash join 效率更高，然后我们看到的执行计划的主要耗时在 pri 表回表获取数据的耗时较长 ；

2. w 表估算不准确的原因为TiDB 会把 w 的条件 有range scan 转换点查，然后利用这个索引的统计信息去估算；

3. 点查估算是会利用对应的 CMSketch 去进行估算，结合 p 表数据量很大，根据经验推测可能是 CMSketch 内部 hash 冲突导致。

案例一、二的延伸扩展：

在SQL优化的工作中，经常会通过加hint的方式改变SQL的执行计划，从而达到了优化的目的，但是缺点是对SQL进行了硬编码，如果业务程序使用了ORM框架，SQL的改造难度会增加。SQL Binding（SPM）则很好的解决了硬编码的问题，通过SQL Binding，DBA可以在不改变SQL文本的情况下，优化sql的执行计划，从而达到优化的目标，从而使SQL优化变得更加优雅。

京东云联合 PingCAP 基于国内开源 NewSQL 数据库 TiDB 打造的一款同时支持 OLTP 和 OLAP 两种场景的分布式云数据库产品，实现了自动的水平伸缩，强一致性的分布式事务，部署简单，在线异步表结构变更不影响业务，同时兼容 MySQL 协议，使迁移使用成本降到极低。了解详情可点击链接：官网链接

-End-