explain_02.md

실행 계획

실행 계획 분석

mysql> explain
    -> select *
    -> from employees e
    -> inner join salaries s on s.emp_no=e.emp_no
    -> where first_name='ABC';
+----+-------------+-------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys        | key          | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | e     | NULL       | ref  | PRIMARY,ix_firstname | ix_firstname | 58      | const              |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | s     | NULL       | ref  | PRIMARY              | PRIMARY      | 4       | employees.e.emp_no |    9 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

MySQL 8.0 부터는 EXPLAIN 명령 결과로 출력되는 실행 계획 포맷을 기존 테이블 포맷과 JSON, TREE 형태로 선택할 수 있다.
아무런 옵션 없이 EXPLAIN 명령을 실행하면 쿼리 문장의 특성에 따라 표 형태로 1줄 이상의 결과가 표시된다.

표의 각 레코드는 쿼리 문장에서 사용된 테이블의 개수만큼 출력된다.
실행 계획은 위에서 아래로 순서대로 표시된다.(UNION이나 상관 서브쿼리의 경우 순서대로 표시되지 않을 수 있음) 출력된 실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 바깥 부분이거나 먼저 접근한 테이블이다.

---

분석 - id 칼럼

하나의 SELECT 문장은 다시 1개 이상의 하위 SELECT 문장을 포함할 수 있다.
실행 계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다.

하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.

mysql> 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          | ux_deptname | 162     | NULL |      9 |   100.00 | Using index    |
|  2 | SUBQUERY    | employees   | NULL       | index | NULL          | ix_hiredate | 3       | NULL | 299969 |   100.00 | Using index    |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)

위 쿼리의 실행 계획의 경우 쿼리 문장이 3개의 단위 select 쿼리로 구성돼 있으므로 실행 계획의 각 레코드가 각기 다른 id값을 지닌다.

mysql> explain
    -> select * from dept_emp de
    -> where de.emp_no= (select e.emp_no
    -> from employees e
    -> where e.first_name='Georgi'
    -> and e.last_name='Facello' limit 1);
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | de    | NULL       | ref  | ix_empno_fromdate | ix_empno_fromdate | 4       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | e     | NULL       | ref  | ix_firstname      | ix_firstname      | 58      | const |  253 |    10.00 | Using where |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

주의할 점은 실행 계획의 id 칼럼이 테이블의 접근 순서를 의미하지는 않는다는 것이다. 위 쿼리는 employees 테이블을 먼저 읽고 그 결과를 dept_emp 테이블과 조인함에도 불구하고 id가 순서와 일치하지 않는 것을 확인할 수 있다.

---

분석 - select_type 칼럼

select_type 칼럼에서는 각 단위 select 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.

다음과 같은 값들이 표시될 수 있다.

• SIMPLE
  • UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리의 경우 SIMPLE로 표시된다(조인도 마찬가지).
  • 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 SIMPLE인 단위 SELECT 쿼리는 단 하나만 존재한다.
  • 일반적으로 가장 바깥 SELECT 쿼리가 SIMPLE로 표시된다.
• PRIMARY
  • UNION이나 서브쿼리를 가지는 SELECT 쿼리의 경우 가장 바깥쪽에 있는 단위 SELECT 쿼리는 PRIMARY로 표시된다.
  • SIMPLE과 마찬가지로 PRIMARY인 단위 SELECT 쿼리는 하나만 존재한다.
• UNION
  • UINON으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리는 UNION으로 표시된다.
  • UNION의 첫 번째 단위 SELECT 쿼리는 UNION이 아니라 UNION되는 쿼리 결과들을 모아 저장하는 임시 테이블 DERIVED가 표시된다.
