저번주에 고객사로부터 한가지 문의사항이 들어왔다.

Q: "OO페이지의 로딩이 너무 느려요. 로딩이 빠르게 되도록 할 순 없을까요?"

그래서 해당 페이지의 쿼리를 확인해보았다. 테이블 구조나 쿼리를 그대로 옮겨올 순 없기에 간단하게 예시를 작성해보았다.

SELECT A.PKID
	, A.TITLE
    , NVL(B.NAME, '탈퇴한 회원'), B.NAME
    , (SELECT COUNT(*) FROM REPLY WHERE P_PKID = B.PKID) AS COUNT1
FROM BOARD B LEFT OUTER JOIN MEMBER M
	ON B.MEM_NO = M.PKID

대충 이런식으로 짜여진 쿼리였다. (실제 쿼리는 JOIN된 테이블도 5~6개 정도 되었고 스칼라 서브쿼리 절도 6개 정도 된다.)

처음 봤을 땐 JOIN절에서 어딘가 문제가 있나하고 살펴보았다. 실행 계획도 살펴보고 쿼리의 일부분을 떼어다가 실행해보고 일부분을 빼고 실행해보기도 하고 바꿔가며 확인해보았다.

그렇게 확인해본 결과 JOIN절에서는 이상이 없다는 것이다. 그렇다면 남은 것은 SELECT절에서 사용되는 COUNT 구문의 스칼라 서브쿼리. 그놈만 쏙 빼고 실행했더니 200초 넘게 걸리던 쿼리가 한 2초 만에 실행되는 것이다.

그렇다면 저걸 어떻게 바꿔야 속도가 잘 나올까?

위 질문을 해결하기 위해서는 스칼라 서브쿼리, 인라인 뷰, 일반적인 서브쿼리를 이해할 필요가 있었다.

보통 서브쿼리를 쓰는 형식에는 크게 3가지가 있다. SELECT 절에 사용하는 스칼라 서브쿼리, FROM 절에 사용하는 인라인 뷰, WHERE 절 등에 사용하는 일반적인 서브쿼리이다.

1. 스칼라 서브쿼리(Scala Subquery)
함수처럼 한 레코드 당 정확히 하나의 값만을 리턴하는 서브쿼리. 기본으로 Outer Join이 적용되어 있다.

2. 인라인 뷰(Inline View)
FROM절에 사용되는 서브쿼리를 지칭. 마치 뷰처럼 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 인라인 뷰(View)라는 이름이 붙었다. 그래서 일반적인 뷰를 정적 뷰(Static View), 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다.

3. 서브쿼리(Subquery)
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 지칭. 위 두가지 경우 이외에 쓰이는 서브쿼리를 보통 통틀어서 일컫는다.

위 3가지 서브쿼리는 이미 데이터베이스라는 것을 공부할 때 접해본 단어들이지만 정확히 어떤 경우에 어떤 것을 쓰며 성능이 어떻게 되는지 까지는 자세히 알지 못하였다.

일단 결론부터 말하자면 위 쿼리의 속도가 안 나오던 이유는 스칼라 서브쿼리의 단점인 반복 수행 떄문이다. 왜? 개발환경에서는 분명 속도가 잘 나왔다. 하지만 운영환경만 가면 200초나 걸리는 것이었다. 데이터 양이 많으면 많아질수록 느려진다는 뜻이다.

일반적으로 스칼라 서브쿼리를 위와 같이 쓰는 경우는 WHERE절에 반복해서 들어가는 B.PKID와 같은 값이 동일한 값, 혹은 거의 변하지 않는 값일 때이다. 스칼라 서브쿼리는 쿼리 수행 횟수를 최소화하기 위해서 입력값과 출력값을 내부 캐시에 저장해둔다. 스칼라 서브쿼리가 실행될 때 일단 입력값을 내부 캐시에서 찾아보고 거기 있으면 해당하는 출력값을 리턴한다. 내부 캐시에 없을 때만 재수행하는 것이다.

위의 스칼라 서브쿼리는 쿼리가 수행될 때마다 B.PKID 값이 계속 달라지는 경우였다. 그러니 속도가 느릴 수 밖에 없었다. 그러면 저것을 어떻게 고쳐야 빨라질까? 답은 LEFT OUTER JOIN이었다.

SELECT A.PKID
	, A.TITLE
    , NVL(B.NAME, '탈퇴한 회원')
    , NVL(R.COUNT1,0)
FROM BOARD B LEFT OUTER JOIN MEMBER M
	ON B.MEM_NO = M.PKID
    LEFT OUTER JOIN (SELECT COUNT(*) COUNT1, P_PKID FROM REPLY GROUP BY P_PKID) R
    ON B.PKID = R.P_PKID

처음 썼던 쿼리와 달라진 점은 스칼라 서브쿼리에 있던 구문을 LEFT OUTER JOIN으로 뺀 것이다. 그런데 JOIN하는 테이블이 조금 다른 것을 알 수 있다. 왜 REPLY 테이블이 아니라 새로이 SELECT절을 써줬을까?

만약 COUNT(*)이 아니라 그냥 P_PKID나 다른 컬럼만 구한다면 REPLY와 JOIN해도 상관 없었다. 하지만 나는 P_PKID 별로 COUNT한 값이 필요했고 그러기 위해서는 P_PKID를 키값으로 GROUP BY 해줘야했다.

(추가로 JOIN을 할 때 조건이 있다면 미리 조건을 건 테이블과 JOIN을 하면 성능이 더 나아진다고 한다. 이건 경우마다 달라서 나중에 해당되는 경우가 있다면 포스팅하도록 해야겠다.)

아무튼 위와 같이 수정을 하여 200초 넘게 걸리던(…) 쿼리를 2~3초 대로 줄였다. 내가 봐도 이전 쿼리의 실행 시간은 말도 안 됐었다. 내가 고객이었다면 당장 키보드를 부셨을지도… 어쨌거나 이번 기회를 통해 서브쿼리에 대해 다시 한번 공부해볼 수 있었던 시간이었다.