Real MySQL 8.0 : 서브쿼리, CTE, 잠금을 사용하는 SELECT

들어가며

Real MySQL 8.0 2권 11장에서는 쿼리 작성과 최적화에 대해 다룬다. 이 글에서는 그중 11.4.10 서브쿼리, 11.4.11 CTE(Common Table Expression), 11.4.13 잠금을 사용하는 SELECT를 정리한다.

서브쿼리는 MySQL 5.5 이하에서 성능이 좋지 않아 기피 대상이었지만, MySQL 8.0에서는 세미 조인 최적화 등 다양한 최적화 기법이 도입되어 상황이 크게 달라졌다. CTE는 MySQL 8.0에서 새롭게 도입된 기능으로, 복잡한 쿼리의 가독성을 높이고 재귀 쿼리를 가능하게 한다. 잠금을 사용하는 SELECT는 동시성 제어의 핵심으로, MySQL 8.0.1부터 NOWAIT과 SKIP LOCKED 옵션이 추가되어 더욱 유연한 잠금 제어가 가능해졌다.

서브쿼리

서브쿼리는 쿼리 내에 포함된 또 다른 쿼리를 말한다. 서브쿼리가 사용되는 위치에 따라 다르게 분류된다.

서브쿼리의 위치에 따른 분류

위치	명칭	예시
SELECT 절	스칼라 서브쿼리	SELECT (SELECT name FROM dept WHERE id = e.dept_id)
FROM 절	파생 테이블 (Derived Table)	SELECT * FROM (SELECT * FROM emp) AS t
WHERE 절	중첩 서브쿼리	SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept)

SELECT 절의 서브쿼리 (스칼라 서브쿼리)

SELECT 절에 사용되는 서브쿼리는 반드시 하나의 칼럼, 하나의 레코드 만 반환해야 한다. 그래서 스칼라 서브쿼리라고 부른다.

SELECT emp_no, 
       (SELECT dept_name 
        FROM departments d
        WHERE d.dept_no = de.dept_no) AS dept_name
FROM dept_emp de
WHERE emp_no = 10001;

스칼라 서브쿼리는 레코드마다 실행 된다. 조회되는 레코드가 많으면 서브쿼리 실행 횟수도 그만큼 늘어난다.

-- 비효율적: 레코드마다 서브쿼리 실행
SELECT emp_no,
       (SELECT dept_name FROM departments WHERE dept_no = de.dept_no)
FROM dept_emp de;

-- 효율적: JOIN으로 변환
SELECT de.emp_no, d.dept_name
FROM dept_emp de
JOIN departments d ON de.dept_no = d.dept_no;

스칼라 서브쿼리는 조회 결과 건수가 적거나, 서브쿼리 결과가 캐시될 수 있는 경우에만 사용하는 것이 좋다. 일반적으로 JOIN이 더 효율적이다.

FROM 절의 서브쿼리 (파생 테이블)

FROM 절에 사용된 서브쿼리는 파생 테이블(Derived Table) 이라고 부른다.

SELECT * 
FROM (SELECT dept_no, COUNT(*) AS emp_count
      FROM dept_emp
      GROUP BY dept_no) AS dept_summary
WHERE emp_count > 10;

MySQL 5.5 이하의 문제점

MySQL 5.5 이하에서는 FROM 절의 서브쿼리가 항상 임시 테이블로 생성 되었다. 임시 테이블에는 인덱스가 없으므로 외부 쿼리와 조인할 때 성능이 저하되었다.

MySQL 5.7+ / 8.0의 최적화

MySQL 5.7부터는 파생 테이블을 외부 쿼리와 병합(Merge) 하는 최적화가 도입되었다.

-- 원본 쿼리
SELECT * FROM (SELECT * FROM emp WHERE hire_date > '2000-01-01') AS t
WHERE t.emp_no = 10001;

-- 병합 후 (옵티마이저가 자동 변환)
SELECT * FROM emp
WHERE hire_date > '2000-01-01' AND emp_no = 10001;

병합이 불가능한 경우도 있다.

• UNION 사용
• GROUP BY, DISTINCT 사용
• 집계 함수 사용
• LIMIT 사용
• 사용자 변수 사용

WHERE 절의 서브쿼리

WHERE 절의 서브쿼리는 반환 형태에 따라 다양하게 사용된다.

동등 비교 (= 서브쿼리)

SELECT * FROM emp
WHERE dept_no = (SELECT dept_no FROM departments WHERE dept_name = 'Sales');

서브쿼리가 하나의 값만 반환해야 한다.

IN (서브쿼리) - 세미 조인

SELECT * FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no = 'd001');

