튜닝 예시 #3

* 부제 : 무분별한 뷰 사용을 지양해야하는 이유 & 힌트가 필요한 이유

• 속도개선 : 2483ms → 64ms

-- 원본쿼리
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT G.상품번호, G.가격, G.kind, G.상품애칭확인, G.상품애칭, 상품명
     , [dbo].[fn_Get상품이미지경로](g.상품번호, 3) as 상품이미지경로
     , dbo.fn_Get할인액(g.상품번호) as 할인액
     , (g.가격-dbo.fn_Get할인액(g.상품번호)) as 할인된가격
     , C.brand_en, C.brand_seq, G.bcomcat, G.btopcat, G.bmidcat
     , (select cnt_month from 상품별판매통계 where 상품번호=c.상품번호) as 판매수량
     , C.Pcondition
  FROM 상품뷰 g
 inner join 상품카테고리매핑 c on g.상품번호=c.상품번호
 WHERE c.Site=N'S'
   and g.판매상태=1
   and g.일본할인액 > 0
 ORDER BY g.Kind asc,C.Pcondition ASC, 판매수량 DESC, C.상품번호 DESC
OFFSET 0 ROWS  FETCH NEXT 201 ROWS ONLY
-- 2483ms
-- 상품뷰는 일본상품테이블과 전체상품뷰를 조인하여 상품상태, 일본판매상태, 번역상태를 필터링하고 있음.
-- 전체상품뷰는 전체상품 테이블에서 특정 카테고리들만 필터링하고 있는 뷰.
-- 플랜은
-- 상품카테고리매핑를 드라이빙 테이블로 잡고 테이블 풀스캔으로 상품카테고리매핑.Site가 'S'인건을 필터링한 상품번호들로
-- 전체상품테이블의 특정카테고리들과 상품상태가 있는 인덱스(커버드)를 이용하여 nl조인하여 seek 및 필터링 후 
-- 다시 전체상품을 pk lookup하여 nl조인 하면서 판매중상태 필터링 및 select 컬럼 추출을 하고
-- 일본상품테이블의 일본판매상태, 번역상태가 있는 인덱스(커버드)를 이용하여 seek 및 필터링 후 나온 결과와 nl 조인 후
-- 다시 일본상품테이블을 pk lookup하여 select 컬럼 추출을 하고 소팅

-- 튜닝쿼리1 : 뷰만 해체
SELECT a.상품번호, b.가격, a.kind, b.상품애칭확인, b.상품애칭, a.상품명
     ,  [dbo].[fn_Get상품이미지경로](a.상품번호, 3) as 상품이미지경로
     , dbo.fn_Get할인액(a.상품번호) as 할인액
     , (b.가격-dbo.fn_Get할인액(a.상품번호)) as 할인된가격
     , C.brand_en, C.brand_seq, a.bcomcat, a.btopcat, a.bmidcat
     , (select cnt_month from 상품별판매통계 where 상품번호=a.상품번호) as 판매수량
     , C.Pcondition
  from 전체상품 a with(nolock)
  join 일본상품 b with(nolock) on a.상품번호 = b.상품번호
  join 상품카테고리매핑 c with(nolock) on b.상품번호 = c.상품번호
 WHERE a.ComCat IN (823, 282, 833, 671, 735, 848, 892, 999, 991, 844, 845, 846, 940, 1007, 948, 971, 1050, 1056, 1075, 1082, 1092, 1109, 1134, 1141, 847, 982, 1170, 1188, 1196, 847, 1089, 1207
                , 1212, 847, 286, 1222, 1226, 1229, 1240, 1248, 1255, 1258, 1264, 1272, 1273, 1245)
   and a.c = 1
   and a.상품상태 = '1'
   and b.번역상태 = N'1'
   and b.일본판매상태 = N'1'
   and b.일본할인액 > 0
   and c.site = N'S'
 ORDER BY a.Kind asc, c.Pcondition ASC, a.판매수량 DESC, a.상품번호 DESC
