JOIN 안에 SUB쿼리를 JOIN으로 바꿔보자

이 글을 쓰는 이유는 예전 프로젝트를 진행하면서 특정 테이블의 GROUP을 만들어서 특정 값을 만든 후 추가 속성으로 다른 값을 그룹화 해야하는 경우가 발생하였는데 그때 SUB쿼리를 쓰고 POST MAN으로 호출시 성능이 많이 떨어지는 경험을 할 수 있었습니다.

 

어제 특정 코딩테스트 대회를 참여하면서 다른 분이 쿼리문에 대해서 질문을 하셨고 저도 예전에 문제를 풀면서 생각했던 것이라서 포스팅하기로 했습니다.

DATABASE

CREATE TABLE USER(
	ID INT,
	CODE_NUMBER VARCHAR(20),
	primary key(ID)
);
    
CREATE TABLE CODE_AMOUNT(
	ID INT,
	FROM_CODE_NUMBER VARCHAR(20),
	TO_CODE_NUMBER VARCHAR(20),
	AMOUNT INT,
	primary key(ID)
);    

INSERT INTO CODE_AMOUNT VALUES(1,"RED","BLUE",300);
INSERT INTO CODE_AMOUNT VALUES(2,"RED","GREEN",600);
INSERT INTO CODE_AMOUNT VALUES(3,"BLUE","YELLO",300);
INSERT INTO CODE_AMOUNT VALUES(4,"RED","YELLO",200);
INSERT INTO CODE_AMOUNT VALUES(5,"GREEN","BLUE",1100);
INSERT INTO CODE_AMOUNT VALUES(6,"YELLO","GREEN",800);    

대충 다음과같은 간단한 테이블과 인풋값을 만들었습니다. 그림으로 표현해보자면  다음과같이 두개의 테이블입니다.

 

id는 각각의 테이블의 기본키입니다. 이를 제외하고 code_number은 "CODE_AMOUNT" 테이블의 form_code_number 또는 to_code_number 입니다. 이해하셧겠지만 특정 코드가 다른코드에게 amount값이 이동했을때의 값 입니다.

 

제가 구하고싶은 것은 특정 code_number의 amount의 흐름의 총합입니다.

예를 들어서  CODE_AMOUNT의 값이

(1,"RED","BLUE",300);
(2,"YELLO","RED",500);

이라고 가정한다면 RED는 1번 에서 -300 , 2번 에서 +500 을 이루면서 총 +200이라는 값을 가지게 됩니다.

 

즉 SELECT 문으로 알고싶은것은 USER의 흐름의 확인 입니다.

 

1. 가장 원초적인 코드를 짜보자.

SELECT A.CODE_NUMBER , IFNULL(SUM(B.AMOUNT),0) - IFNULL((SELECT SUM(AMOUNT) FROM CODE_AMOUNT WHERE FROM_CODE_NUMBER = A.CODE_NUMBER),0)
FROM USER AS A LEFT JOIN CODE_AMOUNT AS B 
ON A.CODE_NUMBER = B.TO_CODE_NUMBER GROUP BY(A.CODE_NUMBER);

 

잘 나옵니다. 하지만 아시다시피 SELECT 절안에 또하나의 서브쿼리가 존재합니다. 이런경우에는 똑같은 테이블에 N개의 CODE_NUMBER가 존재한다면 총 N*N번 N^2번읜 풀스캔이 발생합니다. 서브쿼리 조건문에서 말이죠.(물론 MYSQL의 내부인덱싱으로 인해서 최적화는 되겟지만 그래도 많은 수의 쿼리를 사용한다면 속도적으로는 문제가 있어보입니다.)

2.  구룹화된 테이블 두개를 만들고 다시 한번더 조인.

2-1  TO_CODE_NUMBER을 기준으로하는 JOIN 결과

SELECT A.CODE_NUMBER , IFNULL(SUM(B.AMOUNT),0)
FROM USER AS A LEFT JOIN CODE_AMOUNT AS B
ON A.CODE_NUMBER = B.TO_CODE_NUMBER GROUP BY(A.CODE_NUMBER);

