MySQL을 다루면서 알게된 지식을 간단하게 정리하는 글입니다.

MySQL 옵티마이저의 실행 계획에 대한 내용은 이 글을 참고하시면 도움이 됩니다.

EXPLAIN Statement

EXPLAIN Statement는 옵티마이저가 결정한 실행 계획에 대한 정보를 제공합니다.

EXPLAIN Statement를 통해서 사용자의 요구 사항(SQL 명령)을 어떤식으로 처리할지(테이블 조인 순서, 인덱스 사용 유무 등)에 대한 정보를 알 수 있으므로 SQL 최적화를 진행할 때 없어서는 안될 중요한 명령문 중 하나입니다.

MySQL 문서에서 설명하는 EXPLAIN Statement의 사용 방법은 아래와 같습니다.

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type

explain_type: {
	FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt

explainable_stmt: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

EXPLAIN

EXPLAIN은 SQL 명령문을 실제로 실행하지 않고 실행 계획을 확인하는 방법입니다.

EXPLAIN [explainable_stmt]

미리 작성한 SQL 명령의 실행 계획을 확인하는 방법으로, EXPLAIN을 사용할 때 가장 많이 사용하는 방식입니다.

사용 예시는 아래와 같습니다.

EXPLAIN SELECT * FROM [table] WHERE [where]...;

EXPLAIN [FOR CONNECTION connection_id]

현재 연결된 ConnectionID를 전달하여 실행 계획을 확인하는 방법으로, 해당 ConnectionID에서 현재 실행중인 SQL 명령에 대한 실행 계획을 확인할 수 있습니다.

특정 Connection에서 지연이 발생하는 경우 간편하게 실행 계획을 확인할 수 있는 방법 중 하나입니다.

사용 예시는 아래와 같습니다.

EXPLAIN FOR CONNECTION 223;

Format

EXPLAIN 문에서는 TRADITIONAL과 JSON Format만 사용이 가능합니다.

TRADITIONAL

explain_type을 명시하지 않았을 때 사용하는 기본 Format 형식입니다.

응답 구조

[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "naver_land_articles",
    "type": "ref",
    "possible_keys": "naver_land_articles_trad_tp_cd_created_at_index",
    "key": "naver_land_articles_trad_tp_cd_created_at_index",
    "key_len": "1",
    "ref": "const",
    "rows": 33109,
    "Extra": "Using where"
  }
]

JSON

응답 구조

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "naver_land_articles",
      "access_type": "ref",
      "possible_keys": ["naver_land_articles_trad_tp_cd_created_at_index"],
      "key": "naver_land_articles_trad_tp_cd_created_at_index",
      "key_length": "1",
      "used_key_parts": ["trad_tp_cd"],
      "ref": ["const"],
      "rows": 33106,
      "filtered": 100,
      "attached_condition": "crawler.naver_land_articles.trad_tp_cd <=> 'A1' and crawler.naver_land_articles.trad_tp_cd = 'A1' and crawler.naver_land_articles.region1 in ('경기도','서울')"
    }
  }
}

응답 필드

자세한 응답 필드에 대한 설명은 해당 문서를 참고해주세요.

EXPLAIN ANALYZE [select_statement]

EXPLAIN ANALYZE는 MySQL 8.0.18부터 도입된 기능으로, 쿼리를 실제로 실행하면서 실행 계획과 실행 결과를 확인하는 방법입니다.

EXPLAIN 과는 다르게 구체적인 성능 문제를 진단하는데 사용을 합니다.

요청 SQL문

EXPLAIN ANALYZE SELECT * FROM [table] WHERE [where]...;

Format

EXPLAIN ANALYZE에서는 TREE Format만 사용이 가능합니다.

TREE

응답 구조

-> Sort: orders.created_at DESC  (cost=16.14 rows=43) (actual time=5.157..5.157 rows=0 loops=1)
    -> Filter: (orders.market_code = 'smartstore')  (actual time=5.149..5.149 rows=0 loops=1)
        -> Index lookup on orders using searchRule1 (store_uuid='어쩌구')  (actual time=4.957..5.137 rows=43 loops=1)

참고 문서

• https://dev.mysql.com/doc/refman/8.0/en/explain.html
• https://dev.mysql.com/doc/refman/8.0/en/explain-output.html