[MySQL] 실행계획 & select_type

Mysql에서는 EXPLAIN으로 실행계획을 확인할 수 있다.

실행계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고 더욱 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

Mysql서버에서 쿼리가 실행되는 과정은 크게 3가지로 본다

1. 사용자로부터 요청된 SQL문장을 잘게 쪼개서 Mysql서버가 이해할 수 있는 수준으로 분리

첫번째 단계를 SQL파싱이라고 하며 Mysql서버의 SQL파서라는 모듈로 처리한다.

SQL문장이 문법적으로 잘못됐다면 이단계에서 걸러진다.

SQL파스트리가 만들어진다.

2. SOL의 파싱 정보(파스트리)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택

대표적으로 아래 4가지를 수행한다.

불필요한 조건의 제거 및 복잡한 연산의 단순화

여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정

각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정

가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야하는지 결정

이 단계가 최적화 및 실행계획 수립단계를 만드는 과정이고 Mysql서버의 옵티마이저에서 처리한다.

3. 두번쨰 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

수립된 실행계획대로 스토리지 엔진에 레코드를 읽어오도록 요청한다.

결국 1,2는 Mysql엔진에서 처리하고 3번은 Mysql엔진과 스토리지 엔진이 동시에 참여하게 된다.

 

옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당하고 있다.

현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화방법을 사용한다. 비용 최적화에서 가장 중요한 것은 통계 정보다.

Mysql에서는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부다.

(오라클의 경우 통계정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계정보만 따로 백업하기도 한다)

* MySQL에서 통계정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다.

레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 ANALYZE 명령을 이용해 강제적으로 통계 정볼르 갱신해야 할때도 있다.

특히 이런 현상은 레코드 건수가 얼마 되지않는 개발용 Mysql서버에서 자주 발생한다.

ANALYZE TABLE tb_test;

ANALYZE동안에 MyISAM 테이블은 읽기는 가능하지만 쓰기가 안된다. 하지만 InnoDB의 경우 테이빌 읽기 쓰기 모두 안된다.

서비스 도중에 ANALYZE을 실행하지 않는 것이 좋다.

---

실행계획분석

아무런 옵션없이 EXPLAIN명령만 사용하면 기본적인 쿼리 실행 계획만 보인다.

하지만 EXPLAIN EXTENDED나 EXPLAIN PARTITIONS명령을 이용해 더 상세한 실행 계획을 확인할 수도있다.

EXPLAIN을 실행하면 쿼리 문장의 특성에 따라 표형태로 된 1줄 이상의 결과가 표시된다.

1. 표의 각 라인은 쿼리 문장에서 사용된 테이블(임시테이블 생성경우 그 임시 테이블까지 포함)개수 만큼 출력된다.

2. 실행 순서는 위에서 아래 순서대로 표시된다.

3. 출력된 실행 계획에서 위쪽에 출력된 결과일수록(id 컬럼이 작을수록) 쿼리의 바깥(Outer)부분이거나 먼저 접근한 테이블이고

아래쪽에 출력된 결과일수록(id 컬럼의 값이 클수록) 쿼리의 안쪽(Inner)부분 또는 나중에 접근한 테이블에 해당한다.

복잡하고 무거운 쿼리인 경우에는 실행 계획의 조회가 느려질 가능성이 있다.

그리고 UPDATE, INSERT, DELETE문장에 대해서는 실행 계획을 확인할 방법이 없다.

(반드시 확인하려면 WHERE 조건절만 같은 SELECT문장을 만들어서 대략적으로 계획을 확인해 볼 수 있다.)

SELECT 키워드 단위로 구분한것을 단위(SELECT) 쿼리라고 한다.

실행계획에서 가장 왼쪽에 표시되는 id칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다.

아래의 예는 실행계획에서 최소 2개의 id값이 표시될 것이다.

SELECT ...

FROM(SELECT .... FROM tb_test1) tb1, tbtest2 tb2

WHERE tb1.id=tb2.id;

만약 하나의 SELECT문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획레코드가 출력되지만, 같은 id가 부여된다.

아래 예

SELECT e.emp_no

FROM employees e, salaries s

WHERE e.emp_no=s.emp_no

LIMIT 10;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	e	index	ix_firstname	44		300585	Using index
1	SIMPLE	s	ref	PRIMARY	4	employees.e.emp_no	4

(id값이 1로 같다)

select_type

각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.

SIMPLE

UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT쿼리인 경우, 해당 쿼리 문장의 select_type은 SIMPLE로 표시된다.(쿼리에 조인이 포함된 경우에도 마찬가지이다)

쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 반드시 하나만 존재한다.

(일반적으로 제일 바깥 SELECT쿼리의 select_type이 SIMPLE로 표시된다)

PRIMARY

UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리는 select_type이 PRIMARY로 표시된다.

SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 SELECT쿼리는 하나만 존재하며 쿼리의 제일 바깥 쪽에 있는 SELECT단위 쿼리가 PRIMARY로 표시된다.

UNION

UNION으로 결합하는 단위 SELECT쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다.

UNION의 첫번쨰 단위 SELECT는 select_type이 UNION이 아니라 UNION쿼리로 결합된 전체 집합의 select_type이 표시된다.

