MySQL 쿼리 실행 계획 분석하기

Mar 03, 2024

Published byJynn

999

Query Execution Plan with MySQL

데이터베이스의 쿼리 실행 계획은 효율적인 데이터 처리를 위한 필수적인 요소입니다. 이를 통해 쿼리가 실제로 어떻게 동작하는지를 파악하게 되며, 이 정보는 시스템 반응 시간을 줄이고, 리소스를 보다 효율적으로 활용하는데 도움이 됩니다. 결과적으로, 이러한 정보는 버그를 찾고, 성능 이슈를 해결하며, 그리고 애플리케이션의 효율성을 높이는 데에 중요한 역할을 합니다.

EXPLAIN

SQL 쿼리는 매우 강력한 도구이지만, 때로는 복잡하고 예측하기 어려울 때가 있습니다. 이때, MySQL의 EXPLAIN 명령어를 사용하면 쿼리의 실행 계획을 미리 확인할 수 있어 불필요한 비용을 줄이고, 성능을 개선하는 데 도움이 됩니다.

EXPLAIN 명령어는 주어진 쿼리를 실제로 실행하지 않고, MySQL이 어떻게 해당 쿼리를 실행할 것인지에 대한 계획을 제시합니다. 이 계획에는 어떤 테이블들이 어떤 순서로 스캔되는지, 어떤 종류의 조인이 사용되는지, 인덱스가 어떻게 활용되는지 등 많은 정보가 포함되어 있습니다.

다음은 EXPLAIN 명령어를 사용하여 실행 계획을 생성하는 예시입니다:

explain
select
    students.student_name,
    courses.subject_name
from
    STUDENT_COURSES student_courses
    inner join STUDENTS students on students.student_id = student_courses.student_id
    inner join COURSES courses on courses.subject_code = student_courses.subject_code

MySQL은 이 SELECT 쿼리의 실행에 필요한 각 단계와 각 단계별로 예상되는 행 수를 출력합니다. 여기에는 조인이 어떻게 수행되고, 인덱스가 어떻게 사용되고, 테이블이 어떤 순서로 스캔되는 지 등의 정보가 포함됩니다.

console

+--+-----------+---------------+----------+------+--------------------+-------+-------+----------------------------------------+----+--------+-----------+
|id|select_type|table          |partitions|type  |possible_keys       |key    |key_len|ref                                     |rows|filtered|Extra      |
+--+-----------+---------------+----------+------+--------------------+-------+-------+----------------------------------------+----+--------+-----------+
|1 |SIMPLE     |student_courses|null      |ALL   |FK_Student,FK_Course|null   |null   |null                                    |4385|100     |Using where|
|1 |SIMPLE     |students       |null      |eq_ref|PRIMARY             |PRIMARY|8      |playgrounds.student_courses.student_id  |1   |100     |null       |
|1 |SIMPLE     |courses        |null      |eq_ref|PRIMARY             |PRIMARY|202    |playgrounds.student_courses.subject_code|1   |100     |null       |
+--+-----------+---------------+----------+------+--------------------+-------+-------+----------------------------------------+----+--------+-----------+

MySQL EXPLAIN 명령어를 이용해 SQL 쿼리의 작동 방식을 확인하는 방법에 대해 살펴보았습니다. EXPLAIN 명령어를 통해 얻은 결과는 여러 개의 컬럼으로 구성되어 있었습니다.

Column	Description
id	각 SELECT 문을 식별하는 ID입니다. 하위 SELECT 문이나 UNION 연산으로 인해 여러 SELECT 문이 같은 실행 계획에 표시될 수 있습니다.
select_type	SELECT 문의 유형을 나타냅니다. 단순 SELECT, UNION, 서브쿼리 등에 따라 다른 값이 표시됩니다.
table	현재 행이 선택된 테이블을 나타냅니다.
partitions	레코드를 가져오기 위해 검색된 파티션을 나타냅니다. 이 정보는 파티션된 테이블에만 표시됩니다.
type	테이블의 행이 어떻게 읽혔는지를 나타냅니다. 이 정보는 'ALL, 'index', 'range', 'ref', 'eq_ref', 'const', 'system', 'NULL' 등 다양한 값을 포함할 수 있습니다.
possible_keys	MySQL이 고려한 가능한 모든 인덱스를 나타냅니다. 단, 이 인덱스가 항상 사용되는 것은 아닙니다.
key	MySQL이 선택하여 사용하는 인덱스를 나타냅니다.
key_len	MySQL이 사용하는 인덱스의 길이를 나타냅니다. 이 정보는 본래 인덱스의 길이가 아니라, 액세스 방법에 따라 선택된 인덱스의 길이를 나타냅니다.
ref	인덱스의 키 값이 어떤 칼럼과 비교되었는지를 나타냅니다.
rows	MySQL이 추정하는 필요한 레코드의 수를 나타냅니다. 이 값은 항상 정확한 값일 수는 없습니다.
filtered	테이블 조건에 의해 필터링 될 레코드의 비율을 퍼센트로 나타냅니다.
Extra	옵티마이저가 추가로 제공하는 정보를 나타냅니다. 이 정보는 특정 행의 반환, 다른 테이블과의 조인 방법 등을 분석하는 데 사용됩니다.

