MySql 계층적 조회 쿼리

MySQL 에는 안타깝게도 Oracle 의 start with, connect by 를 지원하는 함수가 없다... 

때문에 아래와 같이 function 을 만들어서 사용한다.

예제 테이블) test.servers_group

create table test.servers_group 

(

 group_idx int auto_increment,     // P.K

 group_name varchar(40),           // group name

 group_depth int,                       // level

 group_order int,                        // group 순서

 parent_idx int,                          // parent group_idx

 manager_idx,                           // 관리자 idx (test.managers_info.idx)

 primary key(group_idx) 

)

---------------------------------------------------------------------------------------------------------------------------

>> Create Function 

CREATE FUNCTION start_with_connect_by(value INT) RETURNS INT

NOT DETERMINISTIC

READS SQL DATA

BEGIN

        DECLARE _id INT;

        DECLARE _parent INT;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;

        SET _id = -1;

        IF @id IS NULL THEN

                RETURN NULL;

        END IF;

        LOOP

                SELECT  MIN(group_idx)

                INTO    @id

                FROM    test.servers_group

                WHERE   parent_idx = _parent

                        AND group_idx > _id;

                IF @id IS NOT NULL OR _parent = @start_with THEN

                        SET @level = @level + 1;

                        RETURN @id;

                END IF;

                SET @level := @level - 1;

                SELECT  group_idx, parent_idx

                INTO    _id, _parent

                FROM    test.servers_group

                WHERE   group_idx = _parent;

        END LOOP;

END

---------------------------------------------------------------------------------------------------------------------------

>> 사용

SELECT  CONCAT(REPEAT('   ', group_depth - 1), CAST(sg.group_idx AS CHAR)) AS group_idx, group_name, parent_idx, group_depth, manager_idx

FROM    (

        SELECT  start_with_connect_by(group_idx) AS id, @level AS level

        FROM    (

                SELECT  @start_with := 0,

                        @id := @start_with,

                        @level := 0

                ) vars, test.servers_group

        WHERE   @id IS NOT NULL

        ) sg2

JOIN    test.servers_group sg

ON      sg.group_idx = sg2.id;

---------------------------------------------------------------------------------------------------------------------------

>> 결과

===========================================================================================

Function 만들기 다른 예제 

CREATE TABLE test.dept_code ( 

code VARCHAR(50), 

name VARCHAR(50), 

parent_code VARCHAR(50) 

);

INSERT INTO test.dept_code VALUES( 'A001', '연구소' , '');

INSERT INTO test.dept_code VALUES( 'A002', '솔루션사업본부' , '');

INSERT INTO test.dept_code VALUES( 'A003', '기술지원부', '' );

INSERT INTO test.dept_code VALUES( 'A004', '개발1팀' , 'A001');

INSERT INTO test.dept_code VALUES( 'A005', '개발2팀' , 'A001');

INSERT INTO test.dept_code VALUES( 'A006', 'OJT' , 'A005');

CREATE FUNCTION GET_DEPT_TREE(value VARCHAR(50)) RETURNS VARCHAR(50) 

NOT DETERMINISTIC

READS SQL DATA

BEGIN

        DECLARE _code VARCHAR(50);

        DECLARE _parent VARCHAR(50);

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @code = NULL;

        SET _parent = @code;

        SET _code = '';

        

        IF @code IS NULL THEN

                RETURN NULL;

        END IF;

       LOOP

                SELECT  MIN(code)

                INTO    @code

                FROM    test.dept_code

                WHERE   parent_code = _parent

                        AND code > _code;

                IF @code IS NOT NULL OR _parent = @start_with THEN

                        SET @level = @level + 1;

                        RETURN @code;

                END IF;

                SET @level := @level - 1;

                SELECT  code, parent_code

                INTO    _code, _parent

                FROM    test.dept_code

                WHERE   code = _parent;

        END LOOP;

END

