[ TIL ] 2022. 05. 20. MySQL 공통 테이블 표현식(CTE_Common Table Expression)

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

도입

오늘은 LeetCode SQL 중 Medium 난이도의 문제인 1613. Find the Missing IDs을 풀었다.

우선 아래와 같이 Customers 테이블이 존재한다.

/*
Table: Customers
Primary Key: customer_id
*/

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| customer_name | varchar |
+---------------+---------+

아래와 같이 연속되지 않고 끊긴 customer_id 필드의 값을 알아내어 반환하는 게 문제의 요구사항이었다.

Input: 
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Alice         |
| 4           | Bob           |
| 5           | Charlie       |
+-------------+---------------+

Output: 
+-----+
| ids |
+-----+
| 2   |
| 3   |
+-----+

이를 해결하기 위해서는 Customers 테이블에서 가장 큰 값을 가진 customer_id 필드를 찾고 1 부터 그 값까지의 모든 정수가 담겨 있는 테이블과 Customers 테이블을 비교해야 한다.

풀이

1 부터 어떤 특정한 값까지, 다시 말해 범위가 존재하는 새로운 테이블을 만들어야 한다는 부분에서 곧바로 공통 테이블 표현식(CTE_Common Table Expression) -이하 CTE- 을 떠올렸다.

결론부터 말하면 풀이는 아래와 같다. CTE 또한 MySQL에서 종류가 크게 세 가지인데 그 중에서도 WITH RECURSIVE 를 활용한 방식으로 문제를 해결했다.

WITH RECURSIVE AllIDs (customer_id) AS (
    SELECT 1
    UNION ALL
    SELECT customer_id + 1
    FROM AllIDs
    WHERE customer_id BETWEEN 1 AND ((SELECT MAX(customer_id) FROM Customers) - 1)
)

SELECT customer_id AS ids
FROM AllIDs
LEFT JOIN Customers
USING (customer_id)
WHERE Customers.customer_id IS NULL
ORDER BY ids ASC;

이전에 프로그래머스에 있는 SQL LEVEL4의 문제 중 입양 시각 구하기(2)를 풀 때도 똑같이 WITH RECURSIVE 를 활용했는데 사실 언제 WITH 과 WITH RECURSIVE 를 구별해서 사용해야 하는지, 또 언제 WITH 를 사용하는 게 좋은지 제대로 고민해보지 않았기 때문에 이번 기회에 좀 정리해보고자 한다.

CTE

먼저 CTE란 도대체 무엇인지 알아야 한다. 공식 문서에는 다음과 같이 설명되어 있다.

공통 표현 테이블 표현식은 단일 명령문 범위 내에 존재하고 이후에 해당 명령문에서 여러 번 참조될 수 있게 명명된 임시 결과 집합이다.

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.

쉽게 임시 테이블(Temporary Table)을 만드는데 여러 번 반복해서 사용할 수 있게 한다는 의미다.

예를 들어 서브쿼리를 활용하여 임시 테이블을 만들어 조회하는 쿼리를 작성한다고 가정해보자. 그러면 기존에는 아래와 같은 방식으로 임시 테이블을 만들고 참조할 수 있다.

SELECT num
FROM (
	SELECT 1 AS num
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
) AS Numbers;

그런데 만약 임시 테이블인 Numbers 중 짝수만을 따로 빼서 홀수인 숫자 옆에 JOIN 을 활용해 결합시키고 싶으면 어떻게 해야할까? 아마도 아래와 같이 귀찮게 서브쿼리로 또 값을 만들어야 할 것이다.

SELECT
	Numbers.num AS odd_num,
    EvenNumbers.num AS even_num
FROM (
	SELECT 1 AS num
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
) AS Numbers
JOIN (
	SELECT 2 AS num
    UNION
    SELECT 4
) AS EvenNumbers
ON Numbers.num + 1 = EvenNumbers.num;

물론 이와 똑같은 예제는 거의 마주할 일이 없겠지만 여러모로 임시 테이블을 반복해서 사용하기 위해 매번 복사하고 붙여넣기를 하거나 UNION 또는 UNION ALL 을 활용하는 건 비효율적이다.

이러한 방식 외에 CREATE 구에 TEMPORARY 키워드를 붙여 임시 테이블을 만드는 방법 또한 존재한다. 하지만 만약 테이블을 수정하거나 만드는 등의 조작을 할 수 있는 권한이 없는 경우는 어떻게 해야할까?

그래서 등장한 게 바로 CTE다. 앞선 홀수, 짝수 예제를 아래와 같이 간단하게 풀 수 있다. 보면 cte 라는 테이블을 재사용한 것을 알 수 있다.

WITH cte (num) AS (
	SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
)

SELECT
	cte.num AS odd_num,
    EvenNumbers.num AS even_num
FROM cte
JOIN (
	SELECT num
    FROM cte
    WHERE (num % 2) = 0
) AS EvenNumbers
ON cte.num + 1 = EvenNumbers.num;

일반적인 CTE

위와 같이 WITH 이라는 키워드에 임시 테이블로 사용할 테이블의 이름을 작성하고 테이블을 생성할 명령문을 작성하는 식으로 만드는 방법이 일반적인 CTE를 만드는 방법이다.

이때 테이블명은 보통 cte 로 많이 명명하지만 마음대로 표현해도 상관없다. 개인적인 의견으로는 가독성을 위해 위 예시 또한 cte 가 아닌 Numbers 등으로 짓는 게 더 좋았을 것 같다.