이제 각 컬럼이 어떤 정보를 나타내는지 하나씩 자세히 살펴보겠습니다.

id

id 항목은 연결된 SELECT 문의 각 단계를 식별합니다. 만약 id가 같다면, 이는 해당 단계가 동일한 SELECT 문의 일부임을 나타냅니다. 아래에서 이를 더 잘 이해할 수 있는 예제들을 살펴보겠습니다.

먼저 아래는 JOIN 연산을 수행하는 쿼리입니다.

explain
select
    *
from
    EMPLOYEES employees
    inner join DEPARTMENTS departments on employees.department_id = departments.id;

해당 쿼리를 실행하면 아래와 같은 EXPLAIN 결과를 얻을 수 있습니다.

console

+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+-------+
| 1  | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL        | NULL    | NULL              | 10   | 100.00   | NULL  |
| 1  | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | e.department_id   | 1    | 100.00   | NULL  |
+----+-------------+-------+------------+--------+---------------+-------------+---------+-------------------+------+----------+-------+

두 레코드의 id 값이 모두 1임을 확인해볼 수 있습니다. 이는 두 레코드가 같은 SELECT 문의 일부라는 것을 명확하게 보여줍니다.

이번에는 서로 다른 id를 가지는 SELECT 문을 살펴보겠습니다:

explain
select
    emp_1.department_id                                                                    as department_id,
    avg(emp_1.salary)                                                                      as average_salary,
    (select count(*) from EMPLOYEES emp_2 where emp_2.department_id = emp_1.department_id) as emp_count
from
    EMPLOYEES emp_1
group by
    emp_1.department_id;

해당 쿼리를 실행하면 아래와 같은 EXPLAIN 결과를 얻을 수 있습니다.

console

+----+--------------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+--------------------------+
| 1  | PRIMARY            | e     | NULL       | index | NULL          | dept_index  | 4       | NULL | 9    | 100.00   | Using index              |
| 2  | DEPENDENT SUBQUERY | e2    | NULL       | ref   | dept_index    | dept_index  | 4       | func | 2    | 100.00   | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+--------------------------+

위 결과에서 주 쿼리의 id는 1이고, 별개로 실행되는 서브쿼리의 id는 2임을 확인할 수 있습니다. 이 쿼리에서 id 값의 차이는 두 SELECT 문이 별개의 단계로 처리되는 것을 의미합니다.

여기서 주의해야 할 점은, 실행 계회의 id 컬럼이 테이블의 접근 순서를 의미하지는 않는다는 것입니다.

select_type

MySQL의 EXPLAIN 명령어에 표시되는 select_type은 쿼리의 개별 컴포넌트가 어떻게 작동하는지, 그리고 어떤 유형의 작업이 수행되는지를 설명하는 방법으로 사용됩니다. 이 필드는 SELECT 연산의 유형을 나타내며, 이는 쿼리의 복잡성과 구조를 이해하고 최적화 하는데 중요한 정보를 제공합니다.

SIMPLE: 이 유형은 서브쿼리나 UNION이 없는 기본 SELECT문을 나타냅니다. 여기선 복잡한 구조를 다루지 않고 단일 테이블에 대한 간결한 조회를 실행합니다.

explain
select
    *
from
    employees;

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

PRIMARY: 이 유형은 가장 외부 레벨의 SELECT문이나, 관계형 서브쿼리 또는 UNION을 가진 쿼리를 나타냅니다.

explain
select
    *
from
    employees
where
    id = (select employee_id from orders where id = 5);

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

UNION: 복수의 SELECT 쿼리 결과를 통합할 때 UNION 명령어를 사용하게 됩니다. 이때 첫 번째 SELECT 이후의 모든 SELECT 절에 대해 이 타입이 표시됩니다.

explain
select * from employees where salary > 50000
union
select * from employees where tenure > 5;

+----+--------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type  | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+--------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   |   33.33  | Using where                     |
|  2 | UNION        | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   |   33.33  | Using where                     |
|NULL| UNION RESULT | <union1,2>| NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary; Using filesort |
+----+--------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

UNION RESULT: UNION 작업이 마무리되면, 그 결과는 UNION RESULT로 나타나게 됩니다. 이것은 UNION 연산의 최종 결과를 제공합니다.

explain
select * from employees where salary > 50000
union
select * from employees where tenure > 5;

+----+--------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type  | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+--------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100  |   33.33  | Using where                     |
|  2 | UNION        | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100  |   33.33  | Using where                     |
|NULL| UNION RESULT | <union1,2>| NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary; Using filesort |
+----+--------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

SUBQUERY: 쿼리 내 서브쿼리가 메인 쿼리와 별개로 작동할 때 이를 SUBQUERY라 합니다. 그러나 이 유형은 서브쿼리가 FROM 절 이외의 위치에 사용될 때만 적용됩니다.

explain
select
    *
from
    employees
where
    salary > (select avg(salary) from employees);

