개요

* 8.0 에서 Descending Index가 구현되었다 (5.7까지는 INDEX 생성구문에 넣을 수는 있었지만 무시되었음).

* 5.7까지 진행했던 방법은, Ascending Index를 Backward index scan으로 읽어들이는 방법을 사용하여 Descending Index의 흉내를 낼 수 있지만, 그에따른 부작용이 발생되었다.
    - ORDER BY 구문에 사용되는 컬럼의 ORDER 순서가 DESC과 ASC이 혼재되어 사용되어져야 하는 경우, index를 (col1:DESC , col2:ASC) 처럼 만들겠지만 실제로는 (col1:ASC , col2:ASC) 로 만들어져 col1의 Backward index scan 만이 가능했다.
    - Backward index scan 이 Forward Index scan 만큼 성능이 좋지 못했다. 이는 처음 구성된 InnoDB 아키텍쳐의 한계 (Page lock order, Page 내의 single linked list를 가진 데이터) 이다.


사용예제

  • 아래 예제는 5000건의 dummy data를 만들고 compositeOrder_idx (c desc, b asc) 라는 Index를 만들어 실행계획을 확인하는 예제이다.

5.7

  • mysql 5.7 [localhost] {msandbox} (test) > create table tbl_alter (a int primary key auto_increment, b int, c int, d varchar(40));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql 5.7 [localhost] {msandbox} (test) > insert into tbl_alter (b,c,d)  select round(rand()*10)+1, round(rand()*1000)+1, uuid()
     from information_schema.columns A1, information_schema.columns B2 limit 5000;
    Query OK, 5000 rows affected (0.22 sec)
    Records: 5000  Duplicates: 0  Warnings: 0
    
    mysql 5.7 [localhost] {msandbox} (test) > explain select c,b from tbl_alter order by c desc, b asc limit 10;
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
    | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
    |  1 | SIMPLE      | tbl_alter | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4999 |   100.00 | Using filesort |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql 5.7 [localhost] {msandbox} (test) > alter table tbl_alter add index compositeOrder_idx (c desc, b asc);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql 5.7 [localhost] {msandbox} (test) > explain  select c,b from tbl_alter order by c desc, b asc limit 10;
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
    | id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                       |
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
    |  1 | SIMPLE      | tbl_alter | NULL       | index | NULL          | compositeOrder_idx | 10      | NULL | 4999 |   100.00 | Using index; Using filesort |
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
    1 row in set, 1 warning (0.00 sec)
    

8.0

  • mysql 8.0 [localhost] {msandbox} (test) > create table tbl_alter (a int primary key auto_increment, b int, c int, d varchar(40));
    Query OK, 0 rows affected (0.07 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > insert into tbl_alter (b,c,d)  select round(rand()*10)+1, round(rand()*1000)+1, uuid() from information_schema.columns A1, information_schema.columns B2 limit 5000;
    Query OK, 5000 rows affected (0.10 sec)
    Records: 5000  Duplicates: 0  Warnings: 0
    
    mysql 8.0 [localhost] {msandbox} (test) > explain select c,b from tbl_alter order by c desc, b asc limit 10;
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
    | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
    |  1 | SIMPLE      | tbl_alter | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5000 |   100.00 | Using filesort |
    +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+----------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > alter table tbl_alter add index compositeOrder_idx (c desc, b asc);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql 8.0 [localhost] {msandbox} (test) > explain  select c,b from tbl_alter order by c desc, b asc limit 10;
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
    | id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tbl_alter | NULL       | index | NULL          | compositeOrder_idx | 10      | NULL |   10 |   100.00 | Using index |
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.01 sec)
    
    --^^ 5.7 과 다르게 file sort가 일어나지 않는것이 확인된다.
    
    
    
    mysql 8.0 [localhost] {msandbox} (test) > explain  select c,b from tbl_alter order by c asc limit 10;
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+----------------------------------+
    | id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                            |
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+----------------------------------+
    |  1 | SIMPLE      | tbl_alter | NULL       | index | NULL          | compositeOrder_idx | 10      | NULL |   10 |   100.00 | Backward index scan; Using index |
    +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+----------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    --^^ Backward index scan 이 진행된다면, 실행계획의 결과로서 "Backward index scan" 문구를 보여준다.
    

