STRAIGHT_JOIN 힌트를 이용한 MySQL Explain 읽는 순서 확인

MySQL의 Explain 결과는 다른 DBMS와는 사뭇 다릅니다. 따라서 MySQL의 Explain 결과를 처음 접했을 때는 약간 당황스럽기도 합니다. 조금 더 익숙해지면 오히려 더 편리할 수도 있을지는 모르겠으나, 저로서는 아직은 생소합니다.

읽는 순서는 매우 단순합니다. “위에서부터 아래로 읽으면 됩니다.”

과연 그럴까요? 이를 검증해보기 위해서 STRAIGHT_JOIN 힌트를 이용해서 몇 가지 경우를 확인해봤습니다. (STRAIGHT_JOIN 힌트는 FROM 절 순서대로 조인을 수행하도록 유도하는 힌트입니다.)

단순 조인인 경우는 위에서부터 읽어내려가면 됩니다. Derived 테이블이 있는 경우는 약간 헷갈린 데, 그 이유는 Derived 테이블의 원본 테이블이 맨 하단부에 위치하기 때문입니다. 이 역시 어느 정도 적응이 필요할 것 같습니다.

참고로 MySQL은 ORACLE의 In-Line View를 Derived 테이블 또는 From 절 내의 Subquery라고 합니다. 그리고 In-Line View와 같이 From 절에 View 쿼리를 직접 작성한 것을 unnamed derived 테이블이라고 하고, View를 named derived 테이블이라고도 합니다.

아래는 테스트 내용입니다.

— 테이블 생성

drop table t1;
drop table t2;
drop table t3;

create table t1 (c1 integer, c2 integer);
create table t2 (c1 integer, c2 integer);
create table t3 (c1 integer, c2 integer);

— STRAIGHT_JOIN 힌트를 적용했으므로 조인 순서는 T3->T2->T1이며
— Explain 결과도 이를 나타내고 있습니다.

explain
select STRAIGHT_JOIN * from t3, t2, t1
where t1.c1 = t2.c1
and   t1.c1 = t3.c1;
+----+-------------+-------+----------------------------------------------------+
| id | select_type | table | Extra                                              |
+----+-------------+-------+----------------------------------------------------+
|  1 | SIMPLE      | t3    | NULL                                               |
|  1 | SIMPLE      | t2    | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t1    | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+----------------------------------------------------+

-- pt-visual-explain 결과는 아래에서 위로 읽습니다.

[root@pgserver ~]# mysql -u root -p mydb01 < ./2.sql | pt-visual-explain
Enter password:
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           1
|        +- Table
|           table          t1
+- JOIN
   +- Join buffer
   |  +- Filter with WHERE
   |     +- Table scan
   |        rows           1
   |        +- Table
   |           table          t2
   +- Table scan
      rows           1
      +- Table
         table          t3

— Derived 테이블이 있는 경우를 살펴보겠습니다.
— 아래의 예제는 (t1 scan) -> derived 테이블 생성 -> t2 -> t3 순서로 수행되며,
— Explain은 이를 다음과 같이 표현하고 있습니다.
— ID가 1인 것들이 조인 대상 집합이며 ID가 2인 것이 derived 테이블 생성 작업을 나타냅니다.

explain
select STRAIGHT_JOIN * from (select c1, count(*) cnt from t1 group by c1) a, t2, t3
where  a.c1 = t2.c1
and    a.c1 = t3.c1;
+----+-------------+------------+----------------------------------------------------+
| id | select_type | table      | Extra                                              |
+----+-------------+------------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL                                               |
|  1 | PRIMARY     | t2         | Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     | t3         | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t1         | Using temporary; Using filesort                    |
+----+-------------+------------+----------------------------------------------------+

-- pt-visual-explain은 관련있는 것을 Tree 구조로 표현해줍니다. 
-- 아래의 결과를 보면, t1 scan -> derived 테이블 생성 작업을 연관해서 트리 구조로 표현합니다.

[root@pgserver ~]# mysql -u root -p mydb01 < ./3.sql | pt-visual-explain
Enter password:
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           1
|        +- Table
|           table          t3
+- JOIN
   +- Join buffer
   |  +- Filter with WHERE
   |     +- Table scan
   |        rows           1
   |        +- Table
   |           table          t2
   +- Table scan
      rows           2
      +- DERIVED
         table          derived(temporary(t1))
         +- Filesort
            +- TEMPORARY
               table          temporary(t1)
               +- Table scan
                  rows           1
                  +- Table
                     table          t1

— 참고로 조인 순서를 변경하면 다음과 같이 Explain 결과가 변경됩니다.
— 이 예제 또한, ID가 2인 것이 맨 아래에 위치한다는 것을 알 수 있습니다.

explain
select STRAIGHT_JOIN * from t2, (select c1, count(*) cnt from t1 group by c1) a, t3
where  a.c1 = t2.c1
and    a.c1 = t3.c1;
+----+-------------+------------+----------------------------------------------------+
| id | select_type | table      | Extra                                              |
+----+-------------+------------+----------------------------------------------------+
|  1 | PRIMARY     | t2         | Using where                                        |
|  1 | PRIMARY     | <derived2> | NULL                                               |
|  1 | PRIMARY     | t3         | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t1         | Using temporary; Using filesort                    |
+----+-------------+------------+----------------------------------------------------+

[root@pgserver ~]# mysql -u root -p mydb01 < ./4.sql | pt-visual-explain
Enter password:
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           1
|        +- Table
|           table          t3
+- JOIN
   +- Bookmark lookup
   |  +- DERIVED
   |  |  table          derived(temporary(t1))
   |  |  possible_keys  <auto_key0>
   |  |  +- Filesort
   |  |     +- TEMPORARY
   |  |        table          temporary(t1)
   |  |        +- Table scan
   |  |           rows           1
   |  |           +- Table
   |  |              table          t1
   |  +- Index lookup
   |     key            derived(temporary(t1))-><auto_key0>
   |     possible_keys  <auto_key0>
   |     key_len        5
   |     ref            mydb01.t2.c1
   |     rows           2
   +- Filter with WHERE
      +- Table scan
         rows           1
         +- Table
            table          t2
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