• DEPENDENT_UNION
  • DEPENDENT란 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 영향을 받는 것을 의미한다.

  • mysql> explain
    -> select *
    -> from employees e1 where e1.emp_no in (
    -> select e2.emp_no from employees e2 where e2.first_name='Matt'
    -> union
    -> select e3.emp_no from employees e3 where e3.last_name='Matt'
    -> );
    +----+--------------------+------------+------------+--------+----------------------+---------+---------+------+--------+----------+-----------------+
    | id | select_type        | table      | partitions | type   | possible_keys        | key     | key_len | ref  | rows   | filtered | Extra           |
    +----+--------------------+------------+------------+--------+----------------------+---------+---------+------+--------+----------+-----------------+
    |  1 | PRIMARY            | e1         | NULL       | ALL    | NULL                 | NULL    | NULL    | NULL | 299969 |   100.00 | Using where     |
    |  2 | DEPENDENT SUBQUERY | e2         | NULL       | eq_ref | PRIMARY,ix_firstname | PRIMARY | 4       | func |      1 |     5.00 | Using where     |
    |  3 | DEPENDENT UNION    | e3         | NULL       | eq_ref | PRIMARY              | PRIMARY | 4       | func |      1 |    10.00 | Using where     |
    |  4 | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL                 | NULL    | NULL    | NULL |   NULL |     NULL | Using temporary |
    +----+--------------------+------------+------------+--------+----------------------+---------+---------+------+--------+----------+-----------------+

  • 위 쿼리의 경우 옵티마이저가 외부의 employees 테이블을 먼저 읽은 다음 서브 쿼리를 실행하는데, 이 때 employees 테이블의 칼럼 값이 서브쿼리에 영향을 준다.
  • 이렇게 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드가 표시된다.
  • 내부적으로는 UNION에 사용된 SELECT 쿼리의 WHERE 조건에 e2.emp_no=e1.emp_no와 e3.emp_no=e1.emp_no라는 조건이 자동으로 추가되어 실행된다.
  • 외부에 정의된 employees 테이블의 emp_no 칼럼이 서브쿼리에 사용되기 때문에 DEPENDENT UNION이 표시되는 것이다.
• UNION RESULT
  • UNION RESULT는 UNION 결과를 담아두는 테이블을 의미한다.
  • 8.0 버전부터 UNION ALL의 경우 임시 테이블을 사용하지 않도록 기능이 개선됐지만, UNION(또는 UNION DISTINCT)은 여전히 임시 테이블에 결과를 버퍼링한다.
  • 이 임시 테이블을 가리키는 select_type이 UNION RESULT 이고, 실제 쿼리의 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다.
  • UNION 대신 UNION ALL을 사용할 경우 해당 실행 계획 레코드는 보이지 않게 된다.
• SUBQUERY
  • select_type의 SUBQUERY는 FROM절 이외에서 사용되는 서브쿼리만을 의미한다.
  • MySQL 서버의 실행 계획에서 FROM 절에 사용된 서브쿼리는 DERIVED로 표시되고, 그 밖의 위치에서 사용된 서브쿼리는 전부 SUBQUERY라고 표시된다.
  • 서브쿼리는 사용하는 위치에 따라 각각 다른 이름을 지니고 있다.
    • 중첩된 쿼리(Nested Query): SELECT되는 칼럼에 사용된 서브쿼리를 네스티드 쿼리라고 한다.
    • 서브쿼리(Subquery): WHERE 절에 사용된 경우에는 일반적으로 그냥 서브쿼리라고 한다.
    • 파생 테이블(Derived Query): FROM 절에 사용된 서브쿼리를 MySQL에서는 파생 테이블이라고 하며, 일반적으로 RDBMS에서는 인라인 뷰, 또는 서브 셀렉트라고 부른다.
  • 또한 서브쿼리가 반환하는 값의 특성에 따라 다음과 같이 구분하기도 한다.
    • 스칼라 서브쿼리(Scalar Subquery): 하나의 값만(칼럼이 단 하나인 레코드 1건만) 반환하는 쿼리
    • 로우 서브쿼리(Row Subquery): 칼럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리
