테이블 Random 액세스 최소화 튜닝, 인덱스 스캔범위 최소화 - SQL전문가 가이드 p.628~p.637

*I/O 튜닝의 핵심 원리

 하나, Random 액세스 발생량을 줄인다.

 둘, Sequential 액세스에 의한 선택 비중을 높인다.

 

1. 테이블 Random 액세스 최소화 튜닝 - SQL전문가 가이드 p.628~p.630

(하나, Random 액세스 발생량을 줄인다.)

1) 인덱스 칼럼 추가
2) Covered Index
3) Include Index
4) IOT, 클러스터형 인덱스, 클러스터 테이블 활용
5) 수동으로 클러스터링 팩터 높이기

 

2. 인덱스 스캔범위 최소화 - SQL전문가 가이드 p.631~p.637

(둘, Sequential 액세스에 의한 선택 비중을 높인다.)

1) 인덱스 선행 칼럼이 범위조건일 때의 비효율

    - 인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를

스캔하는 단계에서 비효율이 발생한다.

2) 범위조건을 In-List로 전환

   - 인덱스 선두 칼럼의 between 조건을 IN-List 조건으로 바꿀 때 IN-List 개수가 적어야 한다. IN-List 개수가 많을 때는, between 조건 때문에 리프 블록을 추가로 스캔하는 비효율보다 IN-List 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있고, 인덱스 높이(height)가 높을 때 특히 그렇다.

   - Oracle이라면 범위조건에서 Index Skip Scan이 유용할 수 있다. Index Skip Scan은 인덱스 선두 칼럼이 누락됐을 때뿐만 아니라 부등호, between, like 같은 범위검색 조건일 때도 사용될 수 있다. 

 

 (가) 1차 단계 - BETWEEN

 SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

 FROM 매물아파트매매

 WHERE 아파트시세코드 = 'A0100'

 AND 평형 = '59'

 AND 평형타입 = 'A'

 AND 인터넷매물 BETWEEN '1' AND '2'

 ORDER BY 입력일 DESC

 ;

 

 (나) 2차 단계 - IN

SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

FROM 매물아파트매매

WHERE 아파트시세코드 = 'A0100'

AND 평형 = '59'

AND 평형타입 = 'A'

AND 인터넷매물 IN ('1',  '2')

ORDER BY 입력일 DESC

;

 3) 조건을 2개 이상 사용할 때의 비효율
 - 인덱스 구성 [회사+지역+상품명]일 때, 아래와 같이 범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.

 

(가) 1차 단계

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE 회사 = :com

AND 지역 LIKE :reg || '%'

AND 상품명 LIKE :prod || '%'

;

 

(나) 2차 단계

  A) CASE 1 - 만약 지역 칼럼에 대한 검색조건이 입력되지 않을 수도 있어 LIKE 연산자를 사용할 것이라면 SQL을 아래와 같이 만들어 사용하는 것이 좋다.

    <SQL1>

    SELECT 고객ID, 상품명, 지역, ...

    FROM 가입상품

    WHERE 회사 = :com

    AND 상품명 LIKE :prod || '%'

    ;

   <SQL2>

   SELECT 고객ID, 상품명, 지역, ...

   FROM 가입상품

   WHERE 회사 = :com

   AND 지역 = :reg

   AND 상품명 LIKE :prod || '%'

   ;

 

 B) CASE 2 - UNION ALL을 사용하는 방법도 있다.

 - 기존의 인덱스 구성 하에서, UNION ALL 상단 쿼리는 기존과 동일한 비효율을 안은 채 수행되겠지만 하단 쿼리만큼은 최상으로 수행될 수 있다. 만약 UNION ALL 상단 쿼리까지 최적화하려면 [회사+상품명] 순으로 구성된 인덱스를 하나 더 추가해야 한다.

 - 인덱스를 새로 추가하는데 부담이 있으면 기존 인덱스 순서를 [회사+상품명+지역] 순으로 변경하는 것을 고려할 수 있는데, 그럴 경우(UNION ALL) 하단 쿼리를 처리할 때 불리해진다. 따라서 이는 상품명 조건에 입력되는 값의 선택도에 따라 결정할 사항이다.                   

 

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE :reg IS NULL 

AND 회사 = :com

AND 상품명 LIKE :prod || '%'

UNION ALL

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE :reg IS NOT NULL

AND 회사 = :com

AND 지역 = :reg

AND 상품명 LIKE :prod || '%'

;