[MySQL] 실행계획-1

해당 내용은 Real MySQL의 내용을 정리

---

ERD

아래와 같은 테이블을 가지고 실행 계획들을 분석

 

 

아래와 같이 실행 계획(EXPLAIN)을 확인해보면

id, select_type, table, partitions, type, key, key_len, ref, rows, Extra 등이 포함된 것을 확인할 수 있다.

EXPLAIN
SELECT e.emp_no, e.first_name, s.from_date, s.salary
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no LIMIT 10;

(1) id 컬럼

• 실행 계획에서 가장 왼쪽에 표시되는 id 컬럼은 단위 SELECT 쿼리별로 부여되는 식별자 값
• 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하는 경우 조인되는 테이블 개수만큼 실행 계획 레코드가 표시되지만 출력되는 id 값은 같다.

EXPLAIN
SELECT e.emp_no, e.first_name, s.from_date, s.salary
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
LIMIT 10;

+----+-------------+-------+------------+-------+---------------+--------------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref                | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+--------------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | index | PRIMARY       | ix_firstname | 58      | NULL               | 299512 |   100.00 | Using index |
|  1 | SIMPLE      | s     | NULL       | ref   | PRIMARY       | PRIMARY      | 4       | employees.e.emp_no |      9 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+--------------+---------+--------------------+--------+----------+-------------+

 

 

 

 

• 3개의 Select 쿼리로 구성돼 있으므로 각 레코드가 다른 id 값을 갖는다.

EXPLAIN
SELECT
( (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments) ) AS total_count;

+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+-------+----------+----------------+
| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+-------+----------+----------------+
|  1 | PRIMARY     | NULL        | NULL       | NULL  | NULL          | NULL      | NULL    | NULL |  NULL |     NULL | No tables used |
|  3 | SUBQUERY    | departments | NULL       | index | NULL          | dept_name | 162     | NULL |     9 |   100.00 | Using index    |
|  2 | SUBQUERY    | employees   | NULL       | index | NULL          | PRIMARY   | 4       | NULL | 17776 |   100.00 | Using index    |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+-------+----------+----------------+

---

(2) select_type 컬럼

• 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다.

SIMPLE

• UNIOIN이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우다.(쿼리에 조인이 포함된 경우도 마찬가지)
• 쿼리 문장이 아무리 복잡해도 SIMPLE인 단위 쿼리는 반드시 하나 존재한다.
• 일반적으로 제일 바깥 SELECT 쿼리이다.

PRIMARY

• UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리이다.
• PRIMARY인 SELECT 쿼리는 하나만 존재한다.

UNION

• UNIOIN으로 결합하는 단위 SELECT 쿼리 중 두 번째 이후 단위 SELECT 쿼리는 UNIOIN으로 표시된다.
• UNIOIN의 첫 번째 단위 SELECT는 select_type이 UNIOIN 쿼리로 결합된 전체 집합의 select_type이다.
• 아래의 예제에서 e1은 전체 UNIOIN 결과를 대표하는 select_type 으로 설정되었고, e2,e3은 UNION으로 표시되었다.
  (세 개의 서브 쿼리로 조회된 결과를 UNIOIN ALL로 결합해 임시 테이블을 만들어서 사용하고 있으므로 UNION ALL의 첫 번째 쿼리는 DERIVED)

EXPLAIN
SELECT * FROM (
    (SELECT emp_no FROM employees e1 LIMIT 10)
    UNION ALL
    (SELECT emp_no FROM employees e2 LIMIT 10)
    UNION ALL
    (SELECT emp_no FROM employees e3 LIMIT 10)
) tb;

+----+-------------+------------+------------+-------+---------------+-------------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL        | NULL    | NULL |     30 |   100.00 | NULL        |
|  2 | DERIVED     | e1         | NULL       | index | NULL          | ix_hiredate | 3       | NULL | 299512 |   100.00 | Using index |
|  3 | UNION       | e2         | NULL       | index | NULL          | ix_hiredate | 3       | NULL | 299512 |   100.00 | Using index |
|  4 | UNION       | e3         | NULL       | index | NULL          | ix_hiredate | 3       | NULL | 299512 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+--------+----------+-------------+

 

 

 

 

