인덱스 기본

인덱스 구조 및 탐색

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색 두 단계로 이루어진다.

인덱스 튜닝

데이터를 찾는 두 가지 방법

테이블 전체를 스캔하는 방법과 인덱스를 이용하는 방법이 존재한다.

테이블 전체를 스캔하는 방법은 튜닝 요소가 많지 않지만,
인덱스와 관련해서는 튜닝 요소가 많이 존재하고 다양한 기법들이 존재한다.

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

인덱스 스캔 효율화 튜닝 : 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 방법
랜덤 액세스 최소화 튜닝 : 인덱스 스캔 후 테이블 레코드 액세스 시 랜덤 I/O를 줄이는 방법

둘 다 중요하지만 성능에 영향이 큰 랜덤 액세스 최소화 튜닝이 더 중요하다.

인덱스를 스캔하는 과정에도 비효율이 있겠지만, 인덱스에 없는 정보를 추가로 찾는 것이 더 부담이다.
즉, 인덱스 튜닝은 랜덤 I/O를 줄이는 것이 핵심이다.

인덱스 구조

DBMS는 일반적으로 B-Tree 인덱스를 사용한다.
나무를 거꾸로 뒤집은 모양으로 위부터 아래로 루트, 브랜치, 리프가 존재한다.

루트와 브랜치 블록에 있는 각 레코드는 하위 블록의 주소값을 갖는다.
상위 레코드가 가리키는 하위 블록에는 상위 값보다 크거나 같은 레코드가 존재한다.
즉, 상위 블록의 키값이 하위 블록의 키값의 범위를 나타낸다.

• LMC (Leftmost Child)
  • 루트와 브랜치 블록에 존재하는 키값을 갖지 않는 레코드
  • 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다.
  • 즉, 키값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 존재한다.

리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있고,
테이블 레코드를 가리키는 주소값인 ROWID를 갖고 있다.
(인덱스 키값이 같은 경우에는 ROWID 순으로 정렬)

• ROWID
  • 데이터 블록 주소
    • 데이터 파일 번호
    • 블록 번호 : 데이터 파일 내에서 부여한 상대적 순번번
  • 로우 번호 : 블록 내 순번번

검색 조건을 만족하는 소량의 데이터를 빠르게 찾아
ROWID를 얻기 위해 인덱스를 스캔하는데
이때 인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나뉜다.

인덱스 수직적 탐색

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

루트 블록에서 시작해 찾고자 하는 값보다 크거나 같은 값을 만나면
직전 레코드가 가리키는 하위 블록으로 이동하며 탐색한다.

인덱스 수평적 탐색

수직적 탐색을 통해 시작점을 찾은 후, 찾고자 하는 데이터가
나타나지 않을 때까지 리프 블록을 수평적으로 스캔한다.

리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는
Double Linked List 구조기에 좌우 수평적 탐색이 가능하다.

수평적으로 탐색하는 이유

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

필요한 컬럼을 인덱스가 모두 갖고 있을 수도 있지만,
일반적으로 인덱스 스캔 후, 테이블 액세스도 하기 때문에
이때 ROWID가 사용된다.

결합 인덱스 구조와 탐색

결합 인덱스 : 두 개 이상의 컬럼을 결합한 인덱스

인덱스 선두 컬럼을 모두 “=” 조건으로 검색하면
인덱스에 사용되는 컬럼들의 순서와는 상관 없이
읽는 인덱스 블록의 수는 똑같다.

Balanced

B-Tree의 B는 Balanced의 약자로
Delete 작업으로 인해 리프 노드들간의 루트 블록과의 거리가
서로 균형 잡히지 않는 현상이 발생할 가능성이 없다.

즉, 루트 블록부터 모든 리프 블록까지의 높이는 항상 서로 같다.

인덱스 기본 사용법

인덱스를 Range Scan 할 수 없게 되는 이유와 범위 스캔 방법

인덱스를 사용한다는 것

시작점을 찾아 필요한 범위만 스캔할 수 있는 이유는
정렬된 상태로 필요한 데이터가 모여있기 때문이다.

하지만 가공한 값이나 중간값을 찾을 때는 색인을 정상적으로 사용할 수 없다.

즉, 인덱스를 정상적으로 사용한다는 것은
수직적 탐색을 통해 시작점을 찾아, 수평적으로 필요한 범위만 스캔하는 것이다.

하지만, 인덱스 컬럼을 가공한 경우에는 시작점을 찾을 수 없기 때문에
리프 노드 전체를 스캔하는 Index Full Scan 방식으로 동작한다.

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

인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다는 말에서
인덱스를 정상적으로 사용할 수 없다는 말은
Index Range Scan을 할 수 없다는 말이다.

가공 시에는 인덱스 스캔 시작점을 찾을 수 없기 때문에
필요한 범위만 탐색할 수가 없기 때문이다.

가공한 값, 치환한 값, 중간에 포함된 값 등은
인덱스에 저장되어 있지 않기 때문에 범위 스캔이 불가능하다.
(substr, nvl, like %s%, or, in)

LIKE 검색 같은 경우 시작하는 값은 범위 스캔이 가능하지만
포함된 값은 특정 구간에 모여있지 않기 때문에 범위 스캔이 불가능하다.

OR 검색 같은 경우도 시작 지점이 한 군데가 아니라
여러 곳에 존재하기 때문에 범위 스캔이 불가능하다.

하지만, OR Expansion을 통해 OR 조건별로 조회 후에
UNION을 통해 결과를 합치는 방식을 사용하면 범위 스캔을 할 수 있다.

IN 조건절도 OR 조건절과 같은 방식이기 때문에 범위 스캔이 불가능하고,
IN 조건절에 대해 옵티마이저는 IN-List Iterator 방식을 사용해
IN-List 크기만큼 범위 스캔을 반복한다. (= OR Expansion)