IN (서브쿼리)는 세미 조인(Semi Join) 으로 최적화된다. 세미 조인은 외부 테이블의 레코드가 서브쿼리의 결과에 존재하는지만 확인하고, 실제 조인 결과를 만들지 않는다.

MySQL 8.0의 세미 조인 최적화 전략

전략	설명
Table Pullout	서브쿼리 테이블을 외부 쿼리로 끌어내어 조인
FirstMatch	첫 번째 일치하는 레코드만 찾고 종료
Loose Scan	인덱스를 스캔하며 중복 제거
Materialization	서브쿼리 결과를 임시 테이블로 구체화
Duplicate Weedout	조인 후 임시 테이블로 중복 제거

-- 실행 계획 확인
EXPLAIN SELECT * FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no = 'd001');

실행 계획에서 SEMIJOIN, FirstMatch, LooseScan 등의 키워드를 확인할 수 있다.

NOT IN (서브쿼리) - 안티 조인

SELECT * FROM employees e
WHERE e.emp_no NOT IN (SELECT de.emp_no FROM dept_emp de);

NOT IN은 안티 조인(Anti Join) 으로 처리된다. MySQL 8.0.17부터 안티 조인도 세미 조인과 유사한 최적화가 적용된다.

NOT IN 서브쿼리에서 서브쿼리 칼럼에 NULL이 포함되면 예상치 못한 결과가 나올 수 있다. NULL과의 비교는 항상 UNKNOWN을 반환하기 때문이다. NOT EXISTS를 사용하거나, NULL을 명시적으로 제외하는 것이 안전하다.

-- NULL 문제 회피
SELECT * FROM employees e
WHERE e.emp_no NOT IN (SELECT de.emp_no FROM dept_emp de WHERE de.emp_no IS NOT NULL);

-- 또는 NOT EXISTS 사용
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM dept_emp de WHERE de.emp_no = e.emp_no);

EXISTS (서브쿼리)

SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM dept_emp de WHERE de.emp_no = e.emp_no);

EXISTS는 서브쿼리의 결과가 존재하는지만 확인한다. 서브쿼리가 하나라도 레코드를 반환하면 TRUE, 아니면 FALSE를 반환한다.

IN vs EXISTS:

• IN: 서브쿼리를 먼저 실행하여 결과 집합을 만든 후, 외부 쿼리와 비교
• EXISTS: 외부 쿼리의 각 레코드마다 서브쿼리를 실행하여 존재 여부 확인

일반적으로 서브쿼리의 결과가 작으면 IN, 외부 쿼리의 결과가 작으면 EXISTS가 유리하다. 하지만 MySQL 8.0의 세미 조인 최적화로 인해 대부분의 경우 옵티마이저가 적절한 방법을 선택한다.

CTE (Common Table Expression)

CTE는 MySQL 8.0에서 새롭게 도입된 기능으로, WITH 절을 사용하여 이름을 가진 임시 결과 집합을 정의한다.

기본 문법

WITH cte_name AS (
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM cte_name;

비재귀적 CTE

비재귀적 CTE는 서브쿼리와 유사하지만, 이름을 붙여 재사용 할 수 있다는 장점이 있다.

-- 파생 테이블 사용
SELECT *
FROM (SELECT dept_no, COUNT(*) AS cnt FROM dept_emp GROUP BY dept_no) AS dept_summary
JOIN departments d ON dept_summary.dept_no = d.dept_no;

-- CTE 사용 (가독성 향상)
WITH dept_summary AS (
    SELECT dept_no, COUNT(*) AS cnt
    FROM dept_emp
    GROUP BY dept_no
)
SELECT *
FROM dept_summary
JOIN departments d ON dept_summary.dept_no = d.dept_no;

여러 CTE 정의

WITH 
cte1 AS (SELECT dept_no, COUNT(*) AS emp_count FROM dept_emp GROUP BY dept_no),
cte2 AS (SELECT dept_no, AVG(salary) AS avg_salary FROM salaries GROUP BY dept_no)
SELECT c1.dept_no, c1.emp_count, c2.avg_salary
FROM cte1 c1
JOIN cte2 c2 ON c1.dept_no = c2.dept_no;

CTE의 장점

1. 가독성 향상: 복잡한 쿼리를 논리적인 단위로 분리
2. 재사용성: 같은 CTE를 쿼리 내에서 여러 번 참조 가능
3. 유지보수: 수정 시 한 곳만 변경하면 됨

재귀적 CTE (WITH RECURSIVE)

재귀적 CTE는 자기 자신을 참조 하여 반복적으로 쿼리를 실행한다. 계층 구조 데이터(조직도, 카테고리, 댓글)를 조회할 때 유용하다.

기본 구조

WITH RECURSIVE cte_name AS (
    -- 비재귀 파트 (앵커 멤버): 시작점
    SELECT ... FROM table_name WHERE condition
    
    UNION ALL
    
    -- 재귀 파트: 자기 자신을 참조
    SELECT ... FROM table_name JOIN cte_name ON ...
)
SELECT * FROM cte_name;

간단한 예제: 1부터 5까지 출력

WITH RECURSIVE cte (n) AS (
    SELECT 1                          -- 비재귀 파트: 시작값 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5 -- 재귀 파트: n이 5보다 작을 때까지 반복
)
SELECT * FROM cte;

-- 결과:
-- n
-- ---
-- 1
-- 2
-- 3
-- 4
-- 5

조직도 조회 예제

-- 조직 테이블
CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT
);

