쿼리 튜닝을 위한 EXPLAIN

EXPLAIN

EXPLAIN
SELECT name, desc FROM Post
WHERE
	created_at BETWEEN STR_TO_DATE('1994-01-01' , '%Y-%m-%d') AND STR_TO_DATE('2000-12-31', '%Y-%m-%d')

| id | select_type | table  | partitions | type  | possible_keys  | key           | key_len | ref  | rows | filtered | Extra                 |
| 1 |   SIMPLE     | Post   | NULL       | range | I_created_at   | I_created_at  | 3       | NULL |    1 |   100.00 | Using index condition |

이제 EXPLAIN을 했을 때 나오는 Column들이 의미하는 것을 설명하도록 한다.

항목	설명
id	아이디(id) 로 SELECT를 구분하는 번호이며 실행 순서를 표시하는 숫자.
table	참조하는 테이블 항목
select_type	SQL 문을 구성하는 SELECT 문의 유형을 출력하는 항목
type	테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목. 조인 혹은 조회 타입
possible_keys	데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트. 옵티마이저가 SQL문을 최적화 하고자 사용할 수 있는 인덱스 목록 출력.
key	실제로 사용할 인덱스. 옵티마이저가 SQL문을 최적화하고자 사용한 기본 키(PK) 또는 인덱스 명
key_len	실제로 사용할 인덱스의 길이
ref	reference의 약자로, 테이블 조인시 어떤 조건으로 해당 테이블에 액세스 되었는지 알려주는 정보 Key 안의 인덱스와 비교하는 컬럼(상수)
rows	쿼리 실행 시 조사하는 행 수립
filtered	어느 정도의 비율로 데이터를 제거했는지 의미하는 항목
extra	SQL문을 어떻게 수행할것인지에 대한 추가 정보를 보여주는 항목

select_type

SQL 문을 구성하는 SELECT 문의 유형을 출력하는 항목.
FROM에 위치하는지, 서브쿼리인지, UNION 절로 묶인 SELECT 인지를 나타내며, 종류는 아래 표와 같다.

항목	설명
SIMPLE	Union 이나 Sub Query가 없는 단순한 SELECT문
PRIMARY	서브쿼리가 포함된 SQL문. 가장 밖에 있는 SELECT문 Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫 번째 쿼리), Union을 사용할 경우 Union의 첫 번째 쿼리
UNION	Union 쿼리에서 PRIMARY를 제외한 나머지 SELECT문.UNION 및 UNION ALL 구문으로 합쳐진 SELECT 문에서 첫 번째 SELECT 구문을 제외한 이후의 SELECT 구문
DEPENDENT_UNION	UNION과 동일하나 바깥 쿼리에 의존성을 가진 Union의 SELECT문
UNION_RESULT	Union 쿼리의 결과물.UNION ALL이 아닌 UNION 구문으로 SELECT 절을 결합했을 때 출력
SUBQUERY	독립적으로 수행되는 서브쿼리. 가장 밖에 있는 SELECT문의 Sub Query또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문즉 스칼라 서브쿼리와 WHERE 절의 중첩 서브쿼리일 때 표시된다.
DEPENDENT_SUBQUERY	UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인테이블에 영향을 받는 경우이다. SUBQUERY와 동일하나 바깥 쿼리에 의존성을 가진 Sub Query의 SELECT문
DERIVED	서브쿼리가 재사용되지 못할 때 출력되는 유형 SELECT로 추출된 테이블(FROM 절에서의 Sub Query 또는 Inline View) FROM절의 별도 임시 테이블인 인라인 뷰를 의미
UNCACHEABLE_SUBQUERY	SUBQUERY와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리 바깥 쿼리에서 공급되는 값이 동일하더라도 Cache된 결과를 사용할 수 없음 - 해당 서브쿼리 안에 사용자 정의 함수나 사용자 변수가 포함되거나 - RAND( ), UUID( ) 함수 등을 사용하여 매번 조회 시마다 결과가 달라지는 경우에 해당
UNCACHEABLE_UNION	UNION과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리
METERIALIZED	IN절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, 조인이나 가공 작업을 수행할 때 출력되는 유형 IN 절의 서브쿼리를 임시 테이블로 만들어서 조인작업을 수행하는 것