EXPLAIN SELECT * FROM(

(SELECT emp_no FROM employees e1 LIMIT 10)

UNION ALL

(SELECT emp_no FROM employees e2 LIMIT 10)
UNION ALL

(SELECT emp_no FROM employees e3 LIMIT 10)) tb;

UNION이 되는 단위 SELECT 쿼리 3개 중에서 첫 번째(e1테이블)만 UNION이 아니고 나머지 2개는 모두 UNION으로 표시돼있다. 

대신 UNION의 첫번째 쿼리는 전체 UNION의 결과를 대표하는 select_type으로 설정됐다. 여기서는 세개의 서브쿼리로 조회된 결과를 UNION ALL로

결합해 임시 테이블을 만들어서 사용하고 있으므로 UNION ALL의 첫 번째 쿼리는 DERIVED라는 select_type을 갖는 것이다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	PRMIARY	<derived2>	ALL				30
2	DERIVED	e1	index	ix_hire	3		30000	using index
3	UNION	e2	index	ix_hire	3		30000	using index
4	UNION	e3	index	ix_hire	3		30000	using index
	UNION RESULT	<union2,3,4>	ALL

* UNION RESULT

UNION 결과를 담아두는 테이블을 의미한다. Mysql에서는 UNION ALL이나 UNION(DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로

생성하게 된다. 실행계획상에서는 UNION RESULT로 표현된다. 실제 쿼리가 아니므로 별도의 Id값은 없다.

위에 <union 2,3,4>의 의미는 id 2,3,4의 조회결과를 UNION했다는 것이다.

DEPENDENT UNION

select_type에 DEPENDENT 키워드를 포함하는 서브 쿼리는 외부 쿼리에 의존적이므로 절대 외부 쿼리보다 먼저 실행될 수가 없다.

그래서 select_type에 DEPENDENT키워드가 포함된 서버 쿼리는 비효율적인 경우가 많다.

SUBQUERY

일반적으로 서브쿼리라고하면 여러가지를 이야기하지만 여기서 SUBQUERY라고하는 것은 FROM 절 이외에서 사용되는 서브 쿼리만을 의미한다.

EXPLAIN SELECT e.first (SELECT COUNT(*) FROM dept_1, do, dept_2 dt WHERE do.no = dt.no) AS cnt

FROM employees e

WHERE e.no = 1001;

id	select_type	table	type	key	key_len	ref	rows	Extra
1	PRIMARY	e	const	PRIMARY	4	const	1
2	SUBQUERY	dt	index	PRIMARY	16		24	using where
2	SUBQUERY	do	ref	PRIMARY	12	employees.dt.no	10000	using index

Mysql의 실행계획에서 FROM절에 사용된 서브쿼리는 select_type이 DERIVED라고 표시되고, 그 밖의 위치에서 사용된 서브 쿼리는

전부 SUBQUERY라고 표시된다.

* 서브 쿼리는 사용되는 위치에 따라 각각 다른 이름을 지니고 있다.

 - 중첩된 쿼리(Nested Query)

SELECT 되는 칼럼에 사용된 서브 쿼리를 네스티드 쿼리라고 한다.

 - 서브쿼리(Sub Query)

WHERE절에 사용된 경우에는 일반적으로 그냥 서브쿼리라고 한다.

 - 파생 테이블(Derived)

FROM절에 사용된 서브 쿼리를 Mysql에서는 파생 테이블이라고 한다.

또한 서브쿼리가 반환하는 값의 특성에 따라 다음과 같이 구분하기도 한다.

 - 스칼라 서브 쿼리(Scalar SubQuery)

하나의 값만(칼럼이 단 하나의 레코드 1건만)반환하는 쿼리

 - 로우 서브쿼리(Row Sub Query)

칼럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리

DERIVED

서브 쿼리가 FROM절에 사용된 경우 Mysql은 항상 select_type이 DERIVED인 실행 계획을 만든다.

DERIVED는 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.

안타깝게도 Mysql은 FROM 절에 사용된 서브 쿼리를 제대로 최적화하지 못할 떄가 대부분이다.

임시 테이브에는 인덱스가 전혀 없으므로 다른 테이블과 조인할때 성능상 불리할 때가 많다.

EXPLAIN SELECT * FROM (SELECT de.emp_no FROM dept_emp de) tb, employees e 

WHERE e.emp_no = tb.emp_no;

사실 위의 쿼리는 FROM 절의 서브 쿼리를 간단히 제거하고 조인으로 처리할 수 있는 형태이다.

실제로 다른 DBMS에서는 이렇게 쿼리를 재작성하는 형태의 최적화 기능도 제공한다. 

하지만 다음 실행 계획을 보면 알 수 있듯이 Mysql에서는 FROM절의 서브 쿼리를 임시 테이블을로 만들어서 처리한다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL				331603
1	PRIMARY	e	eq_ref	PRIMARY	4	tb.emp_no	1
2	DERIVED	de	index	ix_fromdate	3		334868	Using index

* 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야한다.

이후 조인으로 해결할 수 있는 경우라면 서브쿼리보다 조인을 사용할 것을 강력히 권장한다.

UNCACHEABLE SUBQUERY

UNCACHEABLE UNION