SQL EXPLAIN 정리

1. SQL EXPLAIN 정리

1.1 부서 테이블

select * 
  from dbadev.dept
;

부서테이블에는 4개의 행 ( 4rows returned )

1.2 직원 테이블

select * 
  from dbadev.emp
;

직원테이블에는 14개의 행 ( 14rows returned )

1.3 부서와 직원 테이블을 내부조인

SELECT a.deptno
     , a.dname
     , a.loc
     , b.empno
     , b.ename
     , b.job
  FROM dbadev.dept as a
 INNER JOIN dbadev.emp as b
    ON a.deptno = b.deptno
 WHERE a.deptno= '20'
;

1.4 EXPLAIN을 사용한 부서테이블과 직원테이블 내부조인 쿼리 실행결과

EXPLAIN
SELECT a.deptno
     , a.dname
     , a.loc
     , b.empno
     , b.ename
     , b.job
  FROM dbadev.dept as a
 INNER JOIN dbadev.emp as b
    ON a.deptno = b.deptno
 WHERE a.deptno= '20'
;

MySQL 실행 계획 항목 설명

1. id

쿼리의 실행 순서대로 1부터 순차적으로 값을 부여. 즉 쿼리의 실행 순서라고 봐도 무방함.

다만 join 의 경우엔 하나의 구문에서 두개 이상의 테이블을 참조하기때문에 모든 테이블에 같은 순번이 부여됨.

2. select_type

select 구문의 실행 타입.

속성값	내용
SIMPLE	단순 select 구문으로 별다른 조인이나 서브쿼리가 없음.
PRIMARY	서브쿼리를 이용할 경우 서브쿼리의 부모가 되는 select 쿼리
	union을 사용할 경우 union 의 첫번째 select 쿼리.
UNION	union을 사용한 쿼리에서 첫번째를 제외한 나머지 select 쿼리.
DEPENDENT UNION	UNION과 기본적으로 동일하나 외부쿼리에 영향을 받음
UNION RESULT	UNION 쿼리의 결과
UNCACHEABLE UNION	UNION과 기본적으로 동일하나 공급되는 모든 값에 대해 UNION 쿼리를 재처리
SUBQUERY	서브쿼리 또는 서브쿼리를 구성하는 첫번째 select 구문
DEPENDENT SUBQUERY	SUBQUERY와 기본적으로 동일하나 외부쿼리에 영향을 받음
UNCACHEABLE SUBQUERY	SUBQUERY와 기본적으로 동일하나 입력 값에 의한 캐싱을 이용할 수 없음

3. table

테이블명. 약칭(Alias)을 사용할 경우 약칭이 표시됨

4. type

단일 테이블만 사용된 쿼리일 경우 : 테이블 access 형태

두 개 이상의 테이블이 조인된 SQL 일 경우 : 테이블 간의 조인 형태

아래 목록의 순서는 성능이 좋은 것 부터 나열되어 있음 

속성값	내용
sytem	테이블에 row가 1건이라 매칭되는 row도 1건인 경우.
const	옵티마이저가 unique/primary key를 사용하여 매칭되는 row가 1건인 경우.
eq_ref	1:1의 join 관계
	unique/primary key를 사용하여 join을 처리함.
ref	1:n의 join 관계
	non-unique 인덱스가 사용되거나, 복합키로 구성된 인덱스 중,   일부 컬럼만 이용하여 조인될 경우
ref_or_null	ref와 동일하나 null 값에 대한 최적화가 되어있음.
fulltext	fulltext 인덱스를 사용
index_merge	동일한 테이블에서 두개 이상의 인덱스가 동시에 사용됨.(fulltext 인덱스는 제외)
unique_subquery	서브쿼리에서 unique한 값이 생성되는 경우
	index lookup function이 사용됨.(서브쿼리 최적화)
index_subquery	unique_subquery와 비슷하나 결과값이 unique하지 않은 경우
range	주어진 범위내의 row를 스캔함
	범위내의 row가 많으면 많을수록 성능이 저하됨
index	인덱스를 사용하긴 하나 전체 인덱스 block을 스캔함.  즉 인덱스를 사용하긴 하나 all 타입과 흡사함
all	전체 데이터 block을 스캔.(full table scan)

5. possible_keys

옵티마이저가 쿼리 처리를 위해 고려한 인덱스 후보. 즉 사용가능한 인덱스들의 리스트.

possible_keys와 key값은 항상 같지 않다. 즉 옵티마이저가 인덱스를 고려했지만 사용하지 않을수도 있음.

6. key

옵티마이저가 실제로 사용한 인덱스 키.

type값이 index_merge 일때 key값은 사용된 모든 인덱스 키를 출력함.

7. key_len

옵티마이저가 사용한 인덱스 키의 길이값. key컬럼에서 인덱스가 언급되지 않았다면 null값.

key_len값으로 옵티마이저가 실제 복수 컬럼키중 얼마나 많은 부분을 사용할 것인지 알수 있다.

8. ref

행을 추출하는데 키와 함께 사용된 컬럼이나 상수값.

9. rows

쿼리를 수행하기 위해 검색해야 할 row의 개수. 인덱스와 조건을 최적화 해서 row의 개수를 줄이면 줄일수록 퍼포먼스가 향상됨.

10. extra

옵티마이저가 쿼리를 해석한 추가적인 정보를 출력함

위쪽에 정리된 표를 바탕으로 EXPLAIN 실행결과

1번째 row를 해석하면

id가 1 (쿼리의 실행 순서가 1번째임)

SIMPLE (단순 select 구문)

테이블명은 dept 테이블을 alias로 명시한 a로 표시

const (옵티마이저가 unique/primary key를 사용하여 매칭되는 row가 1건인 경우 표시됨, a.deptno = 20으로 명시했으므로)

옵티마이저가 쿼리 처리를 위해 고려한 인덱스 후보는 PRIMARY

옵티마이저가 실제로 사용한 인덱스 키 PRIMARY

옵티마이저가 사용한 인덱스 키의 길이값 4

행을 추출하는데 키와 함께 사용된 컬럼이나 상수 4

쿼리를 수행하기 위해 검색해야 할 row의 개수 1

2번째 row를 해석하면

id가 1 (쿼리의 실행 순서가 1번째임)

SIMPLE (단순 select 구문)

테이블명은 emp 테이블을 alias로 명시한 b로 표시

ALL 전체 데이터 block을 스캔.(full table scan)

쿼리를 수행하기 위해 검색해야 할 row의 개수 14

Using where : where절이 다음 조인에 사용될 row나 출력될 row의 개수를 제한하는 경우 나온다.