正文
mysql explain using filesort
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
创建表，字段tid上无索引（mysql 5.7）
CREATE TABLE `test` (
`tid` int(11) DEFAULT NULL,
`tname` varchar(12) DEFAULT NULL,
`test_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`tvalue` varchar(90) DEFAULT NULL,
`CreateTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`c1` varchar(11) DEFAULT NULL,
`c2` varchar(11) DEFAULT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55652 DEFAULT CHARSET=utf8mb4
tid上无索引
mysql> explain select * from test where test_id < 4 order by tid;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。
【这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作而已】。
当然，using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。
此时，可以进行的优化：
1、修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。
2、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。
mysql> alter table test add index idx_c(c1,c2);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> explain select tid from test where c1='' order by c2 ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | ref | idx_c | idx_c | 47 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
只对order by 后的字段加索引并不能避免filesort，还需要在where条件中使用该字段；
mysql> explain select tid from test where c1='' order by c1 ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_c | idx_c | 47 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select tid from test where c1='' order by c2 ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | ref | idx_c | idx_c | 47 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select tid from test where test_id < 5 order by c2 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select tid from test where test_id < 5 order by c1 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table test add index idx_tid(tid);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from test where test_id < 4 order by tid;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select tid from test where tid < 5 order by tid ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | range | idx_tid | idx_tid | 5 | NULL | 28 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