cte 이름 뒤에 오는 (num) 부분은 선택적으로 사용할 수 있는 부분으로 임시 테이블의 필드를 지정해주는 부분이다. 그리고 뒤에 AS 키워드를 통해 실제로 테이블을 만드는 쿼리를 작성하면 된다.

회기적(Recursive) CTE

그런데 아쉬운 점이 생긴다. 어차피 1 부터 4 까지 작성할 테이블이라면 특정 범위를 지정해놓고 알아서 반복되게 하면 안 되는 건가?

이를 위해 존재하는 게 바로 회기적 CTE다. 위 WITH 구를 아래와 같이 한번 바꿔보자.

WITH RECURSIVE cte (num) AS (
	SELECT 1
    UNION ALL
    SELECT num + 1
    FROM cte
    WHERE num BETWEEN 1 AND 3
)

내부적으로 임시 테이블이었던 cte 를 참조하여 재사용하기 때문에, 다시 말해 회기적(Recursive)으로 임시 테이블을 사용하기 때문에 회기적 CTE라 한다. WITH 키워드 뒤에 RECURSIVE 키워드를 붙여서 사용할 수 있다.

그 외에 사용 방법은 일반적인 CTE와 똑같으며 대신 UNION ALL 키워드를 통해 반복할 쿼리 부분을 결합시키고 해당 쿼리에서 FROM 구는 당연히 앞서 명명한 임시 테이블을 참조하며 WHERE 구에서 그 범위를 지정한다.

이때 UNION ALL 이 아닌 UNION 을 사용해도 된다. 단, UNION 의 경우 내부적으로 중복되는 값을 제거하기 때문에 UNION ALL 보다 성능이 떨어진다. 따라서 범위 지정을 통해 자연스레 반복되는 부분이 없을 것이란 걸 확신할 수 있기 때문에 UNION ALL 을 사용하는 게 성능적으로 우수하다.

제한된 회기적(Recursive) CTE

회기적 CTE를 사용할 때 범위가 중요하다는 걸 눈치챘을 것이다. 적당한 범위를 설정하여 반복을 종료시켜야 프로그램이 원활하게 실행되기 때문이다. 이때 혹시나 하는 개발자의 실수를 줄여주기 위해 존재하는 게 바로 제한된 회기적 CTE다.

아래와 같이 SET 구를 활용하여 SESSION 테이블에 존재하는 cte_max_recursion_depth 설정값의 한계를 직접 지정할 수 있다.

SET SESSION cte_max_recursion_depth = 100;

프로그램의 안정성을 위해 기본값이 설정되어 있는데 1000 으로 설정되어 있다.

물론 이렇게 SESSION 부분을 설정하지 않더라도 아래와 같이 LIMIT 키워드를 활용하여 직접 그 제한값을 WITH 구 내부에서 설정할 수도 있다. 한 가지 유의할 점은 MySQL 8.0.19 버전 이후부터 가능한 방법이다.

WITH RECURSIVE cte (num) AS (
	SELECT 1
    UNION ALL
    SELECT n + 1
    FROM cte
    LIMIT 100
)

결론

MySQL 등의 SQL을 사용하다 보면 의문이 한 가지 생길 수 있다. 다른 일반적인 프로그래밍 언어에서는 for 또는 while 과 같은 문법이 존재하여 반복문을 만들고 사용할 수 있는데 SQL에는 이러한 기능이 없는 것일까?

앞서 WITH 구를 봤기 때문에 SQL에서도 반복문, 다시 말해 반복쿼리를 만들 수 있는 방법이 있다고 생각할 수 있지만 엄밀히 말하면 SQL은 -더 정확하게 RDB는- 반복을 지양한다.

한빛미디어에서 출판한 <SQL 레벨업> 내용 중 5장 반복문 부분을 보면 다음과 같은 내용이 나온다.

RDB를 처음 생각해냈던 Edgar F. Codd는 저서 'Relational database : a practical foundation for productivity'(1989)에서 다음과 같이 말했습니다.

관계 조작은 관계 전체를 모두 조작의 대상으로 삼는다. 이러한 것의 목적은 반복을 제외하는 것이다. 최종 사용자의 생산성을 생각하면 이러한 조건을 만족해야 하다. 그래야만 응용 프로그래머의 생산성에도 기여할 수 있을 것이다.

쉽게 말해 우리가 모델링을 할 때 따르는 원칙 중 정규화(Nomarlization)를 생각하면 편하다. 우리는 RDB(Relational Database)를 사용하는 가장 큰 이유는 중복을 최소화하여 성능을 높이는 데 있다. 그래서 반복문 사용을 꺼리는 것이다.

그러나 RDB 내부적으로도 반복문을 사용하기도 하며 앞서 살펴본 것처럼 WITH RECURSIVE 구 등을 통해 반복문을 사용하기도 한다. 그리고 반복문을 사용하면 단순한 쿼리를 여러 번 반복해서 사용하기 때문에 실행 계획(Execution Plan)도 단순할 수밖에 없다. 이는 곧 높은 안정성을 의미한다. 하지만 단순하다는 만큼 최적화(Optimization)를 위한 튜닝(Tunning)에 별다른 선택지가 없다는 뜻이기도 하다.

여러모로 장단점이 존재하기 때문에 RDB 창시자인 Edgar F. Codd의 말을 한번 더 생각해보며 상황에 맞춰 선택해보면 좋을 것 같다.

이후에 시간이 된다면 반복문에 관련하여 정리해서 글을 한편 쓰고자 한다.