SELECT  dc.code, CONCAT(REPEAT('       ', dc2.lev-1), name) AS name, parent_code, CAST(dc2.lev AS CHAR) AS lev

FROM    (

        SELECT  GET_DEPT_TREE(code) AS code, @level as lev

        FROM    (

                SELECT  @start_with := '',

                        @code := @start_with,

                        @level := 0

                ) vars, test.dept_code

        WHERE   @code IS NOT NULL

        ) dc2

JOIN    test.dept_code dc

ON      dc.code = dc2.code;

아래 다른 방법도 있다. (물론 그리 좋은 방법은 아니다...)

-------------------------------------------------------------------------------------------

- 단순 업데이트형

예전에 많이 쓰였던 비교적 간단한 형태의 계층형 로직을 생각하자면, 글에 답변 달 때 그 위치 위(혹은 아래)의 모든 글이 한칸씩 이동하도록 하면 된다. 

예를 들면, 글을 쓸 때마다 일련번호를 주고, 2번글에 답변을 달면, 2번 윗글은 글번호를 모두 하나씩 더해 주구요(즉 2->3, 3->4, update 문을 이용하면 한번의 쿼리로 이것이 가능하다). 

그리고 답변글에 2라는 번호를 주면 되는 방법이었다

(상당히 무식한 방법이다.) 다음은 그 방법으로 구현할 경우를 보여주고 있습니다

 

질문글만 올라온 경우

글번호	제목
3	세번째로 올라온 글
2	두번째로 올라온 글
1	첫번째로 올라온 글

 

답변이 달리는 경우

글번호	제목
4	세번째로 올라온 글
3	두번째로 올라온 글
2	두번째 글의 답변으로 올라온 글
1	첫번째로 올라온 글

 

이 방식은 ASP 초기에 조금 쓰였던 방법이다. 

그런데, 만약 글이 1만개 인데, 마지막 글에 답변을 한다고 생각해 보면. 

첫 번째글부터 마지막 글까지 1만개 레코드에 글번호를 1씩 더해주어야만 했다. 

1만개의 레코드를 바꾸는 것은 아무리 빠른 컴퓨터라 해도 몇 초가 걸릴 수 있다.

(웹에서 몇 초는 대단히 중요합니다. 백 명이 동시 접속한다면 이론적으로 몇 백초가 걸리는 정도의 부하가 발생하기 때문이다), 현재는 이 방법을 추천하지 않고 있다.

 

이와 같은 로직은 레코드를 추가 할때마다, 그 순서를 update문으로 바꾸기 때문에 '업데이트형'이라고 보통 불다. 1만개의 글에, 마지막에 답변할 사람은 거의 없기때문에, 이 로직을 이용한다고 게시판이 매우 느려지는 것은 사실 아니다. 보통 최근 글에 답변하기 때문에, 많은 행이 업데이트 되진 않는다. 하지만, 어쨌든 비효율적인 것은 사실인지라, 최근에는 위와 같은 로직은 거의 사용하지 않고 대신 일부만 업데이트하는 방식을 사용하고 있다. 

 

 

- 비 업데이트형 (문자열 형식 사용)

일부 게시판 (특히 PHP나 JSP 게시판)은 문자열(varchar) 형으로 트리 형태를 나타내는 필드를 하나두어 답변형 게시판을 구현하기도 했었다.

즉, 문자열 필드는 주(root) 글이면 빈 문자열인데, 답변이 달릴 때마다 a,b,c,d ... 형태로 글자(편의상 알파벳으로 설명)로 번호를 메긴다. 해당 글의 답변에 대해선 그에 덧붙여서 a,b,c,d ... 식으로 글자를 추가한다.

글번호	답변모양	제목
3		세번째로 올라온 글
2		두번째로 올라온 글
2	a	두번째로 올라온 글의 답변
2	aa	두번째로 올라온 글의 답변의 답변
2	aaa	두번째로 올라온 글의 답변의 답변의 답변
2	ab	두번째로 올라온 글의 답변2
1		첫번째로 올라온 글

 

