인덱스 구조 및 탐색

---

• 인덱스의 구조와 탐색 원리에 대해서 간략하게 기록해보고자 한다. 인덱스에 대한 명확한 그림을 제시하는 챕터로 다음 챕터에서 소개할 인덱스 튜딩에 기초가 되는 내용들을 기록할 것이다.
• 인덱스 탐색 과정이 수직적 탐색과 수평적 탐색 두단계로 이루어진다는 것이 이번 포스팅의 핵심이다

미리보는 인덱스 튜닝

• 학생을 찾는다고 가정해보자.

  1. 모든 교실을 돌아다니면서 환숭이라는 학생을 찾는다.
  2. 교무실에 가서 학생 반, 번호 를 확인 한 후 찾아낸다.

• 위 2가지 방법 중 (2)번이 인덱스라고 이해하면 된다.

• 책에서도 문자와 관련하여 index(색인) 이 있을 것이다.

• 교무실에서 학생을 찾을 때, 이름을 기준으로 찾는 것이 빠를까? 아니면 학번을 기준으로 찾는 것이 빠를까?

• 그것은 주어지는 정보에 따라 다를 것이다. 이것이 인덱스 튜닝이라고 생각하면 된다.

• 인덱스를 통해 RowID를 획득 후, 테이블에 접근하는 것을 random i/o 라고 한다.

• 이 i/o 바운드를 최소화 하는 것이 sql 튜닝의 핵심 방법이라고 생각하면 된다.

인덱스 구조

• b* tree 구조로 구성되어 있다.
• 루트노드, 리프노드, 브랜치(가지) 등으로 구성되어있다. 자세한 내용은 다른 포스팅을 참조하자.
• RowId = data block address + row number
• data block address = file number + block number
• block number = sequential disk block number
• row number = number in series of block
• balance 구조로 이루어진 만큼 좌, 우의 데이터 양이 균등하게 되어있어 O(n^2)이 걸리지 않게 한다.

수직적 탐색

• 인덱스 스캔 시작지점을 찾는 과정을 의미한다.
• 루트블록에서 시작해서 left, right 로 뻗어나가면서 leaf node까지 접근하여 원하는 ROWID를 찾는다.

수평적 탐색

• 데이터를 찾는 과정을 의미한다.
• 해당 계층에서 원하는 값을 발견할 때까지 수평적으로 이동한다.

인덱스 기본 사용법

---

인덱스를 사용한다는 것은?

• 일반적으로 책은 ㄱ,ㄴ,ㄷ... 순으로 색인이 되어있다.
• 만일 3으로 시작하는 문자를 찾고자 한다면? 찾을 수 없을 것이다.
• 이처럼 우리가 찾고자 하는 단어들을 무엇으로 구성하는지(인덱스 구성)에 따라 퍼포먼스는 완전히 다를 것이다.
• 인덱스 작동 원리도 비슷하다. 우리가 원하는 문자로 시작하는 값을 찾기 위해 index range scan을 진행한다.
• 만일 제대로 인덱스를 설정하지 않으면 index full scan 을 진행한다.
• 또 찾는 값이 여러개라면? 오히려 data full scan 보다도 성능이 낮아질 것으로 유추할 수 있다.

인덱스를 range scan 할 수 없는 이유

• 인덱스 칼럼을 가공하면 index range scan을 진행할 수 없다.
• 인덱스 칼럼을 수정해서 쿼리를 날리면, 시작점을 찾기 힘들기 때문에 성능이 낮아진다.
• where [index_column] like '%hello%' 를 사용한다고 가정해보면 이해가 쉽다.

중요한 인덱스 사용 조건

• 인덱스를 여러 칼럼으로 구성했다고 가정해보자.
• 부서 + 사원명 + 나이 로 구성되어있다고 가정해보자.
• 만일 조건절에서 나이로 찾는다면..? 인덱스를 활용하는 것보다 차라리 테이블 풀 스캔이 나을 것이다.
• 인덱스를 잘 활용하는 것을 인덱스를 잘 탄다고 표현하기도 한다.
• 인덱스를 잘 타는지 확인하기 위해서는 인덱스 리프 블록에서 스캔하는 양(수평탐색) 을 확인해보면 된다.

인덱스를 이용한 소트 전략

• 인덱스를 통해 range scan을 할 수 있는 이유는 인덱스가 칼럼을 기준으로 정렬되어있기 때문이다.
• 인덱스의 리프노드는 Double Linked List로 구성되어있다.
• 따라서 orderby descent로 인덱스 칼럼을 접근해도 퍼포먼스에는 영향이 가지 않는다.

Order by 절의 칼럼 가공

• 앞서 조건절에서 인덱스 칼럼을 가공하면 퍼포먼스가 낮아진다고 했다.
• order by 절에서 인덱스 칼럼을 가공해서 퍼포먼스가 낮아지는 경우도 존재한다.
• 이미 정렬되어있음에도 불구하고 TO_CHAR등과같이 칼럼의 VALUE를 가공하는 경우 다시 정렬하게 된다.
• 이는 실행계획을 토대로 확인할 수 있다.

select list에서의 칼럼 가공

• 마찬가지로 select에서도 max(NVL... vs NVL(max... 에 따른 퍼포먼스 차이가 존재하기도 한다.
• max, min 등 정렬이 필요한 경우에, 해당 정렬을 먼저 수행하려고 하면 이미 정렬되어 있는 인덱스의 특징에 의해 실행계획이 1개 줄어든다.

자동 형 변환

• 오라클의 경우 Date, char, Number 등 형변환이 제멋대로 이기 때문에 정렬문제에서 퍼포먼스 저하가 나타날 수 있다.
• 항상 실행계획을 확인하는 습관을 들이자.

인덱스 확장기능 사용법

---

index range scan

• 수직탐색 이후 수평탐색 진행

index full scan

• 리프노드 수평 확장 진행해버림
• 인덱스 칼럼들을 기준으로 정렬되어있기 때문에 효과를 보는 경우가 종종 있음

index unique scan

• 하나의 값만 찾는 경우, 수직탐색만 진행

index skip scan

• 인덱스 컬럼이 여러개일 경우, 선행칼럼의 Distinct는 적고, 후행칼럼의 Distinct 개수가 많은 경우 유용

index fast full scan

• 리프노드의 논리적 순서로 배치한 순서가 아닌, 물리적 블록의 주소값을 기준으로 풀스캔 진행
• 물리적인 접근이 빨라지지만 정렬이 수행되지 않는다.
• 그래서 더 빠르다.
• 일반적인 인덱스 range scan과 다르게 MULTIBLOCK I/O를 진행해서 더 빠르다.

index range scan descending

• 트리구조에서 큰 값을 기준으로 먼저 스캔하는 것을 의미한다.
• ORDER BY DESCENDING 과정을 줄이기 떄문에 더 빠르다.