MySQLDBAdmTech: 2017

SQL을 작성할 때, ORDER BY 절의 정렬 컬럼을 조건에 따라 다른 정렬조건을 이용하도록 IF, CASE등의 조건문(conditional order by)을 사용할 수 있다.
conditional order by를 사용한다면 정렬 컬럼 가공 또는 정렬을 위한 filesort가 일어날 수 있다.
그러므로 오직 인덱스만을 사용하여 정렬결과를 추출하는 SQL에 비하여 쿼리 수행 성능은 필연적으로 떨어질 수 밖에 없다.

이 때, 정렬에 영향을 주지 않는 조건문을 사용하므로써 불필요한 성능저하를 유발하는 조건이 있을 수 있다.
이러한 SQL은 conditional order by를 사용하는 것보다, SQL 자체를 분리하여 개발하는 것이 전체적인 성능상 유리하다.

다음의 테스트 시나리오를 살펴보자.TAB_LIST 테이블에는 19064064개의 행이 존재한다.

--==============================================================================

-- 010 Version

--==============================================================================

mysql > select @@VERSION;

+--------------------+

| @@VERSION          |

+--------------------+

| 5.5.37-MariaDB-log |

+--------------------+

--==============================================================================

-- 020 Shows the CREATE TABLE statement that creates the named table

--==============================================================================

CREATE TABLE TAB_LIST

