[MySQL/MariaDB] 불필요한 Table Access와 인덱스 컬럼 변형에 대한 튜닝 사례

MySQL/MariaDB에서는 UPDATE 실행 계획을 볼 수 없습니다. 그래서 UPDATE 문법을 SELECT 문법으로 변경해서 실행 계획을 확인해야 합니다. 주기적으로 수행(매 10초)하는 쿼리라서 최대한 Sending data를 줄이지 않으면 성능에 문제가 발생할 수 있었습니다. 개발자가 작성한 쿼리를 튜닝한 사례입니다.

 

아래는 EST_DB_SVC_CPS 테이블의 인덱스 정보입니다.

• Primary Key : EST_NO, EST_SVC_NO
• Non Unique Index : CONN_TEST_TASK_UPDT_DTTM

1. 원본 쿼리

1.1. 실행 계획 해석
실행계획을 보면 "EST_DB_SVC_CPS" 테이블을 Table Full Scan 하고 있습니다.(type=ALL)
처음 조인하는 테이블에서 다음 읽어야 할 테이블의 Primary Key, Unique Key 컬럼을 사용해서 두 번째 테이블에서 출력되는 레코드가 1건으로 판단합니다.(type=eq_ref)

1.2. 성능 문제점
EST_DB_SVC_CPS 테이블을 두번 액세스 하는 비효율이 발생했습니다. 
"DATE_ADD(SQ2.CONN_TEST_TASK_UPDT_DTTM , INTERVAL + 180 SECOND )"인덱스 컬럼을 변경하면서 적절하게 인덱스를 사용할 수 없었습니다.
 

/* UPDATE 원본 쿼리 */
UPDATE    EST_DB_SVC_CPS A
SET       A.CONN_TEST_TASK_GRP_NO = NULL
WHERE     EXISTS
            (SELECT    'O'
             FROM      (SELECT    SQ2.EST_NO, SQ2.EST_SVC_NO
                        FROM      EST_DB_SVC_CPS SQ2
                        WHERE     SQ2.CONN_TEST_TASK_GRP_NO IS NOT NULL
                        AND       DATE_ADD(SQ2.CONN_TEST_TASK_UPDT_DTTM , INTERVAL + 180 SECOND ) < CURRENT_TIMESTAMP() ) SQ1 
             WHERE     SQ1.EST_NO = A.EST_NO
             AND       SQ1.EST_SVC_NO = A.EST_SVC_NO);

/* 튜닝을 위해서 SELECT로 변경한 원본 쿼리 */
EXPLAIN   EXTENDED 
SELECT    A.CONN_TEST_TASK_GRP_NO
FROM      EST_DB_SVC_CPS A
WHERE     EXISTS
            (SELECT    'O'
             FROM      (SELECT    SQ2.EST_NO, SQ2.EST_SVC_NO
                        FROM      EST_DB_SVC_CPS SQ2
                        WHERE     SQ2.CONN_TEST_TASK_GRP_NO IS NOT NULL
                        AND       DATE_ADD(SQ2.CONN_TEST_TASK_UPDT_DTTM , INTERVAL + 180 SECOND ) < CURRENT_TIMESTAMP() ) SQ1 
             WHERE     SQ1.EST_NO = A.EST_NO
             AND       SQ1.EST_SVC_NO = A.EST_SVC_NO);
          
id|select_type|table|type  |possible_keys                    |key    |key_len|ref                                |ROWS  |filtered|Extra      |
--|-----------|-----|------|---------------------------------|-------|-------|-----------------------------------|------|--------|-----------|
 1|PRIMARY    |A    |ALL   |PRIMARY,EST_DB_SVC_CPS_FK01      |       |       |                                   | 12041|     100|           |
 1|PRIMARY    |SQ2  |eq_ref|PRIMARY,EST_DB_SVC_CPS_FK01      |PRIMARY|8      |ESTDB.A.EST_NO,ESTDB.A.EST_SVC_NO  |   1  |     100|Using where|

