[MYSQL] 실행 계획 수행 - id, select_type

기본 실행 계획 수행

실행계획 : SQL문으로 요청한 데이터를 어떻게 불러올 것인지에 관한 계획, 즉 경로를 의미

실행 계획 키워드 : EXPLAIN, DESCRIBE, DESC

EXPLAIN SQL 문;
DESCRIBE SQL 문;
DESC SQL 문;

 

MYSQL의  실행 계획 수행

SQL문 앞에 EXPLAIN 키워드를 입력하고 실행하면 옵티마이저가 만든 실행 계획 출력

DESC SELECT * FROM 사원 
	WHERE 사원번호 BETWEEN 100001 AND 200000;

결과

 

기본 실행 계획 항목 분석

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

 

id

실행 순서를 표시하는 숫자, SQL문이 수행되는 차례를 ID로 표기

JOIN할 때는 똑같은 id 표시, 즉 id의 숫자가 작을수록 먼저 수행된 것이고 id가 같은 값이라면 두 개 테이블의 JOIN이 이루어졌다고 해석할 수 있음

EXPLAIN 
SELECT 사원.사원번호, 사원.이름, 사원.성, 급여.연봉,
	(SELECT MAX(부서번호)
	FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
	FROM 사원, 급여
	WHERE 사원.사원번호 = 10001 AND 사원.사원번호 = 급여.사원번호

결과

첫 번째 행, 두번째 행의 id 값이 1로 똑같음 → 처음 JOIN 발생

세번째 행의 id 값 = 2 → id가 1인 첫번째 행과 두번째 행의 JOIN이 이루어진 뒤에 세번째 행이 수행

 

select_type

SQL문을 구성하는 SELECT 문의 유형을 출력하는 항목

SELECT 문이 FROM 절에 위치, 서브쿼리, UNION 절로 묶인 SELECT 문인지 등의 정보 제공

 

| SIMPLE |

UNION이나 내부 쿼리가 없는 SELECT 문이라는 것을 의미하는 유형 (단순한 SELECT 구문으로만 작성된 경우)

EXPLAIN 
SELECT * FROM 사원 WHERE 사원번호 = 100000

결과

 

EXPLAIN 
SELECT 사원.사원번호, 사원.이름, 사원.성, 급여.연봉
	FROM 사원,
	(SELECT 사원번호, 연봉
	FROM 급여
	WHERE 연봉 > 80000) AS 급여
WHERE 사원.사원번호 = 급여.사원번호 AND 사원.사원번호 BETWEEN 10001 ANd 10010;

결과

 

| PRIMARY |

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

서브쿼리를 감싸는 외부 쿼리이거나, UNION이 포함된 SQL문에서 첫번째로 SELECT 키워드 작성된 구문에 표시

 

1. 외부 쿼리의 사원 테이블에 우선적으로 접근한다는 의미로 PRIMARY 출력

EXPLAIN 
SELECT 사원.사원번호, 사원.이름, 사원.성,
	(SELECT MAX(부서번호)
	FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
	FROM 사원
	WHERE 사원.사원번호 = 100001;

결과

2. UNION ALL 구문으로 통합된 SQL문에서 처음 SELECT 구문이 작성된 쿼리가 먼저 접근한다는 의미로 PRIMARY 출력

EXPLAIN 
SELECT 사원1.사원번호, 사원1.이름, 사원1.성
	FROM 사원 as 사원1
WHERE 사원1.사원번호 = 100001

UNION ALL

SELECT 사원1.사원번호, 사원1.이름, 사원1.성
	FROM 사원 as 사원1
WHERE 사원1.사원번호 = 100001;

결과

 

| SUBQUERY |

독립적으로 수행되는 서브쿼리를 의미

메인쿼리의 SELECT 절에 정의된 부서사원_매핑 테이블과 급여 테이블에 대해 옵티마이저가 서브쿼리임을 인지하고 있음

→ SELECT 절의 스칼라 서브쿼리와 WHERE 절의 중첩 서브쿼리일 경우 해당

EXPLAIN
SELECT (SELECT COUNT(*)
	FROM 부서사원_매핑 as 매핑) as 카운트,
	(SELECT MAX(연봉) FROM 급여) as 급여;

결과

 

| DERIVED |

FROM 절에 작성된 서브쿼리라는 의미, 즉 FROM 절의 별도 임시 테이블인 인라인 뷰

메인쿼리의 FROM 절에서 급여 테이블의 데이터를 가져오는 인라인 뷰 실행 계획을 통해 두번째(id가 2인 행)로 수행되고 있다는 것을 알 수 있음

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

결과

 

| UNION |

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

UNION 구문의 첫 번째 SELECT 절은 PRIMARY 유형으로 출력

EXPLAIN
SELECT 'M' as 성별, MAX(입사일자) as 입사일자
	FROM 사원 as 사원1
	WHERE 성별 = 'M'
	
	UNION ALL
	
SELECT 'F' as 성별, MIN(입사일자) as 입사일자
	FROM 사원 as 사원2
	WHERE 성별 = 'F';

결과

 

| UNION RESULT |

UNION ALL이 아닌 UNION 구문으로 SELECT 절을 겹합했을 때 출력

UNION은 출력 결과에 중복이 없는 유일한 속성을 가짐 → 중복 체크 → UNION RESULT는 별도의 메모리 또는 디스크에 임시 테이블을 만들어 중복을 제거하겠다는 의미로 해석 가능

UNION 구문으로 결합되기 전의 각 SELECT 문이 중복되지 않는 결과가 보장될 때는 UNION 구문보다는 UNION ALL 구문으로 변경하는 SQL 튜닝 수행

EXPLAIN
SELECT 사원_통합.*
	FROM (
					SELECT 'M' as 성별, MAX(입사일자) as 입사일자
					FROM 사원 as 사원1
					WHERE 성별 = 'M'
	
					UNION
	
					SELECT 'F' as 성별, MIN(입사일자) as 입사일자
					FROM 사원 as 사원2
					WHERE 성별 = 'F'
				) as 사원_통합;

결과

 

| DEPENDENT SUBQUERY |

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우, UNION으로 연결된 단위 쿼리들 중에서 처음으로 작성한 단위 쿼리에 해당되는 경우

UNION으로 연결되는 첫 번째 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로부터 값을 하나씩 공급받는 구조(AND 사원1.사원번호 = 관리자.사원번호) → 성능적으로 불리하여 SQL문이 튜닝 대상

EXPLAIN
SELECT 관리자.부서번호,
		(SELECT 사원1.이름
		FROM 사원 as 사원1
		WHERE 성별='F' AND 사원1.사원번호 = 관리자.사원번호
		
		UNION ALL
		
		SELECT 사원2.이름
		FROM 사원 as 사원2
		WHERE 성별='M' AND 사원2.사원번호 = 관리자.사원번호
		) AS 이름
FROM 부서관리자 AS 관리자;

결과

 

| DEPENDENT UNION |

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우, UNION으로 연결된 단위 쿼리 중 첫 번째 단위 쿼리를 제외하고 두 번째 단위 쿼리에 해당되는 경우

UNION으로 연결되는 두 번째 이후의 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로부터 값을 하나씩 공급받는 구조(AND 사원2.사원번호 = 관리자.사원번호) → 성능적으로 불리하여 SQL문이 튜닝 대상

 

실행 계획은 DEPENDENT SUBQUERY와 동일

 

결과

 

| UNCACHEABLE SUBQUERY |

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

 

1) 해당 서브쿼리 안에 사용자 정의 함수나 사용자 변수가 포함

2) RAND(), UUID() 함수 등을 사용하여 매번 조회시마다 결과가 달라지는 경우에 해당

 

자주 호출되는 SQL문이라면 메모리에 서브쿼리 결과가 상주할 수 있도록 변경하는 방향으로 SQL 튜닝을 검토

 

RAND() 함수는 0~1 사이의 소수점 기준으로 17자리 숫자를 출력하는 함수

매번 출력값이 달라지는 RAND() 함수 때문에 서브쿼리 (SELECT ROUND (RAND()* 1000000))는 메모리에 캐시되지 못함

EXPLAIN
SELECT * FROM 사원
WHERE 사원번호 = (SELECT ROUND(RAND()*1000000));

 

| MATERIALIZED |

IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, JOIN이나 가공 작업을 수행할 때 출력되는 유형, IN 절의 서브쿼리를 임시테이블로 만들어서 JOIN 작업을 수행

IN 절 구문의 서브쿼리(SELECT 사원번호 FROM 급여 WHERE 시작일자 > '2020-01-01') 가 임시테이블을 생성하고, 이 후 사원 테이블과 JOIN을 수행함

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 IN (SELECT 사원번호 FROM 급여 WHERE 시작일자 > '2020-01-01')

 

* 참고

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