慢SQL优化总结

背景

616大促前夕压测检查出有几个供应链的系统有SQL慢查询，于是DBA来了一场大部分系统mysql慢查询全扫描，揪出我们组负责的几个系统用大于有100条慢SQL（查询速度>300ms），下面就是我这次100条慢SQL排查的总结。

慢SQL的原因

1.没有索引

这个没什么好说的，有些数据表数据量比较小，但是由于多张表join之后产生了数据膨胀，所以速度也会很慢。

2.Using filesort

Using filesort 表示的是一个排序操作不能通过索引来完成,需要额外进行外部的排序动作。所以其实跟文件没什么关系~
解决filesort最根本的方式还是让排序字段走索引，比如：

select * from t where a = 12 and b in(1,2) order by c;
# 可以创建 idx_a_b_c 的联合索引，确保c排序能够走索引

另外有filesort并不代表SQL一定会慢
group by 会默认有排序，所以即使SQL没有order by，有group by也可能出现filesort

3.Using temporary

Extra出现Using temporary意味着MySQL需要创建一个临时表来容纳结果。这次分析的过程遇到最多就是这种SQL产生临时表

select * from a 
	join b on a.id=b.user_id 
	join (select user_id,group_concat(dept_id) from c group by user_id) t1 on t1.user_id=a.id
	order by a.create_time

本次大部分解决方式都是将这种子查询拆分换成join，再做group by

4.mysql优化器选择了错误的驱动表

遇到很多这种SQL，

select * from a 
	join b on a.id=b.user_id 
	join c on a.id=c.user_id
	where a.age<100 
	order by a.create_time

explain的结果，发现驱动表变成b，有b表的记录比较少，mysql的优化器选择了b做为驱动表，导致没有按照我们预期的a作为驱动表进行关联，出现了fielsort和temporary。
这类解决的大部分方式就是强行使用straight_join 指定驱动表，比如

select * from a 
	straight_join b on a.id=b.user_id 
	straight_join c on a.id=c.user_id
	where a.age<100 
	order by a.create_time

5.使用not in，会导致不命中索引

not in会导致索引失效

6.对索引字段做函数处理，导致索引失效

比如

 select * from t where t.field_name like '%_time'   
 and str_todate(t.field_value) > '2020-06-12'

即使t.field_value做了索引，也不会命中，可以改成参数类型改为跟字段一致的作比较，而不是直接转换字段内容，比如t.field_value>’2020-06-12’

7.SQL太多的代码逻辑

认识的一个架构师说到一句比较合理的话，SQL更关注的是存储而不是计算，把计算逻辑放到mysql远没有在应用层面操作成本低。

8.left join改为join、straight_join

从业务上看，如果可以没有必要用 left join 就用 join，straight_join，避免mysql的调整导致SQL很慢

9.子表没有跟驱动表直接关联

驱动表join了N张表，但是直接跟驱动表关联的只有一部分，比如：

select * from a join b on on a.id=b.user_id  
join c on b.user_id=c.user_id and c.role_id = 'admin'  
join d on b.user_id = d.adminId   
where a.create_time > '2020-06-20' group by a.name  

其实如果 b c 和d表是有直接关联关系的，可以考虑让 b c d三张表一起join得出一个子查询，再拿子查询与驱动表join，其实速度有可能会快很多很多。

10.using index

建索引的时候注意，如果可以使用到索引覆盖就直接使用索引覆盖。

11.隐式转换导致不能走索引

遇到比较的是，a.value = b.user_id , a的value是varchar，别的user_id是int，导致没有命中索引
这种问题其实可以通过 explain + show warnings 来分析出来

12.走正确索引了还是慢

遇到过一次，SQL写得非常正确规范，但是SQL还是花了1.X秒，explain的结果发现是，命中索引过滤出的行数还是有几十万，导致SQL查询慢。
解决这个问题还是从业务上去解决，添加业务上的过滤条件来过滤出更少的数据，比如加上 create_time > (now() - 7)，把一些无用的老数据过滤掉

额外的发现

1.DBA建议不用前缀索引

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快
前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

2.关闭索引合并

index_merge 表示对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。
如果是intersect类型，取多个索引的交集，但是这种DBA会见直接创建联合索引，因为考虑到一些情况，比如命中A索引的区分度特别高，另一个B索引的字段区分度特别低，那么A查完了还一直在等B，会有可能导致查询速度下降。 union类型就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。