[MySQL] Execution Plan

이번엔 쿼리 실행 계획에 대해 알아보자.

쿼리 실행 계획은 총 3가지 포맷으로 출력이 가능하다.
(테이블 포맷, TREE 포맷, JSON 포맷)

흔히 알고 있는 EXPLAIN 쿼리는 테이블 포맷이다.
테이블 포맷 실행 계획은 다음과 같다.

# 1. 테이블 포맷

EXPLAIN
SELECT *
FROM authors as a
  INNER JOIN books b ON a.book_id = b.id;
WHERE first_name='Alex';

# output
| id | select_type | table | partitions | type | possible_keys         | key          | key_len | ref             | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------
|  1 | SIMPLE      | a     | NULL       | ref  | PRIMARY, ix_firstname | ix_firstname | 47      | const           |    1 |    87.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | ref  | PRIMARY               | PRIMARY      | 6       | authors.book_id |    7 |    87.00 | NULL  |

각 컬럼과 해당하는 값들을 참고하여, 실행 계획을 해석할 수 있다.

다음으로, TREE 형식 실행 계획은 아래와 같이 사용되고, 쿼리가 실행되는 절차를 Indent로 표현한다.

# 2. 트리 포맷

EXPLAIN FORMAT=TREE
SELECT *
FROM authors as a
  INNER JOIN books b ON a.book_id = b.id;
WHERE first_name='Alex';

# output
-> Nested loop inner join (cost=1.40 rows=7)
-> Index lookup on a using ix_firstname (first_name='Alex') (cost=0.90 rows=1)
-> Index lookup on b using PRIMARY (a.book_id=b.id) (cost=0.50 rows=7)

TREE 형식 쿼리 실행 계획에서 들여쓰기는 호출 순서를 의미하며, 아래와 같은 규칙이 표현된다.

• 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행된다.
• 들여쓰기가 다른 레벨에서는 가장 안쪽 라인이 먼저 실행된다.

마지막으로, JSON 형식으로도 실행 계획을 출력할 수 있다.

# 3. JSON 포맷
EXPLAIN FORMAT=JSON
SELECT *
FROM authors as a
  INNER JOIN books b ON a.book_id = b.id;
WHERE first_name='Alex';

# output
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.40"
    },
  "nested_loop": [
    {
      "table": {
        "table_name": "a",
        "access_type": "ref",
        "possible_keys": [
          "PRIMARY", 
          "ix_firstname"
        ],
        "key": "ix_firstname",
        "used_key_parts": [
          "first_name"
        ],
    },
    ...
  ]
}

테이블 포맷을 기준으로, 실행 계획을 분석해보자.

1. id 컬럼

단위 SELECT 쿼리마다 부여되는 값이다.
JOIN 되는 테이블의 개수만큼 실행 계획의 rows가 출력되지만, 같은 id로 나온다.
Sub Query가 있는 쿼리의 경우, 다른 id가 출력된다.
아래와 같은 쿼리의 경우, 단위 SELECT 쿼리가 3개 존재하므로, 서로 다른 3개의 id가 출력된다.
단, id의 값은 쿼리의 실행 순서가 아니다.

EXPLAIN
SELECT
( (SELECT COUNT(*) FROM authors) + (SELECT COUNT(*) FROM books) ) AS total_count;

# output
| id | select_type | table        | type  | key           | ref  | rows | Extra          |
------------------------------------------------------------------------------------------
|  1 | SIMPLE      | NULL         | NULL  | NULL          | NULL |  315 | No tables used |
|  2 | SIMPLE      | authors      | index | ux_bookname   | NULL |  315 | Using index    |
|  3 | SIMPLE      | books        | index | ix_enrolldate | NULL |  315 | Using index    |

2. select_type 컬럼

각각의 SELECT 쿼리가 어떤 타입인지 표시되는 컬럼이다.
여러 값들이 올 수 있지만, SIMPLE, PRIMARY, SUBQUERY, DERIVED에 대해 알아보자.

• SIMPLE
  UNION이나 SubQuery가 사용되지 않은 쿼리인 경우 출력되는 값이다.

• PRIMARY
  UNION이나 SubQuery가 사용되는 쿼리의 가장 바깥쪽에 있는 쿼리인 경우 출력된다.

• SUBQUERY
  FROM절 이외에서 사용되는 SubQuery인 경우 출력된다.

• DERIVED
  FROM절에 사용된 SELECT 쿼리인 경우 출력된다. 이 쿼리가 만드는 임시 테이블을 파생 테이블이라고 한다. 임시 테이블에도 인덱스를 추가할 수 있다. (MySQL 5.6 이후 버전)
  아래에 FROM절에 서브 쿼리와 그 실행 계획이다.

EXPLAIN
SELECT *
FROM (SELECT b.author_id FROM books b GROUP BY b.author_id) t, 
  authors a
WHERE a.id = t.author_id;

