IN 절과 EXISTS 절 간의 처리 방식 비교 (MySQL 5.7)

이번 시간에 다룰 주제는 MySQL의 IN 절과 EXISTS 절 처리 방식입니다.

IN 절과 EXISTS 절은 논리적으로 동일합니다. 즉, IN 절을 사용하던 EXISTS 절을 사용하든 간에 쿼리 수행 결과는 같습니다. 이 같은 속성 때문에 ORACLE과 PostgreSQL은 이 2개의 실행 계획이 같습니다. 즉, 개발자가 어떤 형태로 쿼리를 작성하더라도 구문의 차이 때문에 다른 실행 계획을 수립하지는 않습니다. MySQL을 연구하기 전까지는 이것을 너무나 당연하게 생각했었는데요.

MySQL은 좀 다릅니다. MySQL은 EXISTS 절을 처리하는 방식과 IN 절을 처리하는 방식 간의 차이가 존재합니다. 제가 테스트한 버전은 5.7인데요. 예전 문서를 보면 가급적 IN 절을 사용하지 말라는 글도 있습니다. 5.x 초기 버전에서는 IN 절에 대한 최적화가 미흡했던 것 같습니다. 하지만 정확한 버전은 모르겠지만 제가 테스트한 5.6, 5.7 버전에서는 IN 절에 대한 최적화 기법이 많이 적용된 것을 확인할 수 있었습니다.

자 그럼 테스트를 통해 살펴보겠습니다. 참고로, Handler 관련 통계 정보 확인을 위해서 매 테스트 전에 항상 flush status 명령어를 수행해서 해당 수치를 초기화했고, 아래의 결과는 5.7에서 수행한 결과입니다.

 

테스트 데이터 생성


drop table t1;
drop table t2;

create table t1 (c1 integer primary key, c2 integer, dummy char(100));
create table t2 (c1 integer, c2 integer primary key, dummy char(100));

set max_heap_table_size=16777216*10;

CALL generate_series(1,1000000);

— t1 테이블에는 100 건을 입력하고, t2 테이블에는 100 만건을 입력합니다.

insert into t1 select series, series, 'dummy' from series_tmp order by series limit 100;
insert into t2 select mod(series,100), series, 'dummy' from series_tmp;

 

메인 쿼리 집합이 매우 작고 서브 쿼리 칼럼에 인덱스가 없는 경우


EXISTS 절은 항상 DEPENDENT SUBQUERY 방식으로 수행됩니다. 따라서 이 같은 경우는 EXISTS 절이 유리합니다. (물론 서브 쿼리 칼럼에 적절한 인덱스가 없는 것은 설계 오류이므로, 설명을 위한 예제일 뿐이라고 이해해주시면 좋을 것 같습니다)

DEPENDENT SUBQUERY 방식은 Main 쿼리 집합 건수만큼 반복적으로 서브 쿼리 테이블을 액세스하는 방식입니다. 이때 T1 테이블에서 C2=10 조건을 만족하는 레코드가 1건 뿐이므로 T2 테이블을 1번만 액세스하면 됩니다. 즉, T2 테이블이 크긴 하지만 1번만 Full Scan을 하면 됩니다. 그리고 세미 조인의 특성 상 매칭 되는 레코드를 1건만 찾으면 스캔을 중단하므로, 매칭 되는 레코드가 앞 부분에 위치한다면 스캔의 부하도 크지 않습니다.

Handler_read_rnd_next 수치는 Full Scan 시에 Scan Call을 요청한 횟수를 의미합니다. 현재 수치가 111이며 이 수치는 다음과 같이 해석할 수 있습니다.

  1. T1 테이블을 Full Scan 할때 101번 Call 수행 (100건 + 1회)
  2. T2 테이블에서 매칭되는 레코드를 찾기 위해서 10회 Call 수행. 즉, 앞에서 10 번째에 위치한 레코드를 찾음

— EXISTS 절 사용

explain
select * from t1 where c2=10 and exists (select 1 from t2 where t2.c1=t1.c1);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 991672 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 111   |
+-----------------------+-------+

