MySQL에서 unnamed derived table 사용 시의 주의 사항

MySQL 서버팀에서 제공하는 블로그 내용을 보면 5.7부터 unnamed derived table을 처리하는 방식이 달라졌다는 내용이 있습니다. (여기 참조)
DBMS마다 용어가 달라서 조금 헷갈린대요. unnamed derived table은 ORACLE에서 말하는 in-line view, 즉, FROM 절에 나타나는 SELECT 문장을 의미합니다. 그리고 named derived table은 View입니다. 이 2가지를 통칭해서 derived table이라고 합니다. 그리고 논의의 편의를 위해서 aggregate가 없는 것을 simple한 derived table 이라고 하고, aggregate가 있는 것을 complex한 derived table이라고 하겠습니다.

MySQL의 derived table 처리방식과 타 DBMS의 비교


오라클과 PostgreSQL은 In-line view와 view의 처리 방식이 완벽히 동일합니다. 어찌 보면 사용자 입장에서는 이 방식이 직관적입니다. 그런데 MySQL은 이와 다릅니다. 5.6까지는 simple한 unnamed derived table도 View Merging이 불가능합니다. 즉, 실수로 잘못 코딩한 SQL 때문에 성능이 저하되는 문제가 존재합니다.

5.7부터는 이 문제가 해결되었습니다. 사실 5.6의 동작 방식을 문제라고 해야 할지, 아니면 개발자의 계획된 설계라고 해야 할지는 잘 모르겠습니다. 오히려 이러한 동작 방식을 이용해서 튜닝할 수도 있을 테니까요. 하지만 정확한 동작 원리를 모른 체 작성한 SQL 때문에 생각지도 못한 성능 저하가 발생했다면 이것은 개선해야 할 사항이기도 했을 것 같습니다. 여하튼 5.7에서는 simple 한 unnamed derived table에 대해서는 View Merging 기능을 제공합니다.

테스트 환경 구성


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;

create index emp_deptno_idx on emp (deptno);

analyze table dept;
analyze table emp;

수행 방식 테스트 (5.6, 5.7 버전)


1) 일반 조인

일반 조인은 5.6, 5.7 모두 매우 빠르게 수행됩니다.

explain
select d.dname, d.deptno, e.empno, e.ename
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';

+----+-------------+-------+-----+----------------+----------------+------+-------------+
| id | select_type | table |type | key            | ref            | rows | Extra       |
+----+-------------+-------+-----+----------------+----------------+------+-------------+
|  1 | SIMPLE      | d     |ALL  | NULL           | NULL           | 9749 | Using where |
|  1 | SIMPLE      | e     |ref  | emp_deptno_idx | mysql.d.deptno |  100 | NULL        |
+----+-------------+-------+-----+----------------+----------------+------+-------------+

100 rows in set (0.01 sec)

2) unnamed derived table 사용 시

5.6 버전까지는 unnamed derived table은 항상 temp 테이블을 생성했습니다. 따라서 emp 테이블 전체를 temp 테이블로 생성한 후에 조인을 수행합니다. 이때, temp 테이블에는 index가 없는 셈이므로 내부적으로 auto_key0이라고 하는 temp 테이블용 인덱스를 생성합니다.

물론, 이 역시 부하가 발생하는 작업이지만 dept 테이블 매 건마다 temp 테이블을 full scan 하는 부하보다는 훨씬 더 적은 부하이므로, temp 테이블에 대한 인덱스를 생성하는 작업을 수행하는 것입니다. 이때 Explain의 type 칼럼은 “ref”라고 표시됩니다. 따라서 5.6까지는 unnamed derived table을 사용할 때 이러한 특성을 인지해야만 합니다.

하지만 5.7부터는 simple한 unnamed derived table에 대해서는 View Merging이 가능합니다. 아래의 Explain 결과를 보면 Explain 결과가 2줄로 변경됐고, DERIVED 오퍼레이션이 사라진 것을 알 수 있습니다. 다시말해, 일반 조인과 동일한 방식으로 수행된 것입니다.

explain
select d.dname, d.deptno, e.empno, e.ename
from   dept d, (select empno, ename, deptno from emp) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';

— 5.6 버전 Explain 결과와 수행 시간

+----+-------------+------------+------+-------------+----------------+--------+-------------+
| id | select_type | table      | type | key         | ref            | rows   | Extra       |
+----+-------------+------------+------+-------------+----------------+--------+-------------+
|  1 | PRIMARY     | d          | ALL  | NULL        | NULL           |   9749 | Using where |
|  1 | PRIMARY     |            | ref  |             | mysql.d.deptno |     99 | NULL        |
|  2 | DERIVED     | emp        | ALL  | NULL        | NULL           | 966226 | NULL       |
+----+-------------+------------+------+-------------+----------------+--------+-------------+

100 rows in set (7.29 sec)

— 5.7 버전 Explain 결과와 수행 시간

+----+-------------+-------+-----+----------------+---------------+------+------------+
| id | select_type | table |type | key            |ref            | rows |Extra       |
+----+-------------+-------+-----+----------------+---------------+------+------------+
|  1 | SIMPLE      | d     |ALL  | NULL           |NULL           | 9749 |Using where |
|  1 | SIMPLE      | emp   |ref  | emp_deptno_idx |mysql.d.deptno |  100 |NULL        |
+----+-------------+-------+-----+----------------+---------------+------+------------+

100 rows in set (0.01 sec)

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