계층 구조를 가지는 데이터는 개발에서 너무나 자주 접하게 되며, 또 잘 설계되어야 효율적으로 사용할 수 있는 구조인데요. 때문에 한번 잘 정리해놓으면 좋겠다 싶어서 공부한 내용입니다.
해당 포스팅은 트리 구조의 데이터베이스를 설계하는 방식 중 '클로저 테이블(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) 자료 >
< 클로저 테이블 참고 자료 >
'Programming > Database' 카테고리의 다른 글
(MySQL) IN, NOT IN, EXISTS, NOT EXISTS 동작 방식 정리 (0) | 2023.01.11 |
---|---|
데이터 타입 CHAR, VARCHAR의 차이점, 무엇을 써야할까? (0) | 2022.06.22 |
DB 데이터베이스 인덱스(Index) 기본 개념과 설명 (0) | 2022.02.12 |
데이터베이스 연동 원리 JDBC, DataSource, Connection Pool (0) | 2022.01.30 |
트랜잭션에 락을 사용하는 경우 (격리 레벨 Isolation Level) (0) | 2022.01.28 |