+----+--------------------+-----------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type        | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+--------------------+-----------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1  | PRIMARY            | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   |   33.33  | Using where                  |
| 2  | SUBQUERY           | NULL      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+--------------------+-----------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

DEPENDENT SUBQUERY: 기본 쿼리의 결과에 따라 실행될 수 있는 하위 쿼리를 의미합니다. 보조 쿼리의 종류 중 하나로, 외부 쿼리에 의존적인 보조 쿼리는 외부 쿼리의 결과에 따라 그 결과가 달라지는 보조 쿼리입니다.

explain
select
    emp_no,
    salary
from
    salaries
where
    emp_no in (select emp_no from employees where hire_date > '2000-01-01'); 

+----+--------------------+-----------+------------+--------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type        | table     | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------------+-----------+------------+--------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY            | salaries  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 10   |   10.00  | Using where; Using join buffer (Block Nested Loop) |
|  2 | DEPENDENT SUBQUERY | employees | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |  1   |  100.00  | Using where                                        |
+----+--------------------+-----------+------------+--------+---------------+---------+---------+------+------+----------+----------------------------------------------------+

DERIVED: FROM 절에서 서브쿼리가 사용될 때, 그 서브쿼리에 대한 결과는 일종의 일시적인 테이블을 만드는 것으로 간주됩니다. MySQL에서는 이렇게 FROM 절에서 사용된 서브쿼리를 파생 테이블이라고 하며, 일반적인 RDBMS는 인라인 뷰(Inline View) 또는 서브 셀렉트(Sub Select)라고 부릅니다.

explain
select
    e.*
from
    (select * from employees where salary > 50000) e;
    
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5    |   100.00 | NULL        |
|  2 | DERIVED     | employees  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   |     50.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

table

MySQL의 EXPLAIN 명령어에서 table 필드는 쿼리가 참조하는 테이블의 이름을 나타냅니다. 이 테이블 명칭은 쿼리의 복잡성과 구조를 해석하는데 중요한 단서를 제공합니다.

만약 쿼리가 둘 이상의 테이블을 참조한다면, EXPLAIN 결과에 각각의 테이블에 대한 레코드가 표시됩니다. 여기서 주의할 점은 table 필드가 언제나 물리적 테이블을 나타내는 것은 아니라는 점입니다. 따라서, 복합 쿼리의 일부인 서브쿼리나, 별도의 테이블로 생성된 결과 집합을 표현하는 데 사용할 수 있습니다.

explain
select
    users.id,
    orders.order_number,
    products.product_name
from
    USERS users
    inner join ORDERS orders on orders.user_id = users.id
    inner join PRODUCTS products on products.id = orders.product_id
where
    users.country = 'USA';

+-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+
| id  | select_type |  table   | type  | possible_keys |   key    | key_len |          ref               | rows |   Extra     |
+-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+
|  1  | SIMPLE      | users    | ALL   | PRIMARY       | NULL     | NULL    | NULL                       | 1000 | Using where |
|  1  | SIMPLE      | orders   | ref   | user_id       | user_id  | 5       | database.users.id          |   10 |             |
|  1  | SIMPLE      | products | eq_ref| PRIMARY       | PRIMARY  | 4       | database.orders.product_id |    1 |             |
+-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+

partitions

MySQL의 EXPLAIN 명령어에서 partitions 필드는 파티셔닝된 테이블에 대한 쿼리를 수행할 때 참조되는 파티션을 나타냅니다. 이 파티션 정보는 효율적인 쿼리 실행이 가능한지, 그리고 파티셔닝 설정이 적합한지를 판단하는데 도움이 됩니다.

만약 쿼리가 테이블의 모든 파티션을 스캔한다면, partitions 필드는 NULL 값을 가지며 이는 파티셔닝 최적화가 잘 이루어지지 않은 것을 나타낼 수 있습니다. 반면에 쿼리가 파티션을 올바르게 이용해 한 개 혹은 일부 파티션만 스캔한다면, 이 필드는 해당 파티션 이름을 표시합니다.

explain
select
    users.id,
    orders.order_number,
    products.product_name
from
    USERS users
    inner join ORDERS orders on orders.user_id = users.id
    inner join PRODUCTS products on products.id = orders.product_id
where
    users.country = 'USA';

+-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+-------------+
| id  | select_type |  table   | type  | possible_keys |   key    | key_len |          ref               | rows | partiitions |   Extra     |
+-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+-------------+
|  1  | SIMPLE      | users    | ALL   | PRIMARY       | NULL     | NULL    | NULL                       | 1000 | p0,p1       | Using where |
|  1  | SIMPLE      | orders   | ref   | user_id       | user_id  | 5       | database.users.id          |   10 | p0          |             |
|  1  | SIMPLE      | products | eq_ref| PRIMARY       | PRIMARY  | 4       | database.orders.product_id |    1 | NULL        |             |
+-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+-------------+

type

쿼리의 실행 계획에서 type 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타내는 중요한 요인입니다. 인덱스의 효율적 사용은 쿼리 튜닝의 무엇보다 중요한 부분이므로, type 컮럼을 체크하는 것은 필수적입니다.

