2021 04 05 - sql Antipatterns

댓글의 댓글의 댓글의 댓글....

이번 안티패턴에서는 깊이의 제한이 없는 댓글타래를 구현한다고 가정하자.

여담으로 댓글의 댓글은 예전부터 어렵게 생각했었던 기능인데 이번기회에 구현방법을 알게되어서 뿌듯하다 무야호

목표

계층구조 데이터를 저장 및 조회하기

안티패턴

parent_id칼럼을 추가하는것은 안티패턴이다. 왜냐 항상 부모에 의존하기 때문

이 컬럼은 자기 자신을 FK로 참조해서 항상 부모를 갖게된다.

인접 목록에서 트리 조회하기(특정 댓글의 대댓글 조회!)

select c1.*, c2.*
from comments c1 left outer join comments c2 -- left 조인과 left outer조인은 같다.
on c2.parent_id = c1.comment_id

그러나 이 쿼리는 2단계의 트리만 조회한다. 만약 더 깊은 단계를 조회하려고 하면

아래와 같은 눈물의 쇼를 해야 한다.

select 
    c1.comment_contents as step_1,
    c2.comment_contents as step_2,
    c3.comment_contents as step_3,
    c4.comment_contents as step_4,
from comments c1
left join comments c2
    on c2.parent_id = c1.comment_id
left join comments c3
    on c3.parent_id = c2.comment_id
left join comments c4
    on c4.parent_id = c3.comment_id

인접 목록에서 트리 유지하기

이 구조에서 노드를 만들고 서브트리를 관리하는것은 쉽다.

parent_id는 화면레벨에서 넘겨주면 되고! 부모 노드가 바뀌면 update로 parent_id만 업데이트 해주면 된다.

하지만 삭제는 조금 복잡하다. FK제약조건 때문이다.

여러번 쿼리를 날려서 모든 자손을 끝까지 찾은다음, 가장 아래 단계부터 차례로 삭제해야 한다.

• 예) 2번의 댓글을 모두 삭제해아한다면, 그 자식부터, 자손, 자손의 자식.. 을 모두 조회 후, 맨 하단의 노드부터 삭제해야한다.

만약 삭제할 노드의 자손을 모두 삭제해야 한다면 FK에 ON DELETE CASCADE를 주어서 한번에 전부 삭제하게 할 수도 있다.

안티패턴 사용이 합당한 경우

주어진 노드의 부모나 자식을 바로 얻을 수 있다는것이 강점이므로 상황에 따라서 알맞게 사용하자.

또한 특정 DBMS는 인적 목록 형식으로 저장된 계층구조를 지원하기 위해
SQL-99표준에서는 WITH 키워드에 common table expression을 사용한 재귀적 쿼리문법을 정의했다.

• but oracle 9i와 oracle 10g는 with절을 지원하지만 cte를 통한 재귀적 쿼리문법은 지원하지 않는다.
• 하지만 start with와 connect by prior를 이용한 전용 문법이 있다.

인접목록의 단점 극복 : 대안 트리모델 사용

1) 경로열거 방법

조상 노드들을 각 노드의 속성으로 저장한다. 예를들어 디렉터리 구조도 경로열거 방법이다.

/usr/local/lib/ 에서 local의 부모는 usr이고 자식은 lib이다.

기존 테이블에 parent_id 대신 path컬럼을 추가한다.

• 예를 들어 답글#7의 조상을 찾으려면 다음과 같이 한다. 답글#7의 path는 '1/4/6/7'이다.이런 방법은 처음봄.. oracle에서도 되려나.. like 패턴을 컬럼을 가지고도 만들 수 있구나..
• 아무튼 이렇게 하면 예상했던것처럼 1/%, 1/4/%... 처럼 부모의 노드를 전부 가져오게 됨.
  자식 노드는 모두가 아는대 쿼리다. 해당 쿼리의 where절에서 like 조건을 뒤집어 주면 된다.
• select * from comments c where '1/4/6/7' like c.path || '%'

새로운 노드를 추가하는것도 쉽다. 댓글 7에 새로운 노드 추가하기.