DEPENDENT UNIOIN

• (select_type이) UNION과 같이 UNION 이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다.
• 여기서 DEPENDENT는 UNIOIN이나 UNIOIN ALL로 결합된 단위 쿼리가 외부의 영향에 의해 영향을 받는 것을 의미.
• 하나의 단위 SELECT 쿼리가 다른 단위 SELECT를 포함하고 있으면 이를 서브 쿼리라고 표현한다.
  보통 서브 쿼리가 사용된 경우 외부(Outer) 쿼리보다 서브 쿼리가 먼저 실행되는 것이 일반적이라 반대의 경우보다 빠르지만, DEPENDENT를 포함하는 경우 서브 쿼리는 외부 쿼리에 의존적이므로 먼저 실행될
  수 없고 비효율적인 경우가 많다.

EXPLAIN
SELECT
    e.first_name,
    ( SELECT CONCAT('Salary change count : ', COUNT(*)) AS message
        FROM salaries s WHERE s.emp_no = e.emp_no
      UNION
      SELECT CONCAT('Department change count : ', COUNT(*)) AS message
        FROM dept_emp de WHERE de.emp_no = e.emp_no
    ) AS message
FROM employees e
WHERE e.emp_no = 10001;

+----+--------------------+------------+------------+-------+----------------------------------------+-------------------+---------+-------+------+----------+-----------------+
| id | select_type        | table      | partitions | type  | possible_keys                          | key               | key_len | ref   | rows | filtered | Extra           |
+----+--------------------+------------+------------+-------+----------------------------------------+-------------------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY            | e          | NULL       | const | PRIMARY                                | PRIMARY           | 4       | const |    1 |   100.00 | NULL            |
|  2 | DEPENDENT SUBQUERY | s          | NULL       | ref   | PRIMARY,ix_salary                      | PRIMARY           | 4       | const |   17 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | de         | NULL       | ref   | PRIMARY,ix_from_data,ix_empno_fromdate | ix_empno_fromdate | 4       | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL   | NULL                                   | NULL              | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+-------+----------------------------------------+-------------------+---------+-------+------+----------+-----------------+

 

 

 

UNIOIN RESULT

• UNION 결과를 담아두는 테이블을 의미한다.
• MySQL에서 UNIOIN(DISTINCT 포함) 쿼리는 모두 UNIOIN의 결과를 임시 테이블로 생성하게 되는데, 실행 계획상 이 임시 테이블을 가리키는 select_type이
  UNIOIN RESULT이다.
• UNIOIN RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값을 부여되지 않는다.

EXPLAIN
SELECT emp_no FROM salaries WHERE salary > 100000
UNION ALL
SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';

+----+-------------+----------+------------+-------+--------------------------------+--------------+---------+------+--------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys                  | key          | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+----------+------------+-------+--------------------------------+--------------+---------+------+--------+----------+--------------------------+
|  1 | PRIMARY     | salaries | NULL       | range | ix_salary                      | ix_salary    | 4       | NULL | 188518 |   100.00 | Using where; Using index |
|  2 | UNION       | dept_emp | NULL       | range | ix_from_data,ix_empno_fromdate | ix_from_data | 3       | NULL |   5325 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+--------------------------------+--------------+---------+------+--------+----------+--------------------------+

 

 

 

 

SUBQUERY

• 일반적으로 서브 쿼리는 여러 가지 통틀어서 이야기 하지만, select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미한다.
• MySQL의 실행 계획에서는 FROM 절에 사용된 서브 쿼리는 DERIVED라고 표시되고, 나머지 서브 쿼리는 전부 SUBQUERY라고 표시된다.

