인덱스 개요

인덱스 개요

데이터베이스 테이블에서 데이터를 찾는 방법은 두 가지다. 

1. 테이블 전체를 스캔한다

2. 인덱스를 이용한다.

모든 교실을 돌며 학생을 찾는 경우가 전자에 속하고, 이름순으로 정렬한 학생명부를 이용하는 경우가 후자에 속한다. 테이블 전체 스캔과 관련해서는 튜닝 요소가 많지 않지만, 인덱스와 관련해서는 튜닝 요소가 매우 많고 기법도 다양하다. 그래서 인덱스는 SQL 튜닝을 공부할 때 가장 먼저 다루어야 할 주제다.

 

Table Full Scan vs Index Range Scan

Table Full Scan
Table Full Scan은 시퀀셜 Multiblock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한
모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 '한 번의 수면(I/O Call)을 통해
인접한 수십~수백 개 블록을 한꺼번에 I/O하는 메커니즘'이다. 이 방식을 사용하는 SQL은
스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.
시퀀셜 액세스 Multiblock I/O가 아무리 좋아도 수십~수백 건의 소량 데이터 찾을 때
수백만~수천만 건 데이터를 스캔하는 건 비효율적이다. 큰 테이블에서 소량 데이터를
검색할 때는 반드시 인덱스를 이용해야 한다.

Index Range Scan
Index Range Scan을 통한 테이블 액세스는 랜덤 액세스와 Single Block I/O 방식으로
디스크 블록을 읽는다. 캐시에서 블록을 못 찾으면 '레코드 하나를 읽기 위해 매번 잠을
자는 I/O 메커니즘'이다. 따라서 많은 데이터를 읽을 때는 Table Full Scan보다 불리하다.

예상 커디널리티가 일정량을 넘어서면 인덱스로 테이블을 액세스하는 것이 불리하다.
커디널리티의 일정량은 스스로 경험으로 찾아보자

 

캐시탐색 메커니즘

Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 공유한다. 
1. 인덱스 루트 블록을 읽을 때
2. 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
3. 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
4. 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
5. 테이블 블록을 Full Scan 할 때

캐시버퍼 체인 래치
대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인을 탐색한다.
DBA(Data Block Address)를 해시 함수에 입력하고 거기서 반환된 값으로 스캔해야
할 해시 체인을 찾는다. 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 
일이 생기면 곤란하다. 이를 막기 위해 해시 체인 래치가 존재한다.

버퍼 Lock
읽고자 하는 블록을 찾았으면 캐시버퍼 체인 래치를 곧바로 해제해야 한다. 그래야 해당 래치가
풀리기를 기다리던 다른 프로세스들이 작업을 재개할 수 있다.
래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는 도중에 후행 프로세스가 하필 같은 블록에
접근해서 데이터를 읽고 쓴다면 데이터 정합성에 문제가 생길 수 있다. 이를 방지하기 위해
오라클은 버퍼 Lock을 사용한다. 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을
설정함으로써 버퍼블록 자체에 대한 직렬화 문제를 해결하는 것이다.
같은 로우는 로우 Lock에 의해 보호될 텐데 버퍼 Lock이 왜 필요할까 싶겠지만, 로우 Lock을
설정하는 행위도 블록을 변경하는 작업이다.

 

인덱스 튜닝의 핵심요소

인덱스 튜닝의 두 가지 핵심요소
1. 인덱스 스캔 효율화 튜닝
43세 홍길동을 찾아야 한다면 인덱스 순서가 나이, 이름보다는 이름, 나이로 정렬되어 있을때
훨씬 더 효율적이다.
2. 랜덤 액세스 최소화 튜닝
인덱스 튜닝의 두 번째 핵심요소는 테이블 액세스 횟수를 줄이는 것이다. 테이블 액세스 횟수가
나이 인덱스만 있는 경우보다 이름 인덱스만 있는 경우가 더 효율적일 것이다. 테이블로 접근하는
랜덤 I/O 횟수가 줄기 때문이다. 물론 이름, 나이로 인덱스가 존재하면 훨씬 더 효율적이겠지만. 
'SQL튜닝은 랜덤 I/O와의 전쟁'이다.

 

