MySQL에서 Complex View 처리 시의 문제점 및 코딩 시 주의 사항

MySQL은 Complex View (aggregate가 존재하는 derived table)에 대한 View Merging을 지원하지 않을 뿐만 아니라 JPPD (Join Predicate Push-Down)도 지원하지 않습니다. 게다가 Lateral Inline View도 제공하지 않습니다. (Lateral Inline View는 여기를 참조하세요)

이같은 한계로 인해 “과연 MySQL이 제대로 된 성능을 내고 있을까?” 하는 의문이 들기도 합니다. (아니면 제가 모르는 다른 묘안이 있을까요?)

ORACLE은 Complex View에 대한 View Merging 또는 JPPD의 지원 범위를 꾸준히 늘리고 있습니다. 이제 대부분의 Complex View (Union을 포함한)도 JPPD가 가능할 정도입니다. 이뿐만 아니라 12c 버전에서는 Lateral Inline View를 사용자에게 제공함으로써 사용자가 직접 JPPD를 구현할 수도 있습니다.

PostgreSQL은 아직까지는 제한적인 View Merging과 JPPD를 지원하지만, Lateral Inline View를 제공함으로써 이러한 약점을 극복하고 있습니다.

그런데 MySQL은 현재까지 제가 조사한 바로는 Complex View에 대한 어떠한 튜닝 방안도 제시하고 있지 않습니다. 그나마 여기를 보면 Function 리턴 값을 이용해서 Complex View 내로 특정 상숫값을 푸시하는 기법을 제시하고 있지만, 이 방법은 매우 제한적인 방법입니다. 왜냐면 이 방법은 특정 상수값을 View로 푸시할 수 있는 경우에만 유효한 방법이기 때문입니다. 만일 외부 상수 조건을 푸시하는 것이 아니라 조인 조건을 푸시하는 경우에는 사용할 수 없습니다.

전통적인 튜닝 원칙 중의 하나는 “조인 횟수를 줄이는 것이 좋다“는 것입니다. 이를 위해 group by를 먼저 수행한 후에 조인을 수행하는 방식을 사용하곤 했습니다. 그런데 이 원칙을 MySQL에 적용하면 성능상의 문제가 발생하게 됩니다. 예를 들어 살펴보겠습니다.

테스트 환경 구성


drop table dept;
drop table emp;
 
create table dept (deptno integer primary key, dname char(100));
create table emp  (empno integer primary key, ename char(100), deptno integer, salary double);

generate_series 함수 생성 방법은 여기를 참고하세요.

CALL generate_series(1,1000000);
 
insert into dept select series, concat("dname_", convert(series,char)) from series_tmp order by series limit 10000;
insert into emp  select series, concat("ename_", convert(series,char)), mod(series,10000), rand()*100000 from series_tmp order by series limit 1000000;

analyze table dept;
analyze table emp;

emp 테이블의 dept 칼럼에 인덱스를 생성하지 않고 테스트 수행


아래는 부서별 급여을 추출하는 쿼리입니다. case-1과 같이 쿼리를 작성하면 조인 횟수가 불필요하게 많아지므로 “가능한 조인 횟수를 줄이는 것이 좋다”라는 튜닝 원칙에는 위배됩니다. 따라서 Case-2와 같이 Group by 후에 조인을 수행하는 것이 일반적인 방식이었습니다. 물론 아래와 같이 필터 조건이 없는 경우에는 MySQL도 동일한 원칙이 적용됩니다. Case-1의 수행 속도는 4.2초이고 Case-2의 수행 속도는 1.17초이므로 Case-2가 2배 이상 빠릅니다.

Case-1) 조인 후에 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
group by d.dname;

+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | 991230 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.e.deptno |      1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+

9999 rows in set (4.20 sec)

Case-2) Group by 후에 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal
from   dept d, (select deptno,
                       sum(salary) sum_sal,
                       min(salary) min_sal,
                       max(salary) max_sal
                from   emp
                group by deptno) e
where  d.deptno = e.deptno;
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref            | rows   | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL           |   9749 |   100.00 | NULL                            |
|  1 | PRIMARY     |            | NULL       | ref  |               |             | 5       | mysql.d.deptno |    101 |   100.00 | NULL                            |
|  2 | DERIVED     | emp        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL           | 991230 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+

9999 rows in set (1.17 sec)

아래와 같이 필터 조건이 추가된 경우에도 이전의 예제와 동일하게 Group by 후에 조인하는 패턴이 더 빠른 수행 속도를 나타내고 있습니다. 여기까지는 다른 DBMS와 동일합니다. 그런데 인덱스를 생성한 후에는 전혀 다른 결과를 보입니다.

