MySQL(MariaDB)의 EXPLAIN으로 실행 결과 분석하기

실행 계획이라고 하면, 쿼리가 어떻게 데이터를 불러오는지에 대한 계획을 의미한다.

실행 계획을 통해서 어떤 테이블이 어떤 방식으로 조회됐는지 확인할 수 있다.

조회 쿼리의 인덱스 사용 유무를 확인할 때,

전체 조회 대상 중에서 검색 조건에 의해 어느정도의 비율로 필터링 되는지와 같이

성능 튜닝에 필요한 요소들을 확인하고자 할 때 쿼리 실행 계획을 확인해보는 것이 좋다.

 

실행 계획을 확인하는 데 있어서, MySQL(MariaDB)에서는 EXPLAIN, DESCRIBE, DESC를 사용한다.

세 명령어 모두 같은 결과를 내기 때문에 편한 걸 사용하면 된다.(필자는 EXPLAIN을 많이 쓴다.)

 

사용 예시

EXPLAIN
SELECT *
FROM employee
WHERE employee_num BETWEEN 100001 AND 200000;

실행 예시

id

실행 순서를 표기하며, 숫자가 작을수고 먼저 수행된 것이라고 보면 된다.

JOIN의 경우, 동일 ID를 가질 수 있기 때문에 ID 값이 같으면 JOIN 된 테이블로 해석하면 된다.

 

select_type

SELECT 문의 유형을 출력한다.

SELECT가 FROM 절에 위치했는지, 서브쿼리인지, UNION에 묶인 SELECT인지 확인할 수 있다.

 

 

SIMPLE

UNION, 서브 쿼리 없는 SELECT 문

 

PRIMARY

서브 쿼리가 포함된 SQL에서 첫 SELECT 구문에 표시 가장 외부에 있는 쿼리 or UNION이 있는 쿼리에서 첫번 째 작성된 구문에 표시

 

SUBQUERY

독립 수행되는 서브 쿼리, SELECT의 스칼라 서브쿼리, WHERE의 중첩 서브쿼리

 

DERIVED

FROM 절에 작성된 서브 쿼리

 

UNION

UNION/UNION ALL 구문으로 합쳐진 SELECT에서 첫번째를 제외한 나머지 SELECT에 표기

 

UNION RESULT

UNION ALL이 아닌 UNION 구문으로 결합한 경우 출력

UNION이 출력 결과에 중복이 없는 유일한 속성이기 때문에, SELECT 절에서 데이터를 가져와 정렬 및 중복 체크를 수행하게 된다. 이 타입으로 분류되면 별도의 메모리/디스크에 임시 테이블을 만들어 중복 제거를 의미한다.

 

DEPENDENT SUBQUERY

UNION/UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받을 때, 즉, UNION으로 연결된 단위 쿼리 중 처음 작성된 단위 쿼리에 해당하는 경우를 의미한다.

 

DEPENDENT UNION

앞서 언급한 DEPENDENT SUBQUERY와 동일한 경우면서, 첫번째 쿼리를 제외하고 두 번째 단위 쿼리에 해당하는 경우를 의미한다.

DEPENDENT SUBQUERY와 DEPENDENT UNION의 경우, 성능적 이슈가 있어서 튜닝 대상으로 많이 지목된다.

 

UNCACHEABLE SUBQUERY

재활용 될 서브쿼리가 재사용되지 않는 경우에 출력

서브쿼링 안에 사용자 정의 함수/변수가 있는 경우, 조회 시마다 결과가 달라지는 경우(UUID, RAND)에 해당한다.

 

MATERIALIZED

IN 절 구문에 연결된 서브쿼리가 임시테이블 생성 이후, JOIN 가공 시 출력되는 유형.

 

table

테이블명(alias 지정 시, alias 이름)을 출력

서브쿼리, 임시 테이블인 경우엔 <subquery#>, <derived#>로 표기됨

 

partitions

데이터 저장된 논리 영역을 표시

 

type

테이블의 데이터를 어떻게 찾을지에 대한 정보

전체 검색을 할 것인지, 인덱스를 사용할지에 대한 내용을 볼 수 있다.

 

system

데이터가 없거나, 하나만 있는 경우

 

const

조회 데이터가 1건인 경우, 고유 인덱스, PK를 사용해 접근

 

eq_ref

JOIN이 수행되는 시점에, 고유 인덱스나 PK로 단 1건의 데이터를 조회하여 이 또한 성능 상 유리

 

ref

eq_ref와 유사하면서, JOIN 수행 시 데이터 접근 범위가 2개 이상인 경우.

유니크 인덱스/PK를 통한 검색은 2개 이상의 데이터가 존재할 수 있고, 비고유 인덱스를 사용할 수도 있다.

 

