京东云TiDB SQL层的背景介绍

从总体上归纳 TiDB 和 MySQL 兼容战略，如下表：

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

SQL层的架构

用户的 SQL 恳求会直接或许经过 Load Balancer 发送到 京东云TiDB Server，TiDB Server 会解析 MySQL Protocol Packet，获取恳求内容，对 SQL 进行语法解析和语义分析，拟定和优化查询方案，履行查询方案并获取和处理数据。数据全部存储在 TiKV 集群中，所以在这个进程中 TiDB Server 需要和 TiKV 交互，获取数据。终究 TiDB Server 需要将查询成果返回给用户。

京东云TiDB SQL优化的最佳实践
一条SQL的生命周期图

●SQL优化流程的概览

在 TiDB 中，从输入的查询文本到终究的履行方案履行成果的进程能够见下图：

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

在经过了 parser 对原始查询文本的解析以及一些简略的合法性验证后，TiDB 首要会对查询做一些逻辑上的等价改动，经过这些等价改动，使得这个查询在逻辑履行方案上能够变得更易于处理。在等价改动完毕之后，TiDB 会得到一个与原始查询等价的查询方案结构，之后依据数据散布、以及一个算子具体的履行开销，来取得一个终究的履行方案，一起，TiDB 在履行 PREPARE 句子时，能够挑选开启缓存来降低 TiDB 生成履行方案的开销。

●运用 EXPLAIN 句子检查履行方案

履行方案由一系列的算子构成。和其他数据库一样，在 TiDB 中可经过 EXPLAIN 句子返回的成果检查某条 SQL 的履行方案。

现在 TiDB 的 EXPLAIN 会输出 5 列，分别是：id，estRows，task，access object， operator info。履行方案中每个算子都由这 5 列属性来描绘，EXPLAIN成果中每一行描绘一个算子。每个属性的具体含义如下：

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

● EXPLAIN ANALYZE 输出格式

和 EXPLAIN 不同，EXPLAIN ANALYZE 会履行对应的 SQL 句子，记录其运转时信息，和履行方案一并返回出来，能够视为 EXPLAIN 句子的扩展。EXPLAIN ANALYZE 句子的返回成果中添加了 actRows, execution info,memory,disk 这几列信息：

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

举个比如如下：

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

从上述比如中能够看出，优化器预算的 estRows 和实践履行中统计得到的 actRows 几乎是持平的，说明优化器预算的行数与实践行数的误差很小。一起 IndexLookUp_10 算子在实践履行进程中运用了约 9 KB 的内存，该 SQL 在履行进程中，没有触发过任何算子的落盘操作。

SQL优化事例最佳实践

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

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

SQL履行方案如下：

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

execution info列，有该履行方案的时刻，这个SQL的表的连接次序，要从最里边的循环开端看，如下图，m,d是最早开端进行连接的：

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

关注下图的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的运转时刻及履行方案

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

优化前后的履行方案

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

优化后加了hint的SQL

京东云TiDB 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 协议，使搬迁运用成本降到极低。

作者：赵玉龙