Case-3) 필터 조건 추가 -조인 후 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990'
group by d.dname;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | 991230 |   100.00 | Using where |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.e.deptno |      1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+

1 row in set (3.70 sec)

Case-4) 필터 조건 추가 – Group by 후 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal
from   dept d, (select deptno,
sum(salary) sum_sal,
min(salary) min_sal,
max(salary) max_sal
from   emp
group by deptno) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref            | rows   | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL           |   9749 |    10.00 | Using where                     |
|  1 | PRIMARY     |            | NULL       | ref  |               |             | 5       | mysql.d.deptno |    101 |   100.00 | NULL                            |
|  2 | DERIVED     | emp        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL           | 991230 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+

1 row in set (0.75 sec)

emp 테이블의 dept 칼럼에 인덱스를 생성한 후에 테스트 수행


-- 인덱스 생성
create index emp_deptno_idx on emp (deptno);

인덱스 생성 후에 Case-1 쿼리를 수행하면 기존보다 2배 이상 느려집니다. 그 이유는 조인 순서가 dept->emp로 변경되었고 이로 인해 랜덤 IO가 더 많아졌기 때문입니다. 속도를 향상시키기 위해 생성한 인덱스의 부작용이 발생한 셈이죠. 더군다나 Case-2 쿼리는 기존 대비 7배 이상 느려졌습니다. 기존 방식은 emp 테이블을 full scan하면서 temp table을 생성했지만, 인덱스를 생성함에 따라 emp_deptno_idx 인덱스를 이용해서 건건이 테이블을 액세스하는 부하가 가중되었기 때문입니다. 이 또한, 인덱스 생성에 의한 부작용입니다.

Case-1) 조인 후에 Group By

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
group by d.dname;
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref            | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY        | NULL           | NULL    | NULL           | 9749 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | e     | NULL       | ref  | emp_deptno_idx | emp_deptno_idx | 5       | mysql.d.deptno |  100 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+

9999 rows in set (8.44 sec)

Case-2) Group By 후에 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal
from   dept d, (select deptno,
                       sum(salary) sum_sal,
                       min(salary) min_sal,
                       max(salary) max_sal
                from   emp
                group by deptno) e
where  d.deptno = e.deptno;
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref            | rows   | filtered | Extra |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
|  1 | PRIMARY     | d          | NULL       | ALL   | PRIMARY        | NULL           | NULL    | NULL           |   9749 |   100.00 | NULL  |
|  1 | PRIMARY     |            | NULL       | ref   |                |                | 5       | mysql.d.deptno |    101 |   100.00 | NULL  |
|  2 | DERIVED     | emp        | NULL       | index | emp_deptno_idx | emp_deptno_idx | 5       | NULL           | 991230 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+

9999 rows in set (8.17 sec)

인덱스 생성 후에 Case-3과 Case-4의 결과를 보면, 인덱스 생성 전과 비교해서 확연한 차이를 보입니다. Case-3과 같이 조인을 수행한 후에 group by를 수행하는 경우는 매우 빠른 수행 속도를 보입니다. 그런데 Case-4와 같이 Complex View가 있는 경우는 기존보다 훨씬 더 느립니다.

Case-3) 필터 조건 추가 -조인 후 Group by

select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990'
group by d.dname;
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY        | NULL           | NULL    | NULL           | 9749 |    10.00 | Using where |
|  1 | SIMPLE      | e     | NULL       | ref  | emp_deptno_idx | emp_deptno_idx | 5       | mysql.d.deptno |  100 |   100.00 | NULL        |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+

1 row in set (0.05 sec)

Case-4) 필터 조건 추가 – Group by 후 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal
from   dept d, (select deptno,
                       sum(salary) sum_sal,
                       min(salary) min_sal,
                       max(salary) max_sal
                from   emp
                group by deptno) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref            | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
|  1 | PRIMARY     | d          | NULL       | ALL   | PRIMARY        | NULL           | NULL    | NULL           |   9749 |    10.00 | Using where |
|  1 | PRIMARY     |            | NULL       | ref   |                |                | 5       | mysql.d.deptno |    101 |   100.00 | NULL        |
|  2 | DERIVED     | emp        | NULL       | index | emp_deptno_idx | emp_deptno_idx | 5       | NULL           | 991230 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+

1 row in set (8.39 sec)

정리해보면, 필터 조건이 없이 전체 범위를 처리하는 경우에는 인덱스 없이 (또는 IGNORE INDEX 힌트 이용) Complex View 형태로 처리하는 것이 유리합니다. 하지만 필터 조건이 있다면, 일반 조인 형태로 처리한 후에 Group by를 수행하는 것이 가장 빠릅니다. (물론 이때 조인 칼럼에 인덱스가 있어야 하겠지요)

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