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: 0
mysql> 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)
參考連結