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


 

티스토리 툴바