2-2 FROM_CODE_NUMBER을 기준으로 JOIN한 결과

SELECT A.CODE_NUMBER , IFNULL(SUM(B.AMOUNT),0)
FROM USER AS A LEFT JOIN CODE_AMOUNT AS B
ON A.CODE_NUMBER = B.FROM_CODE_NUMBER GROUP BY(A.CODE_NUMBER);

 

자 이제 두개의 JOIN을 만들고 보니깐 위 테이블에서 밑의 테이블을 빼니 처음 테이블이랑 결과가 동일한 것을 알 수있습니다.

 

따라서 다음과같이 위의 쿼리들을 각각 하나의 테이블로 만들어 다시 join한 테이블에서 값을 가져옵니다.

SELECT A.CODE_NUMBER, A.sum - B.sum
FROM(SELECT A.CODE_NUMBER , IFNULL(SUM(B.AMOUNT),0) AS sum
		FROM USER AS A LEFT JOIN CODE_AMOUNT AS B
		ON A.CODE_NUMBER = B.TO_CODE_NUMBER GROUP BY(A.CODE_NUMBER)) as A 
    join (SELECT A.CODE_NUMBER , IFNULL(SUM(B.AMOUNT),0) AS sum
		FROM USER AS A LEFT JOIN CODE_AMOUNT AS B
ON A.CODE_NUMBER = B.FROM_CODE_NUMBER GROUP BY(A.CODE_NUMBER)) as B on A.CODE_NUMBER = B.CODE_NUMBER;

 

아직 튜닝 기술이 많이 부족해보이고 코드 가독성은 떨어저보이지만 확실한 것은 서브쿼리를 쓰는 것보다 속도적인 측면에서는 많이 향상됩니다. 이유는 JOIN을 3번 하는것으로 N*N의 풀스캔을 방지할 수 있습니다. 데이터의 수가 커질수록 기존의 쿼리는 문제가 많아보입니다.

 

 

이것과 다른 예지만

# 종목 정보 - 19년 4월 거래금액을 추가 - 서브쿼리
SELECT  T1.STK_CD ,T1.STK_NM
        ,(SELECT SUM(A.VOL) FROM HISTORY_DT A
          WHERE A.STK_CD = T1.STK_CD
          AND A.DT >= STR_TO_DATE('20190401','%Y%m%d')
          AND A.DT < STR_TO_DATE('20190501','%Y%m%d')) VOL_1904
FROM    STOCK T1
WHERE   T1.STK_NM IN ('삼성전자','서울반도체')
ORDER BY T1.STK_CD; 

특정 SELECT절에서 sum값을 구하기위해서 sub쿼리를 사용하여 sum값을 구합니다.

# 종목 정보 - 19년 4월 거래금액을 추가 – 조인으로 처리
SELECT  T1.STK_CD ,T1.STK_NM ,SUM(T2.VOL) VOL_1904
FROM    STOCK T1
        INNER JOIN HISTORY_DT T2
           ON (T2.STK_CD = T1.STK_CD
               AND T2.DT >= STR_TO_DATE('20190401','%Y%m%d')
               AND T2.DT < STR_TO_DATE('20190501','%Y%m%d'))
WHERE   T1.STK_NM IN ('삼성전자','서울반도체')
GROUP BY T1.STK_CD ,T1.STK_NM
ORDER BY T1.STK_CD;

다음과 같이 join으로 바꾸는 것으로 같은 결과를 낼 수있지만 내부적으로 전혀 다르게 돌아갑니다.

 

위의 코드는

wikidocs.net/99597

위키독스

 

결론은 서브쿼리를 사용할 때가 좋은 경우도 있습니다. 하지만 서브쿼리의 쿼리문 자체의 레코드수가 많은 경우에는 다음과같이 테이블로 변환해서 레코드수를 줄인후 사용한다면 성능면에서 높은 효과를 가져올 것이라고 기대합니다.