인덱스 구조

인덱스구조

DBMS는 일반적을 B*Tree 인덱스를 사용한다. 루트와 브랜치 블록에 있는 각 레코드는 
하위 블록에 대한 주소값을 갖는다. 루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드가 하나 있다.
가장 왼쪽 첫번째 레코드다. 이를 'LMC'라고 하며 'Leftmost Child'의 줄임말이다.
LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다. LMC가 가리키는 주소로 찾아간 블록에는
키값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장돼 있다.

ROWID = 데이터블록 주소 + 로우 번호
데이터블록 주소 = 데이터 파일 번호 + 블록 번호
블록 번호 : 데이터파일 내에서 부여한 상대저 순번
로우 번호 : 블록 내 순번

인덱스 수직적 탐색
정렬된 인덱스 레크드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정. 
즉, 인덱스 스캔 시작지점을 찾는 과정

인덱스 수평적 탐색
인덱스에서 본격적으로 데이터를 찾는 과정이다.

 

인덱스를 Range Scan 할 수 없는 이유

인덱스를 RangeScan 할 수 없는 경우
1. 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을
 찾을 수 없기 때문이다.
 예)nvl(수량,0) > 500
2. Like 중간 값을 검색할 때도 Range Scan이 불가능하다.
 예)상품명 like '%제약%'
3. OR 조건으로 검색하는 경우
 예) 전화번호 = '0000' OR 이름 = '홍길동'
 UNION ALL과 같은 구조인 'OR Expansion'을 USE_CONCAT 힌트로 유도하면
 실행계획에서 CONCATENATION으로 나오면서 Index Range Scan을 유도할 수 있다.
4. IN 조건절
 예) 이름 in ('홍길동','홍길순')
 옵티마이저가 IN-List Iterator 방식을 사용한다. IN-List 개수만큼 Range Scan을 반복한다.
 UNION ALL으로 변환한 것과 같은 효과를 얻는다.
 
"인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다"

자동 형변환
1. 숫자형 조건의 컬럼 넘버형으로 자동 형변환
 예)생년월일=19781011 => TO_NUMBER(생년월일)=19781011로 해석됨
2. 날짜, 문자의 경우 날짜로 자동형변환
3. DECODE(A,B,C,D)에서 C가 NULL이면 D는 문자형으로 자동 형변환된다.
 C를 TO_NUMBER(NULL) 혹은 0으로 대체해야 한다.

 

인덱스 스캔 방식

1. Index Range Scan
인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다. 인덱스 루트에서 수직적으로 탐색한 후에
필요한 범위만 스캔한다. 인덱스 Range Scan을 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에
사용해야 한다. 실행계획을 보고 '인덱스를 잘 타니까 ok'라고 생각하면 안된다.
"성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐"로 결정된다.

2.Index Full Scan
수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
대게 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
전체 중 극히 일부분이 선택된다면 Table Full Scan보다는 Index Full Scan을 통한 필터링이 효과적이다.

3.Index Unique Scan
수직적 탐색만으로 데이터를 찾는 스캔 방식으로서 Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.

4.Index Skip Scan
인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다.
Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 Index Full Scan을
사용하기도 한다.
인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.
인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 하며, 수행 횟수가 적은 SQL을 위해
인덱스를 추가하는 것이 비효울적일 때 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다.

5.Index Fast Full Scan
논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기
때무이다. Index Full Scan은 인덱스의 논리적 구조를 따라 브랜치1 1 2 3 4번 순으로 블록을
읽어 들인다. 반면 Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프블록을
읽어들인다. 1 3 4 2 6 5 순으로 읽는다. 루트와 브랜치 블록도 읽지만 필요 없는 블록이므로 버린다.
인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키
순서대로 정렬되지 않는다. 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다는
점도 기억할 필요가 있다. 병렬 쿼리도 가능하다.

6.Index Range Scan Descending
Index Range Scan과 기본적으로 동일한 스캔 방식이다. 인덱스를 뒤에서 부터 앞쪽으로 스캔하기 때문에
내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.