使用EXPLIAN字段可以对sql语句进行分析,当extra有Using temporary; Using filesort等时表示需要进行优化,意思分别是用了临时表和一种sql的排序。
eg1:
select * from a inner join b where a.id=b.aid order by a.id;这种情况下:
如果a的集合比b小,那么mysql就会以a为驱动表,这个时候如果a.id有索引的话,那么这个索引是起效的,不会出现Using temporary; Using filesort,查询是高效的。
如果a的集合比b大,那么mysql就会以b为驱动表,这个时候如果a.id有索引的话,那么这个索引是不起效的,order by只能对驱动表进行排序,此时可能会出现Using temporary; Using filesort,在数据量很大的情况下查询非常慢。
解决:
人为的对表进行设置哪个为驱动表,如left join,或者straight_join(强制把左边的表设置为驱动表),然后把order by 字段,加一个索引。
实际问题:
项目中遇到一个页面要20s才加载好,找到sql语句,发现做了2个查询,一个是查询信息,花费10s,另外一个是select count所以时间等同10s,用explian分析:es是主表有15w条记录,sp表只有14条记录,所以使用inner join的话mysql会自动优化为以sp为驱动表,因为如果以sp表为驱动表(排第一个的就是驱动表),那么只需循环14次去匹配15w的es表是比较高校的,但是这里需要对es表中的modifytime进行排序,所以先搜全部符合条件,再对其进行排序,而且不能用索引,所以就会Using temporary; Using filesort,而且特别慢。
措施:
改为straight_join,或者left join
此时,查询速度已经快了很多,但是使用分析,还是有Using temporary; Using filesort,所以对order by的字段添加索引,这时就能做到最好优化此时,explian全部为using where。大功告成