MySQL 공식 문서에서는 type 컬럼을 조인 타입으로 설명하고 있지만, 이를 조인과 직접적으로 연관지어 생각하는 것보다는, 각 테이블에 대한 접근 방식으로 이해하는 것이 더 유익합니다. 계획에서 한 테이블이 어떻게 스캔되는지, 인덱스가 어떻게 사용되는지 등을 나타내는 것이 type 컮럼의 주된 역할입니다.

type 컬럼의 각 값에 대해 속도가 빠른 순서대로 하나씩 살펴보도록 하겠습니다.

system

테이블이 단 하나의 레코드만을 가지거나, 혹은 레코드가 하나도 없을 때 발생합니다.
속도는 모든 타입 중에서 가장 빠르나, 실제 애플리케이션에서 사용되는 쿼리에서는 보기 어렵습니다.

create table SYSTEM_VARIABLES(
    name VARCHAR(50),
    value VARCHAR(50)
);

insert into SYSTEM_VARIABLES values ('max_connection', '100');

select 
    * 
from 
    SYSTEM_VARIABLES 
where
    name = 'max_connection';

+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | SYSTEM_VARIABLES | system| PRIMARY       | PRIMARY | 4       | const | 1    | NULL  |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+

이 쿼리는 SYSTEM_VARIABLES 테이블에서 NAME이 max_connection인 레코드를 찾습니다. 데이터가 단 하나 밖에 없으므로, MySQL은 system 방식으로 테이블에 접근합니다. 이 접근 방식은 매우 속도가 빠르고 효율적이지만, 실제 운영 환경에서 이런 경우는 드뭅니다. 이는 테이블에 저장된 데이터 항목이 단 한 가지뿐이기 때문입니다.

const

테이블의 총 레코드 개수와 상관없이 Primary Key 또는 Unique Index를 통해 오직 단일 레코드만 반환하는 쿼리를 처리할때 사용하는 방식입니다.
다중 컬럼으로 구성된 Primary Key나 Unique Index 중에서 일부 컬럼만 조건으로 사용할 때는 const가 아닌 ref로 처리됩니다.

select 
    * 
from 
    UNIVERSITY 
where 
    id = 1;

+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | UNIVERSITY| const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL  |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+

이 쿼리는 UNIVERSITY 테이블에서 id가 1인 대학을 검색합니다. const 접근 방식을 통해 Primary Key를 사용하여 해당하는 대학을 효율적으로 찾을 수 있습니다. const 방식은 테이블의 특정 레코드만 조회합니다. 따라서 이 방식은 매우 빠르며, 해당 로우가 메모리에 한 번만 읽혀지기 때문에 아주 효율적입니다.

eq_ref

두 테이블 간의 조인에서 모든 조건부분이 Unique Index를 사용하면 이 방식이 사용됩니다.
eq_ref에서 ref은 참조(Reference)를 의미하고, eq는 동일(Equal)을 의미합니다. 즉, eq_ref 접근 방식은 양쪽 테이블이 동일한(개별 다른 테이블의) 참조를 가진다는 것을 의미합니다.
Unique Index나 Primary Key를 통해 데이터 집합에서 단일 행을 찾아낸 후 이를 기반으로 다른 테이블과 연결하게 됩니다.
이 방식은 반복적인 테이블 연결 과정에서 Unique Index를 이용한 최적화를 수행하기 때문에 일반적으로 매우 효율적입니다.

select 
    * 
from 
    STUDENTS students 
    inner join UNIVERSITY universities on students.university_id = universities.id 
where 
    universities.name = 'Some University';

+----+-------------+--------------+--------+---------------+------------+---------+------------------+------+-------+
| id | select_type | table        | type   | possible_keys | key        | key_len | ref              | rows | Extra |
+----+-------------+--------------+--------+---------------+------------+---------+------------------+------+-------+
|  1 | SIMPLE      | universities | const  | PRIMARY       | PRIMARY    | 4       | const            | 1    | NULL  |
|  1 | SIMPLE      | students     | eq_ref | university_id | college_id | 4       | u.id             | 1    | NULL  |
+----+-------------+--------------+--------+---------------+------------+---------+------------------+------+-------+

이 쿼리는 UNIVERSITY 테이블의 name이 Some University인 대학에 참조하고 있는 모든 학생들을 STUDENTS 테이블에서 검색하는 것입니다. 처음에 UNIVERSITY 테이블에서 이름이 Some University인 대학을 찾아 내고 (const 접근 방식), 그 후에 STUDENTS 테이블에서 university_id가 찾아낸 대학의 id와 일치하는 학생들을 검색(eq_ref 방식)합니다.

ref

이 방식은 인덱스의 일부만 일치하더라도 최적의 인덱스를 찾아 활용합니다.
ref는 특정 값과 일치하는 값을 갖는 레코드를 Index나 Non-Unique Index로부터 찾아냅니다.
이 방식은 데이터 집합에서 특정 값과 일치하는 여러 레코드들을 수직적으로 스캔하여 반환합니다.
eq_ref와는 다르게, ref 방식을 사용하면 한 번에 여러 레코드를 선택할 수 있어 속도는 상대적으로 느리지만, 인덱스의 최적화가 잘 이루어져 있다면 여전히 효율적인 방법입니다.