이때, IN 절을 사용하면 MATERIALIZED 방식을 사용합니다. 이때도 DEPENDENT SUBQUERY 방식을 사용하면 좋지만 옵티마이저는 MATERIALIZED 방식이 더 유리하다고 판단한 것 같습니다. (결과적으로는 더 느립니다) MATERIALIZED 방식을 사용하므로 100 만 건이나 되는 T2 테이블을 Scan 한 후에 내부적으로 인덱스를 생성하는 작업을 수행합니다. 이로 인해 수행 속도도 EXISTS 절에 비해서 매우 느립니다. Handler_read_rnd_next 수치를 보면 (T2 테이블 레코드 건수인 100만 건+1회) + (T1 테이블 레코드 건수인 100건+1회)만큼 Scan Call을 수행한 것을 알 수 있습니다.

— IN 절 사용

explain
select * from t1 where c2=10 and c1 in (select c1 from t2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref         | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL        |    100 |    10.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | mysql.t1.c1 |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL        | 991672 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+

수행 결과: 1 row in set (0.48 sec)

show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 2       |
| Handler_read_key      | 3       |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1000102 |
+-----------------------+---------+

 

메인 쿼리 집합이 매우 작고 서브 쿼리 칼럼에 인덱스가 있는 경우


서브 쿼리 칼럼에 인덱스를 생성한 후에 동일한 테스트를 수행하면 EXISTS와 IN 절의 성능은 동일합니다. 아래 2개의 결과를 보면 수행 시간은 모두 0.01초 미만이며, 동일한 IO 일량을 나타냅니다. 이때, IN 절의 Explain 결과를 보면 FirstMatch라고 표시됩니다. FirstMatch는 IN 절을 수행하는 옵티마이저의 전략 중의 하나인데요. 이름 그대로 첫 번째 매칭되는 레코드를 찾으면 더 이상 조인을 수행하지 않겠다는 것입니다. 어찌보면 세미 조인의 원리라고도 할 수 있겠네요. 그리고 이 방식은 DEPENDENT SUBQUERY와 동일합니다. 처리 방식이 동일하니 일량도 동일합니다.

— 인덱스 생성

create index t2_c1_idx on t2(c1);

— EXISTS 절 사용

explain
select * from t1 where c2=10 and exists (select 1 from t2 where t2.c1=t1.c1);
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key       | key_len | ref         | rows  | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL      | NULL    | NULL        |   100 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 10438 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

— IN 절 사용

explain
select * from t1 where c2=10 and c1 in (select c1 from t2);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL        |   100 |    10.00 | Using where                 |
|  1 | SIMPLE      | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 10438 |   100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

메인 쿼리 집합이 매우 큰 경우


테스트 결과 간의 비교를 쉽게 하기 위해서 약간 과장된 예제를 들어보겠습니다. 아래의 예제처럼 메인 쿼리 테이블은 매우 크고, 메인 쿼리에는 필터 조건이 없고, 서브 쿼리 내에는 매우 좋은 필터 조건이 있는 경우를 가정해보겠습니다.
이때 EXISTS 절을 사용하면 매우 비효율적으로 쿼리가 수행됩니다. EXISTS 절은 DEPENDENT SUBQUERY 방식으로만 수행되므로 100만 번 서브 쿼리를 수행합니다.

반면 아래의 IN 절의 Explain 결과를 보면 서브 쿼리가 아닌 조인으로 수행됐고 이때 조인 순서는 T1->T2 인 것을 알 수 있습니다. 이 또한 IN 서브 쿼리 최적화 기법의 하나입니다. 즉, T1 테이블에서 C2=10 조건에 만족하는 1건을 추출한 후에 T2 테이블과 조인을 수행한 것입니다. 이때 t2_c1_idx 인덱스를 이용해서 T2 테이블을 10,000번 액세스하게 됩니다. (Handler_read_next=10000)

— EXISTS 절 사용

explain
select * from t2 where exists (select 1 from t1 where t1.c1=t2.c1 and c2=10);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref         | rows   | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
|  1 | PRIMARY            | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        | 991672 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.t2.c1 |      1 |    10.00 | Using where |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+

수행 결과: 10000 rows in set (2.18 sec)

show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1000001 |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1000001 |
+-----------------------+---------+

— IN 절 사용

explain
select * from t2 where c1 in  (select c1 from t1 where c2=10);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL        |  100 |    10.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 9818 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+

수행 결과: 10000 rows in set (0.08 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 10000 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s