계층 쿼리 (중첩 SELECT_mysql, CTE_mysql, CONNECT BY_oracle)

---

조직도, 제품 카테고리, 댓글 시스템 등에서 계층적 데이터 구조를 흔히 볼 수 있음

 

• MySQL 5.7 이하: 중첩 SELECT와 변수 할당
• MySQL 8.0 이상: CTE (Common Table Expression)
• Oracle: CONNECT BY

---

테스트 데이터

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'John', NULL),
(2, 'Jane', 1),
(3, 'Bob', 1),
(4, 'Alice', 2),
(5, 'Charlie', 2),
(6, 'David', 3),
(7, 'Eva', 4),
(8, 'Frank', 5),
(9, 'Grace', 6),
(10, 'Henry', 7);

// 4단계 깊이의 조직 구조

 

결과는

id | name    | manager_id | level | path
1  | John    | NULL       | 0     | John
2  | Jane    | 1          | 1     | John > Jane
4  | Alice   | 2          | 2     | John > Jane > Alice
7  | Eva     | 4          | 3     | John > Jane > Alice > Eva
10 | Henry   | 7          | 4     | John > Jane > Alice > Eva > Henry
5  | Charlie | 2          | 2     | John > Jane > Charlie
8  | Frank   | 5          | 3     | John > Jane > Charlie > Frank
3  | Bob     | 1          | 1     | John > Bob
6  | David   | 3          | 2     | John > Bob > David
9  | Grace   | 6          | 3     | John > Bob > David > Grace

이렇게 나올 거임

---

1. MySQL 5.7 이하: 중첩 SELECT와 변수 할당

MySQL 옛날 버전에서는 재귀적 쿼리를 직접 지원 안 했음.

대신 사용자 정의 변수랑 중첩된 SELECT 문으로 계층 구조를 탐색했어야 했음.

 

SELECT node.id, node.name, node.manager_id, node.lvl
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := manager_id FROM employees WHERE id = _id) AS manager_id,
        @l := @l + 1 AS lvl,
        name
    FROM
        (SELECT @r := 1) vars,
        employees
    WHERE @r <> 0
) node
JOIN employees e ON node.name = e.name
ORDER BY node.lvl, node.id;

 

 

• @r은 현재 노드의 ID 추적함. @l은 현재 레벨 추적함.
• 중첩된 SELECT 문이 각 반복에서 다음 manager_id 선택함.
• @r이 0 되면 (최상위에 도달했을 때) 재귀 종료됨.

장단점

 

• 좋은 점: 오래된 MySQL 버전에서도 돌아감.
• 안 좋은 점: 복잡하고 이해하기 어려움. 큰 데이터셋에서는 성능 문제 생길 수 있음.

 

고려사항

 

• 성능: 대량의 데이터에서는 느려질 수 있음. 인덱스 잘 설정해야 함.
• 유지보수: 코드 복잡해서 나중에 수정하기 어려울 수 있음.
• 디버깅: 문제 생기면 찾기 어려울 수 있음. 로깅 잘 해야 함.

 <select id="getHierarchy" resultType="map">
        SELECT node.id, node.name, node.manager_id, node.lvl
        FROM (
            SELECT
                @r AS _id,
                (SELECT @r := manager_id FROM employees WHERE id = _id) AS manager_id,
                @l := @l + 1 AS lvl,
                name
            FROM
                (SELECT @r := 1, @l := 0) vars,
                employees
            WHERE @r <![CDATA[<>]]> 0
        ) node
        JOIN employees e ON node.name = e.name
        ORDER BY node.lvl, node.id
    </select>

 

---

2. MySQL 8.0 이상: CTE (Common Table Expression)

MySQL 8.0부터는 표준 SQL의 CTE 지원함. 이거 쓰면 재귀적 쿼리 훨씬 더 깔끔하고 효율적으로 쓸 수 있음.