select 
    * 
from 
    STUDENTS 
where 
    university_id = 1;

+----+-------------+---------+------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | STUDENTS| ref  | university_idx | university_idx  | 5       | const| 500  | Using where |
+----+-------------+---------+------+---------------+------------------+---------+------+------+-------------+

이 쿼리는 STUDENTS 테이블에서 university_id가 1인 학생들을 검색합니다. ref 액세스 방식을 통해 university_idx 인덱스를 사용하여 해당하는 학생들을 효율적으로 찾을 수 있습니다.

fulltext

MySQL에서 제공하는 텍스트 검색 기능을 위한 특수한 인덱스 타입입니다.
이 방식은 필드에 저장된 전체 텍스트를 사용하여 매칭하는 방식으로 레코드를 검색합니다.
이 특별한 타입의 인덱스는 문자의 데이터 타입에만 적용될 수 있으며, 특히 본문 검색과 같은 대용량 텍스트 데이터에서 유용합니다.
Match 함수와 Against 함수를 조합하여 사용하며, 그 결과는 relevancy 점수를 반환합니다. 이 점수는 레코드가 검색 조건과 얼마나 잘 일치하는지를 나타냅니다.

select 
    *, 
    match(BIOGRAPHY) against('mathematics') as relevance 
from 
    STUDENTS 
where 
    match(BIOGRAPHY) against('mathematics') > 0;

+----+--------------+------------+---------+------------------+------------------+---------+-------+------+-------------+
| id | select_type  | table      | type    | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+--------------+------------+---------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE       | STUDENTS   | fulltext| fulltext_idx     | fulltext_idx     | 0       | const | 1    | Using where |
+----+--------------+------------+---------+------------------+------------------+---------+-------+------+-------------+

해당 SQL 명령어는 STUDENTS 테이블의 BIOGRAPHY 컬럼에서 mathematics를 검색합니다. 검색 결과 중 mathematics에 가장 높은 relevancy를 보이는 레코드가 반환됩니다. 이는 fulltext 인덱스 액세스 방식이 사용되었음을 보여줍니다.

ref_or_null

MySQL Optimizer가 인덱스를 사용하면서 동시에 null 값을 포함할 수 있을 때 사용하는 액세스 방법입니다.
이 방식은 ref 액세스 방식과 거의 같지만 차이점은 MySQL이 해당 인덱스에서 NULL 값을 가진 레코드를 추가로 검색할 수 있다는 점입니다.
이 추가적인 NULL 값 검색으로 인해 ref_or_null 액세스 방식은 약간의 성능 저하가 발생할 수 있지만, 특정 상황에서는 NULL 값을 포함하여 검색하는데 유용합니다.
WHERE 절 조건문에서 칼럼 값이 특정 값이거나 NULL 인 경우를 찾는 쿼리에 ref_or_null 액세스 방식이 사용됩니다.

select
    *
from
    STUDENTS
where
    university_id is null or university_id = 1;

+----+-------------+---------+-----------+-----------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type      | possible_keys   | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-----------+-----------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | STUDENTS| ref_or_null | university_idx | university_idx  | 5       | const| 500  | Using where |
+----+-------------+---------+-----------+-----------------+------------------+---------+------+------+-------------+

위 쿼리는 STUDENTS 테이블에서 university_id가 NULL이거나 1인 학생들을 검색합니다. ref_or_null 액세스 방식을 통해 university_idx 인덱스를 사용하여 해당하는 학생들을 효율적으로 찾을 수 있습니다.

index_merge

MySQL에서 제공하는 여러 인덱스를 병합하여 검색하는 기능입니다.
이 방식은 WHERE 절 안에서 OR 또는 AND를 사용하는 복잡한 쿼리에 유용합니다.
MySQL은 각 조건에 맞는 각 개별 인덱스를 찾아 접근하고, 이들 결과를 병합하여 최종 결과를 도출합니다. 이 과정을 통해, 전체 테이블 스캔 보다 훨씬 효율적으로 쿼리를 수행할 수 있게 됩니다.
이 기법은 두 가지의 종류가 있으며, OR의 경우 index_merge_union, AND의 경우 index_merge_sort_union 입니다.

select
    *
from
    STUDENTS
where
     major = 'Mathematics'
  or last_name = 'Smith';

+----+-------------+---------+-------------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type        | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | STUDENTS| index_merge |major_idx,name_idx|major_idx,name_idx| 0       |const | 10   | Using where |
+----+-------------+---------+-------------+------------------+------------------+---------+------+------+-------------+

여기서 SQL 쿼리는 큰 STUDENTS 테이블에서 major가 Mathematics이거나 last_name이 Smith인 학생들을 찾습니다. 이 때 index_merge 액세스 방식이 사용되어 major_idx와 name_idx라는 두 인덱스가 병합되어 실행됩니다. 이 방식은 OR 연산이 포함된 복잡한 쿼리를 효율적으로 처리하기 위해 사용됩니다.

unique_subquery