• DEPENDENT SUBQUERY
  • 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우 DEPENDENT SUBQUERY라고 표시된다.

  • mysql> 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.first_name='Matt';
    +----+--------------------+-------+------------+------+---------------------------+-------------------+---------+----------------------+------+----------+-------------+
    | id | select_type        | table | partitions | type | possible_keys             | key               | key_len | ref                  | rows | filtered | Extra       |
    +----+--------------------+-------+------------+------+---------------------------+-------------------+---------+----------------------+------+----------+-------------+
    |  1 | PRIMARY            | e     | NULL       | ref  | ix_firstname              | ix_firstname      | 58      | const                |  233 |   100.00 | Using index |
    |  2 | DEPENDENT SUBQUERY | de    | NULL       | ref  | PRIMARY,ix_empno_fromdate | ix_empno_fromdate | 4       | employees.e.emp_no   |    1 |   100.00 | Using index |
    |  2 | DEPENDENT SUBQUERY | dm    | NULL       | ref  | PRIMARY                   | PRIMARY           | 16      | employees.de.dept_no |    2 |   100.00 | Using index |
    +----+--------------------+-------+------------+------+---------------------------+-------------------+---------+----------------------+------+----------+-------------+

  • 또한 DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 서브쿼리가 실행돼야 하므로 DEPENDENT 키워드가 없는 일반 서브쿼리보다는 처리속도가 느릴 때가 많다.
• DERIVED
  • DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
  • 5.5 버전까지는 서브쿼리가 FROM절에 사용된 경우 항상 select_type이 DERIVED였지만, 5.6 버전부터 옵티마이저 옵션에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 한다.
  • 5.5 버전까지는 파생 테이블에 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많았는데, 5.6 버전부터 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 만들 수 있게 최적화 됐다.
  • MySQL 서버는 버전이 업그레이드되면서 조인 쿼리에 대한 최적화는 많이 성숙된 상태이므로 파생 테이블에 대한 최적화가 부족한 MySQL 서버를 사용 중일 경우, 가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.
  • 8.0 버전부터는 FROM 절의 서브쿼리에 대한 최적화도 많이 개선되어 가능하다면 불필요한 서브쿼리는 조인으로 쿼리를 재작성해서 처리한다.

  • 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야 한다.
    서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용할 것을 강력히 권장한다.
    

• DEPENDENT DERIVED
  • 8.0 이전 버전에서는 FROM 절의 서브쿼리는 외부 칼럼을 사용할 수 없었는데, 8.0 버전부터 래터럴 조인(LATERAL JOIN) 기능이 추가되었다.
  • DEPENDENT DERIVED 키워드는 해당 테이블이 래터럴 조인으로 사용된 것을 의미한다.
• UNCACHEABLE SUBQUERY
  • 하나의 쿼리 문장에 서브 쿼리가 하나만 있더라도 실제로 그 서브쿼리가 한 번만 실행되는게 아님
  • 그런데 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.
  • select_type이 SUBQUERY인 경우와 UNCACHEABLE SUBQUERY는 이 캐시를 사용할 수 있느냐 없느냐의 차이가 있다.
  • 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있는데, 그럴 경우 select_type이 UNCACHEABLE SUBQUERY로 표시된다.
• UNCACHEABLE UNION
• MATERIALIZED
  • 주로 FROM 절이나 IN 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

  • mysql> explain
    -> select *
    -> from employees e
    -> where e.emp_no in (select emp_no from salaries where salary between 100 and 1000);
    +----+--------------+-------------+------------+--------+-------------------+-----------+---------+--------------------+------+----------+--------------------------+
    | id | select_type  | table       | partitions | type   | possible_keys     | key       | key_len | ref                | rows | filtered | Extra                    |
    +----+--------------+-------------+------------+--------+-------------------+-----------+---------+--------------------+------+----------+--------------------------+
    |  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL              | NULL      | NULL    | NULL               | NULL |   100.00 | NULL                     |
    |  1 | SIMPLE       | e           | NULL       | eq_ref | PRIMARY           | PRIMARY   | 4       | <subquery2>.emp_no |    1 |   100.00 | NULL                     |
    |  2 | MATERIALIZED | salaries    | NULL       | range  | PRIMARY,ix_salary | ix_salary | 4       | NULL               |    1 |   100.00 | Using where; Using index |
    +----+--------------+-------------+------------+--------+-------------------+-----------+---------+--------------------+------+----------+--------------------------+
    3 rows in set, 1 warning (0.00 sec)

