一次mysql 优化 (Using temporary ; Using filesort)

遇到一个SQL执行很慢 SQL 如下:

SELECT 
	...
FROM 
	tableA 
WHERE time >= 1492044535 and time <= 1492046335 GROUP by time, sourceName, serverSite,clientSite;

SELECT 部分忽略没写,是因为通常SQL执行慢不会跟这部分有关系,至少我没见过。

该语句非常简单,但是执行太慢。所以我们看一下执行计划

执行计划有几个字段我们比较关注:

type:range
possible_keys:time
key:time
extra:using index condition; using temporary; using filesort

type 代表连接类型。range是索引范围扫描的时候显示的类型。
possible_keys 和 keys 是可用的索引以及实际的索引
extra 比较关键,我们详细看一下这里的信息:

filesort

是说在排序的时候,没办法使用索引。比如我们这里用的索引是time,但group by 是 time, sourceName, serverSite,clientSite。Mysql有一点很重要是会默认按照group by排序。

group by time, sourceName, serverSite, clientSite 

order by time, sourceName, serverSite, clientSite

开销其实区别不大。所以这里排序不但要按照time 还要按照其它几列

解决办法

加order by null 这样在group by的时候默认不排序,可以去掉filesort。 但实际测试发现还是慢,所以file sort不是性能关键。

using tempoaray

这里是说在mysql执行过程中产生了临时表。这个操作比较耗时间。mysql的文档列出了几种会产生临时表的语法,但和我们这里的情况都不相符合。倒是mariadb的文档,虽然不是很详细,但说明了我们的语句确实可能用到临时表

A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY.

仔细分析一下也有道理,用索引查到数据后,你需要对这些数据分组。这个过程肯定是在一个数据集上操作的,那么这个数据集应该就是临时表了。不想要这个数据集的办法就是取消这个分组操作。我们只需要create一个联合索引

time,sourceName,serverSite,clientSite

这样一个索引可以通过time过滤,天然按照分组的顺序排序,就不用临时表了。

同时可以在执行语句中加个force index强制执行这个索引。
这样就没有using temporary这个操作了

posted on 2017-04-13 14:25  kramer  阅读(18434)  评论(0编辑  收藏  举报

导航