SQL explain 항목 정리

Slow 쿼리나 실제 실행되는 쿼리가 인덱스를 제대로 실행되는지 확인할 때 실행 계획(Explain) 사용한다.
실행 계획을 볼 때 각 항목들(select_type, type, key, ref, filtered, extra) 보게 되는데 이 항목들을 읽을 수 있는 배경지식이 필요하다.하지만 실행 계획을 읽을 때마다 “이 항목은 어떤 의미였지? 이 항목에 대한 타입은 무슨 의미였지?” 까먹거나 정확히 확인하기 위해서 구글링 하는 경우가 많았다. 그래서 이번에 구체적으로 정리하기로 마음 먹었다.

실행계획 조회 예시

 

 

1. Explain 항목

---

[ Select Type ]

쿼리에서 사용된 SELECT 문의 종류를 알려준다.

Select Type 명	설명
SIMPLE	가장 일반적인 SELECT 문이며, 복잡한 UNION이나 서브쿼리가 없는 경우에 해당. 단순히 테이블에서 데이터를 가져와 결과를 반환 하므로 가장 빠르다.
PRIMARY	복잡한 쿼리에서의 최상위 쿼리문인 경우
SUBQUERY	SELECT 문 안에서 서브쿼리를 사용한 경우
DEPENDENT SUBQUERY	서브쿼리가 외부 쿼리로부터 값을 참조하여 실행하는 경우
DERIVED	FROM 절에서 쓰인 서브쿼리 (인라인 뷰)
UNION	UNION 또는 UNION ALL을 사용한 경우
DEPENDENT UNION	UNION의 서브쿼리가 외부 쿼리로부터의 값을 참조하여 실행하는 경우
UNION RESULT	UNION의 결과로 생성된 임시 테이블에 대한 SELECT문을 실행 하는 경우

UNION은 둘 이상의 SELECT 문 결과를 결합하는 데 사용된다.
DEPENDENT UNION과 UNION RESULT은 쿼리가 독립적으로 실행되지 못하고 메인 테이블로부터 매번 값을 하나씩 공급받는 구조이기 때문에(앞에 실행된 SELECT 문 의 결과가 다음에 영향을 미치기 때문에) 성능적으로 좋지 않아 튜닝 대상으로 식별해야 한다.

 

 

[ Type ]

테이블에서 행을 조회하는 방식을 표현한 것으로 테이블에 접근 방식이 효율적인지를 판단하는 근거가 된다.
아래로 갈수록 성능이 더 좋지 않다고 보면 된다.

Type 명	설명
system	테이블에 단 한 개의 행(row)만 있는 경우
const	테이블의 Unique Key 혹은 Primary Key를 상수로 조회하는 경우 (한 건만 존재)
eq_ref	다른 테이블과 조인 시 Primary Key를 이용 (한 건 조회)
ref	다른테이블과 조인시 인덱스에 설정된 모든 칼럼을 조회 조건으로 사용 (사용되는 키 조건이 몇 개의 행에 일치하는 경우, 최소 두개 이상)
fulltext	fulltext 인덱스를 사용하여 수행
ref_or_null	ref와 동일하지만 null 값이 포함됨 MySQL의 경우 NULL에 대해서도 인덱스를 활용할 수 있고 이때 NULL은 가장 앞쪽에 정렬된다.
index_merge	단일 테이블의 여러 범위의 행을 스캔한 결과를 하나로 병합
unique_subquery	IN 절의 서브쿼리에 eq_req 적용
index_subquery	IN 절의 서브쿼리에 PK 가 아닌 인덱스 적용
range	인덱스의 지정 범위 안의 행을 검색 (BETWEEN, LIKE, IN, <, >, <=, >=...등)
index	단일 인덱스의 일부만 충족할 경우(테이블 풀 스캔보다는 유리하지만) 적용된다. 물리적인 인덱스 블록을 처음부터 끝까지 탐색한다.
all	테이블 풀스캔. 테이블의 처음부터 끝까지 탐색한다. 전체 테이블중 10~20% 정도를 조회할 때는 오히려 유리할 때도 있다.

possible_key
- 옵티마이저가 SQL을 튜닝하기 위한 후보 인덱스를 보여 준다. (실제 사용한 인덱스 키 아님) 

