[MySQL] Index 키 설정시 DESC 는 8.0 부터.

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 가 발생하지 않음

참고 : Mysql 8.0 Descending Index (sarc.io)

Mysql 8.0 Descending Index