MySQL의 실행 계획 유형 중 IN 절의 최적화를 목적으로 사용됩니다.
이 방식은 서브쿼리의 결과가 고유한 값을 반환하는 경우 즉, 중복된 결과 값이 없는 경우에 특히 유용하게 사용됩니다.
unique_subquery의 핵심 목적은 IN 절의 처리를 빠르게 하고, 그 방식을 통해 서브쿼리 검색을 단순화하는 것입니다.
unique_subquery 추출 방식는 고유한 결과 값에 대해 단일 인덱스 참조를 수행하므로, 어떤 레코드가 결과 세트에 포함되어야 할지 효율적으로 결정할 수 있습니다.
따라서, 이 방식은 일반적인 서브쿼리 처리 방식에 비해 쿼리 수행 과정에서 효율성을 높이는 데 도움을 줍니다.

select
    *
from
    STUDENTS
where
    id in (select student_id from SCORES where score > 90);

+----+-------------+---------+-----------------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type            | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-----------------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | SCORES  | range           | student_id_idx   | student_id_idx   | 4       | NULL | 100  | Using where |
|  1 | SIMPLE      | STUDENTS| unique_subquery | PRIMARY          | PRIMARY          | 4       | func | 1    | Using index |
+----+-------------+---------+-----------------+------------------+------------------+---------+------+------+-------------+

여기서 unique_subquery 방식이 사용되는 모습을 확인할 수 있습니다. SCORES 테이블에서 score가 90 이상인 학생의 id를 대상으로 STUDENTS 테이블을 검색하는 쿼리를 수행합니다. unique_subquery 액세스 방식을 통해 효율적인 쿼리 수행이 가능합니다.

index_subquery

MySQL의 실행 계획 타입 중 하나로, 특히 IN 절에서 사용됩니다.
이 방식은 서브쿼리가 고유하지 않은 결과(즉, 동일한 값을 가진 여러 레코드)를 반환하는 경우, 인덱스를 통해 중복을 제거할 수 있을때 적용됩니다.
이 실행 타입이 선택되면, MySQL은 서브쿼리의 각 반환 결과에 대해 인덱스 검색을 수행하고, 해당 값에 일치하는 모든 레코드를 반환합니다.
비록 이 방식이 ref 나 eq_ref에 비해 비교 연산 비용이 추가로 들 수 있지만, 고유하지 않은 결과를 처리해야하는 경우에는 더 효율적으로 동작할 수 있습니다.

select
    *
from
    STUDENTS
where
    major in (select major from MAJORS where faculty = 'Science');

+----+--------------+---------+---------------+--------------+--------------+---------+------+------+-------------+
| id | select_type  | table   | type          | possible_keys| key          | key_len | ref  | rows | Extra       |
+----+--------------+---------+---------------+--------------+--------------+---------+------+------+-------------+
|  1 | PRIMARY      | MAJORS  | ref           | faculty_idx  | faculty_idx  | 32      | const| 10   | Using where |
|  1 | PRIMARY      | STUDENTS| index_subquery| major_idx    | major_idx    | 32      | func | 1    | Using index |
+----+--------------+---------+---------------+--------------+--------------+---------+------+------+-------------+

여기에서 index_subquery 방식이 사용되는 것을 확인할 수 있습니다. MAJORS 테이블에서 faculty가 Science인 전공을 탐색하고, 이 결과를 이용하여 STUDENTS 테이블에서 학생들의 전공을 찾는 쿼리를 수행합니다. index_subquery 액세스 방식을 사용하여, 효율적으로 쿼리 수행이 가능합니다.

range

MySQL이 인덱스를 사용하여 특정 값 범위의 레코드를 검색할 때 사용하는 액세스 방법입니다.
이 접근 방법은 WHERE 절의 비교 연산자 >, <, >=, <=, BETWEEN, IN을 사용하여 한정된 범위의 쿼리를 처리하는 데 유용합니다. range는 인덱스에 따라 자동으로 정렬되기 때문에 추가 정렬 작업이 필요 없습니다.
range 접근 방식은 전체 테이블 스캔에 비해 훨씬 효율적입니다.

select
    *
from
    STUDENTS
where
    gpa between 3.5 and 4.0;

+----+-------------+---------+------+--------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys| key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+--------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | STUDENTS| range| gpa_idx      | gpa_idx     | 8       | NULL | 100  | Using where |
+----+-------------+---------+------+--------------+-------------+---------+------+------+-------------+

이 쿼리는 STUDENTS 테이블에서 평균 학점이 3.5와 4.0 사이인 학생들을 검색합니다. range 액세스 방식을 통해 gpa_idx 인덱스를 사용하여 효율적으로 이 범위의 학생들을 찾을 수 있습니다.

index

MySQL의 인덱스 접근 방법 중 하나로, 테이블 대신 인덱스를 전체 스캔하는 방식을 의미합니다.
이 접근 방식은 WHERE 절이 없거나 인덱스의 모든 열을 사용하지 않는 쿼리에 사용합니다. 다만 index는 range나 eq_ref와 같은 더 효율적인 접근 방식에 비해 선호되지 않습니다.
index 접근 방식은 쿼리의 ORDER BY 절에서 인덱스를 사용하여 결과를 빠르게 정렬할 수 있을 때 유용합니다.

