[SQL 튜닝] 2. 인덱스 기본

책 [친절한 SQL 튜닝; 조시형 지음]을 참고하여 작성하였습니다. 

 

 

인덱스 Range Scan은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것이다. (시작점과 끝점이 있다는 이야기) 애초에 인덱스가 정렬되어 있기 때문에 가능하다. 

 

인덱스 Range Scan 하려면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다.

반대로 말하면, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다.

 

하지만 인덱스를 사용할 수 없는 조건은 다음과 같다. 

• 변형
• not
• null
• like 
• or
• in

'인덱스를 탄다'는 표현은 '인덱스 Range Scan 한다'와 같은 의미다.

 

인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다. 

 

인덱스는 이미 정렬되어 있기 때문에 order by에 해당 인덱스 컬럼을 명시했을 경우 실행 계획에서 SORT (ORDER BY) 연산을 거치지 않는다. 내림차순으로 정렬할 경우 마찬가지로 SORT (ORDER BY)를 사용하지 않고 INDEX RANGE SCAN DESCENDING을 사용한다. 즉 인덱스로 소트 연산을 생략하는 것이다. 

 

order by 절에서 컬럼을 가공하지 않고, 데이터 순서와 인덱스 순서가 같아야 성능이 높아진다. 

 

select-list에서 최대/최소를 구할 때도 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽, 오른쪽 지점으로 내려가서 첫 번째 레코드만 읽는다. 실행계획에서는 FIRST ROW, MIN/MAX로 표시되는데 이는 레코드 하나만 읽고 멈췄다는 뜻이다. 

 

형변환은 명시적으로 지정해 주는 것이 좋다. 형변환 연산을 생략하는 것이 연산 횟수를 줄여 성능을 더 향상시킬 수 있지 않을까 생각할 수 있지만 이는 잘못된 생각이다. 어차피 연산자 기준 두 피연산자의 타입이 다르면 옵티마이저가 자동으로 형변환 연산을 추가한다. SQL 성능은 이런 데서 결정되는 게 아니라 블록 I/O를 줄일 수 있느냐 없느냐에서 결정된다. 

따라서 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다. 

 

1. INDEX RANGE SCAN

• 선두 컬럼을 가공하지 않은 상태로 조건절에 사용
• 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정

2. INDEX FULL SCAN

• 인덱스 리프 블록 처음부터 끝까지 수평 탐색, 인덱스 구조를 따라 스캔
• 결과 집합 순서 보장
• SingleBlock I/O
• 선두 컬럼이 조건절에 없으면 옵티마이저는 Table Full Scan을 고려하는데 대용량 테이블이기 때문에 부담이 크다면 Index Full Scan 방식 고려
• 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황에서 Index Range Scan의 차선책으로 사용
• 하지만 대부분의 레코드를 액세스해야 하는 상황이라면 Table Full Scan보다 오히려 불리
• 대신 부분범위 처리를 활용하여 전체 집합 중 처음 일부를 빠르게 출력할 목적이라면 극적인 성능 개선 효과가 있음

3. INDEX UNIQUE SCAN

• 수직적 탐색만으로 데이터를 찾는 스캔 방식
• 인덱스 키 컬럼을 모두 '=' 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요 없음 

4. INDEX SKIP SCAN

• 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 하는 스캔방식
• 인덱스 선두 컬럼의 distinct value 개수가 적고 후행 컬럼의 distinct value 개수가 많을 때 유용

 

인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 하며, 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다.

 

5. INDEX FAST FULL SCAN

• 논리적인 인덱스 구조를 무시
• 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔
• 결과집합 순서 보장 안 됨
• 병렬 스캔 가능
• 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능
• 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과 발휘

 

6. INDEX RANGE SCAN DESCENDING

• 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합 가져옴
• 아까 설명한 것처럼 MAX 구할 때도 사용할 수 있음