더 중요한 인덱스 사용 조건

인덱스를 만든다는 것은 인덱스에 사용되는 컬럼 순서대로
정렬 기준이 만들어진다는 것이다.

즉, Range Scan을 하기 위해서는 인덱스의 선두 컬럼이
가공되지 않은 상태로 조건절에 있어야 한다.

하지만, 인덱스를 잘 타는 것도 중요하지만 리프 블록에서 스캔하는 양도 중요하다.

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

인덱스를 사용해서 조회하면 결과집합은 인덱스의 정렬 순서대로 출력되기 때문에
정렬 연산을 생략할 수 없게 인덱스가 구성되지 않은 경우를 제외하고
옵티마이저는 SQL에 ORDER BY가 있어도 정렬 연산을 수행하지 않는다.

또한, 리프 블록은 양방향 연결 리스트 구조이기 때문에
내림차순 정렬도 인덱스를 사용해 정렬 연산을 생략할 수 있다.

오름차순 = 좌측으로 수직적 탐색 + 우측으로 수평적 탐색
내림차순 = 우측으로 수직적 탐색 + 좌측으로 수평적 탐색

ORDER BY 절에서 컬럼 가공

인덱스 범위 스캔을 통해 조회한 경우에는 당연히 정렬을 보장하지만
정렬 기준에 가공된 값이 들어가게 된다면 정렬 연산이 발생한다.

SELECT-LIST에서 컬럼 가공

인덱스를 사용해 최소, 최대값을 구할 때도 추가적인 정렬 연산은 없지만
가공된 값을 사용하면 당연히 추가적인 정렬 연산이 발생한다.

자동 형변환

오라클은 조건절에서 양쪽 값의 데이터 타입이 다른 경우
자동 형변환을 사용해 처리하기 때문에 인덱스를 사용할 수 없는 경우가 있다.

데이터 타입마다 다르지만, 자동 형변환 시에도 인덱스를 사용하기 위해서는
인덱스가 존재하는 좌변 컬럼 기준으로 우변이 변환되는 경우에만 가능하다.

LIKE 검색의 경우에는 문자열 검색이기 때문에
숫자형 데이터도 문자로 바꿔버리는 것을 주의해야 한다.

자동 형변환은 가급적이면 피하고 인덱스 컬럼을 기준으로
반대편 컬럼 혹은 값을 정확히 형변환 해주는 것이 중요하다.

형변환 함수를 사용해서 발생하는 연산보다는 블록 I/O를 줄이는 것이 더 중요하고
형변환 함수를 생략해도 옵티마이저가 자동으로 생성하기 때문에 의미 없다.

인덱스 확장기능 사용법

인덱스의 다양한 스캔 방식

Index Range Scan

BTree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식으로,
루트에서 리프 블록까지 수직적으로 탐색해 시작점을 찾고,
필요한 범위만 수평적 탐색으로 스캔한다.

Index Full Scan

데이터 검색을 위한 최적의 인덱스가 없는 경우 사용되는 방식으로,
수직적 탐색 없이 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.

효율

옵티마이저는 조건절에 인덱스의 선두 컬럼이 없으면 Table Full Scan을 고려하는데,
대용량 테이블이라 부담이 크다면 Index Full Scan을 고려한다.

즉, 인덱스 스캔 단계에서 필터링을 통해 테이블 액세스를 줄일 수 있는 경우라면
Table Full Scan보다 Index Full Scan이 유리하다.

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

인덱스를 사용해서 조회한 것이기 때문에 당연히 결과집합도 정렬된 상태기에
정렬 연산을 생략할 목적으로 사용할 수도 있다.

Index Unique Scan

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

Index Range Scan으로 처리되는 경우

• 범위 검색 조건
• 결합 유니크 인덱스의 일부 컬럼만 조건으로 사용하는 경우

Index Skip Scan

인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 스캔 방식

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

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

작동 조건

선두 컬럼이 조건절에 없을 때만 작동하는 것은 아니다.
선두 컬럼에 대한 조건절이 있고, 중간 컬럼에 대한 조건절이 없을 때도
Skip Scan을 사용할 수 있다.

조건절에 없는 컬럼은 Distinct Value가 적어야 한다.

1. 선두 컬럼이 조건절에 없는 경우
2. 중간 컬럼이 조건절에 없는 경우
3. 두 개의 선두 컬럼이 모두 조건절에 없는 경우
4. 범위 검색 조건에서도 사용 가능

즉, 범위 검색이 불가능하거나 효율적이지 못할 때 고려할만한 선택지다.

하지만, 인덱스는 기본적으로 최적의 범위 스캔을 목표로 설계해야 하고,
인덱스를 추가하는 것이 비효율적일 때 차선책으로 선택해야 한다.

Index Fast Full Scan

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

논리적 순서가 아닌 물리적으로 디스크에 저장된 순서대로
리프 블록들을 읽는 방식이다.

디스크로부터 대량의 인덱스 블록을 읽어야 할 때 효과적이고, 속도도 빠르지만,
연결 리스트 구조를 무시한 채 데이터를 읽어 결과집합이 정렬되지 않고,
쿼리에 사용할 컬럼이 모두 인덱스에 포함되어 있어야 사용할 수 있다.

범위 스캔, 전체 스캔과는 다르게 인덱스가 파티션 되어 있지 않아도
Direct Path I/O 방식으로 병렬 쿼리가 가능하다.

Index Range Scan Descending

Index Range Scan과 동일한 스캔 방식이고, 스캔 방향만 다르기 때문에
내림차순으로 정렬된 결과집합을 얻을 수 있다.