MySQL에서 자기 참조 트리 구현하기 실전

MySQL을 사용해 자기 참조 트리를 구현하는 것은 다양한 데이터 구조, 특히 조직도, 카테고리 계층, 포럼 스레드 구조를 효율적으로 관리하는 데 필수적인 기법입니다. 이 글에서는 실무에서 가장 많이 활용되는 MySQL 자기 참조 트리 구현 방법을 중심으로, 테이블 설계부터 쿼리 작성, 성능 최적화까지 완전한 마스터 가이드를 제공합니다.

자기 참조 트리란 무엇인가?

1. 기본 개념 이해

자기 참조 트리는 한 테이블 내에서 특정 열이 다시 자기 자신을 참조하는 구조입니다. 예를 들어, employee 테이블에서 manager_id 컬럼이 동일 테이블의 id 컬럼을 참조할 경우, 이 구조는 자기 참조 트리로 간주됩니다.

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employee(id)
);

이 구조는 다음과 같은 데이터 계층을 표현할 수 있습니다:

• CEO (최상위 노드)
  • 부장
    • 팀장
      • 팀원

2. 왜 자기 참조 트리를 사용하는가?

• 조직도 설계
• 카테고리-서브카테고리 구조 구현
• 댓글 및 대댓글 구조
• 제품 분류 트리 구성

반응형

MySQL에서 자기 참조 트리 테이블 설계 방법

1. 트리형 테이블의 필수 컬럼

자기 참조 트리의 기본 구조를 갖추기 위해 다음과 같은 컬럼이 필요합니다:

컬럼명 설명

id	각 노드를 고유하게 식별
name	노드의 이름 혹은 카테고리명
parent_id	부모 노드의 id를 참조

CREATE TABLE category (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  parent_id INT DEFAULT NULL,
  FOREIGN KEY (parent_id) REFERENCES category(id)
);

2. 기본 데이터 삽입 예제

INSERT INTO category (name, parent_id) VALUES
('전자제품', NULL),
('노트북', 1),
('게이밍 노트북', 2),
('생활가전', 1),
('청소기', 4);

반응형

MySQL 자기 참조 트리 조회 쿼리 방법

1. 기본 재귀 쿼리 사용

MySQL 8.0부터 CTE(Common Table Expression) 기능을 통해 재귀적으로 트리를 조회할 수 있습니다.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 AS level
  FROM category
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM category c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

2. 전체 트리 구조 출력

위 쿼리를 활용하면 전체 트리 구조를 레벨 기반으로 조회할 수 있습니다. level 컬럼을 기반으로 트리 깊이를 파악하거나 정렬에 활용할 수 있습니다.

반응형

트리 구조에서 특정 노드 하위 전체 조회

특정 부모 노드의 하위 모든 자식 노드를 재귀적으로 조회하려면 다음과 같은 쿼리를 사용합니다.

WITH RECURSIVE sub_tree AS (
  SELECT id, name, parent_id
  FROM category
  WHERE id = 2  -- 노트북 카테고리 ID
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM category c
  INNER JOIN sub_tree s ON c.parent_id = s.id
)
SELECT * FROM sub_tree;

이 방식은 카테고리 필터링, 하위 카테고리 일괄 검색, 게시판 스레드 구조 탐색 등에 매우 유용합니다.

트리 구조의 정렬과 깊이 레벨 관리 기법

1. 정렬을 위한 경로 문자열 병합

트리 출력 시 정렬 순서를 제어하기 위해 path와 같은 컬럼을 가상으로 생성할 수 있습니다.

WITH RECURSIVE category_path AS (
  SELECT id, name, parent_id, CAST(id AS CHAR) AS path
  FROM category
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, CONCAT(cp.path, '/', c.id)
  FROM category c
  JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path ORDER BY path;

path를 기준으로 정렬하면 실제 트리 순서와 같은 출력이 가능해집니다.

반응형

자기 참조 트리 성능 최적화 전략

1. 인덱스 최적화

• id는 기본 키로 인덱스가 자동 생성
• parent_id에도 인덱스를 추가하여 조인 속도 개선

CREATE INDEX idx_parent_id ON category(parent_id);

2. 캐시 전략과 뷰(View) 활용

• 정적인 트리의 경우 뷰(View) 또는 일괄 캐싱 전략으로 성능 극대화
• 자주 사용하는 트리 구조는 Redis 또는 메모리 기반 캐시 사용 고려

자기 참조 트리의 응용: 조직도, 메뉴 구조, 댓글 트리

1. 조직도 구현

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  position VARCHAR(100),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employee(id)
);

반응형

재귀 CTE를 통해 팀장-팀원 관계를 자연스럽게 표현 가능

2. 댓글/답글 시스템

CREATE TABLE comment (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT,
  parent_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES comment(id)
);

대댓글 기능 구현에 매우 유용하며, 정렬을 위해 path 혹은 depth 컬럼을 병행 사용 가능

대안 기법: 중첩 집합 모델 vs 경로 열 모델

1. 중첩 집합 모델(Nested Set)

• 각 노드에 lft, rgt 값을 부여
• 정렬 및 전체 서브트리 검색에 강점
• 삽입/삭제 시 노드 재정렬 필요

CREATE TABLE nested_category (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  lft INT,
  rgt INT
);

2. 경로 열 모델(Path Enumeration)

• 트리 경로를 문자열로 저장 (예: /1/4/5)
• LIKE 쿼리를 통해 빠르게 서브노드 검색 가능

SELECT * FROM category WHERE path LIKE '/1/4/%';

관리가 용이하고 성능도 우수한 편이나, 경로 재구성 필요

 

반응형

결론

• 데이터가 자주 변경된다면 Adjacency List + 재귀 CTE 조합이 이상적
• 조회 성능이 우선시되는 경우 Path Model 혹은 Nested Set이 유리
• 인덱스 최적화와 캐시 구조 설계로 실무 퍼포먼스를 극대화할 수 있음