Featured image of post 11.4 쿼리 작성 및 최적화 - SELECT - 서브쿼리 (3)
Real MySQL

11.4 쿼리 작성 및 최적화 - SELECT - 서브쿼리 (3)

Real MySQL 8.0

쿼리를 작성할 때 서브쿼리를 사용하면 단위 처리별로 쿼리를 독립적으로 작성할 수 있다.

조인처럼 여러 테이블을 섞어 두는 형태가 아니어서 쿼리의 가독성도 높아지며, 복잡한 쿼리도 손쉽게 작성할 수 있다.

MySQL 5.6 버전까지는 서브쿼리를 최적으로 실행하지 못할 때가 많았지만 서브쿼리 처리가 많이 개선되었다.

서브쿼리는 쿼리의 여러 위치에 사용될 수 있으며 위치별로 최적화와 성능에 도움이 되는 방법이 조금씩 다르다.

  • SELECT
  • FROM
  • WHERE 등

SELECT 절에 사용된 서브쿼리

SELECT 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 실행하게 만들지는 않기 때문에 서브쿼리가 적절히 인덱스를 사용할 수 있다면 크게 주의할 사항은 없다.

일반적으로 SELECT 절에 서브쿼리를 사용하면 그 서브쿼리는 항상 컬럼과 레코드가 하나인 겨로가를 반환해야 하지만, MySQL에서는 이 체크 조건이 조금 느슨하다.

서브쿼리의 결과가 0건인 경우

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT emp_no
    , (SELECT dept_name FROM departments WHERE dept_name = 'Sales1') subquery
FROM dept_emp LIMIT 10
;

/*
+--------+----------+
| emp_no | subquery |
+--------+----------+
| 110022 | NULL     |
| 110085 | NULL     |
| 110183 | NULL     |
| 110303 | NULL     |
| 110511 | NULL     |
| 110725 | NULL     |
| 111035 | NULL     |
| 111400 | NULL     |
| 111692 | NULL     |
| 110114 | NULL     |
+--------+----------+
*/

사용된 서브쿼리는 항상 결과가 0건이다. 하지만 에러를 발생시키지 않고 결과로 NULL을 채워 반환한다.

2건 이상 레코드를 반환하는 경우

1
2
3
4
5
6

SELECT emp_no, (SELECT dept_name FROM departments)
FROM dept_emp 
LIMIT 10
;

# ERROR 1242 (21000): Subquery returns more than 1 row

에러가 발생하며 쿼리가 종료된다.

2개 이상 컬럼을 가져오려고 하는 경우

1
2
3
4
5
6
7

SELECT emp_no
    ,(SELECT dept_no, dept_name FROM departments WHERE dept_name = 'Sales1')
FROM dept_emp
LIMIT 10
;

# ERROR 1241 (21000): Operand should contain 1 column(s)

2건 이상 결과를 반환하는 서브쿼리처럼 에러가 발생한다.

SELECT 절의 서브쿼리에는 로우(row) 서브쿼리를 사용할 수 없고, 오직 스칼라 서브쿼리만 사용할 수 있다.

조인으로 처리해도 되는 쿼리를 SELECT 절의 서브쿼리를 사용하는 경우

조인으로 처리해도 되는 쿼리를 SELECT 절의 서브쿼리를 사용해서 작성하는 경우가 종종 있는데, 서브쿼리로 실행될 때보다 조인으로 처리할 때 조금 더 빠르기 때문에 가능하다면 조인으로 쿼리를 작성하는 것이 좋다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT 
    COUNT(
        CONCAT(
            e1.first_name,
            (SELECT e2.first_name FROM employees e2 WHERE e2.emp_no=e1.emp_no)
        )
    )
FROM employees e1;

SELECT COUNT(CONCAT(e1.first_name, e2.first_name))
FROM employees e1
    ,employees e2
WHERE e1.emp_no = e2.emp_no
;

처리해야 하는 레코드 건수가 많아지면 성능 차이가 커지므로 가능하면 조인으로 쿼리를 작성하는 방법을 권장한다.

동일한 서브쿼리를 여러번 사용하는 경우

1
2
3
4
5
6
7
8