---

분석 - table 칼럼

MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다.
테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다.

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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

mysql> explain select now() from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

실제 DUAL이라는 테이블은 없지만 오라클 RDBMS에 익숙한 사용자를 위해 MySQL 서버는 내부적으로 DUAL이라는 테이블이 있는 것 처럼 작동한다.
오라클 RDBMS에서는 FROM 절이 없으면 오류가 발생하지만 MySQL 서버는 그렇지 않다.
별도의 테이블을 사용하지 않는 SELECT 쿼리인 경우 위와 같이 NULL이 표시된다.

<derived N> 또는 <union M,N>과 같이 <>로 둘러싸인 테이블은 임시 테이블을 의미한다.
만약 <derived 2>이면 단위 SELECT 쿼리의 id 값이 2인 실행 계획으로부터 만들어진 파생 테이블을 가리킨다.

+----+-------------+------------+------------+--------+---------------------------------------+-------------------+---------+-----------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys                         | key               | key_len | ref       | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------------------------------+-------------------+---------+-----------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                  | NULL              | NULL    | NULL      | 331143 |   100.00 | NULL        |
|  1 | PRIMARY     | e          | NULL       | eq_ref | PRIMARY                               | PRIMARY           | 4       | tb.emp_no |      1 |   100.00 | NULL        |
|  2 | DERIVED     | de         | NULL       | index  | PRIMARY,ix_fromdate,ix_empno_fromdate | ix_empno_fromdate | 7       | NULL      | 331143 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------------------------------+-------------------+---------+-----------+--------+----------+-------------+

• 첫 번째 라인의 테이블이 라는 것을 보아 이 라인보다 id 값이 2인 라인이 먼저 실행되고, 그 결과가 파생 테이블로 준비돼야 한다는 것을 알 수 있다.
• id 값이 2인 라인을 보면 select_type이 derived이고, table이 de인 테이블을 읽어서 파생 테이블을 생성하는 것을 알 수 있다.
• 첫 번째 라인과 두 번째 라인이 같은 id 값을 가지고 있는 것으로 봐서 와 e 테이블이 조인되는 쿼리라는 것을 알 수 있다.
• 그런데 테이블이 e 테이블보다 먼저 표시됐기 때문에 가 드라이빙 테이블이 되고, e 테이블이 드리븐 테이블이 되는 것을 알 수 있다.
• 즉 테이블을 먼저 읽고 e 테이블로 조인을 실행했다는 것을 알 수 있다.

mysql> explain
    -> select *
    -> from (select de.emp_no from dept_emp de group by de.emp_no) tb, employees e
    -> where e.emp_no=tb.emp_no;

---

분석 - partitions 칼럼

partitions 컬럼을 통해 파티션이 여러 개인 테이블에서 불필요한 파티션을 제외하고, 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라낼 수 있다.
이러한 과정을 파티션 프루닝이라한다.

---

분석 - type 칼럼

쿼리 실행 계획에서 type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다.
일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 type 칼럼은 매우 중요한 정보

• system
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• unique_subquery
• index_subquery
• range
• index_merge
• index
• ALL

위 접근 방법 중에서 ALL은 풀테이블 스캔을 의미하고, 나머지는 모두 인덱스를 사용하는 접근 방법이다.
또한 성능이 빠른 순서대로 나열한 것이다.

system

레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라 한다.

const

쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 where 조건절을 가지고 있으며, 반드시 1건을 반환하는 처리 방식을 const라고 한다.

다중 칼럼으로 구성된 프라이머리 키나 유니크 키 중 인덱스의 일부 칼럼만 조건으로 사용할 경우에는 const 타입의 접근 방법을 사용할 수 없다.
프라이머리 키의 일부만 조건으로 사용할 때는 const가 아닌 ref로 표시되고, 모든 칼럼을 조건으로 사용하면 const 접근 방법을 사용할 수 있다.