EXPLAIN
SELECT
    e.first_name,
    (SELECT count(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no = de.dept_no) AS cnt
FROM employees e
WHERE e.emp_no = 10001;

+----+-------------+-------+------------+-------+---------------+---------+---------+----------------------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref                  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+----------------------+-------+----------+-------------+
|  1 | PRIMARY     | e     | NULL       | const | PRIMARY       | PRIMARY | 4       | const                |     1 |   100.00 | NULL        |
|  2 | SUBQUERY    | dm    | NULL       | index | PRIMARY       | PRIMARY | 20      | NULL                 |    24 |   100.00 | Using index |
|  2 | SUBQUERY    | de    | NULL       | ref   | PRIMARY       | PRIMARY | 16      | employees.dm.dept_no | 41758 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+----------------------+-------+----------+-------------+

 

 

 

 

 

DEPENDENT SUBQUERY

• 서브 쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의한 컬럼을 사용하는 경우 DEPENDENT SUBQUERY라고 표현한다.
• DEPENDENT UNION과 마찬가지로 외부 쿼리가 먼저 수행된 후 내부 쿼리(서브 쿼리)가 실행돼야 하므로 DEPENDENT 키워드가 없는 일반 서브 쿼리보다는
  처리 속도가 느릴 때가 많다.

EXPLAIN
SELECT e.first_name,
    (SELECT COUNT(*)
     FROM dept_emp de, dept_manager dm
     WHERE dm.dept_no = de.dept_no AND de.emp_no = e.emp_no) AS cnt
FROM employees e
WHERE e.emp_no = 10001;

+----+--------------------+-------+------------+-------+------------------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys          | key     | key_len | ref                  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-------+------------------------+---------+---------+----------------------+------+----------+-------------+
|  1 | PRIMARY            | e     | NULL       | const | PRIMARY                | PRIMARY | 4       | const                |    1 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | de    | NULL       | ref   | PRIMARY,emp_no,dept_no | PRIMARY | 4       | const                |    1 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | dm    | NULL       | ref   | dept_no                | dept_no | 16      | employees.de.dept_no |    2 |   100.00 | Using index |
+----+--------------------+-------+------------+-------+------------------------+---------+---------+----------------------+------+----------+-------------+

 

DERIVED

• 서브 쿼리가 FROM 절에 사용된 경우 MySQL은 항상 select_type이 DERIVED인 실행 계획을 만든다.
• DERIVED는 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블(파생 테이블)을 생성하는 것을 의미한다.

EXPLAIN
SELECT *
FROM (SELECT de.emp_no FROM dept_emp de) tb,
    employees e
WHERE e.emp_no = tb.emp_no;

// MySQL 5.x.x 버전
+----+-------------+--------------+---------+-------------+--------------+
| id | select_type | table        | type    | key         |  Extra       |
+----+-------------+--------------+---------+-------------+--------------+
|  1 | PRIMARY     | <derived2>   | ALL     |             |              |
|  1 | PRIMARY     | e            | eq_ref  | PRIMARY     |              |
|  2 | DERIVED     | de           | index   | ix_fromdate |  Using index |
+----+-------------+--------------+---------+-------------+--------------+

// MySQL 8.x.x 버전
+----+-------------+-------+------------+------+----------------+--------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key    | key_len | ref                | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+--------+---------+--------------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY        | NULL   | NULL    | NULL               | 299512 |   100.00 | NULL        |
|  1 | SIMPLE      | de    | NULL       | ref  | PRIMARY,emp_no | emp_no | 4       | employees.e.emp_no |      1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+--------+---------+--------------------+--------+----------+-------------+

 

 

 

 

UNCACHEABLE SUBQUERY

• 하나의 쿼리 문장에 서브 쿼리가 하나만 있더라도 실제로 한번만 실행되는 것은 아니다(조건이 똑같은 서브 쿼리가 실행될 때는 캐시 공간에 담아두고 이용,
  여기서 말하는 캐시는 쿼리 캐시나 파생 테이블과는 전혀 무관한 기능)
• 캐시를 사용하지 못하는 요소는 대표적으로 아래와 같은 경우가 있다.
  • 사용자 변수가 서브 쿼리에 사용된 경우
  • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리 내에 사용된 경우
  • UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브 쿼리에 사용된 경우

EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no = (
    SELECT @status FROM dept_emp de WHERE de.dept_no ='d005'
);

+----+----------------------+-------+------------+------+------------------------+---------+---------+-------+--------+----------+--------------------------+
| id | select_type          | table | partitions | type | possible_keys          | key     | key_len | ref   | rows   | filtered | Extra                    |
+----+----------------------+-------+------------+------+------------------------+---------+---------+-------+--------+----------+--------------------------+
|  1 | PRIMARY              | e     | NULL       | ALL  | NULL                   | NULL    | NULL    | NULL  | 299512 |   100.00 | Using where              |
|  2 | UNCACHEABLE SUBQUERY | de    | NULL       | ref  | PRIMARY,emp_no,dept_no | dept_no | 16      | const | 148054 |   100.00 | Using where; Using index |
+----+----------------------+-------+------------+------+------------------------+---------+---------+-------+--------+----------+--------------------------+

 

UNCACHEABLE UNION

• 위의 UNION과 UNCACHEABLE 속성이 혼합된 select_type

---

(3) table 컬럼

• MySQL의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다.
• 아래와 같이 별도 테이블을 사용하지 않는 SELECT 쿼리인 경우 table 컬럼에 NULL이 표시된다.

mysql> EXPLAIN SELECT NOW();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

• 아래와 같은 실행 계획을 분석해보자.
• 첫번째 라인의 테이블이 <derived2>이므로 쿼리의 id가 2번인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비되야한다.
• 쿼리 id 2번(세번째 라인)을 보면 select_type 컬럼이 DERIVED로 이므로 table 컬럼에 표시된 dept_emp 테이블을 읽어서 파생 테이블을 생성한다.
• 첫 번째 라인과 두번째 라인의 id 값이 같으므로 2개 테이블(첫번째 라인의 <derived2>와 두번째 라인의 e 테이블)이 조인되는 쿼리라는 것을 알 수 있다.
  <derived2> 테이블이 e 테이블보다 먼저 표시되었기 때문에 <derived2>가 드라이빙 테이블이 되고, e 테이블이 드리븐 테이블이 된다.
  즉 <derived2> 테이블을 먼저 읽어서 e 테이블로 조인을 실행했다는 것을 알 수 있다.

|   id   |   select_type   |   table    |   type   |   key       |   key_len   |   ref      |   rows   |   Extra   |
|--------|-----------------|------------|----------|-------------|-------------|------------|----------|-----------|
| 1      | PRIMARY         | <derived2> | ALL      |             |             |            | 10420    |           |
| 1      | PRIMARY         | e          | eq_ref   | PRIMARY     | 4           | de1.emp_no | 1        |           |
| 2      | DERIVED         | dept_emp   | range    | ix_fromdate | 3           |            | 20550    |           |

EXPLAIN
SELECT *
FROM (SELECT de.emp_no FROM dept_emp de) tb,
    employees e
WHERE e.emp_no = tb.emp_no;

---

(4) type 컬럼

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types

• type 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다.
  (인덱스를 사용해 레코드를 읽었는지, 풀 테이블 스캔 인지 등등)
• system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery
range, index_merge, index, ALL이 있고 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근이다.(해당 순서는 빠른 순)

system

• 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법이다.
• InnoDB 테이블에서는 나타나지 않고 MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다.

const

• 테이블 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건을 반환하는 쿼리의
  처리 방식을 지칭한다. (다른 DBMS에서는 이를 UNIQUE INDEX SCAN 이라고 표현한다.)

EXPLAIN
SELECT * FROM employees WHERE emp_no = 10001;

+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

 

 

 

• 아래와 같이 다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 컬럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다.(ref로 표시)

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';

+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | dept_emp | NULL       | ref  | dept_no       | dept_no | 16      | const | 148054 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+

# 아래와 같이 프라이머리 키나 유니크 인덱스의 모든 컬럼을 동등 조건으로 WHERE 절에 명시하면 const 접근이 가능
EXPLAIN 
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;
+----+-------------+----------+------------+-------+------------------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys          | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+-------+------------------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | dept_emp | NULL       | const | PRIMARY,emp_no,dept_no | PRIMARY | 20      | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+------------------------+---------+---------+-------------+------+----------+-------+

EXPLAIN
SELECT COUNT(*)
FROM employees e1
WHERE first_name = (SELECT first_name FROM employees e2 WHERE emp_no = 100001);
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | e1    | NULL       | ref   | ix_firstname  | ix_firstname | 58      | const |  248 |   100.00 | Using where; Using index |
|  2 | SUBQUERY    | e2    | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+--------------------------+

실제 이 쿼리는 옵티마이저에 의해 최적화되는 시점에 아래와 같은 쿼리로 변환된다는 것이다.
(즉 옵티마이저에 의해 상수화된 다음 쿼리 실행기로 전달되기 때문에 접근 방식이 const인 것이다.)  

SELECT COUNT(*)
FROM employees e1
WHERE first_name = 'Jasminko'; -- emp_no == 100001인 레코드의 first_name 값

 

 

eq_ref

• 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
• 조인에서 처음 읽은 테이블의 컬럼 값을 그 다음 읽어야 할 테이블의 프라미어 키나 유니크 키 컬럼의 검색 조건에 사용할 때 표시된다.
  (여기서 두 번째 이후에 읽는 테이블의 type컬럼에 eq_ref가 표시된다.)

EXPLAIN
SELECT * FROM dept_emp de, employees e
WHERE e.emp_no = de.emp_no AND de.dept_no = 'd005';

+----+-------------+-------+------------+--------+------------------------------------------+---------+---------+--------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                            | key     | key_len | ref                      | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+------------------------------------------+---------+---------+--------------------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | PRIMARY                                  | NULL    | NULL    | NULL                     | 299512 |   100.00 | NULL        |
|  1 | SIMPLE      | de    | NULL       | eq_ref | PRIMARY,emp_no,dept_no,ix_empno_fromdate | PRIMARY | 20      | employees.e.emp_no,const |      1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+------------------------------------------+---------+---------+--------------------------+--------+----------+-------------+

 

 

 

 

ref

• eq_ref와 달리 조인의 순서와 관계없이 사용되며, 프라이머리 키나 유니크 키 등의 제약 조건도 없다.
• 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다.
• 아래의 예제 쿼리를 살펴보면 Primary Key(emp_no,dept_no) 중 일부만 Equal 조건으로 Where 절에 명시되었기 때문에 레코드가 1건이라는 보장이 없다.

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';

+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | dept_emp | NULL       | ref  | dept_no       | dept_no | 16      | const | 148054 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+

 

 

 

 

fulltext

• MySQL의 Fulltext 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.
• fulltext는 우선순위가 상당히 높은편이며 전문 인덱스와 일반 인덱스의 조건을 사용하면 접근 방법이 const, eq_ref, ref가 아닌 경우
  일반적으로 전문 인덱스를 사용하는 조건을 선택해서 처리한다.
• 아래와 같은 쿼리를 살펴보면 3개의 조건을 가지고 있다.
  • (1) employee_name 테이블의 프라이머리 키를 1건만 조회하는 const 타입
  • (2) emp_no를 범위로 조회하는 range 타입
  • (3) 전문 검색(Fulltext)
• (경험상 전문 검색 인덱스보다 range 접근이 더 빨리 처리되는 경우가 많음)

# (1) Primary Key
EXPLAIN
SELECT *
FROM employee_name
WHERE emp_no = 10001
AND emp_no BETWEEN 10001 AND 10005
AND MATCH(first_name, last_name) AGAINST ('Facello' IN BOOLEAN MODE);

+----+-------------+---------------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee_name | NULL       | const | PRIMARY,fx_name | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+  

# (2) Range vs Fulltext
EXPLAIN
SELECT *
FROM employee_name
WHERE emp_no BETWEEN 10001 AND 10005
AND MATCH(first_name, last_name) AGAINST ('Facello' IN BOOLEAN MODE);

+----+-------------+---------------+------------+----------+-----------------+---------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table         | partitions | type     | possible_keys   | key     | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+---------------+------------+----------+-----------------+---------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | employee_name | NULL       | fulltext | PRIMARY,fx_name | fx_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+---------------+------------+----------+-----------------+---------+---------+-------+------+----------+-----------------------------------+

 

 

 

 

ref_or_null

• ref 접근 방식과 같지만 NULL 비교가 추가된 형태다.(ref 방식 또는 NULL 비교(IS NULL))

EXPLAIN
SELECT * FROM titles WHERE to_date = '1985-03-01' OR to_date IS NULL;

+----+-------------+--------+------------+-------------+---------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table  | partitions | type        | possible_keys | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------------+---------------+-----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | titles | NULL       | ref_or_null | ix_todate     | ix_todate | 4       | const |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------------+---------------+-----------+---------+-------+------+----------+--------------------------+

 

 

 

 

unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방식(서브 쿼리에서 중복되지 않은 유니크한 값만 반환 할 때)
• TODO: Explain

EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
  SELECT dept_no FROM dept_emp WHERE emp_no=10001); 

 

 