SELECT e.emp_no
    ,e.first_name
    ,(SELECT s.salary FROM salaries s WHERE s.emp_no = e.emp_no ORDER BY s.from_date DESC LIMIT 1) salary
    ,(SELECT s.from_date FROM salaries s WHERE s.emp_no = e.emp_no ORDER BY s.from_date DESC LIMIT 1) salary_from_date
    ,(SELECT s.to_date FROM salaries s WHERE s.emp_no = e.emp_no ORDER BY s.from_date DESC LIMIT 1) salary_to_date
FROM employees e
WHERE e.emp_no=499999
;

위 예시의 경우 LIMIT 1 조건으로 인해 salaries 터이블을 조인으로 사용할 수 없는데, MySQL 8.0 버전부터 도입된 래터럴 조인을 이용하면 동일한 결과를 만들 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT e.emp_no
    ,e.first_name
    ,s2.salary
    ,s2.from_date
    ,s2.to_date
FROM employees e
    INNER JOIN LATERAL (
        SELECT *
        FROM salaries s
        WHERE s.emp_no=e.emp_no
        ORDER BY s.from_date DESC
        LIMIT 1
    ) s2 ON s2.emp_no=e.emp_no
WHERE e.emp_no = 499999
;

3번 반복되던 서브쿼리를 래터럴 조인으로 변경하여 한법만 읽어 쿼리를 처리할 수 있다.

래터럴 조인을 사용한 경우 인덱스를 이용해 충분히 정렬된 결과를 가져올 수 있음에도 저열ㄹ을 수행하는 버그가 있다.

  • 래터럴 조인은 내부적으로 임시 테이블을 생성하기 때문에 Handler_write 값과 Handler_read_key 값이 증가할 수 있다.
  • 서브쿼리를 사용한 경우는 테이블을 여러번 읽기 때문에 Handler_read_key 값이 증가할 수 있는 실행계획이다.

FROM 절에 사용된 서브쿼리

MySQL 5.7 전 MySQL 서비에서는 FROM 절에 서브쿼리가 사용되면 항상 서브쿼리의 결과를 임시 테이블로 저장하고 필요할 때 다시 임시 테이블을 읽는 방식으로 처리되어 FROM 절의 서브 외부 쿼리로 병합하는 형태로 튜닝을 했다.

이에 따라 업그레이드 되며 옵티마이저가 FROM 절의 서브쿼리를 외부로 병합하는 최적화를 수행하도록 개선되었다.

MySQL 서버가 서브쿼리를 병합해서 재작성했다면 EXPLAIN 명령을 실행한 후, SHOW WARNINGS 명령을 실행하면 어떻게 병합했는지 확인할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

EXPLAIN
SELECT *
FROM (SELECT * FROM employees) y;

/*
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 300269 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
*/

SHOW WARNINGS;

