[MYSQL] 실행 계획 수행 - table, partitions, type

기본 실행 계획 항목 분석

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, extra

 

table

테이블명을 표시하는 항목, 실행 계획 정보에 테이블명이나 테이블 별칭(alias)를 출력

서브쿼리나 임시 테이블을 만들어서 별도의 작업을 수행할 때는 <subquery#> <derived#>라고 출력

EXPLAIN
SELECT 사원.사원번호, 급여.연봉
	FROM 사원,
		(SELECT 사원번호, MAX(연봉) as 연봉
		FROM 급여
		WHERE 사원번호 BETWEEN 10001 AND 20000 GROUP BY 사원번호) as 급여
WHERE 사원.사원번호 = 급여.사원번호;

첫번 째 행의 table 열 : <derived2>, id = 1

두번 째 행 : id = 1

<derived2> 테이블과 사원 테이블이 JOIN 해석 가능

<derived2>는 id = 2 인 테이블이라는 뜻 → 급여 테이블을 의미, FROM 절의 서브쿼리 구문으로 작성된 급여 테이블과 사원 테이블이 JOIN

 

결과

---

partitions

실행 계획의 부가 정보, 데이터가 저장된 논리적인 영역을 표시하는 항목

사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리

너무 많은 영역의 파티션에 접근하는 것으로 출력 → 파티션 정의 튜닝

---

type

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

테이블을 처음부터 끝까지 전부 확인 or 인덱스를 통해 데이터를 찾아갈지 등을 해석

 

| system |

테이블에 데이터가 없거나 한 개만 있는 경우, 성능상 최상의 type

/* 테이블 생성 */
CREATE TABLE myisam_테이블 (
col1 INT(11) NULL DEFAULT NULL) ENGINE=MYISAM;

/* 데이터 추가 */
INSERT INTO myisam_테이블 VALUES(1);

/* 실행 계획 */
EXPLAIN SELECT * FROM myisam_테이블;

 

결과

 

| const |

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

고유 인덱스나 기본 키를 사용하여 단 1건의 데이터만 접근하면 되므로, 속도나 리소스 사용 측면에서 지향해야할 타입

EXPLAIN
SELECT *
	FROM 사원
WHERE 사원번호 = 10001;

 

결과

 

| eq_ref |

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

→ JOIN이 수행될 때 가장 유리한 경우(드라이빙 테이블과의 JOIN 키가 드리븐 테이블에 유일)

EXPLAIN
SELECT 매핑.사원번호, 부서.부서번호, 부서.부서명
	FROM 부서사원_매핑 as 매핑,
	부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.사원번호 BETWEEN 100001 AND 100010;

Id = 1 동일 → 부서사원_매핑(as 매핑) 테이블과 부서 테이블이 JOIN 수행

부서사원_매핑(as 매핑) 테이블 : 드라이빙 테이블

부서 테이블 : 드리븐 테이블

type : eq_ref → JOIN 수행 시 기본 키나 고유 인덱스를 활용하여 1건의 데이터씩만 검색

 

결과

 

| ref |

eq_ref 유형과 유사한 방식, JOIN을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우를 의미

드라이빙 테이블과 드리븐 테이블 JOIN 수행 : 일대다 관계

드라이빙 테이블 1개 값이 드리븐 테이블에서는 2개 이상의 데이터 존재

기본 키나 고유 인덱스를 활용하면 2개 이상의 데이터가 검색되거나 유일성이 없는 비고유 인덱스를 사용

드리븐 테이블의 데이터양이 많을 때 접근해야할 데이터 범위가 넓어져 성능 저하의 원인 파악 필요

=, <, > 등의 연산자를 사용하여 생성된 열을 비교할 때 출력

EXPLAIN
SELECT 사원.사원번호, 직급.직급명
	FROM 사원,
	직급
WHERE 사원.사원번호 = 직급.사원번호
AND 사원.사원번호 BETWEEN 10001 AND 10100;

1 건의 사원 데이터 대비 여러 개의 직급 데이터 조회될 수 있는 구조

사원 테이블(드라이빙 테이블)의 사원번호를 조인 키로 직급 테이블 데이터에 접근 → 하나의 사원번호당 다수의 직급 데이터 조회 예측

 

결과

 

| ref_or_null |

ref 유형과 유사하지만 IS NULL 구문에 대해 인덱스를 활용하도록 최적화된 방식

MYSQL은 NULL에 대해서도 인덱스를 활용하여 검색 가능 → NULL 은 가장 앞쪽에 정렬

테이블에서 검색할 NULL 데이터양이 적다면 ref_or_null 방식 활용 (효율적인 SQL문)

NULL 데이터양이 많다면 SQL 튜닝 대상

 

- NULL 또는 A 문자 데이터 있는지 검사

- 출입문 열 : 인덱스_출입문 (인덱스 생성)

- 출입문 IS NULL 조건문 → 인덱스 활용해서 데이터를 검색하는 최적화된 방식

EXPLAIN
SELECT *
	FROM 사원출입기록
WHERE 출입문 IS NULL OR 출입문 = 'A';

 

결과

 

| range |

테이블 내의 연속된 데이터 범위를 조회하는 유형, =, < >, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN

연산을 통해 범위 스캔을 수행하는 방식

주어진 데이터 범위 내에서 행 단위로 스캔을 하지만 스캔할 범위가 넓으면 성능 저하의 요인이 될 수 있음 - SQL 튜닝 대상

EXPLAIN
SELECT *
	FROM 사원
WHERE 사원번호 BETWEEN 10001 AND 100000;

 

결과

 

| fulltext |

텍스트 검색을 빠르게 처리하기 위해 전문 인덱스(full text index)를 사용하여 데이터에 접근하는 방식

 

| index_merge |

결합된 인덱스들이 동시에 사용되는 유형, 특정 테이블에 생성된 두 개 이상의 인덱스가 병합되어 동시에 적용

전문 인덱스는 제외

 

사원 테이블의 입사일자 열에 대한 인덱스_입사일자 인덱스와 사원번호 열로 구성된 기본 키 모두를 통합해서 사용

EXPLAIN
SELECT * FROM 사원
	WHERE 사원번호 BETWEEN 10001 AND 100000
	AND 입사일자 = '1985-11-21';

 

결과

 

| index |

type 항목의 index 유형은 인덱스 풀 스캔을 의미, 인덱스 블록을 처음부터 끝까지 훑는 방식

데이터를 스캔하는 대상이 인덱스라는 점이 다를 뿐, ALL 유형(테이블 풀 스캔 방식)과 유사

인덱스는 보통 테이블보다 크기가 작으므로 테이블 풀 스캔 방식보다는 빠를 가능성이 있음

EXPLAIN
SELECT 사원번호
	FROM 직급
WHERE 직급명 = 'Manager';

 

결과

 

| ALL |

테이블을 처음부터 끝까지 읽는 테이블 풀 스캔 방식에 해당되는 유형

ALL 유형은 활용할 수 있는 인덱스가 없거나, 인덱스를 활용하는 게 오히려 비효율적이라고 옵티마이저가 판단했을 때 선택

인덱스를 새로 추가하거나 기존 인덱스를 변경하여 인덱스를 활용하는 방식을 SQL 튜닝을 할 수 있으나, 전체 테이블 중 10~20% 이상 분량의 데이터를 조회할 때는 ALL 유형이 오히려 성능상 유리할 수 있음

EXPLAIN
SELECT * FROM 사원;

 

결과

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 3장 SQL 튜닝의 실행 계획 파헤치기