Mysql调优之Using filesort

前言

在使用 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 (c) 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)