MySQL의 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화하기 때문에 const이다.
where first_name = (select first_name from employees e2 where emp_no=100001)
where first_name = 'Jasminko'로 치환된다.

eq_ref

eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행계획에서만 표시된다.
조인에서 처음 읽은 테이블의 칼럼값을, 그다음 읽어야 할 테이블의 pk나 유니크 키 칼럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 한다.
이 때 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref가 표시된다.

두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 모든 칼럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있다.

즉 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

mysql> 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      | de    | NULL       | ref    | PRIMARY,ix_empno_fromdate | PRIMARY | 16      | const               | 165571 |   100.00 | NULL  |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY                   | PRIMARY | 4       | employees.de.emp_no |      1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+---------------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

employees 테이블의 emp_no는 pk라서 조건에 일치하는 레코드가 1건만 존재하므로 eq_ref가 표시되는 것

ref

ref 접근 방법은 조인의 순서와 상관없이 사용되며 pk, 유니크 키 조건도 없다.
인덱스 종류와 상관없이 동등 조건으로 검색할 때 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다 빠르지 않다. 하지만 동등 조건으로만 비교되므로 매우 빠른 레코드 조회 방법중 하나.

mysql> 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  | PRIMARY       | PRIMARY | 16      | const | 165571 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-------+

dept_emp 테이블의 pk를 구성하는 칼럼 (dept_no, emp_no) 중 일부 detp_no만 동등 조건으로 사용되었기 때문에 조건에 일치하는 레코드가 1건이라는 보장이 없다.

그래서 const가 아닌 ref가 사용되었다.

• const: 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대해 동등 조건으로 검색(반드시 1건 레코드 반환)
• eq_ref: 조인에서 첫 번째 읽은 테이블의 칼럼값을 이용해 두 번째 테이블을 pk나 유니크 키로 동등 조건 검색(두 번째 테이블은 반드시 1건 레코드 반환)
• ref: 조인 순서나 인덱스 종류에 관계없이 동등 조건으로 검색

위 세가지 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상 문제를 일으키지 않는 방법들이다.
쿼리를 튜닝할 때도 이 세 가지 접근 방법에 대해서는 크게 신경쓰지 않고 넘어가도 무방

fulltext

fulltext 접근 방법은 MySQL 서버의 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.
MySQL 서버에서 전문 검색 조건은 우선순위가 상당히 높다. 쿼리에서 전문 검색 인덱스를 사용하는 조건과 그 이외의 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방법이 const나 eq_ref, ref가 아니면 일반적으로 MySQL은 전문 검색 인덱스를 사용하는 조건을 선택해서 처리한다.

전문 검색은 MATCH, AGAINST 구문을 사용해서 실행하는데, 이때 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다.
테이블에 전문 인덱스가 없다면 쿼리는 오류가 발생하고 중지될 것이다.

일반적으로 쿼리에 전문 검색 조건을 사용하면 MySQL은 주저 없이 fulltext 접근 방법을 사용한다.
하지만 전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 사용하는 range 접근 방법이 더 빨리 처리되는 경우가 많으므로 전문 검색 쿼리를 사용할 때는 조건별로 성능을 확인해 보는 편이 좋다.

ref_or_null

이 접근 방법은 ref 접근 방법과 같은데, null 비교가 추가된 형태
실제 업무에서 많이 활용되지는 않지만, 만약 사용된다면 나쁘지 않은 접근 방법 정도로 기억하자

unique_subquery

where 조건절에서 사용될 수 있는 IN 형태의 쿼리를 위한 접근 방법이다. 이름 의미 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.

index_subquery

IN 연산자의 특성상 IN(subquery), IN(상수들) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다. 앞의 unique_subquery 접근 방법은 IN(subquery) 조건의 subquery 결과가 중복된 값을 만들어내지 않는다는 보장이 있으므로 별도의 중복을 제거할 필요가 없었다.
하지만 IN(subquery)에서 subquery가 중복된 값을 반환할 수도 있는데, 이 때 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.

