Descending Indexes降序索引

降序索引主要是用来减少排序，去除filesort的。

MySQL支持降序索引:索引定义中的DESC不再被忽略，而是按降序存储键值。以前，索引可以以相反的顺序扫描，但会影响性能。可以按前向顺序扫描降序索引，效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时，降序索引还可以使优化器使用多列索引。

mysql> CREATE TABLE t (    ->   c1 INT, c2 INT,    ->   INDEX idx1 (c1 ASC, c2 ASC),    ->   INDEX idx2 (c1 ASC, c2 DESC),    ->   INDEX idx3 (c1 DESC, c2 ASC),    ->   INDEX idx4 (c1 DESC, c2 DESC)    -> );Query OK, 0 rows affected (0.40 sec)mysql> insert into t values(1,1),(1,2),(1,3),(1,4),(1,5),    ->                     (2,1),(2,2),(2,3),(2,4),(2,5),    ->                      (3,1),(3,2),(3,3),(3,4),(3,5),    ->                       (4,1),(4,2),(4,3),(4,4),(4,5),    ->                        (5,1),(5,2),(5,3),(5,4),(5,5);Query OK, 25 rows affected (0.12 sec)Records: 25  Duplicates: 0  Warnings: 0mysql> desc select * from t ORDER BY c1 ASC, c2 ASC;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx1 | 10      | NULL |   25 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)mysql> desc select * from t ORDER BY c1 DESC, c2 DESC;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx4 | 10      | NULL |   25 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> desc select * from t ORDER BY c1 ASC, c2 DESC;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx2 | 10      | NULL |   25 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> desc select * from t ORDER BY c1 DESC, c2 ASC;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx3 | 10      | NULL |   25 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

参考链接