Mysql 8.0 Descending Index

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) ~~~