인덱스 기본

인덱스 구조 및 탐색

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가 가리키는 주소로 찾아가면 블록에는 키 값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장돼 있다.

B*Tree 예시

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

인덱스 수직적 탐색

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

인덱스 수평적 탐색

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

결합 인덱스 구조와 탐색

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

결합 인덱스

인덱스 기본 사용법

인덱스를 사용한다는 것

• 인덱스 컬럼(정확히, 선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.
• 정상적인 사용은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 동작을 의미한다.
• 즉 리프 블록 일부만 스캔하는 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장