ref_or_null

ref와 유사하지만 IS NULL 구문이 있는 인덱스를 활용하도록 최적화

MySQL(그리고 MariaDB도) NULL에 대해서 인덱스를 활용해 검색 가능.

 

range

BETWEEN, IN절, 비교연산자(=, <>, >, >=, <, <=, ISNULL, <=>) 조회 유형

 

fulltext

텍스트 검색을 처리하는 경우

 

index_merge

여러 인덱스가 동시에 사용되는 유형

특정 테이블에 사용된 2개 이상의 인덱스가 병합되어 동시에 적용되며, 이 상황에서 full text 인덱스는 제외한다.

 

index

INDEX에 대한 풀 스캔, 인덱스 블록을 처음부터 끝까지 탐색

 

ALL

테이블에 대한 풀 스캔

인덱스가 없거나, 인덱스 활용이 오히려 역효과가 발생할 때 Optimizer에 의해 선택

 

unique_subquery

IN 서브쿼리에서 PK 또는 고유 인덱스를 사용

 

index_subquery

unique_subquery와 유사하나, 고유 인덱스를 사용하지 않는다.

 

possible_keys

Optimizer가 SQL을 최적화할 때 사용 가능한 인덱스 목록(=후보군)

단지 목록만 제공하는 것이고, 실제 사용 여부와는 무관하다.

 

key

Optimizer가 SQL 최적화를 위해 사용한 PK, 인덱스명을 의미

어느 인덱스를 통했는지 확인할 수 있는 중요한 지표.

이 값이 NULL이면 PK도, 인덱스도 사용하지 않은 것.

 

key_len

인덱스 사용한 경우 사용한 인덱스의 바이트 값을 제공.

 

ref(=reference)

테이블 JOIN 시, 어떤 조건으로 해당 테이블에 접근했는지 제공.

 

rows

SQL을 수행하고자 접근하는 데이터의 모든 row의 수를 표시

수치가 정확하진 않으나, 이 수치는 결국 메모리에서 처리할 데이터의 양을 의미하기도 하므로 튜닝 시 살펴볼 지표.

 

filtered

필터 조건에 따라 얼만큼의 비율로 데이터가 제거되었는지를 의미

WHERE 조건에 의해 얼마나 필터링 되는지에 대해 나타내며, 이 또한 rows처럼 근사치로 파악

 

extra

SQL 수행 방식에 대한 추가 정보를 제공한다.

Optimizer 동작에 대해 힌트를 제공받을 수 있다.

 

Distinct

DISTINCT, UNION 구문이 포함된 경우 출력

 

Using where

WHERE 필터 조건으로 추출될 것을 의미

 

Using temporary

중간 결과 저장을 위해 임시 테이블이 생성됨을 의미한다.

DISTINCT, GROUP BY, ORDER BY가 포함된 경우 종종 볼 수 있다.

 

Using index

인덱스만 읽어 처리하는 경우, Convering Index 방식으로 인덱스로만 구성된 열을 사용할 때 출력.

 

Using index for group-by

GROUP BY나 DISTINCT가 포함되는 경우, 인덱스로 정렬을 수행해 최적화한다.

Using index와 유사하지만, GROUP BY에 대해 Covering Index 방식으로 해결 가능함을 의미한다.

 

Using index condition, Using index condition(BKA)

필터 조건을 엔진에 전달해 필터링 작업에 부하를 줄이는 방식. 스토리지 엔진에서의 데이터 결과를 줄여서 최적화하는 방식으로, 여기서 BKA가 추가적으로 붙으면 데이터 검색에서 배치 키 액세스(Batched Key Access)를 사용하는 것이다.

 

Using filesort

정렬이 필요한 데이터를 메모리에 올리고, 정렬을 수행한다.

ORDER BY 인덱스로 해결 못하고, 추가적인 정렬을 수행한 것을 의미한다.

 

Using join buffer

JOIN 수행을 위해 중간 데이터를 저장하는 Buffer를 사용한다.

 

Using union/Using intersect/Using sort_union

type 항목에서의 index_merge 유형의 예시처럼, 여러 인덱스가 병합되어 실행되는 경우, 이에 대한 상세 정보를 이 값들을 통해서 표기한다.

union은 인덱스들을 합집합처럼 모두 결합해 접근하며(보통 OR), intersect는 인덱스들을 교집합처럼 추출(AND), sort_union은 Using when과 유사하지만, 이 때 OR이 동등 조건이 아닌 경우에 표기된다.

 

Not exists

하나의 행을 찾으면 더 이상 찾아도 되지 않는 경우를 의미한다.

LEFT JOIN, RIGHT JOIN에서의 상황을 고려해보자.

 

 

Reference

업무에 바로쓰는 SQL 튜닝

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