[SQL] - 계층 구조 쿼리는 성능을 위해 RECURSIVE하게 짜지 말자

[오늘의 작업]

A. 상품에 대해 RECUSIVE하게 짠 계층구조 쿼리와

B. 상품에 대해 무식하게 짠 계층구조 쿼리

이 둘의 성능 비교를 해보자

 

[전제 조건]

- B(상품에 대해 무식하게 짠 계층구조 쿼리)는 업무적으로 상품의 최대 DEPTH가 정해진 상태여야한다. (나는 상품테이블에 데이터를 최대 3DEPTH로 넣었음)

- MYSQL 사용함

- 상품 테이블(PRODUCT)과 상품 그룹테이블(PRODUCT_GROUP)간의 관계 성립 (별도 설명 없을 예정.쿼리로 이해바람)

 

[테이블 정보]

- 상품테이블명: PRODUCT

- 상품그룹테이블명:PRODUCT_GROUP

- 상품그룹테이블과 상품테이블 조인 컬럼: PRODUCT_GROUP_ID

 

아래의 데이터는 

두가지 방법으로 조회한 동일한 형태의 계층구조 결과이다.

 

[조회 결과]

[A. RECUSIVE하게 짠 계층구조 쿼리] - 무한 DEPTH 가능

WITH RECURSIVE CTE AS (
	/*부모 지정*/
	SELECT 
	    PRODUCT_GROUP_ID, 
	    PRODUCT_GROUP_NM, 
	    PARENT_PRDT_GROUP_ID, 
	    ORDER_SEQ,
	    CONVERT( LPAD(CONVERT(ORDER_SEQ, CHAR(4)), 4, '0'), CHAR(100)) AS SEQ_CHAR,
	    1 AS DEPTH_NO
	FROM PRODUCT_GROUP X
	WHERE PARENT_PRDT_GROUP_ID = 0
	
	UNION ALL
	
	/*A: 현재 정보이자 부모의 자식, B: 부모 정보 (A는 재귀호출됨)*/
	SELECT
	    A.PRODUCT_GROUP_ID, 
	    A.PRODUCT_GROUP_NM, 
	    A.PARENT_PRDT_GROUP_ID, 
	    A.ORDER_SEQ,
	    CONCAT(B.SEQ_CHAR, LPAD(CONVERT(A.ORDER_SEQ, CHAR(4)), 4, '0')) AS SEQ_CHAR,
	    B.DEPTH_NO+1  AS DEPTH_NO
	FROM PRODUCT_GROUP A
	INNER JOIN CTE B 
	ON A.PARENT_PRDT_GROUP_ID = B.PRODUCT_GROUP_ID 
)
SELECT 
    PRODUCT_GROUP_ID,
    PRODUCT_GROUP_NM,
    PARENT_PRDT_GROUP_ID,
    ORDER_SEQ,
    SEQ_CHAR,
    DEPTH_NO
FROM CTE C
ORDER BY SEQ_CHAR

[B. 상품에 대해 무식하게 짠 계층구조 쿼리] - 최대 3DEPTH

SELECT
	P1.PRODUCT_GROUP_ID,
	P1.PRODUCT_GROUP_NM,
	P1.PARENT_PRDT_GROUP_ID,
	P1.ORDER_SEQ,
	CASE WHEN P2.PRODUCT_GROUP_ID IS NULL THEN LPAD(CONVERT(P1.PRODUCT_GROUP_ID, CHAR(4)), 4, '0')
		WHEN P3.PRODUCT_GROUP_ID IS NULL THEN CONCAT(LPAD(CONVERT(P2.PRODUCT_GROUP_ID, CHAR(4)), 4, '0'), LPAD(CONVERT(P1.PRODUCT_GROUP_ID, CHAR(4)), 4, '0'))
		ELSE CONCAT(LPAD(CONVERT(P3.PRODUCT_GROUP_ID, CHAR(4)), 4, '0'), LPAD(CONVERT(P2.PRODUCT_GROUP_ID, CHAR(4)), 4, '0'), LPAD(CONVERT(P1.PRODUCT_GROUP_ID, CHAR(4)), 4, '0'))
	END AS SEQ_CHAR,
	CASE WHEN P2.PRODUCT_GROUP_ID IS NULL THEN 1
		WHEN P3.PRODUCT_GROUP_ID IS NULL THEN 2
		ELSE 3
	END AS DEPTH_NO
FROM PRODUCT_GROUP P1
LEFT OUTER JOIN PRODUCT_GROUP P2
ON P1.PARENT_PRDT_GROUP_ID = P2.PRODUCT_GROUP_ID 
LEFT OUTER JOIN PRODUCT_GROUP P3
ON P2.PARENT_PRDT_GROUP_ID = P3.PRODUCT_GROUP_ID 
ORDER BY SEQ_CHAR

 

 

 

 

[성능 비교] - 실행 계획 PLAN 뜨기

[A. RECUSIVE하게 짠 계층구조 쿼리] - 무한 DEPTH 가능

[B. 상품에 대해 무식하게 짠 계층구조 쿼리] - 최대 3DEPTH

 

type(=access type)필드가 ALL이면 해당 테이블을 Full Scan했다는 의미

rows필드가 69라는 것은 해당 작업을 하는데 사용된 row수를 의미하며

69 * 1 * 1

= P1테이블에서 69개의 ROW를 서치해서

= P2테이블에서 1개의 ROW를 다뤘고

= P3테이블에서 1개의 ROW를 가지고 서치했으므로  69 * 1 * 1가 되는 것이다.

 

A. RECUSIVE하게 짠 계층구조 쿼리 = 69 * 69 * 69 * 6

B. 상품에 대해 무식하게 짠 계층구조 쿼리 = 69 * 1 * 1

 

PLAN 비용은 A가 월등히 높다.

현재 가라데이터의 Full Scan한 데이터가 69 row 밖에 되지않아

recursive 쿼리도 빠르게 조회되는데

DB는 데이터를 가져올때

작은 데이터는 컴퓨터의 메모리에 저장해놓았다가 가지고 와서 사용하는데

큰 데이터는 데이터 전체를 메모리에 적재할 수가 없어서 추가로 컴퓨터의 하드디스크 영역(file I/O)을 사용한다.

컴퓨터의 하드디스크 영역을 사용할때 메모리에 조금씩 데이터를 떼다가 올려놓고 가지고 와서 사용하는 방식이라

작동 시간이 상대적으로 더 걸린다.