[MySQL] EXPLAIN을 이용한 쿼리/테이블 최적화

💡 이 글은 MySQL 공식문서를 읽고 정리한 글입니다.

 

SQL에 EXPLAIN이라는 명령문이 있다. DESCRIBE와 비슷한 기능을 가지고 있는데, 일반적으로 DESCRIBE는 테이블 구조에 대한 정보를 보고자 할 때 사용하고 EXPLAIN은 쿼리 수행 계획을 살펴보는데 사용된다. 즉, 쿼리가 어떻게 실행될지 예측한다. MySQL에서는 DESC와 함께 이 셋을 완전히 동일한 명령어로 취급한다. 따라서 문서에 있는 3개 명령어는 그냥 동일한 것이라고 보면 된다.

문법은 다음과 같다.

{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: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

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

MySQL 5.7과 비교하여 바뀐점은 다음과 같다. 설명이 없는 부분은 뒤에서 자세히 설명한다.

• ANALYZE 키워드 추가
• explain_type에서 EXTENDED 키워드 삭제: 사실 삭제된 게 아니라 디폴트로 적용되어 굳이 붙이 필요가 없게 되었다.
• explain_type에서 PARTITIONS 키워드 삭제: 마찬가지 이유로 사라졌다.
• format_name에 TREE 추가
• explainable_stmt에 TABLE 추가: 이로써 테이블도 같은 명령어로 분석할 수 있다.

테이블 정보 얻기

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

DESCRIBE는 SHOW COLUMNS의 축약 명령어다. 즉, DESCRIBE City는 SHOW COLUMNS FROM City와 같은 결과를 보여준다.
뒤에 틀정 컬럼명을 붙이면 해당 컬럼 정보만 보여준다. 컬럼명은 패턴 문자열이다. SQL의 와일드카드 %와 _를 사용할 수 있다.
ex) DESCRIBE City '%Cou%'
공백이나 기타 특수 문자가 포함되어 있지 않으면 따옴표로 묶지 않아도 된다.

참고로 SHOW CREATE TABLE, SHOW TABLE STATUS, SHOW INDEX문은 테이블에 대한 정보를 제공한다.

실행 계획 정보 얻기

💡 이 기능을 사용하면 쿼리가 수행될 때 어떤 일이 생기는 지 자세히 파악할 수 있다. 이로써 슬로우 쿼리(쿼리 성능이 저하되어 서비스에 영향을 미치는 쿼리)의 어떤 부분에 문제가 있는지 파악할 수 있다.

 

• EXPLAIN 명령어 뒤에 SELECT, DELETE, INSERT, REPLACE, UPDATE, TABLE (8.0.19에 포함됨) 명령문을 붙여서 실행하면, MySQL 옵티마이저가 뒤쪽 문장이 실행될 때의 정보를 제공한다.

mysql> EXPLAIN SELECT * FROM TemplateMetadata;
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | TemplateMetadata | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   17 |   100.00 | NULL  |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+

• 제공되는 정보는 테이블이 조인되는 방법 및 순서와 명령문을 출력하는 형식을 제공한다. 자세한 내용은 8.8.2절을 참고.

  컬럼	JSON 명	의미
  id	select_id	The SELECT identifier
  select_type	None	The SELECT type
  table	table_name	The table for the output row
  partitions	partitions	The matching partitions
  type	access_type	The join type
  possible_keys	possible_keys	The possible indexes to choose
  key	key	The index actually chosen
  key_len	key_length	The length of the chosen key
  ref	ref	The columns compared to the index
  rows	rows	Estimate of rows to be examined
  filtered	filtered	Percentage of rows filtered by table condition
  Extra	None	Additional information

• EXPLAIN FOR CONNECTION {connection_id} 명령으로 현재 수행중인 연결에 대한 id만을 가지고 바로 수행할 수 있다.
  지금 수행중인 프로세스를 보는 명령어인 show processlist와 조합하면 slow query를 찾아 바로 분석할 수 있다. EXPLAIN 뒤에 따로 SELECT문을 안 적어줘도 된다는 뜻.
• EXPLAIN을 수행한 후 SHOW WARNINGS 명령으로 더 자세한 정보를 얻을 수 있다.
• EXPLAIN은 파티션으로 분할된 테이블 정보도 얻을 수 있다.
• FORMAT 옵션은 기본이 TRADITIONAL이고 표형식으로 출력한다. JSON, TREE로 바꿀 수 있다.TREE는 8.0.16이상 버전에서 사용가능하고, 해쉬 조인을 했을 때는 TREE를 사용해야 한다.
• EXPLAIN FORMAT = JSON SELECT * FROM CITY;

EXPLAIN 결과에서 얻은 정보로 인덱스를 어디에 걸어야 하는지, 조인 순서를 어떻게 해야 하는지 알 수 있다.

EXPLAIN ANALYZE

8.0.18 부터 EXPLAIN ANALYZE 명령으로 실제 수행 결과를 옵티마이저가 예측한 정보를 얻을 수 있다.

• 추정되는 수행 시간
• 추정되는 결과 열(row)의 수
• 처음 열을 얻는데 걸리는 시간
• 모든 결과를 얻는데 걸리는 시간
• 이터레이터가 리턴해준 열의 개수(??)
• 루프 수

예를 들어 이런 테이블이 있을 때,

CREATE TABLE t1 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t2 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t3 (
    pk INTEGER NOT NULL PRIMARY KEY,
    i INTEGER DEFAULT NULL
);

수행하면 이런 정보를 준다.

mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
    -> Table scan on t2  (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 8)  (cost=1.75 rows=5)
(actual time=0.019..0.021 rows=6 loops=1)
    -> Table scan on t3  (cost=1.75 rows=15)
(actual time=0.017..0.019 rows=15 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
    -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)