/*
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                      |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`birth_date` AS `birth_date`,`employees`.`employees`.`first_name` AS `first_name`,`employees`.`employees`.`last_name` AS `last_name`,`employees`.`employees`.`gender` AS `gender`,`employees`.`employees`.`hire_date` AS `hire_date` from `employees`.`employees` |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/

서브쿼리에서 발생하는 외부 쿼리 병합은 꼭 FROM 절에서만 적용되는 최적화는 아니고, FROM 사용된 뷰의 경우에도 MySQL 옵티마이저는 뷰 쿼리와 외부 쿼리를 병합해서 최적화된 실행 계획을 사용한다.

FROM 절의 모든 서브쿼리를 외부 쿼리로 병합할 수 있는 것은 아니며, 대표적으로 다음과 같은 기느이 서브쿼리가 사용되면 FROM 절의 서브쿼리는 외부 쿼리로 병합되지 못한다.

  • 집합 함수 사용(SUM, MIN, MAX, COUNT 등)
  • DISTINCT
  • GROUP BY, HAVING
  • LIMIT
  • UNION(UNION DISTINCT), UNION ALL
  • SELECT 절에 서브쿼리가 사용된 경우
  • 사용자 변수 사용(사용자 변수에 값이 할당되는 경우)

외부 쿼리와 병합되는 FROM 절의 서브쿼리가 ORDER BY 절을 가진 경우 외부 쿼리가 GROUP BY나 DISTINCT같은 기능을 사용하지 않는다면 서브쿼리의 정렬 조건을 외부 쿼리로 같이 병합한다.

외부 쿼리에서 GROUP BY, DISTINCT와 같은 기능이 사용되고 있다면, 서브쿼리의 정렬 작업은 무의미하기 때문에 ORDER BY는 무시된다.

WHERE 절에 사용된 서브쿼리

WHERE 절의 서브쿼리는 SELECT 절이나 FROM 절보다는 다양한 형태(연산자)로 사용될 수 있다.

  • 동등 또는 크다 작다 비교 (= (subquery))
  • IN 비교(IN (subquery))
  • NOT IN 비교(NOT IN (subquery))

동등 또는 크다 작다 비교

MySQL 5.5 이전 버전까지는 서브쿼리 외부의 조건으로 쿼리를 실행하고, 최종적으로 서브쿼리를 체크 조근으로 사용했으나, 이러한 방식은 풀 테이블 스캔이 필요한 경우가 많아 성능 저하가 심각했다.

  • MySQL 5.5 이하
    • dept_emp 테이블을 풀 스캔하면서 서브쿼리 조건에 일치하는지 여부를 체크함
  • MySQL 5.5 이상
    • 서브쿼리를 먼저 실행하여 상수로 변환 후 상수값으로 서브쿼리를 대체해여 나머지 쿼리 실행
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

EXPLAIN
SELECT *
FROM dept_emp de
WHERE de.emp_no = (
  SELECT e.emp_no
  FROM employees e
  WHERE e.first_name = 'Georgi'
    AND e.last_name = 'Facello'
    LIMIT 1
  )
;

/*
TABLE
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | de    | NULL       | ref  | ix_empno_fromdate | ix_empno_fromdate | 4       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | e     | NULL       | ref  | ix_firstname      | ix_firstname      | 58      | const |  253 |    10.00 | Using where |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+

TREE
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (de.emp_no = (select #2))  (cost=0.35 rows=1)
    -> Index lookup on de using ix_empno_fromdate (emp_no=(select #2))  (cost=0.35 rows=1)
    -> Select #2 (subquery in condition; run only once)
        -> Limit: 1 row(s)  (cost=65.8 rows=1)
            -> Filter: (e.last_name = 'Facello')  (cost=65.8 rows=25.3)
                -> Index lookup on e using ix_firstname (first_name='Georgi')  (cost=65.8 rows=253)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/

실행 계획을 살펴보면 dept_emp 테이블을 풀 스캔하지 않고 (emp_no, from_date) 조합의 인덱스를 사용했다.

TREE 형식으로 출력한 실행 계획의 마지막 라인에서 Index lookup on e using ix_firstname (first_name='Georgi')를 통해 employees 테이블의 ix_firstname 인덱스로 서브쿼리를 처리한 후, 그 결과를 이용해 dept_emp 테이블의 ix_empno_fromdate 인덱스를 검색해 쿼리가 완료되었음을 알 수 있다.

동등 비교 뿐만 아니라 대소비교가 사용되어도 동일한 실행 계획을 사용한다.

단일 값 비교가 아닌 튜플 비교 방식이 사용되면 서브쿼리가 먼저 처리되어 상수화 되긴 하지만, 외부 쿼리는 인덱스를 사용하지 못하고 풀 테이블 스캔을 실행하는 것을 확인할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

EXPLAIN
SELECT *
FROM dept_emp de
WHERE (emp_no, from_date) = (
  SELECT emp_no, from_date 
  FROM salaries
  WHERE emp_no = 100001
  LIMIT 1)
;

+----+-------------+----------+------------+------+-------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys     | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | de       | NULL       | ALL  | NULL              | NULL    | NULL    | NULL  | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | salaries | NULL       | ref  | PRIMARY,ix_salary | PRIMARY | 4       | const |      4 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------+---------+---------+-------+--------+----------+-------------+

IN 비교

WHERE 절에 사용된 IN (subquery) 형태의 조건을 조인의 한 방식인 세미 조인으로 간주하여 처리한다.

  • 세미 조인(Semi-Join)
    • 실제 조인은 아니지만 테이블의 레코드가 다른 테이블의 레코드를 이용한 표현식(또는 컬럼 그 자체)와 일치하는지를 체크하는 형태
    1
2
3
4
5
6
7

    SELECT *
FROM employees e
WHERE e.emp_no IN (
  SELECT de.emp_no
  FROM dept_emp de
  WHERE de.from_date = '1995-01-01')
;

MySQL 5.5 버전까지는 세미 조인 최적화가 부족하여 대부분 풀 테이블 스캔이 발생하였으므로 사용하면 안되는 패턴으로 알려졌었으나, 현재는 세미 조인의 최적화가 많이 개선되면서 IN (subquery) 형태를 2개의 쿼리로 쪼개어 실행하거나 다른 우회 방법을 찾을 필요가 없어졌다.

MySQL 서버의 세미 조인 최적화는 쿼리 특성이나 조인 관계에 맞게 5개 전략을 사용한다.

  • 테이블 풀-아웃(Table Pull-out)
  • 퍼스트 매치(Firstmatch)
  • 루스 스캔(Loosescan)
  • 구체화(Materialization)
  • 중복 제거(Duplicated Weed-out)

MySQL 8.0을 사용한다면 세미 조인 최적화에 익숙해져야 한다.
예전처럼 불필요하게 쿼리를 여러 조각으로 분리해서 실행하는 습관을 버리고 MySQL 8.0의 기능을 적극 활용해 개발 생산성을 높힐 수 있다.

NOT IN 비교

IN (subquery) 와 비슷하지만 이 경우를 안티 세미 조인(Anti Semi-Join)이라고 명명한다.

일반적인 RDBMS에서 Not-Equal 비교(<>)는 인덱스를 제대로 활용할 수 없듯이 안티 세미 조인 또한 최적화할 수 있는 방법이 많지 않다.

안티 세미 조인 쿼리가 사용되면 2가지 방식으로 최적화를 수행한다.

  • NOT EXISTS
  • 구체화(Materialization)

두 가지 최적화 모두 그다지 성능 향상에 도움이 되지 않는 방법이므로 쿼리가 최대한 다른 조건을 활용해서 데이터 검색 범위를 좁힐 수 있게 하는 것이 좋다.

WHERE 절에 안티 세미 조인 조건만 단독으로 있다면 풀 테이블 스캔을 피할 수 없으므로 주의해야한다.

CTE(Common Table Expression)

CTE(Common Table Expression)는 이름을 가지는 임시 테이블로서, SQL 문장 내에서 한 번 이상 사용될 수 있으며 SQL 문장이 종료되면 자동으로 CTE 임시 테이블은 삭제된다.

CTE는 재귀적 반복 실행 여부를 기준으로 Non-recursive, Recursive CTE로 구분된다.

MySQL 서버의 CTE는 재귀 여부에 관계없이 다음과 다양한 SQL 문장에서 사용할 수 있다.

  • SELECT, UPDATE, DELETE 문장의 제일 앞쪽
    • WITH cte1 AS (SELECT ...) SELECT ...
    • WITH cte1 AS (SELECT ...) UPDATE ...
    • WITH cte1 AS (SELECT ...) DELETE ...
  • 서브쿼리의 제일 앞쪽
    • SELECT ... FROM ... WHERE id IN (WITH cte1 AS (SELECT ...) SELECT ...) ...
    • SELECT ... FROM (WITH cte1 AS (SELECT ...) SELECT ...) ...
  • SELECT 절의 바로 앞쪽
    • INSERT ... WITH cte 1 AS (SELECT ...) SELECT ...
    • REPLACE ... WITH cte 1 AS (SELECT ...) SELECT ...
    • CREATE TABLE ... WITH cte 1 AS (SELECT ...) SELECT ...
    • CREATE VIEW ... WITH cte 1 AS (SELECT ...) SELECT ...
    • DECLARE CURSOR ... WITH cte 1 AS (SELECT ...) SELECT ...
    • EXPLAIN ... WITH cte 1 AS (SELECT ...) SELECT ...

비 재귀적 CTE(Non-Recursive CTE)

MySQL 서버에서는 ANSI 표준을 그대로 이용해서 WITH 절을 이용해 CTE를 정의한다.

기본 사용

1
2
3

WITH cte1 AS (SELECT * FROM departments)
SELECT * FROM cte1
;
  • CTE는 WITH 절로 정의한다.
  • CTE 쿼리로 생성되는 임시 테이블의 이름은 WITH 바로 뒤에 정의한다.
  • 예시 쿼리의 cte1 임시 테이블은 한 번만 사용되기 때문에 FROM 절의 서브쿼리로 바꿔 사용할 수 있다.
    • 실행 계획까지 동일하게 사용한다.

여러개의 임시 테이블을 하나의 쿼리에서 사용

1
2
3
4
5
6

WITH cte1 AS (SELECT * FROM departments),
  cte2 AS (SELECT * FROM dept_emp)
SELECT *
FROM temp1 
  INNER JOIN cte2 ON cte2.dept_no = cte1.dept_no
;

여러 개의 CTE 임시 테이블을 사용하는 쿼리도 FROM 절의 서브쿼리로 대체해서 사용할 수 있지만, 임시 테이블이 여러 번 사용되는 쿼리는 둘의 실행 계획이 조금 달라진다.

CTE를 이용한 실행 계획

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

EXPLAIN
WITH cte1 AS (SELECT emp_no, MIN(from_date) FROM salaries GROUP BY emp_no)
SELECT *
FROM employees e
  INNER JOIN cte1 t1 ON t1.emp_no = e.emp_no
  INNER JOIN cte1 t2 ON t2.emp_no = e.emp_no
;

/*
+----+-------------+------------+------------+--------+-------------------+-------------+---------+-----------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys     | key         | key_len | ref       | rows   | filtered | Extra                    |
+----+-------------+------------+------------+--------+-------------------+-------------+---------+-----------+--------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL              | NULL        | NULL    | NULL      | 293568 |   100.00 | NULL                     |
|  1 | PRIMARY     | e          | NULL       | eq_ref | PRIMARY           | PRIMARY     | 4       | t1.emp_no |      1 |   100.00 | NULL                     |
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>       | <auto_key0> | 4       | t1.emp_no |     10 |   100.00 | NULL                     |
|  2 | DERIVED     | salaries   | NULL       | range  | PRIMARY,ix_salary | PRIMARY     | 4       | NULL      | 293568 |   100.00 | Using index for group-by |
+----+-------------+------------+------------+--------+-------------------+-------------+---------+-----------+--------+----------+--------------------------+
*/

