[친절한 SQL 튜닝] 인덱스 기본

인덱스 구조 및 탐색

미리 보는 인덱스 튜닝

인덱스 튜닝의 두가지 핵심 요소

1. 인덱스 스캔 효율화 튜닝
2. 랜덤 액세스 최소화 튜닝 (더 중요)

SQL 튜닝은 랜덤 I/O와의 전쟁

• 인덱스를 많이 사용하는 OLTP 시스템이라면 디스크 I/O 중에서도 랜덤 I/O가 특히 중요하다.
• IOT, 클러스터, 파티션에서부터 테이블 Prefetch, Batch I/O처럼 숨은 기능들의 본질은 랜덤 I/O를 줄이는데 있다.

인덱스 구조

인덱스를 스캔하는 이유

• 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다.
  • ROWID = 데이터 블록 주소 + 로우 번호
  • 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
  • 블록 번호: 데이터파일 내에서 부여한 상대적 순번
  • 로우 번호: 블록 내 순번

인덱스 탐색 과정

• 수직적 탐색: 인덱스 스캔 시작지점을 찾는 과정
• 수평적 탐색: 데이터를 찾는 과정

인덱스 수직적 탐색

수직적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동한다.

수직적 탐색은 '조건을 만족하는 레코드'를 찾는 과정이 아니라 '조건을 만족하는 첫 번째 레코드'를 찾는 과정임을 반드시 기억하자.

인덱스 수평적 탐색

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

인덱스 리프 블록은 양방향 연결 리스트 구조다.

인덱스를 수평적으로 탐색하는 이유

• 조건절을 만족하는 데이터를 모두 찾기 위해서
• ROWID를 얻기 위해서

결합 인덱스 구조와 탐색

인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같으므로 성능도 똑같다.

인덱스 기본 사용법

인덱스를 사용한다는 것

인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 사용할 수 있다.

'인덱스를 정상적으로 사용한다'의 의미

• 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다.
• 즉 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.

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

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

인덱스 스캔 시작점을 찾을 수 없는 예

where substr(생년월일, 5, 2) = '05'
where nvl(주문수량, 0) < 100
where 업체명 like '%대한%'
where (전화번호 = :tel_no OR 고객명 = :cust_nm) -- 옵티마이저가 IN-List 개수만큼 Index Range Scan을 반복한다
where 전화번호 in ( :tel_no1, :tel_no2 ) -- UNION ALL 방식을 사용하자

OR 또는 IN 조건절은 옵티마이저의 쿼리변환 기능을 통해 Index Range Scan으로 처리되기도 한다.

더 중요한 인덱스 사용 조건

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

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

인덱스를 이용한 소트 연산 생략

인덱스가 정렬돼 있기 때문에 Range Scan이 가능하고, 소트 연산 생략 효과도 부수적으로 얻게 된다.

옵티마이저는 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않을 수도 있다.

컬럼 가공

ORDER BY 절과 SELECT-LIST에서 컬럼을 가공하면 성능상 문제가 생길 수도 있다.

자동 형변환

오라클은 자동으로 형변환 처리를 해주는 DBMS이다.

서로 다른형이 만났을 때

• 숫자형과 문자형이 만나면 숫자형이 이긴다.
• 날짜형과 문자형이 만나면 날짜형이 이긴다.
• 연산자가 LIKE일 때는 문자형이 숫자형을 이긴다.

decode() 함수에서 반환값의 데이터 타입은 세 번째 인자에 의해 결정된다. 그리고 세 번째 인자가 null 값이면 varchar2로 취급한다.

자동 형변환 기능에 의존하지 말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다.

인덱스 확장기능 사용법

Index Range Scan

B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다.

리프 블록까지 수직적으로 타맥한 후에 '필요한 범위만' 스캔한다.

성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.

Index Full Scan

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

대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

Index Full Scan의 효용성

• 인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려한다.
• 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.
• 그럴 때 옵티마이저는 Index Full Scan 방식을 선택한다.

인덱스를 이용한 소트 연산 생략

• first_rows 힌트를 사용하면 소트 연산을 생략함으로써 전체 집합 중 처음 일부를 빠르게 출력할 목적으로 옵티마이저가 Index Full Scan 방식을 선택한다.
• 주의할 것은, 처음 의도(부분범위 처리 활용)와 달리 fetch를 멈추지 않고 데이터를 끝까지 읽는다면 Table Full Scan 보다 훨씬 더 많은 I/O를 일으킨다.

Index Unique Scan

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

Unique 인덱스라고 해도 범위검색 조건이나 Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때는 Index Range Scan이 나타난다.

Index Skip Scan

루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 스캔 방식이다.

조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.

Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다. 부분범위 처리가 가능하다면 Index Full Scan이 도움이 되기도 한다.

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

Index Fast Full Scan

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔한다.

디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘한다.

특징

• 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
• 쿼리에 사용한 컬림이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다.
• 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능하다. 병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 I/O 속도가 더 빨라진다.

Index Full Scan	Index Fast Full Scan
1. 인덱스 구조를 따라 스캔	1. 세그먼트 전체를 스캔
2. 결과집합 순서 보장	2. 결과집합 순서 보장 안 됨
3. Single Block I/O	3. Multiblock I/O
4. (파티션 돼 있지 않다면) 병렬스캔 불가	4. 병렬스캔 가능
5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능	5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

Index Range Scan Descending

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

만약 옵티마이저가 인덱스를 거꾸로 읽지 않는다면, index_desc 힌트를 이용해 유도할 수 있다.

MAX 값을 구하고자 할 때도 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한 건만 읽고 멈추는 실행계획이 자동으로 수립된다.