인덱스 기본

인덱스 구조 및 탐색

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

테이블 전체 스캔
인덱스 이용

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.

세부적인 인덱스 튜닝 방법의 핵심은 크게 두 가지다.

첫 번째는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것이다.

즉, 인덱스 스캔 효율화 튜닝이다.

두 번째는 테이블 액세스 횟수를 줄이는 것이다.

인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O 방식을 사용하므로 이를 '랜덤 액세스 최소화 튜닝'이라고 한다.

둘 중 더 중요한 것은 랜덤 액세스 최소화 튜닝이다.

결국 SQL 튜닝은 랜덤 I/O와의 전쟁이다.

DB 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야 할 데이터량이 많고, 그 과정에 디스크 I/O가 많이 발생할 때 느리다.

인덱스를 많이 사용하는 온라인 트랜잭션 처리 시스템은 디스크 I/O 중에서도 랜덤 I/O가 특히 중요하다.

인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스 하기 위해 사용하는 객체다.
B에서 인덱스 없이 데이터를 검색하면 Full Scan을 한다.
반면 인덱스를 이용하면 읽고 멈출 수 있는데, Range Scan이 가능하기 때문이다.
범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문이다.
DBMS는 보통 B*Tree 인덱스를 사용한다.
Root, Branch, Leaf 노드가 존재한다.
루트와 브랜치 블록의 각 레코드는 하위 블록에 대한 주소값을 갖는다.
루트와 브랜치 블록은 LMC(Leftmost Child)라는 가장 왼쪽의 첫 번째 레코드가 있다.
LMC가 가리키는 주소로 찾아가면 블록에는 키 값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장돼 있다.

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.

인덱스 수직적 탐색

정렬된 인덱스 레코드 조건을 만족하는 첫 번째 레코드를 찾는 과정이다.
인덱스 스캔 시작지점을 찾는 과정.

인덱스 수평적 탐색

수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다.
인덱스에서 본격적으로 데이터를 찾는 과정이다.
인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주솟값을 가지는, 양방향 연결리스트라서 수평적 탐색이 가능하다.

결합 인덱스 구조와 탐색

두 개 이상 컬럼을 결합해서 인덱스를 만들 수 있다.

인덱스 기본 사용법

인덱스를 사용한다는 것

인덱스 컬럼(정확히, 선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.
정상적인 사용은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 동작을 의미한다.
즉 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.
인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 한다.

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

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.
예시로는 like, or, null 값을 다른 값으로 치환해서 조건으로 사용, 날짜 가공 등의 예시가 있다.

더 중요한 인덱스 사용 조건

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실이다. 가공하지 않은 상태로 말이다.
인덱스를 탄다. == 인덱스를 Range Scan한다.
인덱스를 탄다고 꼭 튜닝이 잘 된 것은 아니다.
중요한 것은 인덱스 리프 블록에서 스캔하는 데이터의 양이다.

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

테이블과 달리 인덱스는 정렬 돼 있다. 이것이 바로 우리가 인덱스를 사용하는 이유다.
인덱스가 정렬돼 있기 때문에 Range Scan이 가능하고, 소트 연산 생략 효과도 부수적으로 얻게 된다.
만약 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면, SORT ORDER BY 연산 단계가 추가된다.

ORDER BY 절에서 컬럼 가공

조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.

SELECT-LIST에서 컬럼 가공

인덱스를 이용해 정렬 연산 없이 최소 또는 최댓값을 빠르게 찾을 수 있다.

자동 형변환

선두 컬럼을 조건절에서 가공하지 않았는데도 옵티마이저는 테이블 전체 스캔을 선택할 수 있다.
예를 들어 생년월일 컬럼이 문자형인데 조건절에서 비교값을 숫자형으로 넣는 경우에 그렇다.
종종 이런 자동 현변환에서 성능과 애플리케이션 품질에 문제가 생긴다.
그러므로 자동 형변환에 의존해서는 절대 안된다.

인덱스 확장기능 사용법

Index Range Scan

인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식
인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위만큼만 스캔한다.
인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다.

Index Full Scan

수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
Index Full Scan은 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

효용성

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

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

인덱스를 Full Scan하면 Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬된다.
따라서 Sort Order By 연산을 생략할 목적으로 사용할 수도 있다.

Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.
Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해준다.
따라서 해당 인덱스 키 컬럼을 모두 '=' 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.

Index Skip Scan

이 스캔 방식은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.
예를 들어 고객 테이블에서 Distinct Value 개수가 가장 적은 컬럼은 성별이고, Distinct Value 개수가 가장 많은 컬럼은 '고객번호'다.
루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 스캔 방식이다.

Index Skip Scan이 작동하기 위한 조건

Index Skip Scan은 Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고 후행 컬럼의 Distinct Value 개수가 많을 때 효과적이다.
인덱스 선두 컬럼이 없을 때만 Index Skip Scan이 작동하는 것은 아니다.
선두 컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우에도 사용할 수 있다.
Distinct Value가 적은 두 개의 선두 컬럼이 모두 조건절에 없는 경우에도 사용할 수 있다.

Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다.

부분범위 처리가 가능하다면 Index Full Scan이 도움이 되기도 한다.

Index Fast Full Scan

Index Fast Full Scan은 Index Full Scan보다 빠르다.
빠른 이유는, 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다.
Multiblock I/O 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야할 때 큰 효과를 발휘한다.
결과 집합 순서를 보장하지 않으며, 병렬 스캔이 가능하다.
인덱스에 포함된 컬럼으로만 조회할 때 사용 가능하다.

Index Range Scan Descending

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

참고자료

친절한 SQL 튜닝 2장