이 방식에선 답변할 때 업데이트가 필요없다. 예를 들어, 위의 답변형 형태에서 "두번째로 올라온 글의 답변의 답변"에 답변한다면, 글 번호가 2이고, "답변모양"이 "aa"로 시작하는 것의 최대값을 찾고(위에서는 "aaa"). 그리고, 찾은 "답변모양" 필드의 마지막 글자에 +1 - 즉 "aab" - 해서 그 값으로 레코드를 삽입하는 것이다. 

아마도, "글번호"와 "답변모양"을 합쳐서 인덱스(혹은 기본키)를 주었을 것이므로(꼭 인덱스를 주어야 합니다) 최대값을 찾는 것은 매우 빠를 것입니다. 목록보기에선 글번호 역순(desc), 답변모양 제순서(asc)로 레코드를 가져오면 된다( select ... order by 글번호 desc, 답변모양 asc )

 

이러한 방식에선 답변할 때 업데이트가 필요 없으니, 답변하는 것이 매우 빠르답니다. 단점이라면 varchar 형 필드가 필요해서 글 목록 부분에서 약간 느려질 수 있다는 것(사실, 이것은 DB에 따라 다릅니다. MS SQL이라면 거의 차이가 없을 것 같다), 그리고 약간의 추가 공간이 필요하다는 정도. 해서, 사실 이 방법도 그리 아주 나쁜 방법은 아니다.

 

 

- 일부 업데이트형. 이 게시판의 로직

약간의 제한을 사용해서 오히려 성능을 높이는 방법을 살펴 보자. 우리가 제작할 게시판에서는 하나의 글에 답변할 수 있는 글의 수에 제한을 두었다(성능을 고려한 설정). 

설명을 위해 편의상 1000 개로 한다고 해보자. 그리고, 글번호(예제 게시판에선 thread라는 필드로 되어있다)를 띄엄띄엄, 그 수만큼, 즉 1000 단위로 주게 하는 것.

글번호(thread)	제목
3000	세번째로 올라온 글
2000	두번째로 올라온 글
1000	첫번째로 올라온 글

 

그리고, 답변이 달리는 경우는 각 글별로 업데이트를 해서 정렬하게 할 것이다. 

즉, 글번호가 2000번인 녀석의 하위로 답변이 달리면 그넘들의 글 번호는 1999, 1998, 1997... 이렇게 주어지게 되는 것이구요. 총 999 개의 답변이 달릴 수가 있게 되는 것. 

1000개로 답변을 제한한다는 제약이 있기는 하지만, 아직까지 사이트를 운영하면서 하나의 글에 999개의 답변이 달리는 경우는 보지 못했다. 심지어는 99개의 답변이 달리는 경우도 보지 못했다. 

이제, 위에서 두 번째 글에 답변을 한다고 생각해 보자. 그러면, 원래 그 글밑에 답변으로 달려져 있던 글들을 즉, 글번호가 1999~1000 사이인 글의 글번호를 하나씩 줄여 나가게 합니다. 

그리고, 새로 들어오는 글의 번호를 1999번으로 해서 레코드를 추가하면 두번째 글과 세번째 글 사이에 글이 추가되게 되는 것이다. 

 

그런데 어느 글이 주(root) 글인지 답변인지, 답변이라면 어느 글에 대한 것인지는 어떻게 구분할 수 있을까? 그것은 글 별로, 글이 몇 단계 째인지를 나타내는 필드를 두면 된다. 즉, 주 글은 0, 주 글에 대한 답변이라면 1, 답변에 대한 답변이면 2와 같은 식으로 값을 주면, 그 수만큼 제목 왼쪽을 띄어쓰기 해서 답변 달린 모습으로 만들 수 있게 되는 것이다. 우리가 제작할 게시판에선 이러한 목적의 컬럼이 depth라는 이름의 필드로 되어있다. 그래서 글을 추가하면 다음과 같은 모습이 되어지는 것이다.