# output
| id | select_type | table        | type   | key                | rows  | Extra        |
---------------------------------------------------------------------------------------
|  1 | PRIMARY     | <derived2>   | ALL    | NULL               | 36871 | NULL         |
|  1 | PRIMARY     | a            | eq_ref | PRIMARY            |     1 | NULL         |
|  2 | DERIVED     | b            | index  | ix_author_fromdate | 36871 | Using index  |

3. table 컬럼

쿼리가 사용되는 테이블을 의미한다.
임시 테이블은 <derived N> 또는 <union M,N> 으로 표현된다.

4. partitions 컬럼

쿼리가 참조하는 테이블 내 파티션을 보여준다.

5. type 컬럼

각 테이블의 레코드를 어떤 방식으로 읽었는지 알려준다.
system, const, ref, eq_ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL 중 하나로 나온다.

• system
  레코드가 없거나 1건만 있는 테이블을 참조하는 방식이라는 뜻이다.
  InnoDB를 사용하는 테이블에서는 나타나지 않는다고 한다.

• const
  쿼리가 Primary Key나 Unique Key 컬럼을 이용하는 WHERE 절을 가지고 있으며, 쿼리 결과가 1건인 방식을 말한다.
  다른 DBMS에서 Unique Index Scan이라고도 한다.

• ref
  동등 조건으로 조회할 때, 사용되는 방법을 말한다.
  ref 타입은 반환될 레코드가 꼭 1건이라는 보장이 없기 때문에, const나 eq_ref보다는 느리다.

• eq_ref
  여러 테이블이 조인되는 쿼리에서만 나타나는 값이다.
  JOIN 문에서 처음 읽은 테이블의 컬럼값을,
  다음 읽을 테이블 Primary Key나 Unique Key 컬럼의 검색 조건에서도 사용할 때,
  eq_ref가 표시된다.
  말이 어려운 것 같으니, 아래 쿼리를 살펴보면 더 쉽게 이해될 것 같다.

  EXPLAIN
  SELECT * FROM book_author ba, authors a
  WHERE a.id = ba.author_id AND ba.id = 38;

  # output
  | id | select_type | table | type   | key     | key_len | rows       |
  ----------------------------------------------------------------------
  |  1 | SIMPLE      | b     | ref    | PRIMARY | 20      |     170235 |
  |  1 | SIMPLE      | a     | eq_ref | PRIMARY | 4       |          1 |

　　book_author 테이블을 먼저 읽었고, "a.id = ba.author_id" WHERE 조건으로 authors 테이블을 읽고 있다.

　　그런데 authors 테이블의 id가 Primary Key이므로, 실행 계획의 두 번째 row의 type이 eq_ref이 된다.

• fulltext
  전문 검색(Full-text Search) 인덱스를 통해 조회하는 방식이라는 뜻이다.
  range 방식보다는 느린 경우가 많다.

• range
  Index Range Scan을 사용하여 테이블을 조회한다는 뜻이다.

• index_merge
  2개 이상의 인덱스를 이용해, 각각의 결과들을 합쳐서 처리하는 방식이다.
  type이 range인 방식보다 느리고, 처리된 결과는 2개 이상의 집합이 되므로,
  여기에서 교집합/합집합/중복 제거와 같은 후처리가 필요하다.
  아래의 예시 쿼리를 보면, 이를 확인할 수 있다.

EXPLAIN
SELECT * FROM authors
WHERE id BETWEEN 8001 AND 10000
    OR first_name = 'Nick';

# output
| id | type           | key                   | key_len | Extra                                           |
-----------------------------------------------------------------------------------------------------------
|  1 | index_merge    | PRIMARY, ix_firstname | 4, 64   | Using union(PRIMARY, ix_firstname); Using Where |

id 컬럼에 대한 조건은 Primary Key로, first_name 컬럼에 대한 조건은 ix_firstname 인덱스로 이용할 수 있다.
2개의 인덱스로 결과를 뽑아낸 뒤, 2개의 결과를 합쳐서 처리하게 된다.

• index
  언뜻 보면 index를 잘 타는 쿼리로 생각할 수 있지만,
  Index Full Scan을 사용해 테이블을 조회한다는 뜻이다.

• ALL
  Full Table Scan을 통해 테이블을 조회한다는 뜻이다. 가장 비효율적인 방법이다.

6. possible_keys 컬럼

MySQL Optimizer는 여러 가지 처리 방법을 고려한 뒤, 그 중에 가장 비용이 적은 방법으로 쿼리를 실행한다.
possible_keys에 나타난 값은 Optimizer가 실행을 고려했었던 인덱스의 목록이다.
실제 쿼리에서 사용된 index를 의미하는 것이 아니므로, 크게 중요한 컬럼은 아니다.

7. key 컬럼

MySQL Optimizer가 최종적으로 선택한 실행 계획의 인덱스를 말한다.

8. key_len 컬럼