MariaDB [ESTDB]> show profile for query 4;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000050 |
| Checking permissions   | 0.000014 |
| Opening tables         | 0.000012 |
| After opening tables   | 0.000013 |
| System lock            | 0.000004 |
| Table lock             | 0.000005 |
| Init                   | 0.000046 |
| Optimizing             | 0.000030 |
| Statistics             | 0.000027 |
| Preparing              | 0.000017 |
| Executing              | 0.000012 |
| Sending data           | 0.081115 |   ->> 해당 항목을 줄이는 게 튜닝 목표
| End of update loop     | 0.000014 |
| Query end              | 0.000004 |
| Commit                 | 0.000006 |
| Closing tables         | 0.000003 |
| Removing tmp table     | 0.000005 |
| Closing tables         | 0.000004 |
| Unlocking tables       | 0.000003 |
| Closing tables         | 0.000018 |
| Starting cleanup       | 0.000003 |
| Freeing items          | 0.000027 |
| Updating status        | 0.000015 |
| Reset for next command | 0.000011 |
+------------------------+----------+
24 rows in set (0.000 sec)

 

2. 쿼리 튜닝

불필요한 테이블 엑세스를 줄이고, 인덱스 컬럼 부분을 변형하지 않고, 대입되는 컬럼을 상수 부분에 적용합니다. 

EXPLAIN   EXTENDED
SELECT    A.CONN_TEST_TASK_GRP_NO
FROM      EST_DB_SVC_CPS A
WHERE     A.CONN_TEST_TASK_UPDT_DTTM < DATE_ADD(CURRENT_TIMESTAMP() , INTERVAL - 180 SECOND )
AND       A.CONN_TEST_TASK_GRP_NO IS NOT NULL

id|select_type|table|type |possible_keys            |key                      |key_len|ref|rows|filtered|Extra                             |
--|-----------|-----|-----|-------------------------|-------------------------|-------|---|----|--------|----------------------------------|
 1|SIMPLE     |A    |range|EST_DB_SVC_CPS_IX01|EST_DB_SVC_CPS_IX01            |5      |   | 450|     100|Using index condition; Using where|
 
MariaDB [ESTDB]> show profile for query 7;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000036 |
| Checking permissions   | 0.000005 |
| Opening tables         | 0.000011 |
| After opening tables   | 0.000004 |
| System lock            | 0.000004 |
| Table lock             | 0.000005 |
| Init                   | 0.000012 |
| Optimizing             | 0.000009 |
| Statistics             | 0.000008 |
| Preparing              | 0.000010 |
| Executing              | 0.000003 |
| Sending data           | 0.017332 |  ->> 튜닝 전(0.081115), 튜닝 후 (0.017332)
| End of update loop     | 0.000005 |
| Query end              | 0.000003 |
| Commit                 | 0.000004 |
| Closing tables         | 0.000003 |
| Unlocking tables       | 0.000003 |
| Closing tables         | 0.000005 |
| Starting cleanup       | 0.000003 |
| Freeing items          | 0.000005 |
| Updating status        | 0.000122 |
| Reset for next command | 0.000019 |
+------------------------+----------+
22 rows in set (0.000 sec)

/* 최종 UPDATE 튜닝 쿼리 */
UPDATE    EST_DB_SVC_CPS A
SET       A.CONN_TEST_TASK_GRP_NO = NULL
WHERE     A.CONN_TEST_TASK_UPDT_DTTM < DATE_ADD(CURRENT_TIMESTAMP() , INTERVAL - 180 SECOND )
AND       A.CONN_TEST_TASK_GRP_NO IS NOT NULL
;

 

3. 결과

보통 날짜 데이터에 대해서 쿼리를 작성 할 때 컬럼을 변경해서 대입하는 경우가 상당히 많습니다. 그렇게 작성하는 것보다 인덱스 컬럼은 최대한 변경하지 않은 상태에서 상수 값을 대입하면 인덱스를 적절하게 사용 가능합니다.

그리고 테이블을 여러 번 액세스 해야 하는 경우가 아니라면, 테이블을 한 번만 액세스 해서 성능을 최적화하는 것이 좋습니다.