3장 SQL 튜닝의 실행 계획 파헤치기 - (1)

3.2 실행 계획 수행

실행 계획은 쿼리문을 옵티마이저가 비용 기반 최적화를 적용하여 비용이 최소로 소요되는 방법을 찾아내는 것을 의미한다. 

3.2.1 기본 실행 계획 수행

아래 3가지 방법으로 실행 계획을 확인할 수 있다.

• EXPLAIN 쿼리문
• DESCRIBE 쿼리문
• DESC 쿼리문

일반적으로 EXPLAIN을 사용한다. 예를 들어 아래처럼 실행하여 확인할 수 있다. 

EXPLAIN 
 SELECT *
   FROM employees
  WHERE emp_no BETWEEN 100001 AND 200000;

 

3.2.2 기본 실행 계획 항목 분석

실행 계획을 조회하면 나타나는 각 컬럼들의 역할을 알아보자

 

3.2.2.1 id

단위 SELECT 쿼리별로 부여되는 식별자 값이다. 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id값이 부여된다. 다음 예제에서 SELECT 문장은 하나인데 여러 테이블이 조인되는 경우에는 같은 id값이 부여된다.

EXPLAIN 
SELECT e.emp_no, e.first_name, s.from_date, s.salary
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 LIMIT 10;

 

다음 쿼리의 실행 계획에서는 쿼리 문장이 3개의 단위 SELECT 쿼리로 구성돼 있으므로 실행 계획의 각 레코드가 서로 다른 id 값인 것을 확인할 수 있다.

 
EXPLAIN
SELECT ( (SELECT COUNT(1) FROM employees) + (SELECT COUNT(1) FROM departments) );

여기서 주의해야 할 것은 id값은 테이블의 접근 순서를 의미하는 것은 아니다. 

 

3.2.2.2 select_type

SIMPLE

UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 select_type은 SIMPLE로 표시된다. 쿼리에 조인이 포함된 경우에도 마찬가지다. 아무리 복잡한 쿼리라도 SIMPLE인 단위 쿼리는 하나만 존재하며 여러 레코드라면 id값이 같다.

-- SIMPLE (1)
EXPLAIN 
SELECT * 
  FROM employees 
 WHERE emp_no = 100000;

 

-- SIMPLE (2)
EXPLAIN 
SELECT e.emp_no, e.first_name, e.last_name, s.salary     
  FROM employees e
 INNER JOIN ( SELECT emp_no, salary FROM salaries WHERE salary > 80000 ) s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no BETWEEN 10001 AND 10010;

SIMPLE (2) 쿼리문은 인라인 뷰(FROM 절 서브쿼리)가 사용되었기 때문에 select_type이 DERIVED 가 표시되어야 하지만 SIMPLE 이 표시되었다. 옵티마이저가 서브쿼리 사용 쿼리문을 조인 사용 쿼리문으로 변경하여 실행하였다. 조인 사용 쿼리문은 아래 SIMPLE (3)처럼 변경되었을 것으로 예측된다. SIMPLE(3)의 실행계획이 SIMPLE (2)와 같은 걸 확인할 수 있다.

-- SIMPLE (3)
EXPLAIN  
SELECT e.emp_no, e.first_name, e.last_name, s.salary     
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no BETWEEN 10001 AND 10010
   AND s.salary > 80000

 

PRIMARY

UNION이나 인라인 뷰를 제외한 서브쿼리가 포함된 쿼리문에서 나타난다. UNION의 경우 최상단에 위치한 쿼리가 PRIMARY가 되고 서브쿼리의 경우 가장 외부(Outer)에 있는 쿼리가 PRIMARY가 된다. PRIMARY인 단위 쿼리는 하나만 존재하며 여러 레코드라면 id값이 같다.

EXPLAIN 
SELECT e.emp_no, e.first_name, e.last_name
     , (SELECT MAX(dept_no) FROM dept_emp d WHERE d.emp_no = e.emp_no) as cnt    
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no = 10001;

