인덱스란?
인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트이다. 흔히 말하는 책 뒤쪽에 있는 색인과 같은 역할을 한다.
pointer와 index의 차이
pointer는 메모리 상에서의 위치 정보
index는 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보
🤔 인덱스는 왜 필요한가?
테이블을 처음부터 끝까지 모두 읽지 않고 인덱스를 이용한 범위 스캔(Range Scan)이 가능하기 때문입니다.
DBMS는 일반적으로 BTree 인덱스를 사용한다.
인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다.
- ROWID = 데이터 블록 주소 + 로우 번호
- 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
- 블록 번호 = 데이터 파일 내에서 부여한 상대적 순번
- 로우 번호 = 블록 내 순번
인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.
- 수직적 탐색 : 인덱스 스캔 시작 지점을 찾는 과정
- 수평적 탐색 : 데이터를 찾는 과정
인덱스 수직적 탐색
정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정이다. 즉, 인덱스 스캔 시작 지점을 찾는 과정이다. 인덱스 수직적 탐색은 루트(Root) 블록에서부터 시작한다. 루트를 포함해 브랜치(Branch) 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다. 루트에서 시작해서 리프(Leaf) 블록까지 수직적 탐색이 가능한 이유다.
인덱스 수평적 탐색
수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다. 인덱스에서 본격적으로 데이터를 찾는 과정이다.
인덱스 손익분기점
인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조이다.
따라서, 읽어야 할 데이터가 일정량을 넘는 순간에는 테이블 전체를 스캔하는 것보다 오히려 느려진다.
Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 '인덱스 손익분기점'이라고 한다.
- Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
- Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Sigle Block I/O 방식이다.
- ROWID는 테이블 레코드가 디스크 상에서 어디에 저장되어 있는지를 가리키는 위치 정보
Table Full Scan vs. Index Range Scan
Table Full Scan
- Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다.
- 한 블록에 속한 모든 레코드를 한 번에 읽어들이고 캐시에서 못찾으면 한 번의 I/O call을 통해 인접한 수십~수백개의 블록을 한거번에 I/O하는 메커니즘
- 하지만 큰 테이블에서 소량 데이터를 검색할 때, 불필요한 데이터들까지 스캔하는 것은 비효율적이므로 이런 경우에는 반드시 인덱스를 사용해야 한다.
Index Range Scan
- Index Range Scan을 통한 테이블 액세스는 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.
- 캐시에서 블록을 찾지 못하면 레코드 하나를 읽기 위해 매번 I/O call 발생하는 메커니즘
- 게다가 읽었던 블록을 반복해서 읽는다는 비효율이 존재한다.
🤔 인덱스를 사용하면 항상 유리할까?
정답은 NO! 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해선 안된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.
시퀀셜 액세스 vs. 랜덤 액세스
시퀀셜 액세스
- 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
- 인덱스: 리프 블록이 갖고 있는 주소값을 통해 앞 또는 뒤로 순차적으로 스캔
- 테이블:
- 오라클의 경우 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리
- 익스텐트 목록을 통해 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽음(Full Table Scan)
랜덤 액세스
- 논리적 또는 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식
참고자료
- [책] 친절한 SQL 튜닝 | 조시형