index_subquery

• IN (subquery) 에서 subquery가 중복된 값을 반환할 수는 있지만, 중복된 값을 인덱스를 이용해 제거할 수 있을때 사용된다.
• TODO: Explain

EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
    SELECT dept_no FROM dept_emp WHERE dept_emp.dept_no BETWEEN 'd001' AND 'd003');

 

 

range

• 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미한다. (<, >, IS NULL, BETWEEN, IN, LIKE)

EXPLAIN
SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003';

+----+-------------+----------+------------+-------+-------------------------------------------------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys                                         | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+----------+------------+-------+-------------------------------------------------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | dept_emp | NULL       | range | PRIMARY,emp_no,dept_no,ix_from_data,ix_empno_fromdate | dept_no | 16      | NULL | 119452 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+-------------------------------------------------------+---------+---------+------+--------+----------+--------------------------+

 

 

 

index_merge

• 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식이다.(경험상으로 효율적으로 동작하지 않았음)
  • 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어진다.
  • AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다.
  • 전문 검색 인덱스를 사용하는 쿼리에선느 index_merge가 적용되지 않는다.
  • index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 떄문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 필요하다.

EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
OR first_name = 'Smith';

+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table     | partitions | type        | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | employees | NULL       | index_merge | PRIMARY,ix_firstname | PRIMARY,ix_firstname | 4,58    | NULL | 1001 |   100.00 | Using union(PRIMARY,ix_firstname); Using where |
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+

 

 

 

 

