2.1 인덱스 구조 및 탐색
인덱스 탐색 과정에는 수직적 탐색과 수평적 탐색, 두 단계로 이루어진다.
데이터를 찾는 두 가지 방법
만약 어떤 초등학교를 방문해 '홍길동' 학생을 찾는 방법은 두 가지다.
첫째는, 1학년 1반부터 6학년 맨 마지막 반까지 모든 교실을 돌며 홍길동 학생을 찾는 것이다(Table Full Scan). 교무실에서 학생 명부를 조회해 홍길동 학생이 있는 교실만 찾아가는 것이다(Index Range Scan).
홍길동 학생이 많다면, 전자가 빠르고, 몇 안된다면 후자가 더 빠를 것이다.
데이터베이스 테이블에서 데이터를 찾는 방법도 두 가지다.
- 테이블 전체를 스캔한다.
- 인덱스를 이용한다.
인덱스 튜닝의 두 가지 핵심요소
보통 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.
인덱스 튜닝 방법은 여러가지가 있지만 크게 두 가지로 나뉜다.
- 인덱스 스캔 효율화 튜닝
- 랜덤 엑세스 최소화 튜닝
인덱스 스캔 효율화 튜닝과 랜덤 엑세스 최소화 튜닝 중 어떤것이 더 중요한지 고른다면,
랜덤 엑세스 최소화 튜닝이다. 성능에 미치는 영향이 더 크기 때문이다.
결국 SQL 튜닝은 랜덤 I/O와의 전쟁이다.
SQL 튜닝은 랜덤 I/O와의 전쟁
데이터베이스 성능이 느린 이유는 디스크 I/O 때문이다.
성능을 위해 DBMS가 제공하는 많은 기능이 느린 랜덤 I/O를 극복하기 위해 개발됬다고 한다.
대표적으로 아래와 같은 것들이 있다.
- IOT
- 클러스터
- 파티션
- Table Prefetch
- Batch I/O
- Sort Merge Join & Hash Join(NL 조인을 보완하기 위해 개발됨)
2.1.2 인덱스 구조
DBMS는 일반적으로 B TREE 인덱스를 사용한다.
B TREE 인덱스란 나무를 거꾸로 뒤집은 모양이어서 뿌리(루트)가 위쪽에 있고, 가지(Branch)가 위쪽에 있고, 가지를 거쳐 맨 아래 잎사귀(Leaf)가 있는 구조다.
루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다.
루트와 브랜치 블록에는 키값(하위 블록에 저장된 키값의 범위)을 갖지 않는 특별한 레코드가 있다.
LMC : Leftmost Child의 줄임말이다. 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다.
리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있고, ROWID를 갖는다.
인덱스 키값이 같으면 ROWID 순으로 정렬된다.
ROWID는 아래와 같이 데이터 블록 주소와 로우 번호로 구성된다.
- ROWID = 데이터 블록 주소 + 로우 번호
- 데이터 블록 주소 = 데이터 파일번호 + 블록 번호
- 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
- 로우 번호 : 블록 내 순번
인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.
- 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
- '조건을 만족하는 레코드'를 찾는 과정이 아닌 '조건을 만족하는 첫 번째 레코드'를 찾는 과정' - 수평적 탐색 : 데이터를 찾는 과정
인덱스의 구조에서 인덱스 선두 컬럼을 모두 '=' 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같으므로 성능도 똑같다.
DBMS가 사용하는 B*TREE 인덱스는 엑셀처럼 평면 구조가 아니기 때문에, 인덱스를 이름+ 성별 순으로 구성하는 것과 성별 + 이름으로 구성하는 것은 일량에 차이가 없다.
Balanced의 의미
delete 작업 때문에 인덱스가 불균형 상태에 놓일 수 있다고 설명한 자료들이 있다.
다른 리프 노드에 비해 루트 블록과의 거리가 더 멀거나 가까운 리프 노드가 생길 수 있다는 설명인데, B*Tree 인덱스에서 이런 현상은 절대 발생하지 않는다.
B * TREE 인덱스의 B는 'Balanced'를 의미하고, 'Balanced'란 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음을 의미한다.
출처 : 친절한 SQL 튜닝(디비안, 조시형 저)
