一次慢SQL调优

上周日常系统维护的时候，遇到之前同事做的一个报表，关联的数据不大（SQL用到的主表数据量才5W+，其余的表2W封顶），查询速度出奇的慢，第一次打开的时候直接超时，超过了服务器配置的30S超时时间，折腾一番后，将SQL速度提升到8S，再折腾一番后达到37ms，简单记录下过程。

背景描述：SQL

以下SQL的字段和表名均已经过屏蔽处理。

数据表：

contract： 核心主表，数据量5W
vendor_info： 关联表，数据量1W
data_area_divide： 基础信息表，数据量300 venums_dictionary_lang: 字典表，数据量 1K

模拟的最原始的SQL，查询超时：

explain SELECT v.company_name, v.vendor_code, c.contract_code, c.begin_date
	, c.end_date,  GROUP_CONCAT(da.data_area_name) AS department
FROM contract c
	LEFT JOIN flow_node n
	ON c.flow_id = n.flow_id
		AND n.is_deleted = 0, vendor_info v, contract_department d, area_data da, enums_dictionary_lang l
WHERE c.vendor_basic_id = v.vendor_basic_id
	AND c.contract_id = d.contract_id
	AND d.department_id = da.data_area_id
	AND da.is_deleted = 0
	AND d.is_deleted = 0
	AND c.is_deleted = 0
	AND v.is_deleted = 0
	AND c.contract_template_id = l.value
	AND l.type = 'templateTitle'
	AND l.lang = 'zh_CN'
	AND l.is_deleted = 0
GROUP BY c.contract_id

explain 结果：
l 表 =》

rows	filtered	Extra
219	    1.00	    Using where; Using temporary; Using filesort

检查了下，条件 AND c.contract_template_id = l.value 两个字段的类型不一致, 一个为bigint，一个为varchar处理下，改为AND convert(c.contract_template_id, char) = l.value ，跑下，8S左右可以出结果，但是完全没满足业务要求，继续折腾。

第二阶段分析

再次explain，看到非常意外的结果，v表出现了 Using temporary; Using filesort，并且type是ALL

rows	filtered	Extra
10482	10.00		Using where; Using temporary; Using filesort

明明采用的是c作为驱动表来join v表，并且有索引关联，为何type还是ALL，并且还产生了临时表和文件排序。在解答这些之前，先简单来补充点基础知识。

扯下Nested-Loop Join

mysql的join实现的算法基本都是基于 nest-loop-join，nest-loop-join分三种类型：

Simple Nested-Loop Join

其实就是双层for 循环，比如 a join b on a.id=b.cid，原理实现：

for item in a
	for data in b 
		if item.id = data.cid
			XXXX

比如a有10W条数据，b有1W条，那么就要比较10亿次，这种简单粗暴的操作显然mysql不是采用这种

Index Nested-Loop Join

针对imple Nested-Loop Join进行的改进，使用外层的数据来匹配内层索引进行匹配，进而大大减少内层表匹配的次数。

for item in a
	data = searchFromBIdx(item)

Block Nested-Loop Join

从减少外层的循环次数入手，一次性拿多条数据放到内存（join buffer），然后再拿join buffer的数据批量与内层数据表进行匹配，从而减少外层的循环次数。
相关的配置查看 Show variables like ‘optimizer_switc%’
结果：
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=off,mrr_cost_based=on,block_nested_loop=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
其中 block_nested_loop=off就是配置开启与否

join类型与驱动表

比如 A join B
LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。
但是mysql引擎会根据实际的优化进行调整join类型，我们第二次分析遇到的就是这种情况。

// DBA 原话
因为where条件中有AND v.is_deleted = 0，sql引擎改写成inner join

又因为v表的数据量比c表少，所以v变成了驱动表，这就是为何v的type是ALL，然后group by又是c表的字段，而不是驱动表v的字段，所以导致了v表出现临时表和文件排序。尝试将AND v.is_deleted = 0 去掉，也就是按照我们最初始的设想，c表为驱动表，结果为37ms。跟业务方沟通了，确认可以去掉这个判断逻辑不会影响结果。

STRAIGHT_JOIN

周末在家总结回顾的时候，假设万一业务方还是一定要保留AND v.is_deleted = 0 ，甚至有更复杂的关于v表的过滤条件，有没有办法指定mysql 引擎强制使用c表作为驱动表。找了下，STRAIGHT_JOIN 符合我的期望。 STRAIGHT_JOIN是用来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。
所以最终的解决SQL是：

explain SELECT v.company_name, v.vendor_code, c.contract_code, c.begin_date
	, c.end_date,  GROUP_CONCAT(da.data_area_name) AS department
FROM contract c
	LEFT JOIN flow_node n
	ON c.flow_id = n.flow_id AND n.is_deleted = 0
	STRAIGHT_JOIN vendor_info v, contract_department d, area_data da, enums_dictionary_lang l
WHERE c.vendor_basic_id = v.vendor_basic_id
	AND c.contract_id = d.contract_id
	AND d.department_id = da.data_area_id
	AND da.is_deleted = 0
	AND d.is_deleted = 0
	AND c.is_deleted = 0
	AND v.is_deleted = 0
	AND c.contract_template_id = l.value
	AND l.type = 'templateTitle'
	AND l.lang = 'zh_CN'
	AND l.is_deleted = 0
GROUP BY c.contract_id

效果跟屏蔽AND v.is_deleted = 0一致，也是达到了37ms的查询水平。

扩展：如果有order by 和 limit

如果有SQL有order by XX limit 50,那SQL的效率会如何。如果SQL添加的是 order by c.contract_code limit 50 那SQL效率如何？explain了下，c表出现了temporary 和fielsort，原因是order by是在join完之后再进行排序，而join完之后的结果，是按照c.contract_id进行排序。因此要在order by之后获取limit 50，必须先把join的结果排序,因此会产生临时表和文件排序。如果是order by c.contract_id limit 100，那不需要对join的结果排序再进行limit操作，因此不会出现temporary 和fielsort。

explain SELECT v.company_name, v.vendor_code, c.contract_code, c.begin_date
	, c.end_date,  GROUP_CONCAT(da.data_area_name) AS department
FROM contract c
	LEFT JOIN flow_node n
	ON c.flow_id = n.flow_id AND n.is_deleted = 0
	STRAIGHT_JOIN vendor_info v, contract_department d, area_data da, enums_dictionary_lang l
WHERE c.vendor_basic_id = v.vendor_basic_id
	AND c.contract_id = d.contract_id
	AND d.department_id = da.data_area_id
	AND da.is_deleted = 0
	AND d.is_deleted = 0
	AND c.is_deleted = 0
	AND v.is_deleted = 0
	AND c.contract_template_id = l.value
	AND l.type = 'templateTitle'
	AND l.lang = 'zh_CN'
	AND l.is_deleted = 0
GROUP BY c.contract_id  order by c.contract_id desc limit 50