Print
카테고리: [ MariaDB ]
조회수: 39655

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)