mysql索引测试案例(五)
ql> explain select action,name from user_action order by action asc, name desc;
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
对索引列同时使用了ASC和DESC时，需要使用filesort
mysql> explain select action,name from user_action where user_id=1 order by action asc, name desc;
+----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | user_action | const | PRIMARY,idx_userid | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
虽然也对索引列同时使用了ASC和DESC，但是通过where语句（主键）将order by中索引列转为常量，则直接根据索引顺序回表读数据
mysql> explain select action,name from user_action where action='run' order by action asc, name desc;
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where; Using filesort |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
where语句与order by语句使用了不同的索引也是需要排序的，where使用idx_action，order by使用了idx_useraction_action_name
总上面使用filesort的情况：
1) 查询的行数过多，优化器认为要全表扫描，且没有使用覆盖索引
2) 对索引列同时使用了ASC和DESC时，但是通过where语句（主键）将order by中索引列转为常量例外
3) where语句与order by语句使用了不同的索引也是需要排序的
4) order by子句中加入了非索引列,且非索引列不在where子句中
5) ORDER BY语句中索引列使用了表达式
------end------