(

    LIST_SEQ        bigint(20)    NOT NULL              COMMENT '목록일련번호'

  , LIST_TYPE       varchar(100)  NOT NULL              COMMENT '목록유형'

  , OBJECT_SEQ      int(11)       NOT NULL  DEFAULT '0' COMMENT '항목일련번호'

  , RECM_CNT        int(11)       NOT NULL  DEFAULT '0' COMMENT '추천수'

  , REG_DATE        datetime      NOT NULL              COMMENT '등록일시'

  , UPDT_DATE       datetime      NOT NULL              COMMENT '수정일시'

  , PRIMARY KEY (LIST_SEQ, LIST_TYPE, OBJECT_SEQ)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='목록'

--==============================================================================

-- 030 data distribution

--==============================================================================

mysql > SELECT COUNT(*) FROM TAB_LIST;

+----------+

| COUNT(*) |

+----------+

| 19064064 |

+----------+

mysql > SELECT COUNT(DISTINCT LIST_SEQ) FROM TAB_LIST;

+--------------------------+

| COUNT(DISTINCT LIST_SEQ) |

+--------------------------+

|                       34 |

+--------------------------+

mysql > SELECT COUNT(DISTINCT LIST_SEQ, LIST_TYPE) FROM TAB_LIST;

+-------------------------------------+

| COUNT(DISTINCT LIST_SEQ, LIST_TYPE) |

+-------------------------------------+

|                              313643 |

+-------------------------------------+

Colored by Color Scripter

SELECT 쿼리의 ORDER BY 조건에 "CASE WHEN 0 = 1 THEN RECM_CNT END DESC, OBJECT_SEQ DESC"을 사용하면 "RECM_CNT DESC" 조건은 항상 false이므로 불 필요한 정렬조건이 된다.
(이 예제의 CASE문 0 = 1 조건은 application로직에 의해, 1 = 1 또는 0 = 1 2가지 케이스로만 발생한다.)
이와 같이, 정렬을 위하여 필요하지 않은 조건이 있다면 불필요한 쿼리 성능저하가 나타난다.

--==============================================================================

-- 040 Query - AS-IS

--==============================================================================

mysql > SELECT

            OBJECT_SEQ

        FROM TAB_LIST

        WHERE 1=1

          AND LIST_SEQ = 102

          AND LIST_TYPE = '2311397'

          AND OBJECT_SEQ <= 43518362

        ORDER BY CASE WHEN 0 = 1 THEN RECM_CNT END DESC, OBJECT_SEQ DESC

        LIMIT 0, 5

-- RESULT

+-------------+

| OBJECT_SEQ  |

+-------------+

|    43518362 |

|    43518359 |

|    43518358 |

|    43518357 |

|    43518356 |

+-------------+

5 rows in set (0.05 sec)

-- EXCUTION PLAN

+------+-------------+----------+-------+---------------+---------+---------+------+--------+-----------------------------+

| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows   | Extra                       |

+------+-------------+----------+-------+---------------+---------+---------+------+--------+-----------------------------+

|    1 | SIMPLE      | TAB_LIST | range | PRIMARY       | PRIMARY | 318     | NULL | 118700 | Using where; Using filesort |

+------+-------------+----------+-------+---------------+---------+---------+------+--------+-----------------------------+

성능개선을 위해서는 conditional order by가 아니라 조건별로 SQL을 분리하여 수행되도록 로직을 수정하는 것이 유리하다.

1 = 1 : RECM_CNT DESC, OBJECT_SEQ DESC
0 = 1 : OBJECT_SEQ DESC

0 = 1 케이스의 SQL은 성능이 개선되었다.

--==============================================================================

-- 050 Query - Tuning

--==============================================================================

mysql > SELECT

            OBJECT_SEQ

        FROM TAB_LIST

        WHERE 1=1

          AND LIST_SEQ = 102

          AND LIST_TYPE = '2311397'

          AND OBJECT_SEQ <= 43518362

        ORDER BY OBJECT_SEQ DESC LIMIT 0, 5

-- RESULT

+-------------+

| OBJECT_SEQ  |

+-------------+

|    43518362 |

|    43518359 |

|    43518358 |

|    43518357 |

|    43518356 |

+-------------+

5 rows in set (0.00 sec)

-- EXCUTION PLAN

+------+-------------+----------+-------+---------------+---------+---------+------+--------+--------------------------+

| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |

+------+-------------+----------+-------+---------------+---------+---------+------+--------+--------------------------+

|    1 | SIMPLE      | TAB_LIST | range | PRIMARY       | PRIMARY | 318     | NULL | 118700 | Using where; Using index |

+------+-------------+----------+-------+---------------+---------+---------+------+--------+--------------------------+

Handler(index range scan)의 사용량 등이 극명하게 개선되었음을 확인할 수 있다.

-- HANDLER USAGE(FLUSH STATUS;SHOW STATUS LIKE '%HANDLER%';)

+----------------------------+-------------+--------------+

| Variable_name              | AS-IS Value | Tuning Value |

+----------------------------+-------------+--------------+

| Handler_commit             | 1           | 1            |

| Handler_delete             | 0           | 0            |

| Handler_discover           | 0           | 0            |

| Handler_icp_attempts       | 0           | 0            |

| Handler_icp_match          | 0           | 0            |

| Handler_mrr_init           | 0           | 0            |

| Handler_mrr_key_refills    | 0           | 0            |

| Handler_mrr_rowid_refills  | 0           | 0            |

| Handler_prepare            | 0           | 0            |

| Handler_read_first         | 0           | 0            |

| Handler_read_key           | 1           | 1            |

| Handler_read_last          | 0           | 0            |

| Handler_read_next          | 58652       | 0            |

| Handler_read_prev          | 0           | 4            |

| Handler_read_rnd           | 0           | 0            |

| Handler_read_rnd_deleted   | 0           | 0            |

| Handler_read_rnd_next      | 0           | 0            |

| Handler_rollback           | 0           | 0            |

| Handler_savepoint          | 0           | 0            |

| Handler_savepoint_rollback | 0           | 0            |

| Handler_tmp_update         | 0           | 0            |

| Handler_tmp_write          | 0           | 0            |

| Handler_update             | 0           | 0            |

| Handler_write              | 0           | 0            |

+----------------------------+-------------+--------------+

-- HANDLER USAGE(FLUSH STATUS;SHOW STATUS LIKE '%SORT%';)

+-------------------+-------------+--------------+

| Variable_name     | AS-IS Value | Tuning Value |

+-------------------+-------------+--------------+

| Sort_merge_passes | 4           | 0            |

| Sort_range        | 1           | 0            |

| Sort_rows         | 5           | 0            |

| Sort_scan         | 0           | 0            |

+-------------------+-------------+--------------+

-- HANDLER USAGE(FLUSH STATUS;SHOW STATUS LIKE '%CREATED_TMP';)

+-------------------------+-------------+--------------+

| Variable_name           | AS-IS Value | Tuning Value |

+-------------------------+-------------+--------------+

| Created_tmp_disk_tables | 0           | 0            |

| Created_tmp_files       | 3           | 0            |

| Created_tmp_tables      | 0           | 0            |

+-------------------------+-------------+--------------+

mysqlslap 을 이용하여 0 = 1 케이스의 SQL 성능을 튜닝 전/후를 간단히 비교해보자.

동시 커넥션 수(concurrency)=100
커넥션당 SQL 반복실행횟수(iterations)=500

--==============================================================================

-- Query - AS-IS

-- mysqlslap >> QPS:0.2k, CPU usage:90%,elapsed time:4m20s

--==============================================================================

shell > mysqlslap -uroot -p --sock=/tmp/mysql.sock --port=3306 --delimiter=";" --concurrency=100  --iterations=500 \

          --query="SELECT OBJECT_SEQ FROM TEMPDB.TAB_LIST WHERE 1=1 AND LIST_SEQ = 102 AND LIST_TYPE = '2311397' AND OBJECT_SEQ <= 43518362  ORDER BY CASE WHEN 0 = 1 THEN RECM_CNT END DESC, OBJECT_SEQ DESC LIMIT 0, 5"

        Benchmark

                Average number of seconds to run all queries: 0.512 seconds

                Minimum number of seconds to run all queries: 0.504 seconds

                Maximum number of seconds to run all queries: 0.527 seconds

                Number of clients running queries: 100

                Average number of queries per client: 1

--==============================================================================                

-- Query - Tuning

-- mysqlslap >> QPS:12.2k, CPU usage:60%,elapsed time:4s

--==============================================================================

shell > mysqlslap -uroot -p --sock=/tmp/mysql.sock --port=3306 --delimiter=";" --concurrency=100  --iterations=500 \

          --query="SELECT OBJECT_SEQ FROM TEMPDB.TAB_LIST WHERE 1=1 AND LIST_SEQ = 102 AND LIST_TYPE = '2311397' AND OBJECT_SEQ <= 43518362 ORDER BY OBJECT_SEQ DESC LIMIT 0, 5"

        Benchmark

                Average number of seconds to run all queries: 0.005 seconds

                Minimum number of seconds to run all queries: 0.005 seconds

                Maximum number of seconds to run all queries: 0.009 seconds

                Number of clients running queries: 100

                Average number of queries per client: 1

Colored by Color Scripter

결과는 다음과 같이 불 필요한 조건을 넣지 않은 Order by가 성능면에서 월등하다.

	Query-AS-IS	Query-Tuning
QPS	0.2k	12.2k
CPU usage	90%	60%
Elapsed time	4m 20s	4s

conditional ORDER BY ASC/DESC