index

• 인덱스 풀 스캔을 의미한다.
• 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 데이터 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 효율적이다.
• 아래 조건 중 (첫번째 + 두번째) 또는 (첫번쨰 + 세번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.
  • range나 const 또는 ref와 같은 접근 방식으로 인덱스를 사용하지 못하는 경우
  • 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(즉 데이터 파일을 읽지 않아도 되는 경우)
  • 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)
• 아래의 쿼리는 인덱스를 처음부터 끝까지 읽는 index 접근 방식이지만 LIMIT 조건이 있어서 (역순으로) 읽어서 10개만 가져오면 되므로 효율적인 쿼리다.

EXPLAIN
SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;

+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | departments | NULL       | index | NULL          | dept_name | 162     | NULL |    9 |   100.00 | Backward index scan; Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+

 

 

ALL

• 풀 테이블 스캔을 의미하는 접근 방식이다.
• 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다.
• 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어들이는 기능을 제공하여(InnoDB에서는 이 기능을 리드 어헤드(Read Ahead) 라고 한다.)
  한 번에 여러 페이지를 읽어서 처리할 수 있다.
• 데이터웨어하우스나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리(억지로 인덱스를 사용하도록)보다 더 나은 접근 방법이 되기도 한다.
  즉 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니다.

---

(5) possible_keys

• MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해서 쿼리를 실행한다.
• possible_keys는 후보로 선정했던 접근 방식에서 사용되는 인덱스 목록일 뿐이다.

---

(6) key

• 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
• key 컬럼에 표시되는 값이 PRIMARY인 경우는 프라이머리 키를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.
• type이 index_merge 인 경우는 여러 개의 인덱스가 ","로 구분되어 표시되고 이외에는 하나의 인덱스만 이용할 수 있다.(ALL 일때는 NULL로 표시)

+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table     | partitions | type        | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | employees | NULL       | index_merge | PRIMARY,ix_firstname | PRIMARY,ix_firstname | 4,58    | NULL | 1001 |   100.00 | Using union(PRIMARY,ix_firstname); Using where |
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+

---

(7) key_len

• 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는지 표시한다.(인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값)
  • 실제 업무에서는 단일 컬럼보다는 다중 컬럼으로 만들어진 인덱스가 더 많으므로 매우 중요한 정보 중 하나이다.
• 아래의 예제는 (dept_no, emp_no) 두 개의 컬럼으로 만들어진 프라이머리 키를 포함한 dept_emp 테이블을 조회하는 쿼리이다.
  • query-1
    • dept_no 컬럼 타입이 CHAR(4)이기 때문에 프라이머리 키에서 앞쪽 16바이트만 유효하게 사용했다는 의미이다.(4 * 4바이트)
  • query-2
    • emp_no 컬럼 타입이 INTEGER(4바이트)이기 때문에 key_len이 20으로 표시