위 실행계획에서 첫 번째, 두 번째 레코드의 id값이 1이므로 조인인 것을 알 수 있으며 e, s 알리아스 테이블의 쿼리가 외부 쿼리인 것을 알 수 있고 SELECT절의 서브쿼리의 select_type은 "DEPENDENT SUBQUERY"로 표시되는 걸 확인할 수 있다.

EXPLAIN 
 SELECT emp_no, first_name, last_name
   FROM employees s1
  WHERE emp_no = 100001 
  
  UNION ALL 
  
 SELECT emp_no, first_name, last_name
   FROM employees s2
  WHERE emp_no = 100002;

위 실행계획에서 s1 알리아스 테이블의 쿼리가 UNION 최상단에 위치한 쿼리인 것을 확인할 수 있다.

 

SUBQUERY

인라인 뷰를 제외한 독립적으로 실행되는 서브쿼리를 의미한다.

EXPLAIN  
SELECT ( SELECT COUNT(1) FROM dept_emp ) AS cnt
     , ( SELECT MAX(salary) FROM salaries ) AS salary;

 

EXPLAIN  
SELECT e.first_name, e.last_name
     , ( SELECT COUNT(1) 
           FROM dept_emp de
          INNER JOIN dept_manager dm
             ON dm.dept_no = de.dept_no ) AS cnt
  FROM employees e
 WHERE e.emp_no = 10001;

 

DEPENDENT SUBQUERY

인라인 뷰를 제외한 서브쿼리 중 바깥쪽(Outer) SELECT 쿼리에서 정의된 컬럼을 서브쿼리에서 사용하는 경우이다. 의존하는 서브쿼리의 의미로 독립적으로 실행할 수 없는 서브쿼리이다.

EXPLAIN  
SELECT e.first_name, e.last_name
     , ( SELECT COUNT(1) 
           FROM dept_emp de
          INNER JOIN dept_manager dm
             ON dm.dept_no = de.dept_no
            AND de.emp_no = e.emp_no ) AS cnt
  FROM employees e
 WHERE e.first_name = 'Matt';

 

DERIVED

인라인 뷰를 의미한다. 임시 테이블이 만들어진다.

EXPLAIN 
SELECT s.emp_no, s.salary
  FROM employees e
 INNER JOIN ( 
       SELECT emp_no, MAX(salary) AS salary 
         FROM salaries 
        WHERE emp_no BETWEEN 10001 AND 20000
        GROUP BY emp_no
     ) s
    ON e.emp_no = s.emp_no;

첫 번째 레코드의 table 컬럼의 값이 "<derived2>"로 표시되는데 이것은 id값 2인 임시 테이블을 의미한다. 즉, 세 번째 레코드의 결과를 의미한다. 임시 테이블과 e 알리아스 테이블을 조인한다는 의미이다.

 

UNION

UNION 및 UNION ALL으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다. MySQL 8.0 이전 버전에서는 UNION ALL 경우도 임시테이블을 사용하였지만 8.0 버전에서는 임시 테이블을 사용하지 않도록 개선됐다.

EXPLAIN 
SELECT 'M' AS gender2, gender, MAX(hire_date) AS hire_date
  FROM employees s1
 WHERE gender = 'M'

 UNION ALL 

SELECT 'F', gender, MAX(hire_date)
  FROM employees s2
 WHERE gender = 'F';

 

UNION RESULT

UNION ALL이 아닌 UNION 구문으로 SELECT 절을 결합했을 때 출력된다. UNION은 중복 제거를 해야하므로 데이터를 가져와 정렬하여 중복 체크하는 과정을 거친다. 따라서 UNION RESULT는 별도의 메모리 또는 디스크에 임시 테이블을 만들어 중복을 제거하는 의미로 해석할 수 있다.

EXPLAIN 
SELECT 'M' AS gender2, gender, MAX(hire_date) AS hire_date
  FROM employees s1
 WHERE gender = 'M'

 UNION  

SELECT 'F', gender, MAX(hire_date)
  FROM employees s2
 WHERE gender = 'F';

세 번째 레코드 table컬럼의 값인 "<union1,2>"는 id값 1, 2번의 결과를 임시테이블에 넣고 중복을 제거한 것을 의미한다.

 