key
- SQL문에 사용한 인덱스 키를 의미한다. 어떤 인덱스를 사용했는지 확인 하고 다른 인덱스를 유도하거나, 사용되지 않았다면 인덱스를 선택하도록 튜닝 할 수 있다. 

key_len
- 인덱스의 바이트 byte를 의미한다. int의 경우 4바이트, varchar의 경우 단위당 3바이트이다. 

ref
- 테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 조인한 건지 알려준다. 

rows
- SQL문이 접근한 모든 데이터 행수를 예측해서 나타내는 정보이다. (수치가 완전 정확한건 아니다) 

filtered
- SQL을 통해 가져온 대상 데이터가 필터 조건에 의해 어느 정도 제거 된 것인지를 %로 알려준다. 

 

 

[ Extra ]

SQL문을 어떻게 수행할 것인지 나타낸다. 보통 Using temporary, Using filesort 정도가 튜닝 대상으로 알려져 있다. 
아래로 갈수록 성능이 더 좋지 않다고 보면 된다.

Extra 명	설명
Using index	인덱스를 사용하여 쿼리를 처리하는 것. 일반적으로 인덱스를 사용하는 게 좋지만, 일부는 사용하지 않는 것이 더 효율적일 수 있다. 예를 들어, 인덱스를 사용하는데도 대량의 데이터를 검색해야 할 때는 테이블 스캔을 피하기 위해 인덱스를 조정할 필요가 있다. 물리적으로 인덱스의 사이즈가 작고 정렬이 되어 있다면 성능상으로 효율적이라고 판단할 수있다.
Using index for group-by	Group By나 Distinct 구문으로 그룹화 작업이 수행될 때 인덱스를 사용하는 것. 그룹화 작업을 최적화하거나 인덱스를 활용하여 성능을 개선할 수 있는지 확인해야 한다.
Using where Using index condition	쿼리에서 WHERE 절을 처리하는 방식을 나타내며, 필요한 경우 WHERE 절 조건 항목들을 최적화하여 인덱스를 효과적으로 사용하도록 변경하거나, 불필요한 조건을 줄이는 등의 작업을 고려해야 한다.
Using sort_union()	병합 작업에서 정렬을 수행하는 것을 나타낸다. 성능상 문제가 될 여 지가 있다면 정렬을 최적화하여 성능을 향상시켜야 한다.
Using join buffer (Block Nested Loop, Batched Key Access)	조인 수행 중 중간 데이터 결과를 저장하는 조인버퍼를 사용한다는 의미이다. 성능을 개선하기 위해 조인 버퍼 크기를 조정하거나 인덱스를 사용하여 조인 작업을 최적화 해야 할수있다.
Using join buffer (Block Nested Loop)	조인 수행 중 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미이다. 성능을 향상시키기 위해 조인 버퍼의 크기나 조인 방식을 조정할필요가생길수있다.
Using temporary	데이터 중간 결과를 저장하고자 임시 테이블을 사용하는 것. 임시 테이블은 메모리나 디스크에 생성되어 쿼리 결과를 저장하는 용도로 사 용된다. 임시 테이블을 사용하는 것은 성능에 영향을 줄 수 있으므로, 쿼리를 재작성하거나 인덱스를 추가하여 임시 테이블의 필요성을 줄 일 수 있다. 보통 Distinct, Group By, Order By 구문 등이 포함된 경우 출력된다.
Using filesort	정렬 작업을 위해 파일 정렬을 수행하는 것.이미 정렬된 인덱스를 사용하면 추가적인 정렬 작업이 필요 없지만, 인덱스를 사용하지 못할 경우 정렬을 위해 메모리에 올리므로 성능에 부담을 줄 수 있는 작업이다. 정렬이 필요한 쿼리의 경우 인덱스를 올바르게 사용(이미 정렬 된 인덱스를 생성)하거나 정렬을 애플리케이션에서 최적화하는 등의 방법을 고려해야 한다.

 

정리하자면 select_type 기준으로는 SIMPLE, PRIMARY, DERIVED 성능이 좋고 
type 기준으로는 system, const, eq_ref 성능이 좋고
extra의 경우는 Using index가 성능이 좋으므로 나머지 사항에 대한 실행 계획들을 튜닝 대상 후보로 넣고 속도를 측정해 보아야 한다. 

 

 

[ 참고 ]
개발자 기술 면접 노트 책 - https://www.aladin.co.kr/m/mproduct.aspx?ItemId=336470810