MySQL IN조건은 ‘=‘과 같을까

저같은 쿼리 왕초보들은 where 절의 IN list 조건을 보면
‘=’ 과 동일하게 생각하는 경우가 드문드문 있을 것 같습니다. 일단 전 그랬습니다….
쿼리 검수를 하다가 IN 조건이 무조건 ‘=‘과 동일한 것은 아니다 라는 것을 알게 되어 기록남겨봅니다.

SELECT
t2.column1 ,
t3.column1
FROM tb_test1 t1
INNER JOIN tb_test2 t2 ON t2.column1 = t1.column1
INNER JOIN tb_test3 ON t3.column1 = t1.column1
WHERE
  t1.coulmn1 = #{idNo}
  AND t1.column2 = 'N'
  AND t1.column3 in ( 'A','B','C')
ORDER BY t1.column4 DESC
LIMIT #{offset}, #{limit};

위와 같은 쿼리가 초당 수십건 씩 들어올 것이라고 쿼리검수가 들어왔습니다.
부끄럽지만 쿼리 튜닝을 이제야 공부하고 있는 저는 ..

  • t1 테이블에 조건이 많으니 t1이 드라이빙 테이블로 풀리겠네
  • order by + limit 이 있고 게다가 해당 컬럼도 드라이빙 테이블 컬럼이니 sorting 을 없애면 좋겠다
  • IN도 IN-List Iterator 방식으로 풀리니까 조건도 모두 ‘=’ 조건 이니 쉽네

라는 생각에 아무 의심도 없이 아래와 같은 인덱스를 생성했습니다.

create index idx1_tb_test1 on tb_test1 (column1,column2,column3,column4);

그리고 실행계획을 보는데..

+----+-------------+-------+--------+---------------+---------+---------------------------------------+
| id | select_type | table | type   | key           | key_len | Extra                                 |
+----+-------------+-------+--------+---------------+---------+---------------------------------------+
|  1 | SIMPLE      | t1    | range  | idx1_tb_test1 | 528     | Using index condition; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | 8       | NULL                                  |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | 8       | NULL                                  |
+----+-------------+-------+--------+---------------+---------+---------------------------------------+

=> using filesort, order by가 인덱스를 타지 못해 sorting이 발생하고 있었습니다.
알고보니 그 이유는 IN-List Iterator 에 대한 제 오해에서 비롯되었습니다.

IN-List Iterator로 풀어보면?

SELECT
t2.column1 ,
t3.column1
FROM tb_test1 t1
INNER JOIN tb_test2 t2 ON t2.column1 = t1.column1
INNER JOIN tb_test3 ON t3.column1 = t1.column1
WHERE
  t1.coulmn1 = #{idNo}
  AND t1.column2 = 'N'
  AND t1.column3 = 'A'
UNION ALL
SELECT ~
.
.
  AND t1.column3 = 'B'
UNION ALL
SELECT ~
.
.
  AND t1.column3 = 'C'

ORDER BY t1.column4 DESC
LIMIT #{offset}, #{limit};

=> 원본 쿼리를 IN-List Iterator 방식으로 풀어보면 위와 같이 바꿀 수 있습니다.
즉 sort 연산을 인덱스로 생략하기 위해서는 column3=‘A’ 부터 ‘B’,‘C’ 브랜치를 차례대로 수행했을때 그 결과가 column4 순으로 정렬되어야 합니다.
column3=‘A’의 결과가 column3=‘B’의 결과보다 column4 기준으로 더 큰 결과여야 하고
column3=‘B’의 결과가 column3=‘C’의 결과보다 column4 기준으로 더 커야만 한다는 것이죠
이는 불가능한 일이므로 옵티마이저는 당연히 sort 를 수행하게 됩니다.

IN 조건을 Filter로 풀리도록

create index idx1_tb_test1 on tb_test1 (column1,column2,column4);


+----+-------------+-------+--------+---------------+---------+---------------------------------------+
| id | select_type | table | type   | key           | key_len | Extra                                 |
+----+-------------+-------+--------+---------------+---------+---------------------------------------+
|  1 | SIMPLE      | t1    | range  | idx1_tb_test1 | 406     | Using where; Backward index scan      |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | 8       | NULL                                  |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | 8       | NULL                                  |
+----+-------------+-------+--------+---------------+---------+---------------------------------------+

=> IN 절의 column3 이 빠져서 t1 을 읽을 때 key_len 값이 528=>406 으로 줄어들고
무엇보다 Extra 부분이 Using where; Backward index scan 으로 변경됐습니다.
인덱스 컬럼을 재구성하여 IN list-iterator 부분을 ACCESS 조건이 아니라 filter 로 사용되도록 변경했기 때문입니다.
IN을 무조건 ‘=’ 조건 취급할 게 아니라는 것, 그래서 sort 연산을 생략하려면 IN 조건절이 access 조건이 아닌 filter 로 풀려야 하는 경우도 있다는 것을 알게 되었습니다.