성능테스트

  • 아래 예제는 데이터 100000 건을 Ascending Index와 함께 만들고 “order by d [asc desc] limit 99999,1” 을 통해 [Forward Backward] Index scan의 성능테스트를 진행한 예제이다.
    mysql 8.0 [localhost] {msandbox} (test) > create table tbl_alter (a int primary key auto_increment, d varchar(40), key d_idx(d));
    Query OK, 0 rows affected (0.01 sec)
    
    
    mysql 8.0 [localhost] {msandbox} (test) > insert into tbl_alter (d)  select uuid() from information_schema.columns A1, information_schema.columns B2 limit 100000;
    Query OK, 100000 rows affected (5.38 sec)
    Records: 100000  Duplicates: 0  Warnings: 0
    
    
    mysql 8.0 [localhost] {msandbox} (test) > DELIMITER $$
    mysql 8.0 [localhost] {msandbox} (test) > DROP FUNCTION IF EXISTS `getValue`$$
    Query OK, 0 rows affected (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > CREATE FUNCTION `getValue`() RETURNS INT(11)
        -> DETERMINISTIC
        -> BEGIN
        ->    DECLARE dummy int default 0;
        ->    select 1 into @dummy;
        ->    RETURN dummy;
        ->    END$$
    Query OK, 0 rows affected (0.01 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > DROP FUNCTION IF EXISTS `getValueASC`$$
    Query OK, 0 rows affected (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > CREATE FUNCTION `getValueASC`() RETURNS INT(11)
        -> DETERMINISTIC
        -> BEGIN
        ->    DECLARE dummy int default 0;
        ->    select 1 into @dummy from tbl_alter order by d asc limit 99999,1;
        ->    RETURN dummy;
        ->    END$$
    Query OK, 0 rows affected (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) >
    mysql 8.0 [localhost] {msandbox} (test) > DROP FUNCTION IF EXISTS `getValueDESC`$$
    Query OK, 0 rows affected (0.00 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > CREATE FUNCTION `getValueDESC`() RETURNS INT(11)
        -> DETERMINISTIC
        -> BEGIN
        ->    DECLARE dummy int default 0;
        ->    select 1 into @dummy from tbl_alter order by d desc limit 99999,1;
        ->    RETURN dummy;
        ->    END$$
    Query OK, 0 rows affected (0.00 sec)
    
    
    mysql 8.0 [localhost] {msandbox} (test) > DELIMITER ;
    mysql 8.0 [localhost] {msandbox} (test) > SELECT BENCHMARK(1000,getValue());
    +----------------------------+
    | BENCHMARK(1000,getValue()) |
    +----------------------------+
    |                          0 |
    +----------------------------+
    1 row in set (0.01 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > SELECT BENCHMARK(1000,getValueASC());
    +-------------------------------+
    | BENCHMARK(1000,getValueASC()) |
    +-------------------------------+
    |                             0 |
    +-------------------------------+
    1 row in set (17.84 sec)
    
    mysql 8.0 [localhost] {msandbox} (test) > SELECT BENCHMARK(1000,getValueDESC());
    +--------------------------------+
    | BENCHMARK(1000,getValueDESC()) |
    +--------------------------------+
    |                              0 |
    +--------------------------------+
    1 row in set (21.56 sec)
    
    
    
    --^^ 십만건의 Forward Index Scan 과 십만건의 Backword Index Scan 의 성능을 비교한 결과 20%가량 Forward Index Scan 이 빠른 결과를 보였다. (21.56/17.84 = 1.20)
    

적용범위

  • 위의 예제처럼 ORDER BY 구문에 사용되는 컬럼의 ORDER 순서가 DESC과 ASC이 혼재되어 있는 경우.

  • ORDER BY col DESC 구문이 주로 실행되어질때, Descending Index를 생성을 통해 Forward Index Scan 을 진행하여 성능을 더 끌어올리려는 경우.