SQL튜닝이론 - 인덱스스캔 효율화 방안

SQL튜닝이론 - 인덱스스캔 효율화 방안

 

인덱스의 선행컬럼을 등치 조건절로 사용하기

 

인덱스 컬럼을 조건절에서 모두 등치(=) 조건으로 사용할 때 인덱스 스캔 효율성이 가장 좋습니다. 리프블록에서 스캔한 인덱스 레코드가 하나의 낭비도 없이 모두 테이블 엑세스로 이어지므로 비효율이 없습니다. 단, 조건절에 사용되는 인덱스 컬럼중 마지막 컬럼은 등치 조건이 아니라도 비효율이 없습니다.

 

-- idx [아파트시세코드 + 평형 + 평형타입 + 인터넷 매물]
WHERE 아파트시세코드 = :a AND 평형 = :b and 평형타입 between :c and :d

 

인덱스 선행 컬럼이 조건절에 없거나 부등호, between, like 같은 범위검색 조건이면 인덱스 스캔의 비효율이 발생합니다. 인덱스를 [인터넷 매물 + 아파트시세코드 + 평형 + 평형타입] 순으로 바꾸고 SQL을 수행하면 인덱스 스캔 범위가 널어집니다.

 

-- idx [인터넷 매물 + 아파트시세코드 + 평형 + 평형타입]
SELECT 층, 평당가, 입력일, 도, 매물구분, 일수, 업소코드
FROM 매물아파트매매
WHERE  아파트시세코드 = 'A010...'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 인터넷매물 BETWEEN '1' AND '3'
ORDER BY 입력일 DESC

 

인덱스 선두 컬럼 인터넷 매물에 BETWEEN 연산자를 사용하면 나머지 조건을 만족하는 레코드가 인터넷 매물별로 흩어집니다. 따라서 조건을 만족하지 않는 레코드까지 스캔하는 비효율이 생깁니다. 위의 쿼리에서 인터넷매물의 값이  '2'인 구간은 후행조건과 관계없이 모든 레코드를 읽게됩니다.

 

BETWEEN을 IN-LIST로 전환

 

선행컬럼을 반드시 BETWEEN 연산자를 통해 비교해야하는 경우, 이를 IN-LIST 로 바꿔주면 효과를 얻는 경우가 많습니다. IN-LIST로 변경하면 인덱스의 수직적 탐색이 3번 발생합니다. 수행 통계를 출력해보면 INDEX RANGE SCAN 단계의 STARTS 항목이 '3' 으로 표시됩니다. 이를 통해 인덱스를 세 번 탐색한다는 사실을 알 수 있습니다.

 

-- idx [인터넷 매물 + 아파트시세코드 + 평형 + 평형타입]
SELECT 층, 평당가, 입력일, 도, 매물구분, 일수, 업소코드
FROM 매물아파트매매
WHERE 인터넷매물 in ('1', '2', '3')
AND 평형 = '59'
AND 평형타입 = 'A'
AND 아파트시세코드 = 'A010...'
ORDER BY 입력일 DESC

--EXECUTION PLAN
SELECT STATEMENT
    INLIST ITERATPR
        TABLE ACCESS BY INDEX ROWID
            INDEX RANGE SCAN

 

아래쿼리와 동일하게 작동합니다. IN-LIST의 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 = 조건으로 검색하므로 인덱스 스캔의 비효율이 없습니다.

 

SELECT 층, 평당가, 입력일, 도, 매물구분, 일수, 업소코드
FROM 매물아파트매매
WHERE 인터넷매물 = '1'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 아파트시세코드 = 'A010...'
union all
SELECT 층, 평당가, 입력일, 도, 매물구분, 일수, 업소코드
FROM 매물아파트매매
WHERE 인터넷매물 = '2'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 아파트시세코드 = 'A010...'
union all
SELECT 층, 평당가, 입력일, 도, 매물구분, 일수, 업소코드
FROM 매물아파트매매
WHERE 인터넷매물 = '3'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 아파트시세코드 = 'A010...'
ORDER BY 입력일 DESC

 

주의점

 

IN-LIST의 개수가 많지 않아야합니다. 특히 루트블록에서 브랜치 블록까지 깊이가 깊을때, 수직적 탐색이 많이 발생하면 오히려 BETWEEN 조건절 보다 더 큰 비효율이 발생합니다.

 

