前言

在使用 explain 命令优化SQL语句的时候常常会在Extra列的描述中发现 Using filesort 选项，其实这个名字很容易造成误解，一开始我以为是“文件排序”的意思，进一步说可能就是使用了磁盘空间来进行排序，但是这个理解是错误的，Using filesort 真正含义其实只有 sort 这一个单词，和 file 没有什么关系，Mysql一般是通过内存进行排序的，不过，要是超过了配置中的限制，应该会生成临时表。

分析

Using filesort 的含义很简单，就是使用了排序操作，出现这个选项的常见情况就是 Where 条件和 order by 子句作用在了不同的列上，这种情况还有优化的余地，有些场景由于数据量太小或者语句的简单性可能都不需要优化，既然说Using filesort是使用了排序的意思，那么是不是包含了 order by 子句的查询语句都会有这个选项呢？其实这个排序操作有时是可以避免的。

如果你想把一个表中的所有数据按照指定顺序输出，那么整个排序几乎是不可避免的，比如这个语句select * from a order by id，即使在id列上建立了索引，为了生成指定顺序的数据，那么整个数据的排序也是需要，不过个别时候这个排序还是可以省略的，比如id是该表的主键，并且是自增长的，数据本身就是有序的，那么直接返回数据就行了，相当于 order by id 这一部分被忽略了。

上面提到的常见情况，SQL语句通常写成这样select * from a where type = 5 order by id，这类语句一般会产生 Using filesort 这个选项，即使你在 type 和 id 上分别添加了索引。我们想一下它的工作过程，先根据type的索引从所有数据信息中挑选出满足 type = 5 条件的，然后根据id列的索引信息对挑选的数据进行排序，所以产生了Using filesort选项，想想怎样可以把后面排序的这个步骤省略掉？联合索引可以解决这个问题。

可以在 type, id 两列上建立一个联合索引，索引类型一般是 BTREE，根据Mysql索引的最左原则，可以知道一共建立了type_index和type_id_index两条索引，由于有了 type_id_index 这个联合索引，后面的排序步骤就可以省略了，在按照type = 5 条件挑选数据时，挂在type = 5 节点下的数据，其实按照id列的值也是有顺序的，我们只需要在挑选数据的同时，按照id从小到大的顺序挑选即可，最后得到的数据就是有序的，直接返回就行了，从这一点可以看出，“排序”操作并不是不存在了，只是隐含在了前面必要的步骤中，不需要单独操作了而已，下面举个简单例子，看看具体的效果。

测试环境

Windows 10
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

具体操作

1. 先建立一个测试表格tb，一般为了加快查询速度，会在常用的字段上建立索引

mysql> create table tb(id int, type int, weight int, index t_index(type), index w_index(weight));
Query OK, 0 rows affected (0.02 sec)

2. 创建一个存储fill_test_data用来插入测试数据，创建完成调用一下

CREATE PROCEDURE `fill_test_data`()
BEGIN
    DECLARE i int default 1;
    DECLARE w int default 100;
    DECLARE t int default 1;

    WHILE i <= 100000 do
        insert into tb values(i, t, w);
        set i = i + 1;
        set w = (w + 10) % 1000;
        set t = (t + 1) % 10;
    END WHILE;
END

mysql> call fill_test_data();
Query OK, 1 row affected (25.36 sec)

3. 查询数据，让 Where 条件和 order by 子句作用在不同的列上

mysql> select * from tb where type = 3 order by weight;
+-------+------+--------+
| id    | type | weight |
+-------+------+--------+
| 193   |    3 |     20 |
| 293   |    3 |     20 |
| 393   |    3 |     20 |
...
| 99683 |    3 |    920 |
| 99783 |    3 |    920 |
| 99883 |    3 |    920 |
| 99983 |    3 |    920 |
+-------+------+--------+
10000 rows in set (2.22 sec)

4. 使用 explain命令分析查询语句，就会发现Using filesort出现在了Extra条目中

mysql> explain select * from tb where type = 3 order by weight\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
   partitions: NULL
         type: ref
possible_keys: t_index
          key: t_index
      key_len: 5
          ref: const
         rows: 17672
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

5. 使用SQL命令给表tb的type列和id列添加联合索引

mysql> alter table tb add index tw_index(type, weight);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 再次查询数据，看看与上一次的查询时间相比有没有变化

mysql> select * from tb where type = 3 order by weight;
+-------+------+--------+
| id    | type | weight |
+-------+------+--------+
| 193   |    3 |     20 |
| 293   |    3 |     20 |
| 393   |    3 |     20 |
...
| 99683 |    3 |    920 |
| 99783 |    3 |    920 |
| 99883 |    3 |    920 |
| 99983 |    3 |    920 |
+-------+------+--------+
10000 rows in set (2.13 sec)

7. 再次使用 explain命令分析查询语句，就会发现Using filesort选项已经消失了

mysql> explain select * from tb where type = 3 order by weight\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
   partitions: NULL
         type: ref
possible_keys: t_index,tw_index
          key: tw_index
      key_len: 5
          ref: const
         rows: 17672
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

总结

1. 当Where 条件和 order by 子句作用在不同的列上，建立联合索引可以避免Using filesort的产生
2. 针对当前的例子实际上删除掉type列和id列上的单独索引，只保留联合索引也是可以达到相同效果的
3. 通过比较时间发现去掉了Using filesort情况，耗时少了一点点，实际操作中是不稳定的，但是平均时间可能会有一点提升