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

1.概述

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

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

2.创建测试表

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");

3.复现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。

4. 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仅仅是表明需要进行排序操作，并不能说明是否使用外部排序。

5.什么是外存排序？

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

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

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

6.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)

7.如何判断某条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个文件中的记录排序，然后再合并成一个有序的大文件。