WITH RECURSIVE org_structure AS (
    -- 기본 케이스: 최상위 관리자 선택
    SELECT id, name, manager_id, 0 AS level, CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀 케이스: 하위 직원 찾기
    SELECT e.id, e.name, e.manager_id, os.level + 1,
           CONCAT(os.path, ' > ', e.name)
    FROM employees e
    JOIN org_structure os ON e.manager_id = os.id
)
SELECT id, name, manager_id, level, path
FROM org_structure
ORDER BY path;

• 기본 케이스에서 최상위 관리자(manager_id가 NULL인 애) 선택함.
• 재귀 케이스에서 이전 결과랑 employees 테이블 조인해서 하위 직원 찾음.
• UNION ALL로 두 케이스 합침.

장단점

• 좋은 점: 읽기 쉽고 이해하기 쉬움. 표준 SQL 문법 씀. 성능도 좋음.
• 안 좋은 점: MySQL 8.0 이상 버전 필요함.

 

고려사항

• 성능: 대부분의 경우 중첩 SELECT보다 빠름. 하지만 아주 깊은 계층에서는 주의 필요함.
• 메모리 사용: 재귀가 너무 깊어지면 메모리 문제 생길 수 있음. 제한 설정 고려해야 함.
• 인덱스: 적절한 인덱스 설정하면 성능 더 좋아질 수 있음.

<select id="getHierarchy" resultType="map">
        WITH RECURSIVE org_structure AS (
            SELECT id, name, manager_id, 0 AS level, CAST(name AS CHAR(1000)) AS path
            FROM employees
            WHERE manager_id IS NULL
            
            UNION ALL
            
            SELECT e.id, e.name, e.manager_id, os.level + 1,
                   CONCAT(os.path, ' > ', e.name)
            FROM employees e
            JOIN org_structure os ON e.manager_id = os.id
        )
        SELECT id, name, manager_id, level, path
        FROM org_structure
        ORDER BY path
    </select>

---

3. Oracle: CONNECT BY

Oracle은 계층적 쿼리 위해 특별히 만든 CONNECT BY 구문 제공함. 쓰기도 쉽고 성능도 좋다고 함.

SELECT id, name, manager_id, LEVEL,
       SYS_CONNECT_BY_PATH(name, ' > ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id
ORDER BY LEVEL, id;

 

• START WITH 절이 계층 구조의 루트 지정함.
• CONNECT BY PRIOR가 부모-자식 관계 정의함.
• LEVEL이 각 행의 깊이 자동으로 계산함.
• SYS_CONNECT_BY_PATH가 루트부터 현재 노드까지의 경로 만들어줌.

장단점

• • 좋은 점: 문법 간결하고 강력함. 계층 쿼리에 최적화되어 있음.
  • 안 좋은 점: Oracle 데이터베이스에서만 쓸 수 있음.

 

고려사항

• 성능: 대부분의 경우 매우 빠름. 하지만 아주 큰 데이터셋에서는 튜닝 필요할 수 있음.
• 기능: CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH 같은 유용한 함수들 제공함.
• 제한: 순환 참조 있으면 문제 생길 수 있음. NOCYCLE 옵션 고려해야 함.

<select id="getHierarchy" resultType="map">
        SELECT id, name, manager_id, LEVEL,
               SYS_CONNECT_BY_PATH(name, ' > ') AS path
        FROM employees
        START WITH manager_id IS NULL
        CONNECT BY PRIOR id = manager_id
        ORDER BY LEVEL, id
    </select>

---

하드웨어, 데이터 분포, 인덱스 설정 등에 따라 다를 수 있겠지만

claude 말로는

 

• MySQL 5.7 (중첩 SELECT): 100만 행 처리하는 데 약 15초 걸림.
• MySQL 8.0 (CTE): 100만 행 처리하는 데 약 5초 걸림.
• Oracle (CONNECT BY): 100만 행 처리하는 데 약 3초 걸림.

이라고 한다...