DEPENDENT UNION

UNION 또는 UNION ALL을 사용하는 서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 컬럼을 서브쿼리에서 사용하는 경우이다. 

EXPLAIN 
SELECT dm.dept_no
     , ( SELECT s1.first_name 
           FROM employees s1 
          WHERE s1.gender = 'F'
            AND s1.emp_no = dm.emp_no 
							  
          UNION ALL
							
         SELECT s2.first_name 
           FROM employees s2 
          WHERE s2.gender = 'M'
            AND s2.emp_no = dm.emp_no ) AS manager_name
FROM dept_manager dm;

서브쿼리에서 UNION ALL 아래 쿼리문에서 WHERE절 부분에 "AND s2.emp_no = dm.emp_no" 내용이 있는 부분에서 "dm.emp_no"가 바깥쪽 쿼리에서 정의된 컬럼을 사용한 부분이다.

 

EXPLAIN 
SELECT *
  FROM employees e1
 WHERE e1.emp_no IN (
       SELECT e2.emp_no FROM employees e2 WHERE e2.first_name = 'Matt'
       UNION
       SELECT e3.emp_no FROM employees e3 WHERE e3.last_name = 'Matt'
     );

위 쿼리는 옵티마이저가 IN 내부의 서브 쿼리를 먼저 처리하지 않고 외부 employees 테이블을 먼저 읽은 다음 서브 쿼리를 실행한다. 서브 쿼리의 UNION 상단 쿼리문 WHERE절에 "e2.emp_no = e1.emp_no" 내용이 추가되고 하단 쿼리문 WHERE절에 "e3.emp_no  = e1.emp_no" 내용이 추가된다. 그래서 select_type에 상단 쿼리는 "DEPENDENT SUBQUERY"가 표시되고 하단 쿼리는 "DEPENDENT UNION"이 표시된다.

 

UNCACHEABLE SUBQUERY

조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다. (쿼리 캐시나 파생 테이블과는 전혀 다른 기능이다.) 이러한 캐시 기능을 사용하지 못 할때 UNCACHEABLE SUBQUERY  표시되며 대표적으로 2가지가 있다. 

• 사용자 변수가 서브쿼리에 사용된 경우
• NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼링 내에 사용된 경우

UUID()나 RAND() 함수와 같이 결과값이 호출할 때마다 달라지는 함수를 서브쿼리에서 사용한 경우에도 표시되었으나 MySQL 8.0.41에서는 표시되지 않는다.

EXPLAIN
SELECT *
  FROM employees 
 WHERE emp_no = ( SELECT @STATUS 
                    FROM dept_emp 
                   WHERE dept_no='d005' );

위 쿼리는 사용자 변수 (@STATUS)가 사용된 쿼리 예제이다.

 

MATERIALIZED

주로 인라인 뷰나 IN절에 서브쿼리의 최적화를 위해 사용된다. 임시 테이블을 생성한 뒤 조인이나 가공 작업을 수행할 때 표시된다. 

EXPLAIN
SELECT *
  FROM employees
 WHERE emp_no IN ( SELECT emp_no
                     FROM salaries
                    WHERE salary BETWEEN 100 AND 1000 );

위 쿼리는 MySQL 5.7 버전부터 서브쿼리의 내용을 임시 테이블로 구체화(Materialization)한 후 임시 테이블과 employees 테이블을 조인하는 형태로 최적화된다. 위 실행계획에서 두 번째 레코드 table 컬럼의 값인 "<subquery2>"는 id값 2번의 결과를 employees 테이블과 조인을 했다는 의미이다.

이러한 기능은 세미 조인(Semi Join)의 형태 중 하나이다. 세미 조인을 비활성화하고 위 쿼리를 다시 실행하면 실행계획이 다르게 나타난다. 즉, MySQL 5.7 이전 버전의 실행계획이 표시된다. 아래 명령어는 세미 조인을 비활성화한다. 비활성화 후 위 쿼리를 다시 실행하면 아래 실행계획이 표시된다.

SET SESSION optimizer_switch='semijoin=off';