MySQL优化之Using temporary(临时表)、Using filesort(文件排序)

首先查看explain执行计划,让主要查询语句使用索引,索引type级别最好达到ref | ref_eq级别

其次将extra一栏的Using temporary(临时表)、Using filesort(文件排序)拖出去砍了

第一条语句

1
2
3
explain  
select * from tb_wm_shop where is_delete != 1 and is_authentication = 1
ORDER BY create_time DESC

大家应该知道使用order by的 字段要使用索引,这条语句中create_time已经创建了索引,但是计划中并没有使用该索引,导致出现了Using filesort文件排序,使其查询变慢

解决方法如下:

从where条件开始,依照顺序创建一个组合索引,就可以砍掉Using filesort这个令人讨厌的头颅了

注意:必须依照顺序,在创建组合索引时,where条件的字段在orderBy的字段之前,如果orderBy是多字段,则必须依照顺序创建

详情可参考链接:https://blog.csdn.net/dingxingmei/article/details/49096591

第二条语句

1
2
3
explain  
select s.* from tb_wm_popularize p left join tb_wm_shop s on p.shop_id = s.id
where s.is_delete != 1 AND p.type = 1 order by s.sale_num desc

这条语句就比较讨人厌了,同时出现了Using temporary(临时表)、Using filesort(文件排序)

一个小时的百度,找到了原因

其一,参考:https://www.iteye.com/blog/hudeyong926-785181

发现了错误一:左联接表时,如果orderBy使用的字段是第二张表的字段就会照成Using temporary,修改语句以下是结果

发现结果还是没变,经过确认以上链接的真实性,引出了第二个问题:

and p.type = 1 这个导致了查询之后又将p 表 引为了第一张表,导致第一个问题解决之后任然没有生效,就有了以下改动:

现在临时表没有了,开始解决剩下的Using filesort

上面已经说了,建立一个sale_num的索引就可以了,不过我的语句里面还有一个错误,就是 != 会导致语句不走索引,因程序业务逻辑符合条件 改为is_delete = 0完成优化

至于上面所说的 != 导致不走索引,目前没有发现什么好的方法解决,百度出来有一种方法是通过union函数将大于 和小于连接起来

但是上语句中因为还需要排序,所以会造成另一个额外表,故不采用!