트리구조 DB 설계 방법 - 클로저 테이블(Closure Table)

클로저 테이블(Closure Table)

계층 구조를 가지는 데이터는 개발에서 너무나 자주 접하게 되며, 또 잘 설계되어야 효율적으로 사용할 수 있는 구조인데요. 때문에 한번 잘 정리해놓으면 좋겠다 싶어서 공부한 내용입니다.

해당 포스팅은 트리 구조의 데이터베이스를 설계하는 방식 중 '클로저 테이블(Closure Table)' 방식에 대해서 살펴보고, 직접 구현해본 내용입니다.

 

 

---

 

 

Closure Table

'SQL Anti Patterns' 도서에 수록된 클로저 테이블은 '계층 구조를 저장하는 단순하고 우아한 방법'으로 정의되어 있습니다.

아래에서 자세하게 살펴보겠지만 클로저 테이블은 조상-자손 관계에 대한 경로뿐만 아니라 트리의 모든 경로를 저장하는데요.

obj_table과 clo_table을 통해 자세한 내용 살펴보겠습니다.

 

(기본적인 클로저 테이블은 ancestor, descendant 두 개의 컬럼을 통해 경로를 저장하지만, 조금 더 실용성 있게 추가된 depth 컬럼이 포함된 예제로 바로 살펴보겠습니다.

 

 

- obj_table

idx	name
1	Object
2	MyObjectA
3	MyObjectB
4	MyObjectC
5	MyObjectAa
6	MyOBjectAaa
7	MyObjectBa

 

 

- clo_table

ancestor	descendant	depth
1	1	0
1	2	1
1	3	1
1	4	1
1	5	2
1	6	3
1	7	2
2	2	0
2	5	1
2	6	2
3	3	0
3	7	1
4	4	0
5	5	0
5	6	1
6	6	0
7	7	0

(위에서 언급한 것처럼 depth 컬럼을 추가하여 직관성과 효율성을 좀 더 높였습니다.)

 

 

---

 

 

SELECT Query

트리구조에서 많이 사용되는 조회 쿼리인 '모든 조상을 찾아오는 쿼리', '모든 자손을 찾아오는 쿼리'를 먼저 살펴보면 아래와 같습니다.

 

// 5번의 모든 조상을 찾을 때('5' 자신도 포함한)
SELECT * FROM clo_table WHERE descendant = '5';

// 5번의 모든 조상을 찾을 때('5' 자신을 제외한)
SELECT * FROM clo_table WHERE descendant = '5' AND ancestor<> '5';

(모든 조상을 찾아오는 쿼리)

 

// 2번의 모든 자손을 찾을 때 ('2' 자신도 포함한)
SELECT * FROM clo_table WHERE ancestor = '2';

// 2번의 모든 자손을 찾을 때 ('2' 자신을 제외한)
SELECT * FROM clo_table WHERE ancestor = '2' AND descendant <> '2';

(모든 자손을 찾아오는 쿼리)

 

 

---

 

 

INSERT, UPDATE, DELETE Query

위의 조회 쿼리의 경우 직관적이고 단순한 편입니다. 하지만 트리에 새로운 노드를 넣을 때, 노드의 위치를 수정할 때, 삭제할 때 사용되는 INSERT, UPDATE, DELETE 쿼리의 경우 SELECT에 비하면 조금 복잡할 수 있는데요. 원리를 이해하면서 쿼리를 보면 조금 더 쉽게 이해할 수 있습니다.

관계형 데이터베이스 관리 시스템(RDBMS)은 MySQL을 사용하였고, 각각의 쿼리는 프로시저(Procedure)를 만들어서 사용하였습니다.

 

(프로시저에 대해서 잘 모르시는 경우, 포스팅 맨 하단에 프로시저 사용법에 대한 참고 자료 링크해놓았으니 참고 부탁드리겠습니다.)

 

 

DELIMITER $$
CREATE PROCEDURE insertData(IN cur_idx INT, IN parrent_idx INT)
BEGIN
    INSERT INTO clo_table VALUES (cur_idx, cur_idx, 0);
    
    INSERT INTO clo_table (ancestor, descendant, depth)
    SELECT clo_table.ancestor AS ancestor, cur_idx AS descendant, clo_table.depth + 1 AS depth
    FROM clo_table
    WHERE clo_table.descendant = parrent_idx;
END $$
DELIMITER ;

INSERT PROCEDURE

insert 프로시저의 경우 cur_idx라는 추가할 obj_table row의 idx와 추가할 조상 idx인 parrent_idx를 입력받도록 하였습니다.

 

먼저 (cur_idx, cur_idx, 0) row를 추가하였으며, 이어지는 INSERT INTO SELECT 구문은 SELECT 결과를 INSERT 하는 방법인데요.

parrent_idx를 자손으로 가지는(parrent_idx 포함) row들을 찾아와서 데이터 형식에 맞게 INSERT 하는 것입니다.

 

 

 

DELIMITER $$
CREATE PROCEDURE deleteData(IN cur_idx INT)
BEGIN
    DELETE 
    FROM clo_table
    WHERE descendant
    IN(
        SELECT *
        FROM (
            SELECT descendant
            FROM clo_table
            WHERE ancestor = cur_idx
        ) AS temp
    );
END $$
DELIMITER ;

DELETE PROCEDURE

delete 프로시저의 경우 삭제할 cur_idx 하나만 받습니다.

IN 구문을 통해 조상을 cur_idx로 가진(cur_idx 포함) 모든 row를 찾아와서 삭제합니다.

 

 

(SELECT descendant FROM clo_table WHERE ancestor = cur_idx);

'Error Code: 1093. You can't specify target table 'clo_table' for update in FROM clause'

이때 MySQL에서는 IN 구문 내부를 위 쿼리와 같이 바로 사용하게 되면 해당 오류가 발생하게 되는데요.

 

오라클에서는 오류 없이 정상적으로 작동하지만, MySQL에서는 INSERT, UPDATE, DELETE 수행에서 동일한 테이블로 서브 쿼리를 사용했을 때, 해당 오류가 발생합니다.

따라서 위 예시와 같이 서브 쿼리의 FROM 구를 임시 테이블로 만들어서 사용하게 되면 정상적으로 작동하게 됩니다.

(아래 UPDATE 구문도 같은 오류가 발생하기 때문에 똑같은 방법을 사용했습니다.)

 

 

 

DELIMITER $$
CREATE PROCEDURE updateData(IN cur_idx INT, IN parrent_idx INT)
BEGIN
    DELETE
    FROM clo_table
    WHERE descendant 
    IN (
        SELECT *
        FROM (
            SELECT descendant
            FROM clo_table
            WHERE ancestor = cur_idx
        ) AS temp1
    )
    AND ancestor
    IN (
        SELECT *
        FROM (
            SELECT ancestor
            FROM clo_table
            WHERE descendant = cur_idx
            AND ancestor <> cur_idx
        ) AS temp2
    );
    
    INSERT INTO clo_table (ancestor, descendant, depth)
    SELECT 
        a.ancestor AS ancestor,
        b.descendant AS descendant,
        a.depth + b.depth + 1 AS depth
	FROM clo_table AS a, clo_table AS b
    WHERE a.descendant = parrent_idx
    AND b.ancestor = cur_idx;
END $$
DELIMITER ;

UPDATE PROCEDURE

update 프로시저도 insert와 마찬가지로 수정할 cur_idx와 조상이 될 parrent_idx 두 가지 input을 받습니다.

update의 경우 먼저 기존에 cur_idx 자신을 제외한 자손들을 모두 삭제하고, 다음으로 새로운 조상 아래에 cur_idx를 포함한 기존 자손들을 넣어주는 작업을 하게 됩니다.

(delete와 마찬가지로 자신의 테이블을 서브 쿼리로 사용할 경우 발생하는 에러로 인해 select 구를 임시로 만들어서 작업하였습니다.)

 

 

 

 

< 프로시저(Procedure) 자료 >

MySQL 프로시저 기본적인 사용법, insert 더미데이터 넣기

 

< 클로저 테이블 참고 자료 >

트리-친화적이지-않은-테이블

 

클로저 테이블 #1