04.3 실행 계획 분석

MariaDB에서 쿼리의 실행 계획을 확인하려면 EXPLAIN 명령을 사용하면 됩니다.

EXPLAIN 명령만 사용하면 기본적인 쿼리 실행 계획만 보이지만 EXPLAIN EXTENDED나 EXPLAIN PARTITIONS 명령을 이용해 더 상세한 실행 계획을 확인할 수도 있습니다.

 

실행계획 예

explain
select * from employees e where e.emp_no in (select emp_no from salaries where salary between 10 and 1000);
+------+--------------+-------------+--------+-------------------+-----------+---------+---------------------------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys     | key       | key_len | ref                       | rows | Extra                    |
+------+--------------+-------------+--------+-------------------+-----------+---------+---------------------------+------+--------------------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key      | NULL      | NULL    | NULL                      | 1    |                          |
|    1 | PRIMARY      | e           | eq_ref | PRIMARY           | PRIMARY   | 4       | employees.salaries.emp_no | 1    |                          |
|    2 | MATERIALIZED | salaries    | range  | PRIMARY,ix_salary | ix_salary | 4       | NULL                      | 1    | Using where; Using index |
+------+--------------+-------------+--------+-------------------+-----------+---------+---------------------------+------+--------------------------+

출력 순서는 위에서 아래로 순서대로 표시됩니다. 출력된 실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 Outer 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록 쿼리의 Inner 부분 또는 나중에 접근한 테이블에 해당합니다.

 

4.3.1 id 컬럼

단위 SELECT 쿼리별로 부여되는 식별자 값입니다. 

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

 

4.3.2 select_type 컬럼

SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼입니다.

SIMPLE

UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우에 표시됩니다. 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 반드시 하나만 존재합니다. 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE로 표시됩니다.

 

PRIMARY

UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 Outer에 있는 단위 쿼리에 표시됩니다. SUMPLE과 마찬가지로 PRIMARY인 단위 쿼리는 하나만 존재하며 쿼리의 제일 Outer에 있는 SELECT 단위 쿼리가 PRIMARY로 표시됩니다.

 

UNION

UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT가 UNION으로 표시됩니다.

UNION의 첫번째 단위 SELECT는 DERIVED(임시)로 표시됩니다.

 

MariaDB [employees]> 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      | type  | possible_keys | key         | key_len | ref  | rows   | Extra       |
+------+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL | 30     |             |
|    2 | DERIVED     | e1         | index | NULL          | ix_hiredate | 3       | NULL | 298892 | Using index |
|    3 | UNION       | e2         | index | NULL          | ix_hiredate | 3       | NULL | 298892 | Using index |
|    4 | UNION       | e3         | index | NULL          | ix_hiredate | 3       | NULL | 298892 | Using index |
+------+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
4 rows in set (0.001 sec)

DEPENDENT UNION

UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시됩니다. 그리고 DEPENDENT는 결합된 단위 쿼리가 외부에 의해 영향을 받는 것을 의미합니다.

※서브쿼리가 사용된 경우 Outer쿼리보다 서브쿼리가 먼저 실행되는 것이 일반적이며 대부분이 이반대의 경우보다 빠르게 처리됩니다. 하지만 DEPENDENT키워드를 포함하는 서브 쿼리는 외부 쿼리에 의존적이므로 절대 외부쿼리보다 먼저 실행될 수 없습니다. 그래서 DEPENDENT 키워드가 포함된 서브쿼리는 비효율인 경우가 많습니다

 

UNION RESULT

UNION 결과를 담아두는 테이블을 의미합니다.

 

SUBQUERY

여기서 SUBQUERY라 하고 하는 것은 FROM 절 이외에서 사용되는 서브쿼리 만을 의미합니다.

FROM 절에서 사용된 서브 쿼리는 DERIVED라고 표시되고 그 밖에 위치에서 사용된 서브 쿼리는 SUBQUERY라고 표시됩니다.

 

DEPENDENT SUBQUERY

서브 쿼리가 Outer Select 쿼리에서 정의된 컬럼을 사용하는 경우입니다.

외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행되야 하므로 일반 서브 쿼리보다는 처리 속도가 느릴 때가 많습니다.

 

