[MySQL] 실행계획 (Execution Plan) - (1) id, select_type, table

들어가며

 

테스트는 MySQL 5.6.50 버전에서 진행됐다.

 

 

 

 

실행계획이란?

 

쿼리가 실행될 때 DBMS의 옵티마이저가 만드는 실행계획에 따라 실행되며 개발자가 실행계획을 예측하기는 어렵다.

그러므로 개발자는 옵티마이저가 만들어 실행한 실행계획 결과를 조회하고 분석하며 불합리한 내용이 존재하면 이를 개선 시켜 옵티마이저가 더 최적화된 실행계획을 만들어 내도록 유도해야한다.

 

 

 

 

실행계획 분석

 

실행계획 분석은 보통 SELECT 쿼리에 대해 분석하며 INSERT, UPDATE, DELETE 쿼리에 대해서는 실행계획을 분석할 방법이 없다.그래도 INSERT, UPDATE, DELETE 대상이되는 row를 SELECT로 조회하는 부분에 대해 부분적으로 실행계획을 분석하는 방법으로 대략적인 분석은 가능하다.

 

실행계획은 EXPLAIN 키워드로 조회할 수 있다. 아래는 실행계획 조회 쿼리와 출력된 내용이다.

 

EXPLAIN SELECT * FROM table1;

 

실행계획을 정확히 분석하기 위해서 출력되는 내용의 컬럼들의 역할과 케이스별로 컬럼 마다 어떤 값이 조회될 수 있는지 살펴보자.

 

 

 

 

id

 

쿼리 내 참조 되는단위 식별 값을 의미한다. 쿼리내 참조라는 의미는 이후 내용에서 이해할 수있다.

 

1) select 단위로 id 식별 값이 다르게 부여된것을 확인할 수 있다.

 

EXPLAIN 
SELECT * FROM table1 
	UNION 
SELECT * FROM table2;

 

 ※ 실제 쿼리 실행 순서는 위에서 부터 아래로 실행됐다고 생각하면 된다.

 

 

2) 조인시 조인되는 쿼리들은 한 단위로 보며 식별자 값 또한 동일하게 부여된다.

 

EXPLAIN SELECT * FROM table1 INNER JOIN table2;

 

 

 

 

table

 

select 문 실행시 참조되는 테이블명을 나타낸다. 별칭을 줄경우 그 별칭명이 출력된다.

 

 

 

 

select_type

 

1) SIMPLE

 

서브쿼리나 UNION이 없고 내, 외부를 구분할 필요 없는 단순한 select 문을 의미 한다. 

 

 

 

 

2) PRIMARY

 

서브쿼리나 UNION이 존재할 때 가장 외부에 있는 select 문을 의미 한다.

 

EXPLAIN SELECT (SELECT some_column FROM table2 LIMIT 1) FROM table1;

 

table1을 참조해 실행되는 select 문은 가장 외부에 있으므로 PRIMARY 타입인것을 확인할 수 있다.

 

 

 

 

3) SUBQUERY

 

FROM 절 이외에서 사용되는 서브 쿼리임을 뜻한다. 

 

EXPLAIN SELECT (SELECT some_column FROM table2 LIMIT 1) FROM table1;

 

 

4) DEPENDENT SUBQUERY

 

SUBQUERY와 같은 유형이지만 자신 기준 외부 select 문에 의존성을 가진 서브 쿼리를 뜻한다.

 

 

(1)

 

EXPLAIN
SELECT 
    (SELECT c2.some_column FROM table2 AS c2 WHERE c1.some_column = c2.some_column)
FROM table1 AS c1;

 

c2는 select 실행시 외부의 c1의 컬럼을 참조하기 때문에 select_type이 DEPENDENT SUBQUERY임을 확인할 수 있다.

 

 

 

(2)

 

EXPLAIN
SELECT 
    (SELECT 
        (SELECT c3.some_column FROM table2 AS c3 WHERE c3.some_column = c1.some_column)
     FROM table1 AS c2) 
FROM table1 AS c1;

 

c3는 select 실행시 외부의 c1의 컬럼을 참조하고 c2는 c3의 결과를 그대로 SELECT 절에서 사용하기 때문에

c3, c2의 select_type는 DEPENDENT SUBQUERY임을 확인할 수 있다.

 

 

 

(3)

 

EXPLAIN
SELECT 
    (SELECT 
        (SELECT c3.some_column FROM table2 AS c3) 
    FROM table1 AS c2 WHERE c1.some_column = c2.some_column) 
FROM table1 AS c1;

 

c2는 select 실행시 외부의 c1의 컬럼을 참조하고 c3는 참조하는 외부 컬럼이 없으므로

c2는 select_type이 DEPENDENT SUBQUERY, c3는 SUBQUERY임을 확인할 수 있다.

 

 

 

(4)

 

EXPLAIN
SELECT 
    (SELECT
         (SELECT c3.some_column FROM table2 AS c3 where c3.some_column = c2.some_column)
     FROM table1 AS c2) 
FROM table1 AS c1;

 

c3는 select 실행시 외부의 c2의 컬럼만을 참조하고 c2는 외부의 컬럼을 참조하지 않고 있으므로

c3는 select_type이 DEPENDENT SUBQUERY, c2는 SUBQUERY임을 확인할 수 있다.

 

 

 

 

 

5) DERIVED

 

FROM 절에 사용되는 서브 쿼리를 뜻한다.

 

EXPLAIN SELECT * FROM (SELECT * FROM table1) AS c1;

 

FROM 절의 서브 쿼리로 사용된 select 문 정보가 출력되는 select_type이 DERIVED임을 확인할 수 있다.

 

'<>' 는 임시 테이블을 의미하며 '<>' 안에 표시되는 문자 + 숫자는 select_type + id를 의미한다. 즉, id 값 1을 가지는 select 문은 id값 2를 통해 파생되는 임시테이블 참조한다.

 

 

 

 

6) UNION

 

UNION DISTINCT 또는 UNION ALL 을 통해 결합되는 select 문 단위 가운데 첫 번째를 제외한 두 번째 부터 select 문의 select_type은 UNION 으로 표시된다. 첫 번째 select 쿼리는 UNION이 아니라 UNION으로 결합된 전체 집합의 select_type이 표시된다.

 

UNION는 UNION DISTINCT와 동일하다.

 

EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2;

 

 

 

 

7) UNION RESULT

 

UNION의 결과를 임시로 담아두는 테이블을 의미한다. UNION RESULT는 실제 쿼리 실행시 쿼리 단위로 구분되지 않기 때문에 id값은 부여되지 않는다.

 

EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2;

 

<union1,2>은 id 값 1을 가지는 select 결과와 id 값 2를 가지는 select 결과를 UNION 했다는 것을 의미한다.

 

 

 

 

8) DEPENDENT UNION

 

외부 쿼리 테이블의 컬럼을 참조하는 서브쿼리들이 UNION으로 결합되어있을경우 두 번째 select 문부터 DEPENDENT UNION으로 출력된다. 그리고 첫 번째 select 문은 DEPENDENT SUBQUERY로 노출 된다.

 

 

 

 

9) UNCACHEABLE SUBQUERY

 

서브 쿼리에 포함된 요소에 의해 캐싱이 불가능한 경우 UNCACHEABLE SUBQUERY로 노출 된다.

서브 쿼리 캐싱 기능은 다른 포스팅에서 자세히 정리하자.

 

 

 

 

참고 서적

 

Real MySQL (이성욱)