0. 개선을 하게된 계기.
프로덕트에서 사용중인 쿼리가 너무 느려서 개선을 하게되었다.
해당 쿼리는 WORK_TYPE, WORK_YEAR에 따라서 해당 업무에 맞는 템플릿과 그 템플릿에 맞는 데이터가 나오도록 동적쿼리로 짜여진 범용 조회 쿼리였는데 여러 업무 도메인 중 리스크관리라는 도메인에서 쿼리 작동시간이 13초가 넘어가게되어 해당 도메인 전용으로 쓸 수 있도록 분리를 진행하게 되었다.
1.개선대상 도메인에 대해서 알아보자
리스크관리 도메인은 어떤 기업에서 수행해야하는 여러 업무들 중 발생가능한 위험요소를 관리하는 도메인이다. 특정 업무 수행시 발생가능한 리스크들을 사전에 숙지하고 해당 리스크 예방을위해 진행하고자하는 프로세스와 리스크를 매핑을 하여 상급자에게 줄어든 위험도에 대해 결재를 올린 업무단을 조회하는 도메인이다.
쉽게말해 특정업무 A를 진행하는데 있어 발생가능성이 있는 r, p, q라는 리스크가 있는데 업무A 진행시 프로세스a를 진행하게 되면 리스크 r, p, q의 위험도가 줄어든다는것을 상급자에게 결재를 올리고 해당 결재내역을 조회를 하는 영역이다.
- 개선 대상 쿼리를 우선 알아보자.
리스크관리 조회시 발생하는 개선전 쿼리는 다음과 같다.
SELECT
*
FROM (
SELECT
A.DOC_ID
, A.COMP_CD
, A.CORP_CD
, A.WORK_TYPE
, A.WORK_YEAR
, A.WORK_DEPT_CD
, G.NAME WORK_DEPT_NAME
, A.USER_ID
, H.NAME WORK_USER_NAME
, A.WORK_STATUS
, A.WORK_USE_YN
, DATE_FORMAT(A.WRITE_DT, "%Y-%m-%d") WORK_WRITE_DT
, DATE_FORMAT(A.MOD_DT, "%Y-%m-%d") WORK_MOD_DT
, A.ATTACH_NO
, A.POPUP_YN
, IFNULL(F.ATT_ORG_NAME, '') ATT_ORG_NAME
, IFNULL(F.ATT_FULL_PATH, '') ATT_FULL_PATH
, '' GROUP01
, '' GROUP02
, '' GROUP03
, '' GROUP04
, '' GROUP05
, IFNULL(`TMP_CODE_1`, '') 'TMP_CODE_1'
, IFNULL(`TMP_CODE_2`, '') 'TMP_CODE_2'
, IFNULL(`TMP_CODE_3`, '') 'TMP_CODE_3'
, IFNULL(`TMP_CODE_4`, '') 'TMP_CODE_4'
FROM WORKMASTER A
INNER JOIN WORKDEFINE B
ON A.COMP_CD = B.COMP_CD
AND A.CORP_CD = B.CORP_CD
AND A.WORK_TYPE = B.WORK_TYPE
AND A.WORK_YEAR = B.WORK_YEAR
LEFT JOIN ATTACHFILEINFO F
ON A.ATTACH_NO = F.ATTACH_NO
LEFT JOIN DEPTLANGUAGE G
ON A.COMP_CD = G.COMP_CD
AND A.CORP_CD = G.CORP_CD
AND A.WORK_DEPT_CD = G.DEPT_CD
AND G.LANG_CD = 'KOR'
LEFT JOIN USERLANGUAGE H
ON A.COMP_CD = H.COMP_CD
AND A.WORK_USER_ID = H.USER_CD
AND H.LANG_CD = 'KOR'
LEFT JOIN WORKDETAIL DA
ON A.COMP_CD = DA.COMP_CD
AND A.CORP_CD = DA.CORP_CD
AND A.WORK_TYPE = DA.WORK_TYPE
AND A.WORK_ID = DA.WORK_ID
LEFT JOIN (
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_1'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_1'
) A1
ON A.COMP_CD = A1.COMP_CD AND A.CORP_CD = A1.CORP_CD AND A.DOC_ID = A1.DOC_ID
LEFT JOIN (
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_2'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_2'
) A2
ON A.COMP_CD = A2.COMP_CD AND A.CORP_CD = A2.CORP_CD AND A.DOC_ID = A2.DOC_ID
LEFT JOIN (
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_3'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_3'
) A3
ON A.COMP_CD = A3.COMP_CD AND A.CORP_CD = A3.CORP_CD AND A.WORK_ID = A3.WORK_ID
LEFT JOIN (
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_4'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_4'
) A4
ON A.COMP_CD = A4.COMP_CD AND A.CORP_CD = A4.CORP_CD AND A.WORK_ID =A4.WORK_ID
WHERE A.COMP_CD = 'TEST' AND A.CORP_CD = 'TESTTEST' AND A.WORK_TYPE = 'RISK_WORK' AND A.WORK_YEAR = '2024'
ORDER BY A.WORL_ID, TEMP_CODE1, TMP_CODE2 DESC
) G
3-1. 개선방안 - 인덱스의 도입
해당 쿼리 설계시에는 잘 짜여진 하나의 쿼리로 여러 도메인에 적용을 하여 페이지를 빨리 찍어내는것에 목표가 있었기 때문에 퍼포먼서는 크게 고려하지 않았는데 (속도가 곧 생명이던 시절..) 요구사항이 복잡해지고 테이블 조인건수가 늘고 결제건수가 많아지다보니 약 조회건수가 500건이 넘어가게 되는 순간 조회시간이 급격하게 증가하게 되었고 이를 해결하기 위해 우선 인덱스부터 도입하기로 했다.
3-1-1.
처음 나오는 일반 join문에서 다국어 처리를 위한 테이블인 DEPTLANGUAGE G, USERLANGUAGE H 와 첨부파일 관리테이블인 ATTACHFILEINFO F의 경우 테이블 내에 데이터수가 그렇게 많지 않고 앞으로도 늘어날 가능성이 없다고 판단하여 이 세 테이블은 제외하였다. 그 다음 WORKMASTER A, WORKDEFINE B, WORKDETAIL DA 테이블에 공통적으로 있는 컬럼들 중 업무가 진행되는 연도를 나타내는 WORK_YEAR, 업무의 타입 WORK_TYPE 을 자세히 보자.
이런식으로 연도별, 업무타입별로 업무가 나뉘고. 해당 개별 업무는 고유의 ID값을 갖게 된다.
년도별, 타입별로 업무가 구분이 되기에 이 두 컬럼이 적합하다고 판단하여 인덱스로 지정하였다.
3-1-2.
다음, 서브쿼리가 들어있는 A1~A4단을 보자.
이 서브쿼리들은 리스크 관리 도메인에서 보여줘야 할 특정 템플릿들을 나타낸다.
TEMPLATE T1, TEMPLATEDETAIL T2을 서로 join 후 WORKMASTER A와 한번 더 join을 하는 방식이다.
이 두 테이블에서 공통적으로 사용하는 컬럼은 WORK_DETAIL_CD이고 리스크 관리단 내에서 발생하는 여러 업무들을 또 분류해둔 타입니다. TEMPLATEDETAIL테이블 기준으로 설명을 하면 예를들어서 RISK_FINANCE는 재무적 리스크, RISK_HUMAN의 경우 사람이 발생 시킬 가능성이 있는 리스크를 분류한것이다. 각 리스크마다 보여줘야할 내용들이 다르다. 제목만 보여줄지, 사례까지 같이 보여줄지는 각 영역마다 다른데 공통분류는 WORK_DETAIL_CD로 하기 때문에 이 컬럼을 인덱스로 지정하였다.
실제 인덱스로 지정한 후에 약간 개선된 모습을 보여줬다.
평균 10초 후반 시간이 걸리는데 약 2초정도 줄이는데 성공한 모습니다. 하지만 아직 10초는 너무 갈 길이 멀다.
3-2. 개선방안 - join문 개선
어쩌면 이미 엉망진창인 서브쿼리 구조탓이 더 클지도 모르겠다. join문 내 select를 호출하는 서브쿼리가 A1단부터 시작하여 A2, A3, A4 이렇게 넷이나 붙어있는데 자세히 보면 이들이 뭔가 대단히 다른걸 호출하지도 않는다.
이들 서브쿼리단만 뜯어서 보면
----------------------------------------------------------
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_1'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_1'
---------------------------------------------------------- A1
----------------------------------------------------------
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_2'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_2'
---------------------------------------------------------- A2
----------------------------------------------------------
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_3'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_3'
---------------------------------------------------------- A3
----------------------------------------------------------
SELECT T1.COMP_CD, T1.CORP_CD, T1.WORK_DETAIL_ID, T2.WORK_ID, T1.TMP_VALUE 'TMP_CODE_4'
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.TMP_GROUP_ID = T2.TMP_GROUP_ID
WHERE T1.COMP_CD = 'TEST'
AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID = 'TMP_4'
---------------------------------------------------------- A4
TEMPLATE과 TEMPLATEDETAIL을 join하는데 TEMPLATE.TEMP_ID만 바꿔서 계속 조인을 하는걸 알 수 있다. 이들을 굳이 네번에 나눠서 select를 한다는건 비효율적이므로 이들을 한번의 select로 불러올 방법을 찾아보게 되었다.
SELECT T1.COMP_CD,
T1.CORP_CD,
ANY_VALUE(T2.DOC_ID) AS DOC_ID,
ANY_VALUE(T1.WORK_DETAIL_ID) WORK_DETAIL_ID,
ANY_VALUE(CASE WHEN T1.TMP_ID = 'TMP_1' THEN T1.TEMP_VALUE ELSE '' END) AS TMP_1,
ANY_VALUE(CASE WHEN T1.TMP_ID = 'TMP_2' THEN T1.TEMP_VALUE ELSE '' END) AS TMP_2,
ANY_VALUE(CASE WHEN T1.TMP_ID = 'TMP_3' THEN T1.TEMP_VALUE ELSE '' END) AS TMP_3,
ANY_VALUE(CASE WHEN T1.TMP_ID = 'TMP_4' THEN T1.TEMP_VALUE ELSE '' END) AS TMP_4
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.WORK_DETAIL_TYPE = T2.WORK_DETAIL_TYPE
AND T1.WORK_DETAIL_ID = T2.WORK_DETAIL_ID
WHERE T1.COMP_CD = 'TEST' AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID IN ('TMP_1', 'TMP_2',
'TMP_3', 'TMP_4')
우선 위와 같이 TMP_ID에 IN 조건을 걸어 한꺼번에 불러오도록 하였고 결과는 다음과 같이 나왔다.
TMP 컬럼마다 한줄씩 나오다 보니 동일한 WORK_DETAIL_ID가 4줄씩 나오게 된다. 이제 이들을 한줄씩 나오게만 하면 될 것 같아 GROUP BY를 적용을 해주고 항상 값이 있는 cell을 보여주게 하기 위해 MAX()도 함께 사용한다.
SELECT T1.COMP_CD,
T1.CORP_CD,
ANY_VALUE(T2.DOC_ID) AS DOC_ID,
ANY_VALUE(T1.WORK_DETAIL_ID) WORK_DETAIL_ID,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_1' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_1,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_2' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_2,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_3' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_3,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_4' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_4
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.WORK_DETAIL_TYPE = T2.WORK_DETAIL_TYPE
AND T1.WORK_DETAIL_ID = T2.WORK_DETAIL_ID
WHERE T1.COMP_CD = 'TEST' AND T1.CORP_CD = 'TESTTEST'
AND T1.TMP_ID IN ('TMP_1', 'TMP_2',
'TMP_3', 'TMP_4')
GROUP BY T1.COMP_CD, T1.CORP_CD, T2.WORK_ID
3-3 적용
3-1과 3-2에서 수정한 내용들을 조합해서 최종적으로 쿼리를 완성시켜보자.
SELECT
A.DOC_ID
, A.COMP_CD
, A.CORP_CD
, A.WORK_TYPE
, A.WORK_YEAR
, A.WORK_DEPT_CD
, G.NAME WORK_DEPT_NAME
, A.USER_ID
, H.NAME WORK_USER_NAME
, A.WORK_STATUS
, A.WORK_USE_YN
, DATE_FORMAT(A.WRITE_DT, "%Y-%m-%d") WORK_WRITE_DT
, DATE_FORMAT(A.MOD_DT, "%Y-%m-%d") WORK_MOD_DT
, A.ATTACH_NO
, A.POPUP_YN
, IFNULL(F.ATT_ORG_NAME, '') ATT_ORG_NAME
, IFNULL(F.ATT_FULL_PATH, '') ATT_FULL_PATH
, IFNULL(`T_TEMPLATES.TMP_1`, '') 'TMP_1'
, IFNULL(`T_TEMPLATES.TMP_2`, '') 'TMP_2'
, IFNULL(`T_TEMPLATES.TMP_3`, '') 'TMP_3'
, IFNULL(`T_TEMPLATES.TMP_4`, '') 'TMP_4'
FROM WORKMASTER A
INNER JOIN WORKDEFINE B
ON A.COMP_CD = B.COMP_CD
AND A.CORP_CD = B.CORP_CD
AND A.WORK_TYPE = B.WORK_TYPE
AND A.WORK_YEAR = B.WORK_YEAR
LEFT JOIN ATTACHFILEINFO F
ON A.ATTACH_NO = F.ATTACH_NO
LEFT JOIN DEPTLANGUAGE G
ON A.COMP_CD = G.COMP_CD
AND A.CORP_CD = G.CORP_CD
AND A.WORK_DEPT_CD = G.DEPT_CD
AND G.LANG_CD = 'KOR'
LEFT JOIN USERLANGUAGE H
ON A.COMP_CD = H.COMP_CD
AND A.WORK_USER_ID = H.USER_CD
AND H.LANG_CD = 'KOR'
LEFT JOIN (
SELECT T1.COMP_CD,
T1.CORP_CD,
ANY_VALUE(T2.WORK_ID) AS WORK_ID,
ANY_VALUE(T1.WORK_DETAIL_ID) WORK_DETAIL_ID,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_1' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_1,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_2' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_2,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_3' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_3,
ANY_VALUE(MAX(CASE WHEN T1.TMP_ID = 'TMP_4' THEN T1.TEMP_VALUE ELSE '' END)) AS TMP_4
FROM TEMPLATE T1
LEFT JOIN TEMPLATEDETAIL T2
ON T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD
AND T1.WORK_TYPE = T2.WORK_TYPE
AND T1.WORK_DETAIL_ID = T2.WORK_DETAIL_ID
WHERE T1.COMP_CD = 'TEST' AND T1.CORP_CD = 'TESTTEST' AND T2.WORK_TYPE = 'RISK'
AND T1.TMP_ID IN ('TMP_1', 'TMP_2',
'TMP_3', 'TMP_4')
GROUP BY T1.COMP_CD, T1.CORP_CD, T2.WORK_ID
) T_TEMPLATES
ON A.COMP_CD = T_TEMPLATES.COMP_CD
AND A.CORP_CD = T_TEMPLATES.CORP_CD
AND A.WORK_ID = T_TEMPLATES.WORK_ID
WHERE A.COMP_CD = 'TEST'
AND A.CORP_CD = 'TESTTEST'
AND A.WORK_TYPE = 'RISK'
AND A.WORK_YEAR = '2024'
AND A.DOC_LAST_YN = 'Y'
ORDER BY WORK_DEPT_NM, TMP_1,TMP_1 DESC;
이전보다 길이도 많이 짧아지고 서브쿼리가 하나로 줄어들어 가독성도 더 좋아진 모습이다
실행시간이 200ms대로 나온다 성공이다
- 느낀점
쿼리를 그냥 짜보기만 했지 타인이 작업했던 쿼리를 뜯어서 해당 도메인에 맞게끔 튜닝을 하는 경험은 이번이 처음이었다. 쿼리를 잘 짜는것도 물론 중요하지만 해당 도메인이 동작하는 자세한 배경과 맥락을 파악하고 해당 업무가 가진 의도를 파악하는게 더 중요하다는 사실을 몸소 느낀 경험이었다. 의도와 맥락에 대해서 다시한번 생각해보는 계기가 되었고 문제가 잘 해결되어 다행이다.