select
    id,
    name
from
    STUDENTS
order by
    id;

+----+-------------+---------+-------+---------------+---------+---------+------+------+----------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra          |
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------------+
|  1 | SIMPLE      | STUDENTS| index | null          | PRIMARY | 4       | NULL | 1000 | Using index    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------------+

이 쿼리는 STUDENTS 테이블에서 학생의 id와 name을 id 순으로 정렬하여 검색합니다. index 액세스 방식을 통해 Primary Key를 사용하여 인덱스를 스캔하고, 필요한 필드(id, name)가 인덱스에 포함되어 있으므로 테이블 접근 없이 쿼리를 완료할 수 있습니다.

ALL

MySQL에서 제공하는 가장 기본적이며, 가장 비효율적인 접근 방법으로 전체 테이블 스캔을 의미합니다.
이 방식은 인덱스가 없거나 인덱스를 사용하더라도 의미가 없는 경우 사용됩니다. 또한, 대부분의 로우에 대한 작업이 필요할 때 사용되는 경우도 있습니다.
이 접근 방식은 데이터가 많은 테이블에서는 과도한 시간을 소모할 수 있으므로, 가능한 피해야 합니다.

select
    *
from
    STUDENTS
where
    gpa > 3.5;

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | simple      | STUDENTS| all  | null          | null | null    | null | 1000 | using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

위 쿼리는 STUDENTS 테이블에서 평균 학점이 3.5 보다 큰 학생들을 조회합니다. 만약 gpa에 대한 인덱스가 없다면, MySQL은 테이블의 모든 레코드를 스캔하여 조건에 해당하는 레코드를 찾습니다. 이는 All 접근 방식이므로 효율성이 떨어집니다.

possible_keys

possible_keys는 MySQL이 결과를 얻기 위해 마침내 사용할 수도 있는 인덱스인지 나타냅니다.

key

key는 MySQL이 실제로 사용한 인덱스입니다.

key_len

key_len은 MySQL이 사용할 수 있는 키의 최대 길이를 나타냅니다. 길이가 짧을 수록 스캔 속도가 빨라집니다.

reference

ref는 key와 비교되는 열 또는 상수를 나타냅니다.

rows

rows는 MySQL이 찾아야 하는 것으로 예상되는 행의 수입니다.

filtered

filtered은 MySQL이 찾은 행 중 얼마나 많은 비율이 WHERE 조건에 맞는지 나타내는 예상 백분율입니다.

Extra

Using filesort
Using index
Using temporary
Range checked for each record (index map: %)
Using where with pushed condition
Using index for group-by
Using index for skip scan

EXPLAIN ANALYZE

MySQL에서 쿼리 분석을 위해 사용되는 EXPLAIN 명령어는 쿼리의 최적화된 실행 계획을 제공하지만, 이는 실제로 쿼리가 수행될 때 발생하는 모든 결과를 포함하지 않습니다.

EXPLAIN의 이러한 제약을 보완하기 위해 MySQL 8.0.18 버전부터 EXPLAIN ANALYZE 기능이 도입되었습니다. EXPLAIN ANALYZE는 쿼리 실행 계획을 생성하는 데 그치지 않고, 실제 쿼리를 실행하여 상세한 통계 정보도 수집합니다. 따라서 EXPLAIN을 통해 볼 수 없던 디스크 I/O, CPU 사용률, 예상보다 많은 블록을 읽은 지점 등의 정보도 얻을 수 있게 됩니다.

다음 코드는 EXPLAIN ANALYZE을 사용한 예시입니다:

explain analyze
select
    students.student_name,
    courses.subject_name
from
    STUDENT_COURSES student_courses
    inner join STUDENTS students on students.student_id = student_courses.student_id
    inner join COURSES courses on courses.subject_code = student_courses.subject_code

EXPLAIN ANALYZE의 결과는 쿼리 계획의 작업 간의 관계와 순서를 보여주는 TREE 포맷으로 출력됩니다. 여기서 들여쓰기는 작업 간의 상대적인 종속성과 순서를 나타내며, 들여쓰기가 많을 수록 쿼리 계획에서 더 내부 단계에 있는 작업을 의미합니다. 이 포맷은 첫번째 들여쓰기가 가장 상위 단계의 작업을 의미하고, 들여쓰기가 늘어날 때마다 그 단계 내부의 작업을 나타내게 됩니다.

쿼리의 실행 순서를 이해하려면 EXPLAIN ANALYZE의 결과에서 들여쓰는 방식을 이해해야 하는데 다음과 같은 규칙을 따릅니다:

각 레벨에서 작업의 순서는 위에서 아래로 읽습니다.
동일한 레벨에서 상단에 위치한 작업이 먼저 실행됩니다.
그러나 작업이 여러 레벨로 중첩된 경우가 있을 수 있으며, 이 경우에는 먼저 가장 안쪽(가장 많은 들여쓰기를 가진 레벨)에 있는 작업이 실행됩니다.
모든 내부 단계가 완료되면 그 바로 위 레벨의 다음 작업이 실행됩니다.

