TiDB | 一些很有意思的SQL调优案例分享~

 本期内容 

 SQL调优案例分享 

上周支持了一个金融场景的TiDB项目，集群版本是5.1.2，因为某些原因，未使用tiflash组件。

在生产中又确实有许多复杂的SQL需要执行,且存在部分高并发的SQL，基于现状，就做了很多SQL调优的工作。

不禁想到，在很多项目中，我们都会遇到调优的需求。所以这篇文章，我们就分享一些作者做过的比较有意思的SQL调优的方式方法~

内连接中的类似 

笛卡尔积现象导致oom 

 场景简述 

应用反馈有个功能有时候能跑出来，有时候跑不出来(内存占用超过10G）。

在dashboard慢查询中定位到了对应的SQL，对SQL和执行计划进行分析后，发现这个SQL是对三张表的一个inner join的关联查询.

执行计划显示，三张表经过过滤出来结果集分别约为3千条、20万条、1000万条数据，进行连接后最终的结果集超过11亿行数据。

经过对比分析，功能跑不出来的原因是每次计算到11亿行数据时容易触发oom,导致查询失败报错。

 分析与现象还原 

当时第一眼很困惑，在左连接中没有一个超过11亿行的表，为什么最终join的结果集这么大？？

后来分析定位发现，两表中的中关联条件存在大量重复的数据，导致产生了一个类似笛卡尔积的现象，导致结果集过大。

举例与演示：

原始SQL在join后会产生10行记录，原因是b表中多行记录其实能和a表中多行记录匹配到。

结果集数量类似于笛卡尔积的那种产生方式，在关联的表数据量大的时很容易oom（#这里是2*3+1*4=10）

由于在这个SQL中，b表的作用只是相当于取name列的数据，到a表name列中进行过滤，且中间的多行结果集并不影响最终结果。

这里可以加一个临时表，先将b表数据进行去重。

在真实的场景中，数据量特别大时，去重后，连接计算量会明显变小，内存消耗变小，结果集变小，SQL不会oom了，SQL也更快了。（#有时候SQL消耗的很大一部分内存是连接时候的一个内存放大）

单表有多种查询时  

索引的建立 

 场景描述 

通常我们通过dashboard抓取到慢SQL时，会进行执行计划分析。

如果发现多次查询，且查询的数据量很少，同时对表的查询没有走索引，在执行计划中是全表查询的，然后再到内存中进行过滤。

这个时候我们就会考虑对过滤条件的字段建立索引。

执行计划大致如下：

但当你准备给这张表的部分字段添加索引时，你发现这张表已经有5~6个索引了，这时就不能继续添加新索引。

因为维护索引是有成本的，而且为了维护一致性，在高并发的场景中不适合添加太多的索引，这个时候你就需要综合考虑所有对表的操作，来添加有限的索引。

 权衡添加索引 

# 不是所有的过滤条件都需要添加索引

当表A已经有索引A(a,b,c)时，这时有个查询的过滤条件字段分别是(b,a,d)。

如果表A中，字段a和字段b的过滤性都不错，就无需再单独对(b,a,d)添加索引了。

在SQL实际执行时，会先利用索引A对条件字段(a,b)进行过滤（最左匹配原则），再到内存中对条件字段d进行过滤。

类似的执行计划：

# 字段过滤性越好，优先级越高

举例：

假设需要对【org_no】【table_name】【up_org_no】三个条件添加索引，根据直方图中distinct_count列的过滤性显示，索引字段的顺序应该是【org_no】【up_org_no】【table_name】。

 注：有时候也考虑字段内容，例如长文本等就不建议添加索引。

# 字段复用率越高，查询频次越高越应该添加索引

面对表的多个查询的过滤条件都涉及的字段，我们越应该将它添加到索引中，且应该放在索引左边，以便复用。

和应用开发人员确认，执行频率越高的SQL的过滤条件，我们越应该添加索引。

# 依据最左匹配原则减少索引数量

例如有4组查询条件：

（a,b）

（a,b,c）

（a）

（a,b,c,d）

(a,b,c)过滤性良好的情况下，只需要用（a,b,c)字段创建一个索引就行，且越被复用的字段就应该越放在左边。

# 综合考虑拆表

当查询的种类变多时，建立索引就要考虑到整体影响。

一般而言，一张表的索引数量不应该超过6个。如果对表的需求很多，则建议拆分表，将宽表变成多个窄表。

绑定执行计划 

纠正索引选择 

 场景简述 

在一次排查慢SQL的过程中，发现有一条SQL会偶发性的执行时间特别长.

经过对比执行计划，发现耗时长的SQL在进行算子查询时，特别耗时。再去查看算子的执行信息，发现这个算子索引的选择与其他的索引选择不一致（索引走错了），查看表索引，发现这个表的索引较多，且部分索引会部分重复。

（V5.1.4)多次发现，当表的索引很多时，执行器偶尔会错误地选择索引，不选最佳的索引。

分析执行计划表展示：

 SQL绑定执行计划 

举例：这里强行表b走索引x_y_z，去绑定执行计划。

其他情况 

除了上述问题外，还有一些其他情况，统一列举在此。

 SQL中有查询视图 

有一次出现了一个慢SQL，在查看执行计划时，发现是有多个表关联，但是SQL很简单。

后面意识到是有视图，在这个SQL中，视图其实就可以看成一个提前定义好的子查询。所以针对这个SQL的优化也需要考虑到视图（子查询）的优化，添加索引等。

 强行定义子查询让某些表先连接 

有时侯多表连接时，某些表如果先连接，计算会更高效，这样我们可以定义子查询指定某些表先连接。

举例：

 原始SQL变动改造 

有时候开发人员在编写SQL时，由于考虑到拼接复用，或是由某些工具生成的SQL,并未考虑SQL执行性能，也未考虑实际需要。

这里就需要多和应用人员一起去核对部分慢SQL。

考虑：是否需要全表查询、全表关联、一次性取太多数据等等的问题

这里分享两个曾经做个的SQL改造：

#1 

SQL每次向应用服务器返回几十万条数据，然后到应用端再去过滤.某次并发高了，应用服务器oom了。这种就需要在SQL上，限值查询返回的数据量。

#2

关联的表未加任何过滤条件，全表关联。和应用确认后，业务上可以先添加过滤条件，先过滤，再关联，该造后SQL耗时大大变小

 将exsit改造成join去实现 

这里不是去讲exsit和in的相互替换与区别。

根据TiDB官方学习视频，exsit和in在执行时都会转化为连接去实现，但是根据多次实测，建议尽量主动去将SQL中exsit改写成inner join去实现。

写在最后  

#1

本文是作者根据以往经历所写，如有笔误欢迎指正~

#2

SQL调优还有大量的典型案例，估计大家都知道，这里就都没讲。只是重点讲了部分比较有意思的、偏门的。

#3

SQL调优是一个持续优化，不断精进的事情。希望能和大家一起多思考、多探索！