前言
MySQL 8.0終于支持降序索引了。其實，從語法上，MySQL 4就支持了，但正如官方文檔所言，"they are parsed but ignored"，實際創建的還是升序索引。
無圖無真相，同一個建表語句，看看MySQL 5.7和8.0的區別。
| create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc)); |
MySQL 5.7
| mysql> show create table slowtech.t1/G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`,`c2`)) ENGINE=InnoDB DEFAULT CHARSET=latin1row in set (0.00 sec) |
雖然c2列指定了desc，但在實際的建表語句中還是將其忽略了。再來看看MySQL 8.0的結果。
| mysql> show create table slowtech.t1/G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`,`c2` DESC)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow in set (0.00 sec) |
c2列還是保留了desc子句。
降序索引的意義
如果一個查詢，需要對多個列進行排序，且順序要求不一致。在這種場景下，要想避免數據庫額外的排序-“filesort”，只能使用降序索引。還是上面這張表，來看看有降序索引和沒有的區別。
MySQL 5.7
| mysql> explain select * from slowtech.t1 order by c1,c2 desc;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+row in set, 1 warning (0.00 sec) |
MySQL 8.0
| mysql> explain select * from slowtech.t1 order by c1,c2 desc;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+row in set, 1 warning (0.00 sec) |
