<범위 조건을 같다 조건으로 바꿔주는 기준일자 테이블>
‘이어지는 이야기 .03’에서 기준일자 테이블에 대해 설명했다. 이번에는 기준일자 테이블을 활용해 성능을 개선하는 경우를 살펴보자.
SQL BOOSTER 에 이어지는 이야기들입니다.~!
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.
www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877
SQL BOOSTER
프로젝트 성공을 위한 SQL 필독서. 프로젝트를 진행하는 순서처럼 구성되어 있다. 프로젝트 투입을 위해 필요한 SQL 기술을 설명하고, 성능 테스트를 위해 필요한 기술을 설명한 뒤에 마지막으로
www.aladin.co.kr
설명의 편의상 반말체로 작성한 점 양해바랍니다. pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.
복합인덱스를 이용해 데이터를 찾을 때, 선두 컬럼이 같다(=) 조건으로 사용되어야 뒤쪽 컬럼도 데이터 검색에 효율적으로 사용될 수 있다. (이 내용은 SQL BOOSTER의 ‘6.3.1절. 복합 인덱스 – 컬럼 선정과 순서#1’에 설명되어 있다.) 바꿔 말하면, 복합 인덱스의 선두 컬럼에는 범위 조건이, 뒤쪽 컬럼에는 같다(=) 조건이 사용된 SQL이 있다면, 선두 컬럼의 범위 조건을 같다(=) 조건으로 변경해 성능을 향상 시킬 수도 있다. 여기서는 일자 테이블을 이용해 날짜에 대한 범위 조건을 같다(=) 조건으로 변경해 볼 예정이다.
이와 같은 내용은 이미 많은 사람들이 알고 있는 기술이다. 정확히 기억나지 않지만, 어느 책에선가 본 기억도 있으며, 구글에서 MySQL 성능 관련 자료를 찾다가도 발견한 적이 있다. 직접 실습해보고 싶은 독자들을 위해 ‘이어지는 이야기’에서 다루어 보기로 했다. SQL BOOSTER는 실제 연습 해볼 수 있게 구성되어 있는 장점을 가지고 있다.
특정 고객들의 고객별 2017년 1월 주문 금액을 조회하는SQL을 만들어 보자. 여기서는 T_ORD_BIG 테이블을 사용한다.
T_ORD_BIG의 인덱스를 확인해보자. ‘이어지는 이야기 .02’에서 인덱스 리스트를 조회하는 SQL은 이미 살펴보았다. T_ORD_BIG에는 아래와 같이 인덱스가 구성되어 있다.
T_ORD_BIG의 인덱스 리스트
INDEX_OWNER TABLE_NAME INDEX_NAME IND_COLS
============ ========== ============= =====================
ORA_SQL_TEST T_ORD_BIG X_T_ORD_BIG_1 ORD_YMD
ORA_SQL_TEST T_ORD_BIG X_T_ORD_BIG_2 RNO
ORA_SQL_TEST T_ORD_BIG X_T_ORD_BIG_3 ORD_YMD,CUS_ID
ORA_SQL_TEST T_ORD_BIG X_T_ORD_BIG_4 CUS_ID,ORD_YMD,ORD_ST
ORA_SQL_TEST T_ORD_BIG X_T_ORD_BIG_TEST ORD_SEQ
많은 인덱스가 있지만, 여기서는 ORD_YMD, CUS_ID 순서로 구성된 X_T_ORD_BIG_3 인덱스만 사용할 수 있다고 가정한다. 주어진 인덱스에서 최대의 성능이 나오도록 튜닝 연습을 할 필요가 있다. 인덱스로 모든 것을 해결하기 보다는 주어진 상황에서 먼저 해결책을 찾는 것이 좋다. (인덱스로 계속 덧칠한 시스템을 볼 때면 너무 가슴이 아프다.)
특정 고객들의 고객별 주문 금액을 조회하는 SQL은 아래와 같다. X_T_ORD_BIG_3 인덱스를 사용하도록 힌트를 주었다.
[SQL-1] 특정 고객들의 고객별 2017년 1월 주문 금액 조회하기
SELECT /*+ INDEX(T2 X_T_ORD_BIG_3) */
T1.CUS_ID
,SUM(T2.ORD_AMT) ORD_AMT
FROM M_CUS T1
,T_ORD_BIG T2
WHERE T2.CUS_ID = T1.CUS_ID
AND T1.CUS_GD = 'A'
AND T1.RGN_ID = 'A'
AND T2.ORD_YMD LIKE '201701%'
GROUP BY T1.CUS_ID;
실행계획은 아래와 같다.
[실행계획-1] 특정 고객들의 고객별 2017년 1월 주문 금액 조회하기
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 |00:00:07.48 | 736K|
| 1 | SORT GROUP BY NOSORT | | 1 | 18 |00:00:07.48 | 736K|
| 2 | NESTED LOOPS | | 1 | 540K|00:01:24.55 | 736K|
| 3 | NESTED LOOPS | | 1 | 540K|00:01:21.99 | 196K|
|* 4 | TABLE ACCESS BY INDEX ROWID| M_CUS | 1 | 20 |00:00:00.01 | 3 |
| 5 | INDEX FULL SCAN | PK_M_CUS | 1 | 90 |00:00:00.01 | 1 |
|* 6 | INDEX RANGE SCAN | X_T_ORD_BIG_3 | 20 | 540K|00:00:54.67 | 196K|
| 7 | TABLE ACCESS BY INDEX ROWID | T_ORD_BIG | 540K| 540K|00:00:02.38 | 540K|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("T1"."RGN_ID"='A' AND "T1"."CUS_GD"='A'))
6 - access("T2"."ORD_YMD" LIKE '201701%' AND "T2"."CUS_ID"="T1"."CUS_ID")
filter(("T2"."ORD_YMD" LIKE '201701%' AND "T2"."CUS_ID"="T1"."CUS_ID"))
실행계획의 총 Buffers는 736K다. 실행계획의 6번 항목을 보면 X_T_ORD_BIG_3을 이용해 NL 조인을 처리하고 있다. X_T_ORD_BIG_3 인덱스는 ORD_YMD, CUS_ID 순서로 구성되어 있다. 조인 처리시에 ORD_YMD에는 범위(LIKE) 조건이, CUS_ID에는 같다(=) 조건이 사용된다. 선두 컬럼이 범위 조건으로 사용되어 CUS_ID 조건은 조인에서 좋은 역할을 할 수 없게 된다.
사실 위 SQL은 CUS_ID, ORD_YMD 순서로 구성된 X_T_ORD_BIG_4 인덱스를 사용하는 것이 성능이 더 좋다. 여기서는 성능 개선 연습을 위해 무조건 X_T_ORD_BIG_3 인덱스만 사용하기로 한다.
X_T_ORD_BIG_3 인덱스를 효율적으로 사용하려면 ORD_YMD가 같다(=) 조건으로 사용되어야 한다. 이를 위해 기준일자 테이블을 활용할 수 있다. 아래 SQL을 보자.
[SQL-2] 특정 고객들의 고객별 2017년 1월 주문 금액 조회하기 – 기준일자 테이블 사용
SELECT /*+ NO_MERGE(T1) INDEX(T2 X_T_ORD_BIG_3) */
T1.CUS_ID
,SUM(T2.ORD_AMT) ORD_AMT
FROM (
SELECT A.CUS_ID ,B.BAS_YMD
FROM M_CUS A
,C_BAS_YMD B
WHERE A.CUS_GD = 'A'
AND A.RGN_ID = 'A'
AND B.BAS_YM = '201701'
) T1
,T_ORD_BIG T2
WHERE T2.CUS_ID = T1.CUS_ID
AND T2.ORD_YMD = T1.BAS_YMD
GROUP BY T1.CUS_ID;
[SQL-2]는 M_CUS와 C_BAS_YMD를 카테시안-조인 한 후에 T_ORD_BIG과 조인 처리하고 있다. 조회할 고객별로 1월 한 달의 일자 데이터를 만든 후에 T_ORD_BIG에 ORD_YMD와 CUS_ID를 같다(=) 조건으로 공급해주는 것이다. 이로 인해 ORD_YMD가 선두 컬럼인 X_T_ORD_BIG_3 인덱스를 효율적으로 사용해 조인을 처리할 수 있게 된다. 실행계획을 살펴보자.
[SQL-2] 특정 고객들의 고객별 2017년 1월 주문 금액 조회하기 – 기준일자 테이블 사용
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 |00:00:03.97 | 544K|
| 1 | HASH GROUP BY | | 1 | 18 |00:00:03.97 | 544K|
| 2 | NESTED LOOPS | | 1 | 540K|00:00:04.28 | 544K|
| 3 | NESTED LOOPS | | 1 | 540K|00:00:00.20 | 4057 |
| 4 | VIEW | | 1 | 620 |00:00:00.01 | 8 |
| 5 | MERGE JOIN CARTESIAN | | 1 | 620 |00:00:00.01 | 8 |
|* 6 | TABLE ACCESS FULL | M_CUS | 1 | 20 |00:00:00.01 | 6 |
| 7 | BUFFER SORT | | 20 | 620 |00:00:00.01 | 2 |
|* 8 | INDEX RANGE SCAN | X_C_BAS_YMD_01 | 1 | 31 |00:00:00.01 | 2 |
|* 9 | INDEX RANGE SCAN | X_T_ORD_BIG_3 | 620 | 540K|00:00:00.14 | 4049 |
| 10 | TABLE ACCESS BY INDEX ROWID| T_ORD_BIG | 540K| 540K|00:00:03.71 | 540K|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(("A"."RGN_ID"='A' AND "A"."CUS_GD"='A'))
8 - access("B"."BAS_YM"='201701')
9 - access("T2"."ORD_YMD"="T1"."BAS_YMD" AND "T2"."CUS_ID"="T1"."CUS_ID")
Buffers가 736K에서 544K로 줄어들었다. 조인은 한 번 늘어났지만, 성능은 오히려 좋아졌다. X_T_ORD_JOIN_3 인덱스의 선두 컬럼인 ORD_YMD를 같다(=) 조건으로 처리했기 때문이다.
항상 그래왔듯이, 실행계획을 보고 실제로 성능이 개선되었는지, 자신이 원하는 방향으로 SQL이 처리되었는지 확인하는 것이 중요하다. 데이터에 따라 성능이 더 나빠질 수도 있으며, 실행계획이 다르게 풀릴 수도 있다. 여기서 사용한 예제는 필자가 몇 번의 수고 끝에, 기준일자 테이블을 사용하면 성능이 좋은 경우를 억지로 찾아낸 것이다. 중요한 건 이러한 방법이 있다는 것을 기억하고, 상황에 따라 응용해 보고, 실행계획까지 확인하는 것이다.
오늘은 여기까지입니다. 감사합니다.
'SQL > SQL BOOSTER' 카테고리의 다른 글
| SQL BOOSTER 이어지는 이야기 .10 (0) | 2020.10.27 |
|---|---|
| SQL BOOSTER 이어지는 이야기 .08 (0) | 2020.10.26 |
| SQL BOOSTER 이어지는 이야기 .07 (0) | 2020.10.26 |
| SQL BOOSTER 이어지는 이야기 .06 (0) | 2020.10.26 |
| SQL BOOSTER 이어지는 이야기 .04 (0) | 2020.10.25 |
| SQL BOOSTER 이어지는 이야기 .03 (0) | 2020.10.25 |
| SQL BOOSTER 이어지는 이야기 .02 (0) | 2020.10.25 |
| SQL BOOSTER 이어지는 이야기 .01 (0) | 2020.10.25 |
