Using filesort

Using filesort

简介

当我们使用order by语句的时候，经常会看到执行计划存在Using filesort。那么什么是Using filesort呢？先看官方定义：
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

This section describes when MySQL can use an index to satisfy an ORDER BY clause, 
the filesort operation used when an index cannot be used, 
and execution plan information available from the optimizer about ORDER BY.

在排序语句中，当我们使用不到索引的时候，在执行计划中我们就会看到filesort。
官方的定义是，MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause . The keys then are sorted and the rows are retrieved in sorted order。

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

如下表结构

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

desc SELECT * FROM user ORDER BY created DESC limit 20,40;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.00 sec)

Extra 中可以看到 Using filesort

原因分析

filesort是通过相应的排序算法将取得的数据在内存中进行排序，所使用的内存区域也就是通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，可能同一时刻在MySQL 中存在多个 sort buffer 内存区域。
MySQL中filesort 的实现算法有两种：
１.双路排序：对需要排序的记录生成 <sort_key,rowid> 的元数据进行排序，该元数据仅包含排序字段和rowid。排序完成后只有按字段排序的rowid，因此还需要通过rowid进行回表****操作获取所需要的列的值，可能会导致大量的随机IO读消耗；
2. 单路排序：对需要排序的记录生成 <sort_key,additional_fields> 的元数据，该元数据包含排序字段和需要返回的所有列。排序完后不需要回表，但是元数据要比第一种方法长得多，需要更多的空间用于排序。
MySQL主要通过比较所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。
using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，每次在mysql中进行排序，还是会有影响的。

优化思路

利用mysql聚集索引的性质，分页查询id，避免了Using filesort，这个查询是很快的。而在分页的数据量上，再去查询所有数据，性能就很高了。
比如刚才的sql 我们可以使用如下语句

mysql> desc select * from (select id from user order by created DESC limit 20,40) a left join user b on a.id=b.id;
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL        | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | PRIMARY     | b          | NULL       | ALL   | PRIMARY       | NULL        | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | user       | NULL       | index | NULL          | idx_created | 6       | NULL |    1 |   100.00 | Using index                                        |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------------------------------------+