FROM 절의 서브쿼리를 이용한 실행 계획

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

EXPLAIN
SELECT *
FROM employees e
  INNER JOIN (SELECT emp_no, MIN(from_date) FROM salaries GROUP BY emp_no) t1
  INNER JOIN (SELECT emp_no, MIN(from_date) FROM salaries GROUP BY emp_no) t2
;

/*
+----+-------------+------------+------------+-------+-------------------+---------+---------+------+--------+----------+-------------------------------+
| id | select_type | table      | partitions | type  | possible_keys     | key     | key_len | ref  | rows   | filtered | Extra                         |
+----+-------------+------------+------------+-------+-------------------+---------+---------+------+--------+----------+-------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL              | NULL    | NULL    | NULL | 293568 |   100.00 | NULL                          |
|  1 | PRIMARY     | <derived3> | NULL       | ALL   | NULL              | NULL    | NULL    | NULL | 293568 |   100.00 | Using join buffer (hash join) |
|  1 | PRIMARY     | e          | NULL       | ALL   | NULL              | NULL    | NULL    | NULL | 300269 |   100.00 | Using join buffer (hash join) |
|  3 | DERIVED     | salaries   | NULL       | range | PRIMARY,ix_salary | PRIMARY | 4       | NULL | 293568 |   100.00 | Using index for group-by      |
|  2 | DERIVED     | salaries   | NULL       | range | PRIMARY,ix_salary | PRIMARY | 4       | NULL | 293568 |   100.00 | Using index for group-by      |
+----+-------------+------------+------------+-------+-------------------+---------+---------+------+--------+----------+-------------------------------+
*/

