저자는 반복해서 말한다. SQL 튜닝은 랜덤 I/O와의 전쟁이다. 데이터베이스 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야할 데이터량이 많고, 그 과정에서 디스크 I/O가 많이 발생할 때 느리다. 특히나 디스크 I/O 중에서도 랜던 I/O가 특히 중요하다. 이러한 과정을 이해하기 위해 인덱스의 구조를 살펴보고자 한다.
🔥 핵심요약
- 인덱스란 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트다.
- 인덱스 튜닝은 랜덤 액세스 최소화하는 것이 중요하다.
- 인덱스를 사용한다고 해서 무조건 성능이 좋아지는 것은 아니다.
1. 인덱스 구조 및 탐색
DB 데이터를 찾는 두 가지 방법
- 테이블 전체를 스캔한다.
- 인덱스를 이용한다.
인덱스 튜닝의 두 가지 핵심요소
- 인덱스 스캔 효율화 튜닝: 인덱스 스캔과정에서 발생하는 비효율을 줄임
- 랜덤 액세스 최소화 튜닝: 테이블 액세스 횟수를 줄임(성능에 미치는 영향이 더 큼)
인덱스 구조
인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트다. 인덱스는 모든 책 뒤쪽에 있는 색인과 같은 역할을 한다.
-
인덱스 탐색 과정
- 수직적 탐색: 인덱스 스캔 시작지점을 찾는 과정
- 수평적 탐색: 데이터를 찾는 과정
인덱스 수직적 탐색
수직적 탐색은 ‘조건을 만족하는 레코드’를 찾는 과정이 아니라 ‘조건을 만족하는 첫 번째 레코드’를 찾는 과정이다. 인덱스를 수직적으로 탐색할 때, 루트를 포함한 브랜치 블록은 등산 푯말과 같은 역할을 한다.
인덱스 수평적 탐색
수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다. 이는 인덱스에서 본격적으로 데이터를 찾는 과정이다.
- 인덱스를 수평적으로 탐색하는 이유
- 조건적을 만족하는 데이터를 모두 찾기 위해
- ROWID를 얻기 위해
2. 인덱스 기본 사용법
인덱스를 정상적으로 사용(Range Scan(하기 위해서는 인덱스 컬럼을 가공하지 않아야 한다. 인덱스 컬럼을 가공하면 인덱스 스캔 시작점을 찾을 수 없기 때문이다.
인덱스를 사용한다는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다.
-- 인덱스를 타지 않는 경우(Range Scan할 수 없는 경우)
where substr(생년월일, 5, 2) = '05'
where nvl(주문수량, 0) < 100
where 업체명 like '%대한%'
where (전화번호 = :tel_no OR 고객명 :cust_nm)
where 전화번호 in (:tel_no1, :tel_no2_
-- 인덱스 스캔 가능
select *
from 고객
where 고객명 = :cust_nm -- 고객명이 선두 컬럼인 인덱스 Range Scan
union all
select *
from 고객
where 전화번호 = :tel_no -- 전화번호가 선두 컬럼인 인덱스 Range Scan
and (고객명 <> :cust_nm or 고객명 is null)더 중요한 인덱스 사용 조건 (인덱스 만능주의 X)
인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다. 문제는 인덱스를 Range Scan한다고 해서 항상 성능이 좋은 건 아니라는 사실이다. “인덱스 튜닝”에서 살표보겠지만 인덱스를 사용하는 것은 고비용 구조이기 때문이다.
3. 인덱스 확장기능 사용법
Index Range Scan
B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다.
인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후 ‘필요한 범위만’ 스캔한다.
성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있는냐로 결정된다.
Index Full Scan
수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
Index Unique Scan
수직적 탐색으로만 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 ‘=’ 조건으로 탐색하는 경우에 작동한다.
Index Fast Full Scan
말 그대로 Index Fast Scan은 Index Full Scan보다 빠르다. Index Fast Full Scan이 Index Full Scan보다 빠른 이유는, 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다.
| Index Full Scan | Index Fast Full Scan |
|---|---|
| 인덱스 구조를 따라 스캔 | 세그먼트 전체를 스캔 |
| 결과집합 순서 보장 | 결과집합 순서 보장 안 됨 |
| Single Block I/O | Multiblock I/O |
| (파티션 돼 있지 않다면) 병렬스캔 불가 | 병렬 스캔 가능 |
| 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능 | 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능 |
Index Range Scan Descending
Index Range Scan과 기본적으로 동일한 스캔 방식이며, 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
