My Books

My Slides

rss 아이콘 이미지

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

MySQL 2017.04.21 21:39 Posted by 시연아카데미

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     |  | 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     |  | 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  
   |  |  +- Filesort
   |  |     +- TEMPORARY
   |  |        table          temporary(t1)
   |  |        +- Table scan
   |  |           rows           1
   |  |           +- Table
   |  |              table          t1
   |  +- Index lookup
   |     key            derived(temporary(t1))->
   |     possible_keys  
   |     key_len        5
   |     ref            mydb01.t2.c1
   |     rows           2
   +- Filter with WHERE
      +- Table scan
         rows           1
         +- Table
            table          t2


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

MySQL의 Explain 결과를 보는 몇 가지 방법들

MySQL 2017.04.13 04:32 Posted by 시연아카데미

드디어 MySQL 성능 이야기를 시작합니다.

 

성능 이야기의 첫 주제는 Explain입니다. 튜닝의 시작 단계에서는 Explain을 잘 활용해야 하기 떄문입니다.

 

그런데 MySQL의 Explain은 조금 당황스럽네요. 지금 MySQL 필드를 뛰시는 튜너 분들은 Explain 결과를 이용해서 어떤식으로 튜닝을 진행하는지가 궁금해졌습니다.

 

왜냐면 MySQL의 Explain은 Run-Time 결과를 제공하지 않기 때문입니다. 다시 말해, Explain 결과 중에서 속도가 느리거나, Disk IO, 또는 Buffer IO가 과다한 Step을 찾을 수 없습니다.

 

물론, profiling을 이용해서 SQL 레벨의 수행 시간, CPU Time, Disk IO Block 수는 확인할 수 있지만, 쿼리 내의 문제 부분을 특정할 수 없다는 것은 튜닝 작업의 걸림돌이 되는 것만은 확실할 것 같습니다.

 

그리고 기본적으로 제공되는 Explain의 결과는 테이블 Format입니다. 즉, 트리구조로 제공되지 않습니다. 이 역시 가독성을 떨어뜨리는 요소입니다.

 

이를 조금이나마 보완하는 방법은 3가지입니다.

 

1. JSON Format (이 역시 JSON에 익숙하지 않으면 보기 어려움)

2. pt-visual-explain (percona toolkit 설치 필요)

   - 해당 Toolkit은 PERL을 사용하므로 PERL 관련 모듈 몇 개(DBI, DBD::mysql)를 설치해야 합니다.   

3. MySQL Workbench의 Visual Explain (JSON을 그래픽으로 변환해서 보여주는 기능)

 

출력 결과는 다음과 같습니다.

 

1. JSON Format

 

| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.60"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "c1"
          ],
          "attached_condition": "(`mydb01`.`a`.`c1` is not null)"
        }
      },
      {
        "table": {
          "table_name": "c",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "2.40",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "c1"
          ],
          "attached_condition": "(`mydb01`.`c`.`c1` = `mydb01`.`a`.`c1`)"
        }
      },
      {
        "table": {
          "table_name": "b",
          "access_type": "ref",
          "possible_keys": [
            "t2_idx01"
          ],
          "key": "t2_idx01",
          "used_key_parts": [
            "c1"
          ],
          "key_length": "5",
          "ref": [
            "mydb01.a.c1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "3.60",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "c1"
          ]
        }
      }
    ]
  }
} |

2. pt-visual-explain

[root@pgserver ~]# mysql -u root -p mydb01 -e "explain select * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1" | pt-visual-explain
Enter password:
JOIN
+- Index lookup
|  key            b->t2_idx01
|  possible_keys  t2_idx01
|  key_len        5
|  ref            mydb01.a.c1
|  rows           1
+- JOIN
   +- Join buffer
   |  +- Filter with WHERE
   |     +- Table scan
   |        rows           1
   |        +- Table
   |           table          c
   +- Filter with WHERE
      +- Table scan
         rows           1
         +- Table
            table          a

 

3. MySQL Workbench의 Visual Explain

 

 

 

 

참고사이트

 

http://stackoverflow.com/questions/36305809/is-there-a-way-to-show-mysql-explain-plan-like-oracle-as-a-tree

 

 

사족

 

새로운 블로그에만 기술 컨텐츠를 작성하려고 했으나, 워드프레스가 구글 검색이 잘 안 되는 문제가 있습니다. SEO가 잘된다고 해서 워드프레스를 선택한 이유도 있는데, 무료라서 그런 것인지? 아니면 설치형이 아닌 가입형이라서 그런 것인지? 동일한 내용을 구글 웹마스터에서 크롤링을 시켜봐도 워드프레스가 티스토리에 비해서 검색이 잘 안됩니다. 따라서 약간의 공수가 더 들겠지만, 동일한 내용은 양쪽 블로그에 모두 포스팅하도록 하겠습니다.

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


 

티스토리 툴바