[4] 인덱스 확장기능 사용법

이 글은 [친절한 SQL 튜닝] 을 학습하고 정리한 글입니다.

 

3번 게시물까지는 Index Range Scan 중심으로 다루었는데

Index Full Scan, Index Unique Scan, Index Skip Scan, Index Fast Full Scan 등 

인덱스 스캔 방식은 다양합니다. 하나하나 알아보겠습니다. 

 

Index Range Scan

B* Tree의 가장 일반적인 액세스 방식인데요

인덱스 Root에서 Leaf 까지 값을 비교하며 수직적으로 탐색한 후에

Leaf에서부턴 필요한 범위만 스캔합니다.

 

인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에서 사용해야 합니다. 

중요한 점은 인덱스 스캔범위와 테이블 액세스 횟수를 얼마나 줄일 수 있냐로 결정됩니다. 

 

select * from emp where deptno = 20; 일 때

deptno에 인덱스가 걸려있으면, 해당 인덱스를 이용해서 Range Scan을 하게 되겠지요.

 

Index Range Scan (출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148220&boardConfigUid=9&boardIdx=140&boardStep=1)

Index Full Scan

수직 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방법입니다. 

검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됩니다. 

 

select * from emp where sal > 2000 order by ename; 

이고 index가 (ename, sal) 로 걸려있다고 하면

sal이 인덱스의 선두 컬럼이 아니여도

테이블을 통채로 스캔하는거보다 인덱스를 스캔하는 것이 크기가 더 작으니 

Index Full Scan 을 이용해서 찾습니다. 

 

Index Full Scan (출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148220&boardConfigUid=9&boardIdx=140&boardStep=1)

 

Index Unique Scan

Unique index를 = 조건으로 탐색하는 경우에 작동하게 됩니다. 

 

index가 empno에만 있는 경우

select * from emp where empno = 7788;

의 경우엔 해당 인덱스에 해당하는 값만 딱 가져오면 되기 때문에

수직 탐색 이후 수평 탐색을 하지 않아도 됩니다. 

 

Index Unique Scan (출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148220&boardConfigUid=9&boardIdx=140&boardStep=1)

 

인덱스를 이용한다고 해도 범위에 대해 검색을 하게 되면

수직 탐색으로만 조건에 해당하는 레코드를 찾을 수 없기 때문에 

Index Range Scan을 하게 됩니다. 

 

또, 여러개의 Index가 걸려있는 상황에서 일부 인덱스로만 검색을 하면

당연히 Index Range Scan이 수행됩니다. 

 

Index Skip Scan

선두 컬럼을 조건절에 사용하지 않으면 기본적으로 Table Full Scan을 선택하는데요

이보다 I/O를 더 줄일 수 있고, 정렬된 결과를 쉽게 얻을 수 있으면 

Index Full Scan을 사용하기도 합니다. 

 

인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식인데요

조건절에 빠진 인덱스 선두 컬럼의 Cardinality 보다

후행 컬럼의 Cardinality가 클 때 유용합니다. 

 

Index Skip Scan (출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148220&boardConfigUid=9&boardIdx=140&boardStep=1)

 

만약 성별과 연봉 순으로 인덱스가 적용되어 있는 테이블이 있고

select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000

다음 쿼리가 들어오면, 먼저 성별이 남성이고 연봉이 2000보다 큰 테이블의 첫번째 레코드를 찾습니다. 

이런 요청은 그냥 index range scan 이겠죠

 

select * from 사원 where 연봉 between 2000 and 4000

다음과 같이 성별 조건이 빠져있으면 어떻게 스캔할까요?

 

옵티마이저는 동일한 사원이면 연봉에 대해 정렬이 되어있다는 것만 알고있습니다. 

어떤 사원이 있는지 모르니 우선 인덱스의 첫번째 지점으로 이동할 것입니다.

첫번째 리프블록이 남 & 800 이하인 레코드를 담고 있으니, 이를 스킵하고

두번쨰 블록도 남 & 800 이상, 남 & 1500 이하인 레코드를 담고 있어 이를 스킵합니다. 

세번째 블록은 남 & 1500 ~ 남 & 5000 이하인 레코드를 담고 있으니 탐색합니다.

 

블록을 통채로 스킵한다는 건 블록의 레코드를 하나씩 스캔하지 않고 해당 블록을 껑충 하고 뛰어넘습니다.

남 & 10000 이상은 탐색하지 않고 스킵할까? 아니다. 왜냐면 앞쪽 구분이 바뀌는 경계이기 때문입니다.

 

사실 그런 경우가 아니더라도, 범위 조건에 대한 스캔을 하는 경우 

조건에 맞는 값을 포함할 가능성이 있는 리프 블록만 골라서 액세스할 수 있습니다.

정렬되어 있는 성질을 이용해서 굳이 전체를 가져와서 비교해야 할 필요가 없는 경우에

Index Skip Scan이 빛을 발한다고 합니다.

 

Index Fast Full Scan

애는 매 블록을 탐색할 때 마다 가져오는게 아니라 세그먼트 전체를

multiblock I/O 방식으로 가져와서 스캔을 해버리기 때문에

대량의 인덱스 블록을 읽어야 할 때 Index Full Scan 보다 빠릅니다.

 

속도는 빠르지만 연결리스트 구조를 무시한 채 데이터를 읽기 때문에

결과 집합이 인덱스 키 순서대로 정렬되지 않는다. 

 

가장 큰 장점은 병렬 쿼리를 처리할 수 있다는 점입니다. 

병렬 쿼리시 Direct Path I/O 방식을 사용해서 I/O 속도가 더 빨라집니다. 

대용량 데이터를 읽고 쓰거나, 재사용 가능성이 없는 경우엔 버퍼캐시를 경유하지 않는게 더 유리합니다. 

 

Index Range Scan Descending

인덱스 스캔을 거꾸로 수행하는 경우입니다.

범위에 대한 쿼리를 내림차순으로 정렬한 결과를 얻고 싶을때 이를 사용합니다. 

 

Range Scan Descending (http://www.dbguide.net/db.db?cmd=view&boardUid=148220&boardConfigUid=9&boardIdx=140&boardStep=1)

 

Reference

wiki.gurubee.net/pages/viewpage.action?pageId=4949506

www.dbguide.net/db.db?cmd=view&boardUid=148220&boardConfigUid=9&boardIdx=140&boardStep=1