转载

ＭySQL中Using filesort问题的优化方法

在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么MySQL有可能就要进行文件排序

MySQL [db1]> CREATE TABLE `test` (     ->   `a` int(11) DEFAULT NULL,     ->   `b` int(11) DEFAULT NULL,     ->   `c` int(11) DEFAULT NULL     -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec)  MySQL [db1]> SELECT * FROM test; Empty set (0.00 sec)  MySQL [db1]> EXPLAIN SELECT * FROM test; +----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ | id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               | +----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ |  1 | SIMPLE      | test  | system | NULL          | NULL | NULL    | NULL |    0 | const row not found | +----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+ 1 row in set (0.00 sec)  MySQL [db1]> INSERT INTO test VALUES(1,2,3); Query OK, 1 row affected (0.00 sec)  MySQL [db1]> INSERT INTO test VALUES(2,3,4); Query OK, 1 row affected (0.00 sec)  MySQL [db1]> EXPLAIN SELECT * FROM test; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ |  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)  MySQL [db1]> EXPLAIN SELECT * FROM test WHERE a=1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)  MySQL [db1]> EXPLAIN SELECT * FROM test WHERE a=1 ORDER BY b; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ |  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)

对列a和b添加索引之后：

MySQL [db1]> ALTER TABLE test ADD index(a,b); Query OK, 2 rows affected (0.00 sec) Records: 2  Duplicates: 0  Warnings: 0  MySQL [db1]> EXPLAIN SELECT * FROM test WHERE a=1 ORDER BY b; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ |  1 | SIMPLE      | test  | ref  | a             | a    | 5       | const |    1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

从以上例子可以看出test表中，由于最初没有(a,b)索引，所以要进行filesort，添加索引之后，问题解决。

filesort是通过相应的排序算法将取得的数据在内存中进行排序，所使用的内存区域也就是通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，可能同一时刻在MySQL 中存在多个 sort buffer 内存区域。

MySQL中filesort 的实现算法有两种：

１.双路排序：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。

2. 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

MySQL主要通过比较所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。

using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，每次在mysql中进行排序，还是会有影响的。