OFFSET 0 ROWS  FETCH NEXT 201 ROWS ONLY
-- 345 ms
-- 플랜은 마찬가지로 상품카테고리매핑을 드라이빙 테이블로 잡고 진행하지만
-- 그 후 일본상품을 먼저 조인하고 나온 결과와 전체상품을 조인하여 속도를 85%이상 줄인 상태

-- 튜닝쿼리2 : 힌트 추가
SELECT a.상품번호, b.가격, a.kind, b.상품애칭확인, b.상품애칭, a.상품명
     ,  [dbo].[fn_Get상품이미지경로](a.상품번호, 3) as 상품이미지경로
     , dbo.fn_Get할인액(a.상품번호) as 할인액
     , (b.가격-dbo.fn_Get할인액(a.상품번호)) as 할인된가격
     , C.brand_en, C.brand_seq, a.bcomcat, a.btopcat, a.bmidcat
     , (select cnt_month from 상품별판매통계 where 상품번호=a.상품번호) as 판매수량
     , C.Pcondition
  from 전체상품 a with(nolock, index(IX_전체상품_32))
  join 일본상품 b with(nolock) on a.상품번호 = b.상품번호
  join 상품카테고리매핑 c with(nolock) on b.상품번호 = c.상품번호
 WHERE a.ComCat IN (823, 282, 833, 671, 735, 848, 892, 999, 991, 844, 845, 846, 940, 1007, 948, 971, 1050, 1056, 1075, 1082, 1092, 1109, 1134, 1141, 847, 982, 1170, 1188, 1196, 847, 1089, 1207
                  , 1212, 847, 286, 1222, 1226, 1229, 1240, 1248, 1255, 1258, 1264, 1272, 1273, 1245)
   and a.판매상태 = 1
   and a.상품상태 = '1'
   and b.번역상태 = N'1'
   and b.일본판매상태 = N'1'
   and b.일본할인액 > 0
   and c.site = N'S'
 ORDER BY a.Kind asc, c.Pcondition ASC, a.판매수량 DESC, a.상품번호 DESC
OFFSET 0 ROWS  FETCH NEXT 201 ROWS ONLY
OPTION(FORCE ORDER)
-- 64ms
-- 하지만 궁극적으로 전체상품을 판매상태와 상품상태로 필터링하면 5천건 미만만 존재하게 되고
-- 전체상품을 드라이빙테이블로 잡게 하고(그 경우 비효율적인 인덱스를 사용하여 적합한 인덱스로 고정)
-- 이후 일본상품, 상품카테고리매핑 순서로 조인을 하게 하여 100ms 미만으로 나오도록 변경

* 튜닝 포인트
1. OPTION(FORCE ORDER)
 - 오라클과 달리 MySQL과 SQLServer는 특정 테이블을 드라이빙 테이블로 지정하는
   힌트는 존재하지 않아서 STRAIGHT_JOIN(MySQL), FORCE ORDER(SQLServer)와 같이
   쿼리 순서대로 조인 순서를 정하는 힌트를 활용 

2. with(index(IX_전체상품_32))
 - 특정인덱스를 사용하도록 지정 

3. 뷰 해체
 - 튜닝예시 #2의 경우 union all로 된 뷰였다면
   이번 예시는 일반적으로 많이 사용하는 형태의 뷰로
   옵티마이저가 올바른 플랜을 세우는데 혼동을 유발하는 전형적인 케이스

4. 힌트 사용
 - 조인의 복잡성 및 인덱스 통계 업데이트 상황등에 따라 옵티마이저는 최적의 판단을 내리려고 하지만
   아직은 딥러닝AI 수준이 아니므로 이와 같이 올바른 플랜이 안될 수 있으므로
   힌트를 통해 바르게 잡아주는 케이스.
   물론 데이터들의 카디널리티는 지속적으로 변하기 마련이므로 꾸준한 모니터링과 튜닝이 필요함.
   SQLServer과 MySQL의 경우는 힌트의 강제성이 많이 떨어지므로
   적합한 힌트가 아닐 경우 힌트를 줘도 무시하는 존재함.