My Books

My Slides

rss 아이콘 이미지

이번 시간에 살펴볼 내용은 Derived Table을 이용해서 Nested Loop 조인 시에 발생하는 BNL에 대한 튜닝 방법입니다. 이 내용은 이전 포스트인 “MySQL – Block Nested-Loop (BNL)에 대한 이해“와 연결된 내용입니다.

 

이번 포스팅에서 다룰 내용이 길지 않음에도 별도의 포스트로 분리한 이유는, 나름 참신한 방법으로 Nested Loop 시에 발생하는 BNL을 튜닝할 수 있는 방법이기 때문입니다. 결론부터 말하면, Derived Table의 특징을 이용하면 인덱스를 생성하지 않고도 BNL에 대한 성능 튜닝이 가능합니다. 왜냐면 TEMPTABLE 알고리즘을 이용하는 Derived Table은 조인 칼럼에 대해서 내부적으로 임시 인덱스를 생성하기 때문입니다. 이 원리를 이용하면 매우 극적인 튜닝이 가능합니다. 예제를 통해 살펴보겠습니다.

 

앞선 테스트에서 이어서 테스트를 진행하므로, 일단 인덱스를 drop 합니다.

alter table t2 drop index t2_c1_idx;

그리고 다음과 같이 TEMPTABLE 알고리즘을 이용하는 뷰 (named Derived Table)를 생성합니다. 뷰를 생성하는 이유는 5.7부터는 aggregate가 없는 인라인 뷰 (unnamed Derived Table)를 Merge 방식을 사용하므로 TEMPTABLE 방식으로 유도되지 않기 때문입니다. (이 내용은 여기를 참고하세요)

create or replace ALGORITHM=TEMPTABLE view t2_v 
as select * from t2;

 

뷰를 이용한 Explain 결과를 보면, t2 테이블을 스캔한 후에 c1 칼럼에 대해서 내부적인 임시 인덱스 (auto_key0) 생성한 후, 해당 키를 이용해서 조인을 수행한 것을 알 수 있습니다. 그리고 수행 속도를 보면, BNL 방식을 사용할 때보다 월등히 빨라졌습니다. 거의 인덱스를 생성할 때의 수행 속도와 비슷할 정도입니다. 뷰를 생성하는 번거로움은 있지만, 성능 개선 효율을 보면 생각해 볼 만한 방식인 것은 분명합니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL       |  4956 |    11.11 | Using where |
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 5       | mysql.a.c1 |    10 |   100.00 | NULL        |
|  2 | DERIVED     | t2         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL       | 49720 |   100.00 | NULL        |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+------------+---------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                       |
+----------+------------+---------------------------------------------------------------------------------------------+
|        1 | 0.06712000 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 | 0.06936275 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 0.11768975 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+------------+---------------------------------------------------------------------------------------------+

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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

MySQL 2017.05.02 00:52 Posted by 시연아카데미

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)


저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License


 

티스토리 툴바