背景知识

Using filesort是MySQL执行计划Extra字段中的一个重要值，表示查询出所有数据再进行排序。此排序方式为文件排序，没有走索引排序using index.
一般来说，执行计划中如果Extra字段中值为Using filesort时，那么type字段(查询类型)一般为index或ALL。（两者都是查询所有数据，index与ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从全表扫描读取）

type字段的结果值，从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说，好的sql查询至少达到range级别，最好能达到ref

[

](https://blog.csdn.net/Bronze5/article/details/113817719)

表准备

  1. CREATE TABLE `using_index`
  2. (
  3.     `id` int NOT NULL auto_increment,
  4.     `a`  varchar(255) DEFAULT NULL,
  5.     `b`  varchar(255) DEFAULT NULL,
  6.     `c`  varchar(255) DEFAULT NULL,
  7.     PRIMARY KEY (`id`),
  8.     KEY `idx_abc` (`a`, `b`, `c`)
  9. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

插入数据：

  1. insert into using_index(a, b, c) value ('a01', 'b01', 'c01');
  2. insert into using_index(a, b, c) value ('a02', 'b02', 'c02');
  3. insert into using_index(a, b, c) value ('a03', 'b03', 'c03');
  4. insert into using_index(a, b, c) value ('a04', 'b04', 'c04');
  5. insert into using_index(a, b, c) value ('a00', 'b04', 'c04');
  6. insert into using_index(a, b, c) value ('a00', 'b04', 'c04');
  7. insert into using_index(a, b, c) value ('a00', 'b04', 'c04');
  8. insert into using_index(a, b, c) value ('a00', 'b04', 'c04');
  9. insert into using_index(a, b, c) value ('a00', 'b00', 'c04');

MySQL的每条查询，都会先选择一个适合的索引。每个索引都有自己的字段列表，如表索引名称为idx_abc，该索引包含三个字段：a,b,c

场景

查询字段在索引列表中

1.1 没有where条件只有order by

只需要让order by后面的字段，遵循最左前缀即可

  1. mysql> explain select a,b,c from using_index order by a,b;
  2. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  3. | id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  5. |  1 | SIMPLE      | using_index | NULL       | index | NULL          | idx_abc | 3069    | NULL |    9 |   100.00 | Using index |
  6. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  7. 1 row in set, 1 warning (0.01 sec)

上述SQL走：idx_abc索引，基于索引a_b_c的有序数据返回

以下场景都会出现using filesort

  1. order by的字段顺序与索引字段顺序不一致
  2. order by的字段没有遵循最左前缀原则
  3. order by字段非连续
  4. order by字段升序降序混合

如排序字段不连续, 无法按最左前缀匹配：

  1. mysql> explain select a,b,c from using_index order  by a,c;
  2. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  3. | id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
  4. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  5. |  1 | SIMPLE      | using_index | NULL       | index | NULL          | idx_abc | 3069    | NULL |    9 |   100.00 | Using index; Using filesort |
  6. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  7. 1 row in set, 1 warning (0.00 sec)

1.2 有where + 常量字段+ order by

where子句常量的字段 + order by后面的字段，遵循最左前缀即可

  1. mysql> explain select a,b,c from using_index where a='a01' order by b;
  2. +----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  3. | id | select_type | table       | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  4. +----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  5. |  1 | SIMPLE      | using_index | NULL       | ref  | idx_abc       | idx_abc | 1023    | const |    1 |   100.00 | Using index |
  6. +----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  7. 1 row in set, 1 warning (0.00 sec)

上述SQL走：idx_abc索引，命中字段a，然后基于b排序，本质还是最左前缀匹配，通过a字段来选择索引。

1.3 where + 范围查询字段 + order by

通过a字段查找索引后，然后走索引内部有序，避免filesort

  1. mysql> explain select a,b,c from using_index where a >='a01' order by a,b;
  2. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
  3. | id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
  4. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
  5. |  1 | SIMPLE      | using_index | NULL       | range | idx_abc       | idx_abc | 1023    | NULL |    4 |   100.00 | Using where; Using index |
  6. +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
  7. 1 row in set, 1 warning (0.00 sec)

查询字段不在索引列表中

只需判断查出的结果行，是否小于总行数的50%即可。如果小于50%就不会出现using filesort的情况。

注：select后面尽量都是索引列表中的字段，避免回表。

  2. mysql> explain select id,a,b,c from using_index where a='a00' order by b,c;
  3. +----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  4. | id | select_type | table       | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  5. +----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  6. |  1 | SIMPLE      | using_index | NULL       | ref  | idx_abc       | idx_abc | 1023    | const |    8 |   100.00 | Using index |
  7. +----+-------------+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
  8. 1 row in set, 1 warning (0.00 sec)

最新版本的MySQL测试显示：a=a00,结果行大于总数的50%，也会走索引匹配。

参考资料

https://blog.csdn.net/Bronze5/article/details/113817719