MySQL 실행계획 확인하기

MySQL에서 실행계획을 확인하기 위해서는 EXPLAIN을 사용합니다.

 

아래와 같이 EXPLAIN을 SELECT SQL 앞에 붙여서 실행합니다. 실행하면, SQL은 실제 실행되지 않고, 예상 실행계획이 출력됩니다.

EXPLAIN
SELECT  T2.CUS_GD
        ,COUNT(*)
FROM    T_ORD_BIG T1
        INNER JOIN M_CUS T2
          ON  (T2.CUS_ID = T1.CUS_ID)
WHERE   T1.ORD_YMD LIKE '201703%'
GROUP BY T2.CUS_GD;

# 결과
id   select_type   table   partitions   type     possible_keys                                             key             key_len   ref                         rows     filtered   Extra                                       
==== ============= ======= ============ ======== ========================================================= =============== ========= =========================== ======== ========== =========================================== 
1    SIMPLE        T1      None         range    X_T_ORD_BIG_1,X_T_ORD_BIG_3,X_T_ORD_BIG_4,X_T_ORD_BIG_5   X_T_ORD_BIG_5   35        None                        360858   100.0      Using where; Using index; Using temporary   
1    SIMPLE        T2      None         eq_ref   PRIMARY                                                   PRIMARY         162       db_mysqlbooster.T1.CUS_ID   1        100.0      None                                        

 

오라클과 같은 Tree 형태가 아니어서 해석이 쉽지 않습니다. 아래와 같은 내용들을 기억하고 실행계획을 해석해야 합니다.

 

* id : 해당 단계의 ID입니다. 값이 작을 수록 먼저 실행된다고 해석할 수 있습니다.

* select_type : 해당 단계의 쿼리 유형을 나타냅니다.
   - SIMPLE :  단순한 SELECT를 나타냅니다. 가장 흔하게 나타는 경우입니다.
   - PRIMARY : UNION이나 서브쿼리 존재시에 가장 바깥쪽 쿼리를 뜻합니다.
   - UNION : UNION이 존재하는 쿼리에서 두 번째 UNION 이후의 쿼리 블록입니다.
   - SUBQUERY : SELECT 절 또는 WHERE 절의ㅣ 서브쿼리입니다.
   - DERVIED : FROM절의 서브쿼리입니다.(인라인-뷰) 

   - DEPENDEN SUBQUERY : SELECT, WHERE 절 서브쿼리가 바깥쪽 SELECT에 컬럼을 사용할 때입니다.
* table : 해당 단계의 관련 테이블입니다.
* type :  해당 단계의 접근 유형입니다.
   - const = PK, UNIQUE KEY로 한 건만 조회 하는 경우입니다. (오라클의 INDEX UNIQUE SCAN)
   - ref = INDEX RANGE SCAN 인데, 같다(=) 조건을 처리하는 경우입니다.
   - range = INDEXRANGE SCAN 인데, 범위 조건입니다.
   - index = INDEX FULL SCAN , 이 부분을 가장 유의해야 합니다. 
   - ALL = TABLE ACCESS FULL(TABLE FULL SCAN)

    * 위에서 type이 index나 ALL이면 성능에 문제가 있을 수 있습니다.
   - eq_req = 조인에서 후행 접근하는 쪽에 나타납니다. INDEX UNIQUE SCAN 정도로 이해하면 됩니다.
* possible_keys : 후보 인덱스 목록입니다.
* key : 실제 사용한 인덱스
* key_len : 인덱스를 몇 바이트 사용했는지 표시합니다. 바이트에 따라, 몇 개 컬럼에 인덱스가 적용되었는지 알 수 있습니다.
* rows : 예측 레코드 건수입니다.

 

추가로, MySQL에서 실행계획을 볼때는 JSON 형태로도 출력이 가능합니다. 아래와 같습니다.

EXPLAIN FORMAT = JSON
SELECT  T2.CUS_GD
        ,COUNT(*)
FROM    T_ORD_BIG T1
        INNER JOIN M_CUS T2
          ON  (T2.CUS_ID = T1.CUS_ID)
WHERE   T1.ORD_YMD LIKE '201703%'
GROUP BY T2.CUS_GD;

{
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "478369.15"
     },
     "grouping_operation": {
       "using_temporary_table": true,
       "using_filesort": false,
       "nested_loop": [
         {
           "table": {
             "table_name": "T1",
             "access_type": "range",
             "possible_keys": [
               "X_T_ORD_BIG_1",
               "X_T_ORD_BIG_3",
               "X_T_ORD_BIG_4",
               "X_T_ORD_BIG_5"
             ],
             "key": "X_T_ORD_BIG_5",
             "used_key_parts": [
               "ORD_YMD"
             ],
             "key_length": "35",
             "rows_examined_per_scan": 360858,
             "rows_produced_per_join": 360858,
             "filtered": "100.00",
             "using_index": true,
             "cost_info": {
               "read_cost": "45339.55",
               "eval_cost": "36085.80",
               "prefix_cost": "81425.35",
               "data_read_per_join": "195M"
             },
             "used_columns": [
               "ORD_SEQ",
               "CUS_ID",
               "ORD_YMD"
             ],
             "attached_condition": "(`db_mysqlbooster`.`t1`.`ORD_YMD` like '201703%')"
           }
         },
         {
           "table": {
             "table_name": "T2",
             "access_type": "eq_ref",
             "possible_keys": [
               "PRIMARY"
             ],
             "key": "PRIMARY",
             "used_key_parts": [
               "CUS_ID"
             ],
             "key_length": "162",
             "ref": [
               "db_mysqlbooster.T1.CUS_ID"
             ],
             "rows_examined_per_scan": 1,
             "rows_produced_per_join": 360858,
             "filtered": "100.00",
             "cost_info": {
               "read_cost": "360858.00",
               "eval_cost": "36085.80",
               "prefix_cost": "478369.15",
               "data_read_per_join": "1G"
             },
             "used_columns": [
               "CUS_ID",
               "CUS_GD"
             ]
           }
         }
       ]
     }
   }
 }

 

아래와 같이 FORMAT을 Tree형태로 출력도 할 수 있습니다. 오라클을 사용하는 사용자라면 훨씬 친숙합니다. (사실 이 방법이 훨씬 실행계획 이해하기에 유리합니다.)

EXPLAIN FORMAT = TREE
SELECT  T2.CUS_GD
        ,COUNT(*)
FROM    T_ORD_BIG T1
        INNER JOIN M_CUS T2
          ON  (T2.CUS_ID = T1.CUS_ID)
WHERE   T1.ORD_YMD LIKE '201703%'
GROUP BY T2.CUS_GD;


-> Table scan on <temporary>
     -> Aggregate using temporary table
         -> Nested loop inner join  (cost=478369.15 rows=360858)
             -> Filter: (t1.ORD_YMD like '201703%')  (cost=81425.35 rows=360858)
                 -> Index range scan on T1 using X_T_ORD_BIG_5  (cost=81425.35 rows=360858)
             -> Single-row index lookup on T2 using PRIMARY (CUS_ID=t1.CUS_ID)  (cost=1.00 rows=1)
 

 

 

이 정도를 알면, 실행계획 해석에 큰 어려움은 없을거 같습니다.

참고로, MySQL 8에서는 EXPLAIN ANALYZE를 통해 트리 형태로 실제 실행된 실행계획도 확인할 수 있습니다.

sweetquant.tistory.com/29

MySQL 실제 실행계획

이상입니다~!