mysql 계층 쿼리 정리 및 분석

mysql version : 5.7 을 사용하였습니다. 주로 오라클을 사용해서 개발해온 저에게 mysql 문법 을 분석하는 일은 쉽지 않았습니다. mysql 함수를 분석하면서 배운 것들이 많아서 공유하고자 합니다. 노드랑 행이라는 용어를 혼용해서 사용하였습니다. node = row 로 해석하셔도 무방합니다.

oracle 에서는 계층 구조의 데이터를 가져올 때 start with connect by 를 사용합니다. mysql 은 위와 같은 쿼리 문이나 재귀함수도 지원하지 않습니다.웹상에서 관련 정보 수집시 자주 검색되는 방법은 함수와 사용정의변수를 활용하여 데이터를 가져오는 것이었습니다.

주로 참고한 블로그 첨부합니다.

EXPLAIN EXTENDED

위의 블로그에서 설명하고있는 계층 쿼리를 테이블, 컬럼 등을 유지하고 조금씩 수정해서 그대로 사용가능하지만, 제가 원하는 결과는 oracle 의 rollup? 과 같은 기능입니다. 출력된 계층쿼리에서 전체 평균값을 상단에 출력하는 것이 였습니다. 그래서 좀 더 깊이 있게 응용해보기 위해서 해당 쿼리에서 사용하고 있는 함수를 분석해보았습니다.

복잡한 쿼리 문을 결과물만 보고 구조를 파악하기 힘들어서 임시 테이블 temp_log(table) 을 생성하였습니다.

분석하려고 수정한 함수는 아래와 같습니다.

loop 횟수를 체크하려고 @loop_cnt 를 추가하고 temp_log(_id, _parent, min) 에 log 를 입력하는 쿼리를 추가하였습니다.

 

마찬가지로 @loop_cnt(사용자정의변수) 를 추가해주었다. 이 같이 쓰는 변수들이 사용자 정의 변수라고 한다.

위의 쿼리를 돌리면 행수만큼 test_k(테이블)에 있는 row 수만큼 실행이 되고 temp_log(테이블) 에 같은 수의 row 가 입력된다. 

data 를 조회할때마다 log 가 계속 쌓여서 데이터 삭제 및 인덱스를 계속 초기화 하였다. 

delete from temp_log;

ALTER TABLE temp_log AUTO_INCREMENT=1;

출력된 data 와 log 를 종합하여 엑셀로 만들어서 첨부한다.

mysql_계층쿼리.xls

1 번째 쉬트가 종합한 데이터이고 

2 번재 쉬트가 test_k 에 있는 원본 데이터 이다. 

여기서 내가 중요하게 본건 loop count 이다. 처음 예상과는 달리 loop 횟수가 그렇게 크지 않았다. 

각 행마다 평균 2~3 회 loop 를 돌았다. 

함수를 간략하게 설명하면 이전 행의 @id 를 기반으로 해서 다음 행의 데이터를 출력한다.

처음에는 (@id := 0,) 으로 id 가 0으로 초기화 되어 있다. 

다음 행에서는 @id 에는 그 이전 행의 @id 값이 그대로 사용된다.  그래서 2번째 행에서는 함수 상단에 set _parent = @id 로 인해서 

_parent 는 1 이다. 이 값을 가지고 loop 1회전 시 min 값은 null 이고 _parent 값은 0이 아니라서 retrun 을 하지 않고 다시 loop 2회전을 한다. 

2회전 시에는 

SELECT  id, parent

INTO    _id, _parent

FROM    test_k

WHERE   id = _parent;

쿼리 문 때문에 상위 노드 로 다시 돌아와서 같은 계층의 노드 (동료 노드? level 1 row? ) 를 가져오게 된다. 

함수 내부를 설명하면 (SET _id = -1;) _id 값은 계속 -1 로 초기화 된다. 하지만 (SET _parent = @id;) _parent 는 사용자 변수를 사용한다. 

위 쿼리를 분석하면서 자료구조의 깊이 우선 탐색이 생각 났다. 우선 하위노드 끝까지 내려갔다가 없으면 다시 올라와서 다시 내려가는 탐색 이다. 

구글에서 검색한 이미지 첨부한다. 

위 이미지 와 비슷한 방식으로 진행 된다. 

이 함수를 응용하여 하위 노드들의 평균을 자신의 노드에 저장하는 기능을 만들려 했지만... 힘들것같다.

함수 내부에 자식노드를 그룹화 하여 가져 오고 있을거라 생각 했는데 이전 행의 데이터만 가지고 다음행을 가져오고 있었다... 

그래서 해당 테이블에 데이터 입출력이 있을때마다 상위 노드들의 값을 업데이트 해주면 되겠다고 생각하고 

트리거를 만들었다.

입력, 삭제, 수정 될 시에 최대 level - 1 만큼 update 문을 날리면 상위노드들은 하위노드의 평균 값을 가지게 된다. 

update 문은 아래와 같다.