DB part.3

Jcob.moon 2025. 6. 9. 16:45

WITH 절

이제는 슬슬 중급 이상의 DQL 테크닉을 배울때가 왔습니다. 여러분들이 한 번쯤은 들어보셨을 서브쿼리가 그것인데요. MySQL 8 이상에선 서브쿼리 기능 대신 WITH 절을 통해서 더욱 가독성있고 재사용하기 쉽게 해줍니다!

그렇다면, WITH 절은 어떠한 기능을 제공할까요?

WITH 절은 복잡한 SQL 쿼리에서 1) 임시 테이블을 정의하고 이를 나중에 2) 쿼리에서 마치 테이블처럼 사용할 수 있게 해주는 기능이에요. 이걸 공통 테이블 표현식(CTE)라고도 하고요. 서브쿼리와 비슷한 역할을 하지만 가독성과 재사용성에서 훨씬 더 좋은 점이 있어요!

즉, 특정 테이블들을 기반으로 연산한 쿼리 결과를 다른 쿼리에 공급해야 할 때 많이 쓸 수 있어요! 그렇다면, 어떻게 쓰는지 살펴볼게요!

WITH player_total_hours AS (
  SELECT player_id, SUM(hours_played) AS total_hours
  FROM player_activity
  GROUP BY player_id
)
SELECT pa.player_id, q.quest_name, q.difficulty, pa.hours_played
FROM player_activity pa
INNER JOIN quests q ON pa.quest_id = q.quest_id
INNER JOIN player_total_hours pth ON pa.player_id = pth.player_id
WHERE pth.total_hours >= 5;

이 쿼리는 각 플레이어가 완료한 퀘스트의 난이도를 표시하면서, 총 플레이 시간이 5시간 이상인 플레이어만 필터링하려고 하는 상황에서 사용할 수 있는 쿼리입니다. 쿼리 내용들을 차근차근 살펴볼게요.

WITH 절로 player_total_hours라는 임시 테이블을 먼저 만들었어요. 여기서 플레이어별로 총 플레이 시간을 계산해요.
이후 이 임시 테이블(pth라는 별칭 사용)을 JOIN을 통해 다시 player_activity 테이블과 결합하여 총 플레이 시간이 5시간 이상인 플레이어만 필터링합니다!

즉, pth라는 테이블은 원래 현실 테이블에선 존재하지 않지만 우리 쿼리에 필요하기 때문에 임시로 만든 임시 테이블인 것이에요. 또한, 아래와 같이 여러 DQL로 구성된 하나의 쿼리를 실행할 때도 유용하게 쓸 수 있어요.

WITH player_total_hours AS (
    SELECT player_id, SUM(hours_played) AS total_hours
    FROM player_activity
    GROUP BY player_id
  )
SELECT p.player_id, p.total_hours
FROM player_total_hours p
WHERE p.total_hours >= 5
UNION ALL
SELECT p.player_id, p.total_hours
FROM player_total_hours p
WHERE p.total_hours < 5;

이 쿼리는 플레이어별로 총 플레이 시간을 계산하고, 플레이 시간이 5시간 이상인 플레이어와 5시간 미만인 플레이어를 각각 구분하여 보여주는 쿼리입니다. 쿼리는 WITH 절로 먼저 플레이어별 총 플레이 시간을 계산한 후에 이를 기반으로 두 개의 조건을 적용한 데이터를 결합(UNION ALL)해서 결과를 보여줘요.

위의 쿼리에서도 보면 p라는 별칭으로 2개의 DQL에서 각각 사용하는 것을 확인할 수 있죠! 즉, 하나의 쿼리 내에서는 한 번만 계산을 하고 무제한으로 사용할 수 있으니 매우 좋아요! 다만, WITH 절이 동시에 많은 양이 돌아가게 되면(즉, CTE가 여러 개가 동시에 실행이 되면) DB 자체의 메모리에 부하가 세게 걸릴 수 있어요!

WITH 절을 쓰는 것이 무조건 좋은가?

이쯤에서 이런 의문이 들 수 있습니다.

어렵게 WITH 절 쿼리 쓰지말고 그냥 어플리케이션 레벨에서 쿼리 나눠서 쓰면 안되나요?

이런 의문은 상당히 합당한 의문이에요. 그렇다면, Node.js 기반으로 실제로 코드를 작성해보죠.

const ids = await db.query(
'SELECT player_id FROM player_activity GROUP BY player_id HAVING SUM(hours_played) >= 5'
); // 여기선 그냥 HAVING을 이용해 쿼리로 필터링 
const playerIds = ids.map(id => id.player_id);

이렇게 첫 번째로 쿼리를 실행하면 플레이어 ID를 가져오는 쿼리를 실행하고 그 결과를 저희가 만든 게임 서버에서 특정 조건에 맞는 플레이어 ID를 추출한 후에 playerIds와 같은 배열로 저장을 하죠.

즉, 후처리(Post-processing)를 한 번 더 합니다. 일단, 후처리가 한 번 더 실행이 되는게 상당한 성능적 병목이죠. 이제, 우리는 어렵사리 확보한 플레이어 ID 목록을 통해 한 번 더 쿼리를 실행합니다.

const result = await db.query(
'SELECT player_id, quest_id FROM player_activity WHERE player_id IN (?)', 
[playerIds]);

이렇게 구현하면 개발자들 입장에선 보다 더 쉽게 이해할 수는 있겠죠. 다만, 이렇게 쿼리를 두 번 실행하는 방식은 두 번의 네트워크 요청을 필요로 합니다. 이렇게 네트워크 요청이 늘어나면 백엔드 서버와 DB 간의 네트워크 왕복(Round Trip)이 발생하게 되고 이는 곧 네트워크 레이턴시가 늘어난다는 것을 의미해요.

또한, 첫 번째 쿼리에서 플레이어 ID 목록이 너무 크다면, 이를 애플리케이션으로 가져온 뒤 다시 두 번째 쿼리로 전달하는 과정에서 1) 게임 서버 메모리 사용량 증가나 2) 쿼리 성능 저하가 발생할 수 있어요.

그리고, 이게 끝이 아닙니다! 이건 극히 드문 일이긴 하다만 첫 번째 쿼리와 두 번째 쿼리 사이에 다른 플레이어가 데이터를 변경하면 일관성이 깨질 수도 있어요! 플레이어 ID 목록이 3명이었는데 운이 없게도 2번째 쿼리를 시작하기 직전에 한명이 마침 5시간 이상 플레이를 했다면 result는 최신 결과를 반영을 못하게 됩니다.

이런 의미에서 쿼리의 결과가 바로 다음 쿼리의 결과에 영향을 끼친다면 쿼리를 쪼개서 코딩하는 것보다는 WITH 절과 같은 테크닉을 사용해서 한 번의 쿼리로 해결해보시길 바랍니다! 다만, 이 역시 호출 빈도나 데이터 크기와 같은 여러가지 외부 요인을 고려해서 어떻게 구현을 해야될지 결정해주세요!

CTE는 메모리에서 처리되므로 대용량 데이터에서는 성능 문제가 발생할 수 있으니 대용량 데이터를 리턴하는 경우가 아니면 WITH 절을 사용하셔도 괜찮을 것 같아요! 호출 빈도에 대한 테스트는 따로 해보는 것이 좋겠습니다.