인덱스 확장 기능 사용법

인덱스 스캔 방식은 여러가지가 있다. 각 스캔 방식의 주요 특징을 비교해보자.

 

 

1. Index Range Scan

B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다. 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 ‘필요한 범위'만 스캔한다.

• 앞선 내용에 있지만, 범위지정시캔을 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다.
• 다시 강조하지만 인덱스를 태운다고 성능이 좋다고 생각하면 안된다. 스캔 범위와 액세스 횟수를 얼마나 줄일 수 있느냐로 성능이 결정된다.

 

 

2. Index Full Scan

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

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

 

 

Index Full Scan 의 효용성

• 선두컬럼이 없으면 옵티마이저는 먼저 Table Full Scan 을 고려한다.

• 인덱스에 존재하지 않는 컬럼의 값이 필요한 경우에는 현재 읽은 레코드 식별자를 이용하여 테이블을 엑세스 해야한다. 그러나 필요로하는 모든 컬럼이 인텍스 구성 칼럼에 포함된 경우 테이블에 대한 액세스는 발생하지 않는다.
• 만약 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.
  ⇒ 이때 옵티마이저는 Index Full Scan 방식을 선택함.

💡 Index Ragne Scan 의 손익분기점

 인덱스는 항상 정렬된 상태를 유지하지만 그 외에 테이블 정보들은 삽입/삭제 등의 작업을 거치면서 정렬된 상태가 언젠가는 흐트러지기 마련이다.

 손익분기점을 넘어서서 고비용의 Random access 비율이 높아지게 되면 오히려 table full scan이 유리한 상황이 된다.
즉, 테이블의 대부분의 데이터(5%~20%를 넘어서는 양)를 찾을 때는 한 블록씩 읽는 인덱스 스캔 방식보다 어짜피 대부분의 데이터를 읽을 거라면 한번에 여러블록씩 읽는 전체 테이블 스캔방식이 더 유리하다.

-참고
https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=vefe&logNo=221402694129

 

 

고비용? Random access 비율이 높아지게 되면? 오히려 table full scan이 유리한 상황?

무슨 말인지 이해하기 위해 살짝만 알아보자.

 

 

💡 Random access ( Table Access By Index Rowid )

 ‘Table Access By Index Rowid’ 또는 ‘RID(=Bookmark) Lookup’으로 표현되는 테이블 Random 액세스의 내부 메커니즘.
인덱스에 저장돼 있는 rowid는 흔히 ‘물리적 주소정보’라고 일컬어지는데, 오브젝트 번호, 데이터 파일 번호, 블록 번호 같은 물리적 요소들로 구성돼 있기 때문이다.

하지만 보는 시각에 따라서는 ‘논리적 주소정보’라고 표현하기도 한다. rowid가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문이다.

anyway
중요한 것은, rowid가 메모리 상의 위치정보가 아니라 디스크 상의 위치정보라는 사실이다.

그리고 데이터 블록을 읽을 때는 항상 버퍼 캐시를 경유하므로 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와 알고리즘을 사용한다.

인덱스 ROWID를 이용해 테이블 블록을 읽는 메커니즘 요약 (아마 다음에 더 자세히 다룰듯.)
1. 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.
2. 해시 값을 이용해 해시 버킷을 찾아간다.해시 버킷에 연결된
3. 해시 체인을 스캔하면서 블록 헤더을 찾는다.
4. 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.
5. 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾는다.디스크에서 읽은 블록을 적재6. 하기 위해 빈 캐시 공간을 찾는 것이다.
7. LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.

위 처리 과정 중에는 래치(Latch), 버퍼 Lock 같은 Internal Lock을 획득하거나 다른 백그라운드 프로세스의 선처리 결과를 기다리는 내부 메커니즘이 작동한다.

그런 과정에 경합까지 발생한다면 블록 하나를 읽더라도 생각보다 큰 비용을 치르게 된다. Oracle이나 SQL Server 같은 디스크 기반 DBMS에서 인덱스 rowid에 의한 테이블 액세스가 생각만큼 빠르지 않은 이유가 여기에 있다.

추가로, 인덱스 손익분기점은 일반적으로 5~20%의 낮은 수준에서 결정되지만 클러스터링 팩터에 따라 크게 달라진다.
(이건 너무 깊어지므로 나중에 따로 다룸)

-참고
https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=367

 

3. Index Unique Scan

• 수직적 탐색으로만 데이터를 찾는 스캔방식.
• Unique 인덱스를 '='조건으로 탐색하는 경우에 작동한다.
• Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해 준다. 따라서 해당 인덱스 키 컬럼을 모두 '='조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.
• Unique 인덱스라고 해도 범위검색 조건(between, 부등호, like)으로 검색할 때는 Index Range Scan으로 처리된다.
• 또한, Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타난다. 예를 들어, 주문상품 PK 인덱스를 '주문일자 + 고객ID + 상품ID'로 구성했는데, 주문일자와 고객ID로만 검색하는 경우를 말한다. (이전에 다룬 예시)

 

4. Index Skip Scan

• 인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. Table Full Scan 보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan을 사용하기도 한다.
• 오라클은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식을 9i 버전에서 선보였는데, Index Skip Scan이 바로 그것이다.
  • postgresql 에는 technique라는게 있음!
• Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 스캔 방식이다.
• 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용함

 

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

• Index Skip Scan은 Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고 후행 컬럼의 Distinct Value 개수가 많을 때 효과적이라고 했다. 하지만 인덱스 선두 컬럼이 없을 때만 Index Skip Scan이 작동하는 것은 아니다.

1. 선두 컬럼(=업종유형코드)에 대한 조건절은 있고, 중간 컬럼(=업종코드)에 대한 조건절이 없는 경우에도 Skip Scan을 사용할 수 있다.
2. Distinct Value가 적은 두 개의 선두컬럼이 모두 조건절에 없는 경우에도 유용하게 사용할 수 있다.
3. 선두 컬럼이 부등호, BETWEEN, LIKE 같은 범위검색 조건일 때도 Index Skip Scan을 사용할 수 있다.

• Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다. 부분범위 처리가 가능하다면 Index Full Scan이 도움이 되기도 한다. 하지만 이들 스캔 방식이 최선책일 수 없다. 인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 하며, 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다.

 

 

4. Index Fast Full Scan

• Index Fast Full Scan은 Index Full Scan 보다 빠르다. 더 빠른 이유는 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다.
• 예시
  • Index Full Scan은 인덱스의 논리적 구조를 따라 루트 → 브랜치1 → 1 → 2 → 3 → 4 → 5 → 6 → 7 → 8 → 9 → 10번 순으로 블록을 읽어들인다.
  • Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다. Multiblock I/O 방식으로 왼쪽 익스텐트에서 1 → 2 → 10 → 3 → 9번 순으로 읽고, 그 다음 오른쪽 익스텐트에서 8 → 7 → 4 → 5 → 6번 순으로 읽는다. 루트와 두 개의 브랜치 블록도 읽지만 필요 없는 블록이므로 버린다.

• multiblock I/O 방식을 사용하므로 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
• 쿼리에 사용한 컬럼이 모두 인덱스에 포함되어 있을 때만 사용할 수 있다는 점도 기억할 필요가 있다.
• 인덱스가 파티션 되어있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징 중 하나다.

 

5. Index Range Scan Descending

• Index Range Scan과 기본적으로 동일한 스캔 방식이다.
• 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
• MAX 값을 구하고자 할 때도 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한 건만 읽고 멈추는 실행계획이 자동으로 수립된다.