DERIVED

단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미합니다. select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 합니다. MariaDB 5.3버전 부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있도록 최적화되었습니다.

 

UNCACHEABLE SUBQUERY

하나의 쿼리문장에서 서브 쿼리가 하나만 있더라도 실제 그 서브 쿼리가 한 번만 실행되는 것은 아닙니다. 그런데 조건이 똑같은 서브 쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브 쿼리의 결과를 내부적인 캐시 공간에 담아둡니다. 서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수 있는데 이러면 UNCACHEABLE SUBQUERY로 표시됩니다.

 

UNCACHEABLE UNION

UNCACHEABLE과 UNION, 두 개의 키워드의 속성이 혼합된 type입니다.

 

MATERIALIZED

주로 FROM 절이나 IN(subquery)형태의 쿼리에 사용된 서브 쿼리의 최적화를 위해서 사용됩니다.

 

INSERT

MariaDB10.0.5부터 INSERT문장과 UPDATE그리고 DELETE 문장에 대해서도 실행 계획을 조회할 수 있게 되었습니다.

INSERT문장의 실행 계획인 경우에는 select_type만 INSERT라고 출력되고 실행 계획의 나머지 부분은 SELECT 쿼리 문장과 거의 비슷하게 출력됩니다.

 

 

4.3.3 Table 컬럼

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

Table컬럼에 <derived N> 또는 <unionM,N>과 같은 형식은 임시 테이블을 의미합니다. 그리고 표시되는 숫자는 SELECT 쿼리의 id를 지칭합니다.

 

4.3.4 type 컬럼

MariaDB서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냅니다.

type에 표시될 수 있는 값은 아래와 같습니다.

 

system

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

const

테이블의 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 where 조건절을 가지고 있으며 반드시 1건을 반환하는 쿼리의 처리 방식 (유니크 인덱스 스캔이라고도 함)

eq_ref

조인에서 처음 읽은 테이블의 컬럼 값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때 사용

ref

조인의 순서와 관계없이 사용되며 프라이머리 키나 유니크 키등의 제약조건도 없습니다. 인덱스 종류와 관계없이 동등조건으로 검색할 때는 ref 접근방법이 사용됩니다.

fulltext

MariaDB의 전문검색 인덱스를 사용해 레코드를 읽는 접근 방법. 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 합니다(MATCH... AGAINST 구문) 

ref_or_null

ref 접근방식과 같은데 NULL 비교가 추가된 형태 ref 방식 또는 IS NULL 접근 방식

unique_subquery

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

 

index_subquery

서브 쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery접근방법이 사용됩니다.

range

인덱스 레인지 스캔 형태의 접근 방법입니다. 

index_merge

2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식.

index

INDEX FULL SCAN을 의미

ALL

TABLE FULL SCAN을 의미

 

4.3.5 possible_keys 컬럼

MariaDB옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록. 목록일 뿐이기 때문에 쿼리를 튜닝하는데 아무런 도움이 되지 않습니다.

 

4.3.6 key 컬럼

최종 선택된 실행 계획에서 사용하는 인덱스. 쿼리를 튜닝할 때는 key컬럼에 의도 했던 인덱스가 표시되는지 확인하는 것이 중요합니다.

PRIMARY인 경우에는 PK를 사용한다는 의미입니다.

 

 

4.3.7 key_len 컬럼

쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇개의 칼럼까지 사용했는지 알려줍니다. 더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값입니다. 그래서 다중 칼럼 인덱스 뿐 아니라 단일 컬럼으로 만들어진 인덱스에서도 같은 지표를 나타냅니다.

 

4.3.8 ref 칼럼

접근 방법이 ref 방식이면 참조 조건(Equal 비교조건)으로 어떤 값이 제공됐는지 보여줍니다. 만약 상수 값을 지정했다면 const로 표시되고 다른 테이블의 칼럼값이면 그 테이블 명과 칼럼 명이 표시됩니다.

 

4.3.9 rows 컬럼

실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줍니다.

예상값이라 실제값과 같지는 않습니다. rows 칼럼에 표시되는값은 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미합니다.