type

type은 어떤 방식으로 데이터 접근할지 접근 방식을 표시하는 필드.
Optimizer가 join, select시에 어떤 방법으로 row를 조회하는지 나타내는 칼럼.
접근 방식은 대상 테이블로의 접근이 효율적일지 여부를 판단하는 데 아주 중요한 항목.
풀스캔할 지, 인덱스 사용할 지 등등을 알 수 있다.
(상위에 위치한 type일수록 빠르다)

항목	설명
system	테이블에 단 한개의 데이터만 있는 경우. 가장 빠름
const	SELECT에서 기본 키(Primary Key) 혹은 고유 키 (Unique Key)를 이용하여조회하는 경우로 결과는 최대 1건. 성능이 매우 좋다. - 기본 키 또는 고유키에 의한 loockup(등가비교), 조인이 아닌 가장 외부의 테이블에 접근 하는 방식 - 단 기본 키, 고유 키를 사용하고 있으므로 범위 검색으로 지정하는 경우 const가 되지 않음
eq_ref	조인을 할 때 Primary Key를 사용한것. 조인 시 드라이빙 테이블이 드리븐 테이블 접근하여 고유 인덱스나 기본 키를 사용하여 단 1건의 데이터를 조회할 때 표시 - 드라이빙 테이블과의 조인 키가 드리븐 테이블에 유일하므로 조인 수행시 성능상 가장 유리한 경우임
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우. 드리븐 테이블의 데이터 접근 범위가 2개 이상인 경우. - 조인할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우 또는 WHERE 절의 비교 연산자(<, >, =)에 표시된다. - 드리븐 테이블에 접근 데이터가 2개 이상인 경우는 드라이빙-드리븐가 일대다 관계
ref_or_null	ref 와 같지만 null 이 추가되어 검색되는 경우 IS NULL 구문에 대해 인덱스를 활용하도록 최적화된 방식 - ref와 유사하다. SQL 문의 IS NULL 구문을 수행 시 인덱스 활용할 때 표시 - 인덱스 접근 시 맨 앞에 저장되어 있는 NULL의 엔트리를 검색 - 검색할 NULL데이터가 적다면 ref_of_null 방식을 활용했을 때 효율적이지만 많다면 튜닝 대상
index_merge	두 개의 인덱스가 병합되어(동시에 사용되는) 검색이 이루어지는 경우 - 전문 인덱스는 제외된다
unique_subquery	IN 서브쿼리 접근에서 기본 키 또는 고유 키를 사용한다. 이 방식은 쓸데 없는 오버헤드를 줄여 상당히 빠르다. 다음과 같이 IN 절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 - SELECT * FROM tab01 WHERE col01 IN (SELECT Primary Key FROM tab01);
index_subquery	unique_sunquery와 거의 비슷하지만 고유한 인덱스를 사용하지 않는 점이 다르다. 이 접근 방식도 상당히 빠르다 unique_subquery와 비슷하나 Primary Key가 아닌 인덱스인 경우 - SELECT * FROM tab01 WHERE col01 IN (SELECT key01 FROM tab02);
range	특정 범위 내에서 인덱스를 사용하여 범위 비교할 때 사용 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음 - =, <>, >, >=, <, <=, IS NULL , <=>, BETWEEN, IN 연산 등을 통한 범위 스캔 수행 방식 스캔할 범위가 넓으면 성능 저하의 요인이 될 수 있으므로 SQL 튜닝 검토 대상
index	인덱스 풀스캔. 인덱스를 처음부터 끝까지 찾아서 검색하는 경우 ALL 유형의 테이블 풀 스캔 방식과 유사하지만 테이블 풀 스캔보다는 빠르다.
all	테이블 풀스캔. 테이블을 처음부터 끝까지 검색하는 경우 검색하려는 데이터가 전체 데이터의 20% 정도 이상일때는 ALL 성능이 인덱스 조회보다 좋을 수 있습니다.

