前言

最近发现了一个慢SQL，记录下对这个慢SQL优化的过程。

三种写法

涉及的三张表数据量如下：

table_a；915371
table_b：3298911
table_c：77536

先JOIN再JOIN

从数据量来看三张表并不算多，甚至可以任务表很小了。但是将三个表通过LEFT JOIN的方式关联起来查询耗时为8.308s，这个耗时可谓相当惊人了，特别当并发查询量升高时，更会造成严重的block情况。

SELECT 
   count(0)
FROM table_a  AS a 
   LEFT JOIN table_b  AS b ON a.field_id = b.field_id 
   LEFT JOIN table_c  AS c ON a.field_id = c.field_id
WHERE (b.field = 'value' OR c.field = 'value');

执行耗时：

为什么LEFT JOIN耗时这么长呢？

使用EXPLAIN看下上述SQL的执行策略。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	index	NULL	idx_field_id	8	NULL	898773	100	Using index
1	SIMPLE	b	NULL	eq_ref	PRIMARY	PRIMARY	8	dbname.a.field_id	1	100	NULL
1	SIMPLE	c	NULL	eq_ref	PRIMARY	PRIMARY	8	dbname.a.field_id	1	100	Using where

从上表中以看出，已经用到了index，所以并不是因为没有索引导致查询耗时较长，这里耗时长的原因是没有用到field的index。接着往下看，

先JOIN再UNION ALL

通过业务和逻辑分析，认为可以将LEFT JOIN改为先JOIN再UNION ALL。并进行了尝试。

SELECT 
   sum(cnt)
FROM 
   (
      SELECT 
         count(0) CNT
      FROM table_a  AS a 
         LEFT JOIN table_b  AS b ON a.field_id = b.field_id
      WHERE b.field = 'value'
      UNION ALL
      SELECT 
         count(0)
      FROM table_a  AS a 
         LEFT JOIN table_c  AS c ON a.field_id = c.field_id
      WHERE c.field = 'value'
   )  a;

执行耗时：

使用EXPLAIN看下上述SQL的执行策略。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		NULL	ALL	NULL	NULL	NULL	NULL	2	100	NULL
2	DERIVED	b	NULL	ref	PRIMARY,idx_filed_IsActivated	idx_filed_IsActivated	4	const	253	100	Using where; Using index
2	DERIVED	a	NULL	ref	idx_field_id	idx_field_id	8	dbname.b.field_id	76	100	Using index
3	UNION	c	NULL	ref	PRIMARY,idx_filed	idx_filed	4	const	3918	100	Using where; Using index
3	UNION	a	NULL	ref	idx_field_id	idx_field_id	8	dbname.c.field_id	76	100	Using index

此处修改后，从执行策略中可以看出，使用的索引值新增了idx_filed_IsActivated以及idx_field_id，从查询耗时中可以明显看出时间大幅降低了。此外，从rows这一列中也可以看出查询的结果数量也大幅降低。

先UNION再JOIN

实际上上述SQL的性能已经可以达到业务需求，并且在当前表数据结构中也不会存在数据偏差，但是更深入思考了下，上述的SQL可能会存在重复数据的情况。因此，决定对SQL再进行一次优化。改为先UNION再JOIN。

SELECT 
   count(0)
FROM table_a  AS a 
   JOIN 
   (
      SELECT 
         field_id
      FROM table_b  AS b
      WHERE b.field = 'value'
      UNION
      SELECT 
         field_id
      FROM table_c  AS c
      WHERE c.field = 'value'
   )  e ON a.field_id = e.field_id;

执行耗时：

使用EXPLAIN看下上述SQL的执行策略。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		NULL	ALL	NULL	NULL	NULL	NULL	4171	100	NULL
1	PRIMARY	a	NULL	ref	idx_field_id	idx_field_id	8	e.field_id	76	100	Using index
2	DERIVED	b	NULL	ref	idx_filed_IsActivated	idx_filed_IsActivated	4	const	253	100	Using index
3	UNION	c	NULL	ref	idx_filed	idx_filed	4	const	3918	100	Using index
NULL	UNION RESULT	<union2,3>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

从先JOIN再UNION ALL和先UNION再JOIN两次的执行策略看出来，使用的索引几乎都一样，查询耗时也几乎差不多。

先UNION ALL再JOIN

进一步从业务上分析，是可以忍受重复数据的查询的，因此又将UNION改为UNION ALL。

SELECT 
   count(0)
FROM table_a  AS a 
   JOIN 
   (
      SELECT 
         field_id
      FROM table_b  AS b
      WHERE b.field = 'value'
      UNION ALL
      SELECT 
         field_id
      FROM table_c  AS c
      WHERE c.field = 'value'
   )  e ON a.field_id = e.field_id;

使用UNION ALL和UNION执行耗时几乎一样，如下：

使用EXPLAIN看下上述SQL的执行策略。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		NULL	ALL	NULL	NULL	NULL	NULL	4171	100	NULL
1	PRIMARY	a	NULL	ref	idx_field_id	idx_field_id	8	e.field_id	76	100	Using index
2	DERIVED	b	NULL	ref	idx_filed_IsActivated	idx_filed_IsActivated	4	const	253	100	Using index
3	UNION	c	NULL	ref	idx_filed	idx_filed	4	const	3918	100	Using index

比较先UNION再JOIN和先UNION ALL再JOIN两次的执行策略看出来，先UNION ALL再JOIN的执行策略中少了UNION RESULT的过程。这个过程的性能消耗对于十几万的查询影响并不大，但是随着数据量的上升，会对SQL的执行性能有很大的影响。

综合以上，我们选在了先UNION ALL再JOIN的优化后的SQL作为最终上线的SQL。

SQL操作符

上文SQL中涉及的操作符说明。

LEFT JOIN
- 从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
UNION
- 合并两个或多个 SELECT 语句的结果集。
- UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
- UNION不仅对多个sql的查询结果做了合并，还在合并的基础上做了默认排序，同时还去除了重复行。
UNION ALL
- UNION ALL只是简单的将两个结果合并后就返回
- UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

EXPLAIN

上文中用到了EXPLAIN分析SQL性能，查询的结果中每一列的含义总结如下：

id
- SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type
- SELECT 查询的类型.
table
- 查询的是哪个表
partitions
- 匹配的分区
type
- join 类型
possible_keys
- 此次查询中可能选用的索引
key
- 此次查询中确切使用到的索引.
ref
- 哪个字段或常数与 key 一起被使用
rows
- 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered
- 表示此查询条件所过滤的数据的百分比
extra
- 额外的信息

总结

持续优化慢SQL是一个长期的过程，优化时不仅仅要单单从技术角度来看，也同时要结合业务场景进行合理的分析。使用Explain分析优化SQL也是很有必要的。

参考文献

MySQL 性能优化神器 Explain 使用分析

记一次JOIN和UNION的SQL优化

前言

三种写法

先JOIN再JOIN

先JOIN再UNION ALL

先UNION再JOIN

先UNION ALL再JOIN

SQL操作符

EXPLAIN

总结

参考文献