즉
unique_subquery 접근 방법은 IN(subquery) 조건에서 서브쿼리의 반환 값이 유니크 값들이므로 별도의 중복 제거 작업이 필요하지 않다는 의미이고
index_subqurey 접근 방법은 IN(subquery) 조건에서 서브쿼리 반환 값이 중복된 값이 있을 수 있지만 인덱스를 이용해 중복 값을 제거할 수 있다는 의미이다.

range

range는 말그대로 인덱스 레인지 스캔 형태의 접근 방법이다.
range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.

얼마나 많은 레코드를 필요로 하느냐에 따라 차이는 있겠지만 range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있다.

mysql> explain
    -> select * from employees where emp_no between 10002 and 10004;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

보통 인덱스 레인지 스캔이라고 하면 const, ref, range 세 가지 접근 방법을 모두 묶어서 지칭하는 것이라고 이해하고
업무상 개발자나 DBA와 소통할 때도 const, ref, range 접근 방법을 구분해서 언급하는 경우는 거의 없었다고 한다.
일반적으로 인덱스 레인지 스캔, 레인지 스캔으로 언급할 때가 많음
"인덱스를 효율적으로 사용한다"라는 표현은 이 세가지 접근 방법을 사용했을 때를 의미한다.

index_merge

index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.
그다지 효율적이지 않다.

• 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어짐
• 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않음
• index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 추가로 요구된다

mysql> explain
    -> select * from employees
    -> where emp_no between 10001 and 11000 or first_name='Smith';
+----+-------------+-----------+-------------+----------------------+----------------------+------------------------------------------------+
| id | select_type | table     | type        | possible_keys        | key                  | Extra                                          |
+----+-------------+-----------+-------------+----------------------+----------------------+------------------------------------------------+
|  1 | SIMPLE      | employees | index_merge | PRIMARY,ix_firstname | PRIMARY,ix_firstname | Using union(PRIMARY,ix_firstname); Using where |
+----+-------------+-----------+-------------+----------------------+----------------------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

위 쿼리에서 or로 연결된 두 개 조건이 모두 각각 다른 인덱스를 최적으로 사용할 수 있는 조건이다.
따라서 옵티마이저는 between 조건은 employees 테이블의 pk를 이용해 조회하고, name 조건은 ix_firstname 인덱스를 이용해 조회한 후 두 결과를 병합하는 형태로 처리하는 실행 게획을 만들어낸다.

index

index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
인덱스를 이용하기는 하지만 range 처럼 인덱스의 필요한 부분만 읽는 것이 아니기 때문에 효율적으로 인덱스를 사용한다고 보기는 어렵다.

다만 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같아도 인덱스는 일반적으로 데이터 전체 파일보다 크기가 작으므로 더 빠르게 처리되며
쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있다는 점에서 훨씬 효율적이라 할 수 있다.

다음 세가지 조건 가운데 (1, 2) 조건을 충족하거나 (1, 3) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.

• range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
• 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우
• 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(별도의 정렬 작업을 피할 수 있는 경우)

mysql> 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       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

위 실행 계획은 테이블의 인덱스를 처음부터 끝까지 읽는 index 접근 방법이지만 limit 조건이 있기 때문에 상당히 효율적이다. 단순히 인덱스를 역순으로 읽어 10개만 가져오면 되기 때문이다.
하지만 limit 조건이 없거나 가져와야 할 레코드 건수가 많아지면 상당히 느린 처리를 수행한다.

all

all은 풀 테이블 스캔을 의미하는 접근 방법이다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재한다면)하고 반환한다.
풀 테이블 스캔은 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법이다.

다른 DBMS와 마찬가지로 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 리드 어헤드(Read Ahead)기능을 제공한다. 데이터 웨어하우스나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리(억지로 인덱스를 사용하게 튜닝된 쿼리)보다 더 나은 접근 방법이기는 하다.

쿼리를 튜닝하는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니라는 점을 기억해야한다.

일반적으로 index와 all 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다. 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해보고 적용하는 것이 좋다.

Reference

위 글은 책 RealMySQL 8.0을 구입하여 읽고 정리한 내용입니다.

• 도서 홈페이지 https://wikibook.co.kr/realmysql801/