Extra

옵티마이저가 SQL문을 어떻게 해석하여 수행할것인지에 대한 추가 정보를 보여주는 항목.
옵티마이저의 동작에 대해서 우리에게 알려주는 힌트이며 행동을 파악할때 아주 중요하다.

항목	설명
Using where	WHERE 절의 필터 조건을 사용해 MySQL 엔진으로 가져온 데이터를 추출한 경우 테이블에서 행을 가져온 후 추가적으로 검색조건을 적용해 행의 범위를 축소한 것을 표시한다.
Using index	커버링 인덱스라고도 하며 index only scan이라고도 부른다 - 테이블에는 접근하지 않고 인덱스에서만 접근해서 쿼리를 해결하는 것을 의미한다.
Distinct	중복이 제거되어 유일한 값을 찾을 때 출력되는 정보. 중복 제거가 포함되는 distinct 키워드나 union 구문 포함된 경우 출력
Using index for group-by	Using index와 유사하지만 GROUP BY가 포함되어 있는 쿼리를 커버링 인덱스로 해결할 수 있음을 나타낸다 - 인덱스 루스 스캔일 때 출력
Using filesort	정렬이 필요한 데이터를 메모리에 올리고 작업을 수행하겠다는 의미 ORDER BY 인덱스로 해결하지 못하고, filesort(MySQL의 quick sort)로 행을 정렬한 것을 나타낸다. 추가적인 정렬 작업이므로 인덱스를 활용하도록 SQL 튜닝 검토 대상이 될 수 있다.
Using temporary	암묵적으로 임시 테이블이 생성된 것을 표시한다. 데이터 가져와 저장한 뒤 정렬 작업 수행하거나 중복 제거 작업 수행할 때 표시된다. - 데이터를 가져와 저장한 뒤 정렬을 수행하거나 중복 제거하는 작업 수행 - DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 출력 - 임시 테이블을 메모리에 생성하거나, 메모리 영역을 초과하여 디스크에 임시 테이블을 생성하면 Using Temporary는 성능 저하의 원인이 될 수 있다. - 따라서 Using temporary가 출력되면 SQL 튜닝의 대상이 될 수 있다.
Using where with pushed	다시 말해 distinct, group by, order by 구문이 포함된 경우 표시된다.엔진 컨디션 pushdown 최적화가 일어난 것을 표시한다. 현재는 NDB만 유효
Using index condition	인덱스 컨디션 pushdown(ICP) 최적화가 일어났음을 표시한다. ICP는 멀티 칼럼 인덱스에서 왼쪽부터 순서대로 칼럼을 지정하지 않는 경우에도 인덱스를 이용하는 실행 계획이다. 필터 조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 Mysql 엔 진의 부하를 줄이는 방식. 이는 스토리지 엔진의 데이터 결과를 Mysql 엔진으로 전송하는 데이터양을 줄여 성능 효율을 높일 수 있는 옵티마이저의 최적화 방식
Using MRR	멀티 레인지 리드(MRR) 최적화가 사용되었음을 표시한다.
Using join buffer(Block Nested Loop)	조인에 적절한 인덱스가 없어 중간 데이터 결과를 저장하는 조인 버퍼 사용 의미
Using join buffer(Batched Key Access)	Batched Key Access(BKAJ) 알고리즘을 위한 조인 버퍼를 사용했음을 표시한다.
Not exists	하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력되는 유형 왼쪽 외부 조인 또는 오른쪽 외부 조인에서 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생
Using union / Using intersect / Using sort_union	인덱스가 병합되어 실행되는 경우 어떻게 병합했는지에 대한 상세 정보 출력 - Using union : 인덱스들을 합집합처럼 모두 결합하여 데이터에 접근. 보통 OR 구문 - Using intersect : 인덱스들을 교집합처럼 추출하는 방식. 보통 AND 구문 - Using sort_union : Using union과 유사하지만, OR 구문이 동등 조건이 아닐 때 확인할 수 있는 정보