CTE를 이용한 쿼리에서는 임시 테이블을 한 번만 생성하지만, FROM 절에 서브쿼리를 이용한 쿼리에서는 2개의 임시 테이블을 생성하기 위해서 각 서브쿼리에서 salaries 테이블을 읽었다. 이뿐만 아니라 CTE로 생성된 임시 테이블은 다른 CTE 쿼리에서 참조할 수 있다는 장점도 있다.

결론

  • CTE 임시 테이블은 재사용 가능하므로 FROM 절의 서브쿼리보다 효율적이다.
  • CTE로 선언된 임시 테이블을 다른 CTE 쿼리에서 참조할 수 있다.
  • CTE는 임시 테이블의 생성 부분과 사용 부분의 코드를 분리할 수 있으므로 가독성이 높다.

재귀적 CTE(Recursive CTE)

MySQL 8.0 부터 CTE를 이용한 재귀 쿼리가 가능해졌다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

WITH RECURSIVE cte (no) AS (
  SELECT 1
  UNION ALL
  SELECT (no + 1)
  FROM cte WHERE no < 5
)
SELECT * FROM cte;

/*
+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
*/

재귀적 CTE 쿼리는 비 재귀적 쿼리 파트와 재귀적 파트로 구분되며, 이 둘을 UNION(UNION DISTINCT) 또는 UNION ALL로 연결하는 형태로 반드시 쿼리를 작성해야 한다.

  • 비재귀적 파트: SELECT 1
  • 재귀적 파트: UNION ALL SELECT (no + 1) FROM cte WHERE no < 5