다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는 지 알려준다.
인덱스의 각 레코드에서 몇 바이트까지 사용했는 지 보여주는 컬럼이다.

9. ref 컬럼

접근 방법이 ref인 경우, 참조 조건으로 어떤 값이 제공됐는지 보여준다.

10. rows 컬럼

MySQL Optimizer는 주어진 쿼리에 대해 가능한 처리 방식들을 나열하고, 그 중에 가장 효율적인 방식으로 쿼리를 실행한다.
rows 컬럼은 MySQL Optimizer가 선택한 처리 방식이 읽어야 할 레코드의 개수이다.
또 MySQL 스토리지 엔진이 가진 통계정보를 바탕으로 한 예측값이다.
착각하기 쉬운 것은 해당 쿼리가 가져올 레코드의 건수가 아니라는 점이다.

11. filtered 컬럼

rows 컬럼의 값은 인덱스를 사용하는 조건에만 일치하는 레코드 수를 예측한 것이다.
하지만, 쿼리에서 WHERE 절에 인덱스를 사용하지 못하는 경우도 있다.
JOIN이 사용되는 경우에 WHERE 절에서 인덱스를 사용하지 못할 때에도, 조건에 일치하는 레코드 건수를 파악해야 한다.

다음 쿼리는 authors 테이블과 salaries 테이블을 조인한다.
authors 테이블의 a.first_name='Brian' 조건은 인덱스를 사용할 수 있고, salaries 테이블은 s.salary BETWEEN 60000 AND 70000 조건이 인덱스를 사용할 수 있다.
authors와 salaries 테이블 중에서 레코드 건수가 적은 테이블이 드라이빙 테이블이 될 것이다.

EXPLAIN
SELECT *
FROM authors a, salaries s
WHERE a.first_name='Brian'
    AND a.enroll_date BETWEEN '1993-01-01' AND '1994-01-01'
    AND a.company_no = s.company_no
    AND a.id = s.author_id
    AND s.from_date BETWEEN '1993-01-01' AND '1994-01-01'
    AND s.salary BETWEEN 60000 AND 70000

# output
| id | select_type | table | type          | key             | rows | filtered |
--------------------------------------------------------------------------------
|  1 | SIMPLE      | a     | ref           | ix_firstname    |  412 |    24.14 |
|  1 | SIMPLE      | s     | ref           | PRIMARY         |   24 |     1.73 |

filtered 컬럼값은 필터링되어 남은 레코드의 건수를 말한다.
authors 테이블에서 인덱스 조건에 일치하는 레코드는 412개이며, 그 중에서 24.14%만 인덱스를 쓸 수 없는 조건(a.enroll_date BETWEEN '1993-01-01' AND '1994-01-01')에 일치한다.
따라서 authors 테이블에서 salaries 테이블로 JOIN을 수행한 레코드 건수는 대략 99건(412 * 0.2414)이라고 볼 수 있다.

여기서 테이블 조인 순서가 반대로 바뀌면 어떻게 될 지 보자.

EXPLAIN
SELECT *
FROM salaries s, 
    authors a
WHERE a.first_name='Brian'
    AND a.enroll_date BETWEEN '1993-01-01' AND '1994-01-01'
    AND a.company_no = s.company_no
    AND a.id = s.author_id
    AND s.from_date BETWEEN '1993-01-01' AND '1994-01-01'
    AND s.salary BETWEEN 60000 AND 70000

# output
| id | select_type | table | type          | key             | rows | filtered |
--------------------------------------------------------------------------------
|  1 | SIMPLE      | s     | range         | ix_salary       | 2538 |    10.06 |
|  1 | SIMPLE      | a     | ref           | PRIMARY         |    3 |     3.00 |

salaries 테이블을 조인의 선행 테이블로 선택했다면, salaries 테이블의 255건(2538 * 0.1006)이
조건에 일치해서 authors 테이블로 조인을 수행할 것이다.
효율적으로 JOIN을 사용하려면 authors 테이블을 먼저 쓰는 것이 좋을 것이다.
하지만 이 역시 예측값이므로 실제로 쿼리를 실행했을 때 성능이 다를 수 있다.
filtered 컬럼에 나오는 값을 더 정확히 예측하려면, MySQL 히스토그램을 사용해야 한다.

12. Extra 컬럼

쿼리 성능에 대한 중요한 참고 사항들이 나온다.
MySQL 내부 알고리즘의 깊이있는 내용을 알려주는 경우도 있다.
잘 파악한다면, 쿼리 튜닝에 큰 도움이 될 것 같다.

• 1 const row not found
  type 컬럼의 const 방식으로 테이블을 조회했으나 레코드가 1건도 없는 경우이다.

• Using where
  쿼리가 실행되는 순서를 보면, MySQL 스토리지 엔진에 값을 가져온 뒤, MySQL 엔진에서 연산을 수행한다.
  MySQL 엔진에서 Filtering 작업을 하는 경우에 Using where라고 표현된다.