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

드디어 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

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