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: 0 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)
只对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: 0 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)
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)
