EXPLAIN sql优化方法（2） Using temporary ; Using filesort

优化group by语句

explain select id, sum(moneys) from sales2 group by id \g

explain select id, sum(moneys) from sales2 group by id order by null \g

你可以通过比较发现第一条语句会比第二句在extra:里面多了using filesort.而恰恰filesort是最耗时的。

优化order by语句

在某些情况中，mysql可以使用一个索引来满足order by子句，而不需要额外的排序。where 条件和 order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

例如：

select * from t1 order by key_part1,key_part2,....:

select * from t1 where key_part1 = 1 order by key_part1 desc,key_part2 desc;

select * from t1 order by key_part1 desc, key_part2 desc;

但是以下的情况不使用索引：

select * from t1 order by key_part1 desc, key_part2 asc;

--order by的字段混合asc 和 desc

select * from t1 where key2=constant order by key1;

----用于查询行的关键字与order by 中所使用的不相同

select * from t1 order by key1, key2;

----对不同的关键字使用order by

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id order by b . id ;

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

3 rows in set ( 0.00 sec )

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id order by a . id ;

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

对于上面两条语句，只是修改了一下排序字段，而第一个使用了using temporary，而第二个却没有。在日常的网站维护中，如果有using temporary出现，说明需要做一些优化措施了。

而为什么第一个用了临时表，而第二个没有用呢？

因为如果有order by子句和一个不同的group by子句，或者如果order by或group by中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。

那么，对于上面例子中的第一条语句，我们需要对jos_categories的id进行排序，可以将sql做如下改动：

mysql > explain select b . id , b . title , a . title from jos_categories a left join jos_content b on a . id = b . catid left join jos_sections c on b . sectionid = c . id order by a . id ;

+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+

| 1 | simple | b | ref | idx_catid | idx_catid | 4 | joomla_test . a . id | 3328 | |

这样我们发现，不会再有using temporary了，而且在查询jos_content时，查询的记录明显有了数量级的降低，这是因为jos_content的idx_catid起了作用。

所以结论是：

尽量对第一个表的索引键进行排序，这样效率是高的。

我们还会发现，在排序的语句中都出现了using filesort，字面意思可能会被理解为：使用文件进行排序或中文件中进行排序。实际上这是不正确的，这是一个让人产生误解的词语。

当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。

然而，当我们回过头来再看上面运行过的一个sql的时候会有以下发现：

mysql > explain select a . id , a . title , b . title from jos_content a , jos_categories b , jos_sections c where a . catid = b . id and a . sectionid = c . id order by c . id ;

+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+

| 1 | simple | a | ref | idx_catid , idx_section | idx_section | 4 | joomla_test . c . id | 23293 | using where |

这是我们刚才运行过的一条语句，只是加了一个排序，而这条语句中c表的主键对排序起了作用，我们会发现using filesort没有了。

而尽管在上面的语句中也是对第一个表的主键进行排序，却没有得到想要的效果（第一个表的主键没有用到），这是为什么呢？实际上以上运行过的所有left join的语句中，第一个表的索引都没有用到，尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪！

于是我们继续测试了下一条sql：

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id where a . id < 100 ;

+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

3 rows in set ( 0.05 sec )

然后，当再次进行排序操作的时候，using filesoft也没有再出现

mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id where a . id < 100 order by a . id ;

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+

| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |

这个结果表明：对where条件里涉及到的字段，mysql会使用索引进行搜索，而这个索引的使用也对排序的效率有很好的提升。

写了段程序测试了一下，分别让以下两个sql语句执行200次：

select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id

select a . id , a . title , b . title from jos_content a , jos_categories b , jos_sections c where a . catid = b . id and a . sectionid = c . id

select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id 　 order by rand () limit 10

select a . id from jos_content a left join jos_categories b on b . id = a . catid left join jos_sections c on a . sectionid = c . id order by a . id

结果是第(1)条平均用时20s ，第(2)条平均用时44s ，第(3)条平均用时70s ，第(4)条平均用时2s 。而且假如我们用explain观察第(3)条语句的执行情况，会发现它创建了temporary表来进行排序。

综上所述，可以得出如下结论：

1. 对需要查询和排序的字段要加索引。

2. 在一定环境下，left join还是比普通连接查询效率要高，但是要尽量少地连接表，并且在做连接查询时注意观察索引是否起了作用。

3. 排序尽量对第一个表的索引字段进行，可以避免mysql创建临时表，这是非常耗资源的。

4. 对where条件里涉及到的字段，应适当地添加索引，这样会对排序操作有优化的作用。

6. 从第4点可以看出，如果说在分页时我们能先得到主键，再根据主键查询相关内容，也能得到查询的优化效果。通过国外《high performance mysql》专家组的测试可以看出，根据主键进行查询的类似“select ... from... where id = ...”的sql语句（其中id为primarykey），每秒钟能够处理10000次以上的查询，而普通的select查询每秒只能处理几十次到几百次。涉及到分页的查询效率问题，网上的可用资源越来越多，查询功能也体现出了它的重要性。也便是sphinx、lucene这些第三方搜索引擎的用武之地了。

7. 在平时的作业中，可以打开mysql的slow queries功能，经常检查一下是哪些语句降低的mysql的执行效率，并进行定期优化。

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

继续阅读

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

Effective Java 8:通用程序设计

SequoiaDB巨杉数据库C++驱动概述

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method