위 예시가 동작하는 방법은 아래와 같다.

  1. CTE 쿼리의 비 재귀적 파트의 쿼리를 실행
  2. 1번의 결과를 이용해 cte라는 이름의 임시 테이블 생성
  3. 1번의 결과를 cte라는 임시 테이블에 저장
  4. 1번 결과를 입력으로 사용해 CTE 쿼리의 재귀적 파트를 실행
  5. 4번의 결과를 cte라는 임시 테이블에 저장(이때 UNION, UNION DISTINCT의 경우 중복 제거를 실행)
  6. 전 단계의 결과를 입력으로 사용해 CTE 쿼리의 재귀적 파트 쿼리를 실행
  7. 6번 단계에서 쿼리 결과가 없으면 CTE 쿼리를 종료
  8. 6번의 결과를 cte라는 임시 테이블에 저장
  9. 6번으로 돌아가 반복 실행

1번 과정에서 매우 중요한 CTE 임시 테이블 구조가 결정된다.

  • CTE 테이블의 구조(테이블의 컬럼명과 컬럼의 데이터 타입)는 CTE 쿼리의 비 재귀적 파트의 결과로 결정된다.

    • 비 재귀적 파트의 결과와 재귀적 파트의 결과에서 컬럼 개수나 컬럼 타입, 컬럼 이름이 다른 경우 비 재귀적 파트에 정의된 결과를 사용한다.
    • CTE의 비 재귀적 쿼리 파트는 초기 데이터와 임시 테이블의 구조를 준비한다.
    • 재귀적 쿼리 파트에서는 이후 데이터를 생성해내는 역할을 한다.

  • 재귀적 쿼리 파트를 실행할 때는 모든 단계에서 만들어진 결과 셋이 아니라 직전 단계의 결과만 재귀 쿼리의 입력으로 사용된다.

재귀적으로 실행되는 CTE에서 주의할 것은 반복 실행의 종료 조건이다.

  • 모든 재귀 쿼리에는 종료 조건이 필요할 것처럼 보이지만 실제 재귀 쿼리가 반복을 멈추는 조건은 재귀 파트의 결과가 0건일 때까지다.
  • 실제 응용 프로그램의 쿼리에서 사용하는 데이터는 몇단계까지 재귀적으로 실행할지 알 수 없는 경우가 더 많다.
  • 데이터의 오류나 쿼리 작성자의 실수로 종료 조건을 만족하지 못해 무한 반복하는 경우가 발생할 수 있다.
    • cte_max_recursion_depth 시스템 변수를 이용해 최대 반복 실행횟수를 제한할 수 있다.
    • 시스템 변수의 값을 적절히 낮은 값으로 변경하고, 꼭 필요한 쿼리에서만 SET_VAR힌트를 이용해 해당 쿼리에서만 반복 호출 횟수를 늘리는 방법을 추천한다.

CTE 임시 테이블의 컬럼명을 변경하고자 한다면 CTE 별명 뒤에 (...)를 이용해 새로운 이름을 부여할 수 있다.