INSERT INTO organization VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Team Lead', 2),
(5, 'QA Team Lead', 2),
(6, 'Developer', 4),
(7, 'Developer', 4);

-- 모든 하위 조직 조회 (CEO부터 시작)
WITH RECURSIVE org_tree AS (
    -- 비재귀 파트: 최상위 (CEO)
    SELECT id, name, parent_id, 0 AS depth
    FROM organization
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 재귀 파트: 하위 조직 탐색
    SELECT o.id, o.name, o.parent_id, ot.depth + 1
    FROM organization o
    JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS org_structure, depth
FROM org_tree
ORDER BY depth, id;

-- 결과:
-- org_structure    | depth
-- -----------------+------
-- CEO              | 0
--   CTO            | 1
--   CFO            | 1
--     Dev Team Lead| 2
--     QA Team Lead | 2
--       Developer  | 3
--       Developer  | 3

재귀 CTE 동작 원리

1. 비재귀 파트 실행: 초기 결과 집합 생성 (앵커 멤버)
2. 재귀 파트 실행: 이전 결과를 참조하여 새로운 행 생성
3. 반복: 재귀 파트가 빈 결과를 반환할 때까지 2번 반복
4. 종료: 모든 결과를 UNION ALL로 합침

재귀 CTE에서 종료 조건이 없으면 무한 루프에 빠질 수 있다. MySQL의 cte_max_recursion_depth 시스템 변수(기본값 1000)로 최대 재귀 깊이를 제한한다.

-- 재귀 깊이 변경
SET SESSION cte_max_recursion_depth = 5000;

잠금을 사용하는 SELECT

InnoDB에서 일반적인 SELECT는 잠금을 사용하지 않고 MVCC(Multi-Version Concurrency Control) 를 통해 스냅샷을 읽는다. 하지만 특정 상황에서는 명시적으로 잠금을 걸어야 할 때가 있다.

FOR SHARE와 FOR UPDATE

-- 공유 잠금 (S Lock): 다른 세션의 읽기는 허용, 쓰기는 차단
SELECT * FROM employees WHERE emp_no = 10001 FOR SHARE;

-- 배타적 잠금 (X Lock): 다른 세션의 읽기/쓰기 모두 차단 (잠금 읽기 기준)
SELECT * FROM employees WHERE emp_no = 10001 FOR UPDATE;

FOR SHARE는 기존의 LOCK IN SHARE MODE를 대체한다. 기능은 동일하지만 FOR SHARE는 NOWAIT, SKIP LOCKED 옵션을 지원한다.

잠금 호환성

	X Lock (FOR UPDATE)	S Lock (FOR SHARE)
X Lock 보유	❌ 대기	❌ 대기
S Lock 보유	❌ 대기	✅ 허용

중요한 점: 일반 SELECT는 잠금을 요청하지 않으므로, FOR UPDATE로 잠긴 레코드도 일반 SELECT로는 즉시 읽을 수 있다.

-- 세션 1
SELECT * FROM emp WHERE id = 1 FOR UPDATE;  -- X Lock 획득

-- 세션 2
SELECT * FROM emp WHERE id = 1;             -- 즉시 반환! (MVCC 스냅샷 읽기)
SELECT * FROM emp WHERE id = 1 FOR SHARE;   -- 대기... (S Lock 요청)

NOWAIT

MySQL 8.0.1부터 NOWAIT 옵션이 추가되었다. 잠금을 획득할 수 없으면 대기하지 않고 즉시 에러를 반환 한다.

-- 세션 1
START TRANSACTION;
SELECT * FROM seats WHERE seat_no = 'A1' FOR UPDATE;

-- 세션 2
SELECT * FROM seats WHERE seat_no = 'A1' FOR UPDATE NOWAIT;
-- ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired 
-- immediately and NOWAIT is set.

사용 사례: 특정 좌석을 예매하려고 할 때, 이미 다른 사용자가 선택 중이면 즉시 알려주는 경우

SKIP LOCKED

SKIP LOCKED는 잠긴 레코드를 건너뛰고 잠금이 걸리지 않은 레코드만 반환한다.

-- 세션 1
START TRANSACTION;
SELECT * FROM coupon WHERE id = 1 FOR UPDATE;  -- id=1에 X Lock

-- 세션 2
SELECT * FROM coupon FOR UPDATE SKIP LOCKED;
-- 결과: id=1을 제외한 나머지 레코드만 반환

큐(Queue) 구현

SKIP LOCKED를 활용하면 DB만으로 큐를 구현 할 수 있다. 대표적인 예가 선착순 쿠폰 발급이다.

-- 쿠폰 테이블 (user_id = 0이면 미발급)
CREATE TABLE coupon (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    coupon_code VARCHAR(50),
    user_id BIGINT DEFAULT 0,
    INDEX idx_user_id (user_id)
);

-- 1000개 쿠폰 미리 생성
INSERT INTO coupon (coupon_code, user_id) VALUES ('COUPON-001', 0), ...;

-- 발급 쿼리
START TRANSACTION;

SELECT * FROM coupon 
WHERE user_id = 0 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

UPDATE coupon SET user_id = ? WHERE id = ?;

COMMIT;

동시에 여러 세션이 요청해도 각 세션은 서로 다른 쿠폰 을 가져간다.

세션1: 쿠폰#1 → X Lock → UPDATE → COMMIT
세션2: 쿠폰#1 잠김, SKIP → 쿠폰#2 → X Lock → UPDATE → COMMIT
세션3: 쿠폰#1,#2 잠김, SKIP → 쿠폰#3 → X Lock → UPDATE → COMMIT

FOR UPDATE vs SKIP LOCKED 성능 비교

[FOR UPDATE] - 직렬 처리 (대기)
요청1: ──────────────────────>
요청2:        (대기)──────────────────────>
요청3:              (대기)(대기)──────────────────────>

[FOR UPDATE SKIP LOCKED] - 병렬 처리
요청1: ──────────────────────>  (쿠폰#1)
요청2: ─skip─>──────────────────────>  (쿠폰#2)
요청3: ─skip─>─skip─>──────────────────────>  (쿠폰#3)

SKIP LOCKED는 일관성 없는 뷰 를 반환할 수 있다. 따라서 일반적인 트랜잭션 작업에는 적합하지 않고, 큐나 선착순 발급처럼 어떤 레코드든 하나만 가져가면 되는 경우에 사용해야 한다.

NOWAIT vs SKIP LOCKED 비교

옵션	잠긴 레코드 발견 시	적합한 상황
NOWAIT	즉시 에러 반환	특정 레코드 필요 (좌석 예매)
SKIP LOCKED	건너뛰고 다음 레코드	아무 레코드나 필요 (쿠폰 발급)

잠금 테이블 지정 (OF 절)

조인 쿼리에서 특정 테이블에만 잠금을 걸 수 있다.

SELECT *
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
FOR UPDATE OF e;  -- employees 테이블에만 X Lock

마치며

MySQL 8.0의 서브쿼리, CTE, 잠금 관련 기능은 이전 버전 대비 크게 발전했다. 핵심 내용을 정리하면 다음과 같다.

서브쿼리:

• MySQL 8.0의 세미 조인 최적화로 IN (서브쿼리) 성능이 크게 향상되었다
• 스칼라 서브쿼리는 레코드마다 실행되므로 주의가 필요하다
• NOT IN은 NULL 처리에 주의해야 한다

CTE:

• 복잡한 쿼리의 가독성과 재사용성을 높인다
• 재귀적 CTE로 계층 구조 데이터를 쉽게 조회할 수 있다
• 종료 조건 없이 사용하면 무한 루프에 빠질 수 있다

잠금을 사용하는 SELECT:

• FOR UPDATE는 배타적 잠금, FOR SHARE는 공유 잠금을 건다
• 일반 SELECT는 잠금을 요청하지 않으므로 MVCC로 즉시 읽는다
• NOWAIT은 잠금 실패 시 즉시 에러, SKIP LOCKED는 잠긴 레코드를 건너뛴다
• SKIP LOCKED를 활용하면 DB만으로 큐를 구현할 수 있다

---

References

• Real MySQL 8.0 2권 - 백은빈, 이성욱
• MySQL 8.0 Reference Manual - Locking Reads
• MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows