재귀 **공통 테이블 표현식(CTE)**는 SQL에서 자기 참조 쿼리를 사용하여 계층적 데이터를 탐색할 수 있게 해주며, 세트 기반 방식으로 실행됩니다. 구조는 고정 멤버(기본 경우, 일반적으로 manager_id가 NULL인 루트 노드)와 재귀 멤버(부모-자식 관계에서 CTE를 자기 자신과 조인하는 반복 부분)로 구성됩니다. 데이터베이스 엔진은 새로운 행이 반환되지 않을 때까지 재귀 멤버를 반복적으로 실행하여 명시적인 반복 구조 없이 결과 집합을 점진적으로 구축합니다.
이 접근 방식은 SQL의 선언적 특성을 활용하여 최적화 도구가 CURSOR 또는 WHILE 루프에 고유한 행별 처리 대신 일반적으로 해시 또는 머지 조인과 같은 효율적인 조인 알고리즘을 선택할 수 있도록 합니다. 구문은 PostgreSQL 및 MySQL에서는 WITH RECURSIVE를 사용하고, SQL Server에서는 (재귀가 암시적일 때) 간단히 WITH를 사용하여 이어지는 CTE 이름과 열 목록을 지정합니다.
12,000명의 직원을 보유한 다국적 기업은 SOX 준수 감사를 위한 완전한 보고 체계를 생성해야 했습니다. 기존 시스템은 각 직원을 반복적으로 통과하고 스칼라 함수를 재귀적으로 호출하여 관리자를 찾고 계층 구조를 문자열로 구성하는 T-SQL CURSOR를 사용했습니다. 이 프로세스는 완료하는 데 47분이 걸렸으며, HR 업데이트를 방해하는 Employees 테이블에 대한 잠금을 유지했으며, 깊이 100단계를 초과하는 계층 구조를 처리할 때 스택 오버플로우 오류로 자주 실패했습니다(엔지니어링 부서의 매트릭스 구조에서 일반적임).
해결책 A: 임시 테이블과 최적화된 CURSOR. 팀은 먼저 결과를 임시 테이블에 저장한 후_bulk_ 삽입하는 방법으로 커서를 다시 작성하는 것을 고려했습니다. 이는 잠금 시간을 47분에서 약 40분으로 줄일 수 있었습니다. 장점: 코드 변경 최소화, 레거시 팀이 익숙한 패턴. 단점: 여전히 행별 처리 및 깊은 재귀 스택 오버플로우에 취약하며 성능 문제를 완전히 해결하지 않습니다.
해결책 B: HierarchyID 데이터 유형. 테이블을 SQL Server의 고유한 HierarchyID 유형으로 마이그레이션하여 트리 위치를 탐색을 최적화한 인코딩된 경로로 저장하게 했습니다. 장점: O(1) 서브트리 검색, GetAncestor() 및 GetDescendant()와 같은 내장 메서드, 읽기 집중적 워크로드에 대해 매우 빠름. 단점: 대규모 스키마 마이그레이션 필요 (12,000행 + 역사 데이터), 재조직 중 유지 관리가 복잡함(관리자를 업데이트하려면 모든 하위 경로를 다시 계산해야 함), 회사가 PostgreSQL 마이그레이션을 고려하는 동안 SQL Server에 자물쇠가 걸림.
해결책 C: 사이클 감지가 있는 재귀 CTE. 직원 테이블을 manager_id에서 자기 자신과 조인하는 재귀 CTE를 구현하여 순환 참조로 인한 무한 루프를 방지하기 위해 방문한 노드를 추적하는 경로 배열을 사용하는 것입니다(데이터 입력 오류로 인해 두 번 발생했습니다). 장점: 순수 ANSI SQL 표준(마이그레이션 시 PostgreSQL로 휴대 가능), 집합 기반 실행이 실행 시간을 4분 12초로 줄였고, 실행 중에 테이블 잠금을 유지하지 않습니다(스냅샷 격리 사용), 무제한 깊이를 처리하며 스택 오버플로우 없이 자동으로 데이터 품질 문제(주기)를 감지합니다.
팀은 해결책 C를 선택했습니다. 구현은 PostgreSQL의 배열 집계(또는 SQL Server의 문자열 연결)를 사용하여 path 열에 직원 ID를 누적하고 새로운 manager_id가 누적된 경로에 존재하지 않도록 하는 WHERE 절을 사용했습니다. 결과적으로 91%의 성능 향상, 생산 잠금 제거 및 이전에 애플리케이션 충돌을 일으켰던 순환 보고 관계의 조기 감지를 얻었습니다.
재귀 CTE는 어떻게 종료되며, 데이터에 순환 참조가 포함되면 어떻게 됩니까?
후보자들은 종종 재귀 CTE에는 내장 사이클 감지가 있다고 믿지만, 표준 SQL 재귀는 재귀 멤버가 0개의 새로운 행을 반환할 때만 종료됩니다. 직원 A가 B에게 보고하고, B가 C에게 보고하고, C가 다시 A에게 보고하면 CTE는 무한히 실행됩니다(또는 SQL Server의 기본 100 재귀 한도에 도달할 때까지). 이 솔루션은 방문한 노드 ID를 누적하는 경로 열(배열 또는 구분 문자열을 사용)로 수동 사이클 감지가 필요하며 WHERE new_id != ALL(path_array)로 필터링합니다. 최신 PostgreSQL(14+) 및 SQL Server(2022+)는 표준 SQL:1999 CYCLE 절을 지원합니다: WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path로, 이를 자동으로 처리합니다.
재귀 CTE와 커서 기반 접근 방식 간의 실행 계획은 어떻게 다르며, 이는 동시성에 왜 중요한가요?
주니어 후보자들은 종종 CTE가 "더 빠르다"고 주장하지만 실행 모델을 이해하지 못합니다. SQL Server 또는 PostgreSQL의 CURSOR는 엔진이 결과 집합을 물질화하고 행별로 반복해야 하므로 일반적으로 잠금 또는 tempdb 자원이 반복되는 동안 필요합니다. 이는 위의 예시와 같이 전체 47분 동안 기본 테이블에 공유 잠금(또는 업데이트 잠금)을 생성합니다. 반대로 재귀 CTE는 단일 SELECT 문입니다. 읽기 커밋된 스냅샷 격리(RCSI) 또는 스냅샷 격리 하에서 데이터의 일관된 시점 보기 읽기를 수행하며, 잠금을 유지하지 않고 버전 저장소를 사용합니다. 최적화 도구는 일반적으로 재귀 멤버에 대해 인덱스 탐색 작업과 함께 중첩 루프 조인을 선택하므로 커서 접근 방식의 O(n²) 대신 O(n log n)이 됩니다.
재귀 CTE와 계층적 데이터에 대한 Nested Sets Model의 차이점은 무엇이며, 언제 각각 선택하나요?
후보자들은 종종 탐색 방법과 저장 모델을 혼동합니다. 재귀 CTE는 인접 목록(parent_id 외래 키)에서 작동하는 쿼리 시간 탐색 기술입니다. Nested Sets Model(왼쪽/오른쪽 값)은 트리 탐색 경로를 사전에 계산하는 저장 설계 패턴입니다. 쓰기 중심 작업 부하(빈번한 관리자 변경)의 경우, 인접 목록의 재귀 CTE가 우수합니다. 단일 parent_id를 업데이트하는 것이 O(1)인 반면, nested sets는 이동된 노드 오른쪽의 모든 오른쪽 값을 O(n)로 업데이트해야 합니다. 읽기 집중적이며 정적 계층 구조(매달 변경되는 조직도)의 경우, nested sets는 O(1) 서브트리 검색(WHERE left BETWEEN parent.left AND parent.right)을 제공하며 재귀 CTE는 O(n)을 필요로 합니다. 하이브리드 접근 방식은 Closure Tables를 사용하여 모든 조상-후손 쌍을 저장하는 별도 테이블로, 탐색과 모든 자식을 찾는 데 O(1)을 제공하지만 O(n²) 저장소와 더 복잡한 유지 관리를 요구합니다. 선택은 읽기/쓰기 비율에 따라 다릅니다: 쓰기가 작업의 5%를 초과할 때 재귀 CTE를 사용하고, 주로 정적인 트리에 대해 nested sets나 closure tables을 사용합니다.