글번호(thread)	depth	제목
3000	0	세번째로 올라온 글
2000	0	두번째로 올라온 글
1999	1	두번째로 올라온 글의 답변1
1000	0	첫번째로 올라온 글

 

글을 추가할 때마다 같은 방식으로 하고, 목록을 보여줄때는 글 번호를 기준으로 역순으로 정렬을 하면 순서대로 계층적으로 보여지게 된다. 그리고, depth 가 0 이상인 경우 (주 글이 아닌) 답변 글이니, 그 depth 수 만큼 왼쪽에 공간을 두어 답변을 표시하면 우리가 기대한 대로 화면을 출력할 수 있게 되는 것이죠. 같은 방식으로 여러 행을 답변하면 어떻게 될지 생각해 보자.

 

글번호(thread)	depth	제목
3000	0	세번째로 올라온 글
2000	0	두번째로 올라온 글
1999	1	두번째로 올라온 글의 답변2
1998	2	두번째로 올라온 글의 답변의 답변2
1997	1	두번째로 올라온 글의 답변1
1000	0	첫번째로 올라온 글

 

이처럼, 중간에 갭을 두어서, 중간 부분만 업데이트하면 됩니다.예를 들면, 여기서 "두번째로 올라온 글의 답변2"에 답변을 단다면 1001~1998 사이에 있는 두개의 레코드가 업데이트 되어 한칸씩 뒤로 물러나게 한다는 것입니다(자기가 들어갈 자리보다 높은 번호를 가진 글들은 업데이트를 할 필요가 없겠죠? 예를 들면, 지금의 경우는 1998 번보다 높은 글들은 업데이트를 할 필요가 없다는 겁니다). 다시 말해서, 자신의 글번호보다 낮은 글번호들 중 1000보다는 글번호가 큰 것들만 업데이트 한다는 것이다. 그리고, 새로운 답변글은 1998라는 번호를 갖게 되고, depth 는 2가 된다는 것. depth는 답변을 달 글의 depth에 하나를 더한 값으로 계산해 주면 된다..

  

어쨋든 이번 로직은 첫 번째로 소개한 로직에 비해서 업데이트하는 양이 대폭 감소한 것을 알 수 있습니다. 100만개의 글이 있다 쳐도, 그 글에 속한 답변만 업데이트하면 되니까요. 우리의 경우라면 하나의 답변이 달릴 경우 최대 999개만 업데이트 하면 되겠죠? 물론, 이 방식도 하나의 글에 예컨대 500개의 답변이 달려있는 경우라면 비효율적일 수 있습니다. (최대 500개의 레코드를 업데이트 해야하므로) 그러나,

 

1) 답변이 그렇게 많이 달리는 일은 거의 없다. 
    아무리 활성화 된 게시판이라 해도 많아야 10개, 보통은 1~2개의 답변이 달린다. 
2) 답변을 다는 일은 게시판 목록을 보는 것보다 빈도수가 적다. 
    약 1/10 이내일 듯 하기에, 그렇다면 목록 보는 쪽에서 속도를 낼 수 있도록 설계하는 것이 낫다.

 

해서, 이 방법이 비교적 쓸만한 방법이라고 볼 수 있다.

 

위에서 설명했던 두 번째 로직(비 업데이트 형)의 경우는 컬럼의 타입을 varchar로 하는데, 이는 목록보기에서 약간은 속도 저하가 있을 수 있습니다. (상황에 따라, 데이터베이스에 따라 다르긴 합니다만) 한편, 이 로직은 int 형 필드 하나로 정렬하기 때문에, 업데이트하는 약간의 속도저하는 목록에서 충분히 커버를 할 수가 있다고 본다.