생성된 EXPLAIN ANALYZE의 실행 계획의 분석 결과는 다음과 같습니다:

-> Nested loop inner join  (cost=3512 rows=4385) (actual time=0.169..17.4 rows=4385 loops=1)
    -> Nested loop inner join  (cost=1977 rows=4385) (actual time=0.138..8.95 rows=4385 loops=1)
        -> Filter: ((student_courses.student_id is not null) and (student_courses.subject_code is not null))  (cost=442 rows=4385) (actual time=0.101..2.51 rows=4385 loops=1)
            -> Table scan on student_courses  (cost=442 rows=4385) (actual time=0.0998..2.04 rows=4385 loops=1)
        -> Single-row index lookup on students using PRIMARY (student_id=student_courses.student_id)  (cost=0.25 rows=1) (actual time=0.00123..0.00128 rows=1 loops=4385)
    -> Single-row index lookup on courses using PRIMARY (subject_code=student_courses.subject_code)  (cost=0.25 rows=1) (actual time=0.00153..0.00158 rows=1 loops=4385)

위 쿼리의 실행 계획을 상세히 살펴보면 다음과 같은 순서로 해석할 수 있습니다:

Line 4: Table scan on student_courses이 시작되어 student_courses 라는 테이블을 전체적으로 스캔합니다.
Line 3: Filter단계가 시작되는데 여기서는 student_courses.student_id 및 student_courses.subject_code 필드가 null은 아닌지 확인합니다.
Line 5: Single-row index lookup on students using PRIMARY (student_id=student_courses.student_id)가 수행되어 student_courses 테이블의 student_id를 가진 students 테이블의 레코드를 찾습니다.
Line 2: Nested loop inner join의 결과가 후속 작업에 연결됩니다. 이 작업은 이전에 찾아낸 students 테이블 레코드와 조인합니다.
Line 6: Single-row index lookup on courses using PRIMARY (subject_code=student_courses.subject_code)가 실행되어, student_courses 테이블에 있는 subject_code 와 일치하는 courses 테이블 레코드도 찾아냅니다.
Line 1: 최종적으로, 이 모든 결과는 처음 시작 됐던 Nested loop inner join로 전달됩니다.

이렇게 해서 주어진 쿼리의 결과가 생성되며 해당 쿼리의 실행이 끝납니다. 쿼리의 결과가 생성되는 과정을 이해했다면 이제 EXPLAIN ANALYZE의 출력 결과에서 주요 컬럼의 의미를 살펴보겠습니다:

Term	Description
Cost	해당 작업의 예상 시간을 나타냅니다. 디스크 페이지를 읽어야 하는 시점부터 모든 데이터를 처리하는 데 필요한 최대 시간을 의미합니다. 파라미터는 시작 시점과 마지막 시점의 비용을 표시합니다.
Actual Time	쿼리의 각 단계를 완료하는데 소요된 실제 시간을 표시합니다. 예를 들어, `actual time=0.0998..2.04` 같이 숫자 값이 두 개가 표시되는데, 첫번째 수치는 해당 단계가 시작하고 첫번째 레코드를 가져오는 데 걸린 평균 시간, 두번째 수치는 해당 단계에서 마지막 레코드를 가져오는 데 걸린 평균 시간을 의미합니다. 단위는 밀리초(ms)입니다.
Rows	각각의 작업 단계에서 생성되거나 또는 처리되는 레코드의 수를 나타냅니다.
Loops	상위 단계에서 하위 단계가 반복되어 수행된 횟수를 나타냅니다. SQL 쿼리의 작업 단계가 획기적으로 증가하면 이는 종종 성능 문제의 원인이 됩니다.
Filter	특정 조건을 기반으로 행을 걸러내는 작업을 설명합니다.
Single-Row Index Lookup	이는 주어진 조건에 따라 특정 행을 찾는 데 사용되는 인덱스를 나타냅니다. 이 단계에서는 보통 하나의 행만 반환됩니다.
Nested Loop Inner Join	이는 두 테이블 간의 결합을 처리하는 방법을 나타냅니다. 이 방법은 왼쪽 테이블의 각 행에 대해 오른쪽 테이블을 스캔하는 방식으로 작동합니다.
Table Scan	데이터베이스가 테이블의 모든 행을 검색하며, 이는 종종 비효율적일 수 있습니다. 가능하다면, 인덱스 스캔을 사용하는 것이 선호됩니다.

EXPLAIN ANALYZE를 이해하는 것은 쿼리 성능 최적화에 매우 중요하지만, 비용이 큰 쿼리의 경우 실제로 쿼리를 실행하므로 주의가 필요합니다. 실행 시간이 길거나 많은 리소스를 사용하거나 사이드 이펙트를 발생시킬 수 있는 쿼리에 대해서는 EXPLAIN을 먼저 사용하고, 결과를 분석한 후에 EXPLAIN ANALYZE를 사용하는 것이 더 안전합니다.

📚 Real MySQL 8.0
🔗 MySQL Reference Manaual

Database
MySQL