Mysql 8.0 버전부터는 Descending Index 를 지원합니다.
5.7버전에서도 Descending index 생성구문은 지원되었으나 실제로는 ASC 인덱스로 생성되었고 단순히 Ascending Index를 Backward 로 읽는 방식으로 동작했습니다.
Mysql 5.7
mysql> alter table index_test add index idx_1 (b asc,c desc);
Query OK, 0 rows affected (3.88 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table index_test;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_test | CREATE TABLE `index_test` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(100) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `ix_c` (`c`),
KEY `idx_1` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1048561 DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)=> (b asc, c desc) desc index를 생성하나 실제로는 (b asc, c asc) 인덱스가 생성됨
mysql> explain select c,b from index_test order by b asc, c desc limit 10;
+----+-------------+------------+------------+-------+---------------+-------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | index_test | NULL | index | NULL | idx_1 | 308 | NULL | 995930 | 100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
=> 실제로 Descending index가 지원되는 것이 아니기 때문에 order by b asc, c desc 했을 때 Using filesort 가 발생함
Mysql 8.0
mysql> show create table index_test;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_test | CREATE TABLE `index_test` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(100) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `ix_c` (`c`) /*!80000 INVISIBLE */,
KEY `idx_1` (`b`,`c` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=1000014 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)=> KEY 'idx_1' ('b','c' DESC) Descending index 생성됨
mysql> explain select c,b from index_test order by b asc, c desc limit 10;
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | index_test | NULL | index | NULL | idx_1 | 308 | NULL | 10 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)=> 5.7 과는 다르게 Descending index가 동작하여 Using filesort 가 발생하지 않음
5.7
mysql> select c,b from index_test order by b asc, c desc limit 10;
+--------------------------------------+------+
| c | b |
+--------------------------------------+------+
| eb23fc36-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb237bca-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb21ffe2-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb20da6e-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb1eddc4-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb1ed875-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb1e162b-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb1da96b-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb1d2326-f2e9-11e9-8f4c-0242ac110003 | 0 |
| eb1b7a7c-f2e9-11e9-8f4c-0242ac110003 | 0 |
+--------------------------------------+------+
10 rows in set (1.48 sec)
8.0
mysql> select c,b from index_test order by b asc, c desc limit 10;
+--------------------------------------+------+
| c | b |
+--------------------------------------+------+
| fffb9ec4-f182-11e9-a6c1-0242ac110002 | 0 |
| ffee9e1d-f182-11e9-a6c1-0242ac110002 | 0 |
| ffe7dba4-f182-11e9-a6c1-0242ac110002 | 0 |
| ffdc6470-f182-11e9-a6c1-0242ac110002 | 0 |
| ffd639a7-f182-11e9-a6c1-0242ac110002 | 0 |
| ffd5cc5e-f182-11e9-a6c1-0242ac110002 | 0 |
| ffbe33db-f182-11e9-a6c1-0242ac110002 | 0 |
| ffbb79cf-f182-11e9-a6c1-0242ac110002 | 0 |
| ffb625a1-f182-11e9-a6c1-0242ac110002 | 0 |
| ffb25da4-f182-11e9-a6c1-0242ac110002 | 0 |
+--------------------------------------+------+
10 rows in set (0.01 sec)