조건에 의해 선택되는 인덱스 레코드가 멀리 떨어져있을때만 유용합니다. 레코드간 거리가 가까우면 BETWEEN 조건으로 인해 흩어지는 범위가 적기때문에 오히려 수직적 탐색으로 발생하는 블록 I/O가 더 비효율을 일으킵니다.

 

정리하면 BETWEEN 조건으로 인덱스 스캔의 비효율이 발생하더라도 인덱스 리프 블록에는 매우 많은 레코드가 담기기 때문에 BLOCK I/O는 적게 발생합니다. 반면 IN-LIST는 LIST 크기에 비례하여 블록 I/O를 일으키므로 기회비용을 따져 결정해야합니다.

 

INDEX SKIP SCAN 활용

 

BETWEEN 조건을 IN-LIST 조건으로 변환하면 도움이 되는 상황에서 조건절 변경없이 힌트만으로 같은 효과를 낼 수 있습니다. INDEX SKIP SCAN을 활용한 방식입니다.

 

SELECT COUNT(*)
FROM 월별고객별판매집계 t
WHERE 판매구분 = 'A'
AND 판매월 between '201801' and '201812'

 

위 쿼리를 최적으로 수행하려면 = 조건인 판매구분이 선두컬럼인 인덱스를 구성해야합니다.

 

create index 월별고객별판매집계_IDX1 on 월별고객별판매집계(판매구분, 판매월);
-- 인덱스를 사용할때의 트레이스로 281개의 블록 I/O가 발생하고 테이블 액세스는 발생하지 않는다.
--     SORT AGGREGATE (CR=281 pr = 0 pw = 0)
--         INDEX RANGE SCAN 월별고객별판매집계_IDX1(cr=281)

 

BETWEEN 조건의 판매월 칼럼이 인덱스 선두인 경우 판매구분이 A인 레코드는 흩어집니다.

 

create index 월별고객별판매집계_IDX2 on 월별고객별판매집계(판매월, 판매구분);

-- 인덱스를 사용할때의 트레이스 결과로, 3090개의 블록I/O가 발생하고 테이블 액세스는 발생하지 않는다.
-- SORT AGGREGATE (CR=3090 pr = 0 pw = 0)
-- INDEX RANGE SCAN 월별고객별판매집계_IDX2(cr=3090)

 

판매구분이 선두인 인덱스의 스캔결과에 비해 많은 블록 I/O가 발생합니다. 이유는 BETWEEN 조건으로 인해 판매구분이 B인 레코드까지 모드 스캔하기 때문입니다. BETWEEN 조건에 사용된 컬럼이 인덱스 선두컬럼인 인덱스를 사용하는 쿼리를 IN-LIST로 변경한 결과는 다음과 같습니다.

 

select /*+index( t 월별고객판매집계_IDX2)*/
count(*)
FROM 월별고객별판매집계 t
where 판매구분 = 'A'
and 판매월 in ('201801','201802' ... '201812')
-- 인덱스를 사용할때의 트래이스 결과로, 281개의 블록I/O가 발생하고 테이블 액세스는 발생하지 않는다.
-- SORT AGGREGATE (CR=314 pr = 0 pw = 0)
-- INLIST ITERATOR (CR=314 pr = 0 pw = 0)
-- INDEX RANGE SCAN 월별고객별판매집계_IDX2(cr=314)

 

인덱스의 수직적 탐색이 12번 발생해 인덱스 브랜치 블록을 12번 탐색했지만 BETWEEN 조건을 사용했을때에 비해 성능이 10배 좋아진것을 확인할 수 있습니다. 마지막으로 힌트를 통해 INDEX SKIP SCAN 으로 유도한 결과입니다.

 

select /*+index_ss( t 월별고객판매집계_IDX2)*/
count(*)
FROM 월별고객별판매집계 t
where 판매구분 = 'A'
and 판매월 between '201801' and '201812'

-- SORT AGGREGATE (CR=300 pr = 0 pw = 0)
-- INDEX SKIP SCAN 월별고객별판매집계_IDX2(cr=300)

 

BETWEEN 연산자를 사용했지만 큰 비효율 없이 300 블록만 읽고 마칩니다. 판매구분이 A인 데이터가 서로 멀리 떨어져있을때, INDEX RANGE SCAN 을 통해 성능을 크게 개선할 수 있습니다.