# query-1
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | dept_emp | NULL       | ref  | dept_no       | dept_no | 16      | const | 148054 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-----------------------+

# query-2
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;
+----+-------------+----------+------------+-------+------------------------------------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys                            | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+-------+------------------------------------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | dept_emp | NULL       | const | PRIMARY,emp_no,dept_no,ix_empno_fromdate | PRIMARY | 20      | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+------------------------------------------+---------+---------+-------------+------+----------+-------+

---

(8) ref

• 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여준다.
  • 상수값을 지정했으면 ref 컬럼은 const로 표시되고, 다른 테이블의 컬럼값이면 그 테이블 명과 컬럼 명이 표시된다.
• 일반적으로 신경쓰지 않아도 무방하지만 func 라고 표시될 때는 조금 주의해서 볼 필요가 있다.
  • query-1: 조인 조건에 사용된 emp_no 컬럼의 값을 가공하지 않으므로 ref 컬럼의 조인 대상 컬럼의 이름이 그대로 표시된다.
  • query-2: 산술 표현식을 쿼리에 추가했기 때문에 func로 표시된다.
    • 사용자가 명시적으로 값을 변환할 때뿐만 아니라 MySQL 서버가 내부적으로 값을 변환해야 할 때도 func로 표시된다.
      (문자집합이 일치하지 않는 두 문자열 컬럼을 조인하거나, 숫자 타입의 컬럼과 문자열 타입의 컬럼을 조인할 때 등)

# query-1
EXPLAIN
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no;
+----+-------------+-------+------------+------+----------------------------------+---------+---------+--------------------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                    | key     | key_len | ref                | rows   | filtered | Extra |
+----+-------------+-------+------------+------+----------------------------------+---------+---------+--------------------+--------+----------+-------+
|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY                          | NULL    | NULL    | NULL               | 299512 |   100.00 | NULL  |
|  1 | SIMPLE      | de    | NULL       | ref  | PRIMARY,emp_no,ix_empno_fromdate | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------------------------+---------+---------+--------------------+--------+----------+-------+

# query-2
EXPLAIN
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = (de.emp_no-1);
+----+-------------+-------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | de    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 331143 |   100.00 | NULL        |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |      1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+

---

(9) rows

• MySQL 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해 최종적으로 하나의 실행 계획을 수립한다.
• 비용을 산정하는 방법은 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지, 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측한다.
  (통계 정보를 참조해 예측하므로 실제 레코드 건수와 일치하지 않는 경우가 많다.)
• query-1: from_data의 인덱스가 존재하지만 전체 레코드(331,603)의 대부분을 비교해봐야 한다고 판단했으므로 풀 테이블 스캔이 선택되었다.
• query-2: 대략 292건의 레코드를 예측했으므로 풀 테이블 스캔이 아닌 range로 인덱스 레인지 스캔을 사용하였다.
• query-3: LIMIT 조건이 포함된 쿼리는 rows 컬럼에 표시되는 값이 오차가 심한 것을 확인할 수 있다.

# query-1
EXPLAIN
SELECT * FROM dept_emp WHERE from_date >= '1985-01-01';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | dept_emp | NULL       | ALL  | ix_from_data  | NULL | NULL    | NULL | 331143 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

# query-2
EXPLAIN
SELECT * FROM dept_emp WHERE from_date >= '2002-07-01';
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | dept_emp | NULL       | range | ix_from_data  | ix_from_data | 3       | NULL |  292 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

# query-3
EXPLAIN
SELECT * FROM dept_emp WHERE from_date >= '1985-01-01' LIMIT 10;
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+--------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | dept_emp | NULL       | range | ix_from_data  | ix_from_data | 3       | NULL | 165571 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+--------+----------+-----------------------+