EXPLAIN이 뭔지 설명해줘!

옵티마이저... 옵티마이저를 보자!

MYSQL엔진의 구조

요청이 들어온 SQL문에 대해 파서가 문법의 오류를 검사하고 그 이후 옵티마이저가 쿼리 트리를 바탕으로 실행 계획을 세웁니다.

데이터베이스에서 쿼리의 비용을 계산 할때 보는것이 실행 계획입니다. 실행 계획이 어떻냐에 따라 쿼리의 실행 속도의 차이가 발생합니다.옵티마이저가 만든 실행 계획에 따라 실행 엔진이 스토리지 엔진에 요청하게 됩니다.

 

그래서... EXPAIN이 뭐냐고....

EXPLAIN은 MYSQL에서 쿼리에 대해 옵티마이저가 생성한 실행 계획을 보여주는 명령문 입니다.

EXPLAIN
select * from 사원;

다음과 같은 명령으로 쿼리를 실행 한다면 실행 계획에 대한 테이블을 보실 수 있을 것 입니다.

 

EXPAIN의 결과 테이블은 어떻게 해석해?

이제부터 EXPAIN 테이블의 컬럼과 값을 하나씩 분석 해 봅시다!

 

 

id

실행 순서를 표시하는 숫자입니다. 위 이미지에서 id가 1이므로 첫번째로 실행된다는 의미입니다.

숫자가 작을수록 먼저 실행되며 조인 할때는 같은 숫자로 표시되게 됩니다.

 

select_type

각 단위 select 문이 어떤 타입의 쿼리인지를 표현합니다.select문이 단순히 FROM 절에 위치한 것인지, 서브쿼리인지, UNION절로 묶인 SELECT문인지 등의 정보를 제공합니다.

 

SIMPLE

UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 SIMPLE로 표시된다.

EX)  SELECT * FROM 사원;

 

PRIMARY

서브쿼리가 포함된 SQL문이 있을 때 첫 번째 SELECT 문에 해당하는 구문에 표시되는 유형

EX) SELECT * ,(SELECT MAX(연봉) FROM 급여) AS 급여 FROM 사원;

 

FROM절의 사원 테이블에 대해 PRIMARY 가 표시된걸 보실 수 있습니다.

 

SUBQUERY

독립적으로 수행되는 서브쿼리를 의미합니다.SELECT절의 스칼라 서브쿼리와 WHERE절의 중첩 서브쿼리에 해당

위의 예시에서 보실 수 있습니다.

 

DERIVED

FROM절에 작성된 서브쿼리라는 의미입니다.임시 파생 테이블 이라고도 합니다.

 

 

UNION

UNION 및 UNION ALL 구문으로 합쳐진 SELECT 문에서 첫번째 SELECT 구문을 제외한 SELECT문

 

UNION RESULT

UNION이 아닌 UNION ALL을 통해 SELECT 절을 결합 했을때 출력.

UNION은 중복이 없는 특징을 가지기 때문에 임시 메모리에 불러와서 중복 체크를 하는 과정을 거칩니다.

이때 임시 테이블로 가져와 중복을 없애겠다는 의미로 UNION RESULT가 표시됩니다.

 

DEPENDENT SUBQUERY

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우

서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우를 DEPENDENT SUBQUERY라고 한다.

DEPENDENT UNION

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우

UNION으로 연결된 단위 쿼리중 첫 번째 단위 쿼리를 제외하고 두 번째 단위 쿼리에 해당하는 경우

 

UNCACHEABLE SUBQUERY

메모리에 상주하여 재활용 되어야 할 서브쿼리가 재사용 되지 못할 때 출력

사용자 정의 함수나 RAND(),UUID() 등

 

MATERIALIZED

IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤,조인이나 가공 작업을 수행 할 때 출력

 

 

---

table

테이블 명을 표시하는 항목, 임시테이블이 생성되는 경우 <> 형식으로 출력

 

partitions

데이터가 저장된 논리적인 영역

---

type

테이블의 데이터를 어떻게 찾을 것인지에 관한 정보를 제공하는 항목

system

- 테이블의 데이터가 없거나 한 개만 있는 경우 성능상 최상의 타입

const

- 조회되는 데이터가 단 1건일때 출력되는 유형 성능상 매우 유리

eq_ref

조인이 수행될 때 드리븐 테이블의 데이터에 접근하여 고유 인덱스 또는 기본키로 단 1건의 데이터를 조회하는 방식

 

ref

조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상인 경우

 

ref_or_null

IS NULL 구문에 대해 인덱스를 활용하도록 최적화된 방식

 

range

테이블 내의 연속된 데이터를 조회하는 유형

 

index_merge

특정 테이블에 생성된 두개 이상의 인덱스가 병합되어 동시에 사용

 

index

인덱스 풀 스캔을 의미

 

table

테이블을 처음부터 끝까지 읽는 테이블 풀 스캔 방식

---

possible_keys

옵티마이저가 SQL문을 최적화 하고자 사용할 수 있는 인덱스 목록

---

key

옵티마이저가 SQL문을 최적화 하고자 사용한 기본 키 또는 인덱스 명

 

---

key_len

사용한 인덱스의 바이트수

---

ref

테이블 조인을 수행 할 때 어떤 조건으로 해당 테이블에 액세스 되었는지 알려주는 정보

---

rows

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

---

extra

sql 문을 어떻게 수행할 것인지에 관한 추가 정보

 

Distinct	중복이 제거되어 유일한 값을 찾을때
using where	where절을 사용해 필터링
using temporary	임시테이블을 생성하겠다는 의미,(distinct,group by, order by)
using index	데이터 파일을 읽지 않고 인덱스만 읽는 경우
using filesort	정렬이 필요한 데이터를 메모리에 올리고 정렬 수행
using join buffer	조인을 수행하기위해 중간 데이터를 저장하는 조인 버퍼 사용

 

 

참고

업무에 바로 쓰는 SQL 튜닝 -양바른 지음