update comments
    set path = (select path from comments where  comment_id = 7)
        || -- insert성공한 댓글의 comment_id 가져오기 || '/'
        where comment_id = -- insert성공한 댓글의 comment_id

2) 중첩 집합

각 노드를 추가하고 삭제하는경우가 많을때에는 지양하자. 노드를 추가하고 이동하는것은 왼쪽, 오른쪽 값을 재 계산해야 되기 때문에 복잡하다.

서브트리를 쉽고 빠르게 조회하는 것이 중요할 때 잘 맞는다.

(저자님.. 그렇다면 왜 댓글을 예로 들었나요...ㅠ)

각 노드가 자신의 부모를 저장하는 대신 자기 자손의 집합에 대한 정보를 저장한다.

자손으로 타고 내려갈땐 nsleft에 값을 할당하고, 올라올때는 nsright에 값을 할당한다.

예를들어,

현재 노드의 nsleft와 nsright가 각각 범위가 된다. 테이블을 풀스캔하면서, nsleft가 6과 13사이인 노드는 모두 #4노드의 자손노드이다.

SELECT C2.*
FROM Comments as c2
    JOIN Comments as c2
    ON c2.nsleft BEETWEEN c1.nsleft AND c1.nsright
WHERE c1.comment_id = 4

(위의 쿼리에서 왜 조인을 썼을까 했는데 c1의 nsleft와 nsright를 가져오기 위함이고, 이 범위에 해당하는 레코드만 가져오기 위함이였다.)

조상노드를 구하는 방법은,

책의 설명이랑 쿼리랑 다른건지, 아니면 내가 이해를 잘못한건지(!) 책속의 쿼리는 c1의 nsleft를 가져와서, c2를 훑는다.. 범위에 해당하는지...

SELECT C2.*
FROM Comments as c1
    JOIN Comments as c2
    ON c1.nsleft BEETWEEN c2.nsleft AND c2.nsright
WHERE c1.comment_id = 6

중첩 집합모델의 강점중 하나는 자식을 가진 노드를 삭제했을때, 그 자손들이(삭제한 노드의 자식노드) 삭제한 노드의 부모의 자손이 된다는 점이다. -> 노드를 삭제하더라도 계층구조를 유지하기 쉽다.

중첩 집합모델의 단점

(중첩 집합모델을 보면서 가장 의아했던점은, 자손노드나 조상노드를 쉽게 가져올 수 있지만, 어떻게 depth를 구분할 수 있는지 였다.그러니까, #4 라는 댓글의 하위의 모든 댓글들을 가져올 수 있지만, 댓글, 대댓글, 대댓글을 어떻게 구분해서 화면에 보여줄수 있을지 혼란스러웠다.)

--> 아 알았다. nsleft로 orde by해서 구분한다..

하지만, 단점은 바로 특정 노드의 바로 하위에 위치하는 자식노드나, 바로 상위에 위치하는 부모노드를 구하기 어렵다는것이다. join을 한번 더 걸어야 한다.

SELECT parent.*
FROM Comment AS c --기준이 되는 Comment
    JOIN Comment AS parent
        ON c.nsleft BETWEEN parent.nsleft AND parent.nsright
    LEFT JOIN Commen AS in_beetween
        ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright
        AND in_between.neleft BETWEEN parent.nsleft AND parent.nsright
WHERE c.comment_id = 6
    AND in_between.comment_id IS NULL

인접모델 에서는 쉬웠었는데.....ㅜㅜ

또한, 예상했던것처럼 노드의 추가, 이동하는것도 다른 모델을 사용하는것보다 어렵다. 새로운 노드를 추가 한 경우 그 주변 노드의 nsleft와 nsright 값을 업데이트 해줘야 하기 때문.. 또한 이 과정이 없으면 트리구조를 표시하는데 문제가 생긴다....

UPDATE Comment
SET nsleft = CASE WHEN nsleft >= 8 THEN nsleft+2 ELSE nsleft END,
    nsright = nsright +2
WHERE nsright >= 7

-- 답글 #5에 대댓글 달기..
INSERT INTO Comment (nsleft, nsright, author, comment)
VALUE (8, 9, 'Fran', 'Me too!');