MYSQL 쿼리 튜닝 - force index, join 컬럼 type 수정

이관 쿼리를 작성하던 중 시스템컬럼(감사컬럼)에 사용되는 등록자번호가 tobe 기준에 맞춰 변환한 후 이관해야 하는 경우가 있었다.

따라서 이관 대상인 A 테이블의 등록자번호, 변경자번호, 삭제자번호 등을 tobe에 맞게 신규 채번되어있는 매핑테이블 B와 조인하여 B의 테이블의 신규 번호를 가져와야 하는 상황이었다.

단순히 A 테이블의 등록자번호, 변경자번호, 삭제자번호 3개를 B테이블과 조인해 보았다.

A 테이블의 데이터 정합성을 위해 LEFT JOIN 이었으며, 매핑테이블 B에는 조인 대상 컬럼에 INDEX가 생성해 놓은 상태였다.

먼저 조인을 해 보았다.

SELECT ...
  FROM TABLE_A asis
  LEFT JOIN TABLE_B tobe
    ON A.COL = B.COL

결과 : 

id|select_type|table|type|possible_keys     |key|key_len|ref|Extra                                         |rows|
--+-----------+-----+----+------------------+---+-------+---+----------------------------------------------+----+
 1|SIMPLE     |asis |ALL |                  |   |       |   |                                              |3160|
 1|SIMPLE     |tobe |ALL |IX_인덱스명        |   |       |   |Range checked for each record (index map: 0x2)|   1|

왜 type이 ALL일까.

난 분명 index를 다 추가했는데?

 

문제는 join 되는 컬럼 끼리의 type 문제였던 것 같다.

매핑테이블 B의 컬럼은 varchar였고, A테이블의 컬럼은 int 였다.

오라클과 마찬가지라면, 옵티마이저는 조인을 위해 컬럼 변환을 시작할 것이고 컬럼 변환 우선순위는

int > varchar 이므로 varchar로 되어있는 B 컬럼을 int로 변경할 것이다.

내부 실행 로직을 쿼리로 보면 아래와 같을 것이다.

SELECT ...
  FROM TABLE_A asis
  LEFT JOIN TABLE_B tobe
    ON A.COL = cast(B.COL AS integer) -- 이 부분!

B 컬럼이 변형이 되므로 인덱스를 타지 못하게 되는 현상일 것으로 예상이 된다.

일단 B 컬럼의 타입을 A 컬럼과 동일하게 int로 수정했다.

결과 :

id|select_type|table|type|possible_keys     |key|key_len|ref|Extra      |rows|
--+-----------+-----+----+------------------+---+-------+---+-----------+----+
 1|SIMPLE     |asis |ALL |                  |   |       |   |           |3160|
 1|SIMPLE     |tobe |ALL |                  |   |       |   |Using where|   1|

그래도 index를 타지 못한다.

이상한 점은 SELECT 절에  조인에 사용한 컬럼만 사용하면 INDEX를 잘 잡는데.. 다른 컬럼을 같이 SELECT하면 INDEX를 못찾았다.. 이건 이유를 아직 모르겠다.

따라서 INDEX를 강제로 지정해 주었다.

 

SELECT ...
  FROM TABLE_A asis
  LEFT JOIN TABLE_B tobe FORCE INDEX (인덱스명)
    ON A.COL = B.COL

결과 : 

id|select_type|table|type|possible_keys     |key               |key_len|ref                           |rows|Extra|
--+-----------+-----+----+------------------+------------------+-------+------------------------------+----+-----+
 1|SIMPLE     |asis |ALL |                  |                  |       |                              |3160|     |
 1|SIMPLE     |tobe |ref |인덱스명           |인덱스명           |5      |TABLE_A.COL                   |   1|     |

 

성공이다.

기존 6분 컬리던 데이터 이관이 8초!! 까지 줄었다.

쿼리 튜닝이 이정도라니 새삼 놀랐다.