MySQL使用explain分析SQL中filesort的理解

在使用explain分析SQL时,经常在extra列中看到Using filesort。什么时候会出现Using filesort呢,该如何优化?

1. 创建测试表

CREATE TABLE test (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255),
   `sno` char(16) ,
   PRIMARY KEY (`id`)
);

# 在sno列创建索引
CREATE INDEX sno_index ON test(sno);  

# 随便插入几行测试数据
INSERT INTO test (name, sno)values('Lily', '001'), ("Bob", "002");

2. 复现Using filesort

执行SQL:
explain select * from test order by name asc;

执行结果:

mysql> explain select * from test order by name asc;   
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+   
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |   
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+   
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |   
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+   
1 row in set (0.00 sec)   

从执行结果可以看到,Extra列中出现了Using filesort。

3. Using filesort的含义

filesort直接翻译过来就是文件排序,所以在很多文章中提到,Using filesort是使用外部文件排序(由于查询结果较多,在内存中排序会消耗大量内存),由于需要在硬盘中读写文件,会导致SQL性能大幅度下降。其实这是一种错误的说法。根据MySQL官方文档: ORDER BY Optimization中的描述,如果不能使用索引进行排序,就会执行filesort。原文如下:

If an index cannot be used to satisfy an ORDER BY clause, MySQL performs a filesort operation that reads table rows and sorts them.

所以说,Using filesort仅仅是表明需要进行排序操作,并不能说明是否使用外部排序。

4. 什么是外存排序?

众所周知,内存的访问速度比硬盘速度快很多倍。如果排序过程在内存中进行,与在硬盘中排序相比肯定快很多。

排序过程在内存中进行的排序叫做内部排序,同理,外部排序是指待排序的记录太多,无法一次性装入内存中排序,只能将记录存储在外部存储器上,将记录分成若干部分,对每一部分分别进行内部排序,最后将各个部分进行合并。多路归并排序是常见的外部排序算法。

例如,要排序的记录有5G,而内存容量只有4G,显然不能将记录一次性读入内存中。 或者待排序记录有1G,但是可能有10个用户同时调用排序功能,此时也不能将记录一次性读入内存中,否则将造成内存溢出。

5. MySQL中什么时候会使用外部排序?

内部排序比外部排序速度要慢,显然,MySQL会优先采用内部排序来提高效率。只有当要排序的记录大小超过某个值后,才使用外部排序。这个值就是sort_buffer_size

这意味着经常需要调用外部排序的情况下,调高sort_buffer_size的值可以使排序在内存中进行,从而提高效率。当然,也要考虑内存溢出的问题。

查看sort_buffer_size的值:

mysql> show global variables like 'sort_buffer_size';    
+------------------+--------+   
| Variable_name    | Value  |   
+------------------+--------+   
| sort_buffer_size | 262144 |
+------------------+--------+

调小sort_buffer_size的值,方便下面演示外部排序:

mysql> SET GLOBAL sort_buffer_size=32678;
Query OK, 0 rows affected (0.00 sec)

6. 如何判断某条SQL查询是否使用外部排序?

从MySQL 5.6起,可以使用optimizer_trace可以查看优化器生成的信息。该功能默认是关闭的。查看是否开启:

mysql> show variables like '%optimizer_trace%';   
+------------------------------+----------------------------------------------------------------------------+   
| Variable_name                | Value                                                                      |   
+------------------------------+----------------------------------------------------------------------------+   
| optimizer_trace              | enabled=off,one_line=off                                                   |   
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |   
| optimizer_trace_limit        | 1                                                                          |   
| optimizer_trace_max_mem_size | 16384                                                                      |   
| optimizer_trace_offset       | -1                                                                         |   
+------------------------------+----------------------------------------------------------------------------+   
5 rows in set (0.06 sec)

如果查询结果返回空,说明不支持optimizer_trace,请确认MySQL版本高于5.6。
上面查询结果中,optimizer_trace的值enabled=off说明optimizer_trace是关闭状态的。一般用法是开启optimizer_trace、执行SQL、查看优化过程、关闭optimizer_trace。具体用法如下:

SET optimizer_trace="enabled=on";
要查看优化过程的SQL
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";   

返回结果中有一个QUERY字段,是以json格式保存的优化过程。翻到json的最后面,可看到排序的处理过程:

......
"filesort_summary": {
    "rows": 804,
    "examined_rows": 804,
    "number_of_tmp_files": 6,
    "sort_buffer_size": 32576,
    "sort_mode": "<sort_key, packed_additional_fields>"
}
......

在filesort_summary中可以看到SQL查询结果的总行数rows、检查的行数examined_rows等信息。
其中number_of_tmp_files字段表明排序过程产生的临时文件个数,如果是0,说明排序在内存中进行,大于0则表示使用了外部排序。在上例中,number_of_tmp_files的值为6,意味着在排序时,MySQL将804条记录分别存到6个临时文件中,分别对这6个文件中的记录排序,然后再合并成一个有序的大文件。