[SQLD] 인덱스

Fastcampus 합격의 하이패스 : SQLD 자격 과정 강의를 들으면서 학습한 내용을 정리한 글입니다.

합격의 하이패스 : SQLD 자격 과정 | 패스트캠퍼스

 

개념 정리

인덱스

인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념입니다. 검색 조건에 부합하는 데이터를 효과적으로(빠르게) 검색할 수 있도록 도와줍니다.한 테이블은 0~N개의 인덱스를 가질 수 있고 테이블에 인덱스가 과도하게 많으면, INSERT, UPDATE, DELETE와 같은 DML 작업 시 부하가 발생합니다.

 

B*Tree 인덱스

DBMS에서 널리 사용되는 일반적인 인덱스입니다. 루트 블록, 브랜치 블록, 리프 블록으로 이루어져 있습니다. 가장 상위에 존재하는 것이 루트 블록이고, 브랜치 블록은 분기를 목적으로 하는 블록입니다.

리프 블록은 트리의 가장 아래 단계에 존재하는 블록입니다. 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자인 ROWID로 구성되어 있습니다.

 

 

 

---

인덱스의 상세 구조

루트 / 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖습니다. 키 값은 하위 블록에 저장된 키 값의 범위를 나타냅니다. LMC(LeftMost Child, 자식 노드 중 가장 왼쪽 끝에 위치한 블록)가 가리키는 주소로 찾아간 블록에는 키 값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장되어 있습니다.

리프 블록에 저장된 각 레코드는 키 값 순으로 정렬되어 있을 뿐만 아니라, 테이블 레코드를 가리키는 주소값인 ROWID를 가지고 있습니다.

인덱스의 키 값이 같으면 ROWID 순으로 정렬됩니다. 인덱스를 스캔하는 이유는 검색조건을 만족하는 소량의 데이터를 빠르게 찾고 거기서 ROWID를 얻기 위함입니다.

ROWID의 구성

항목	구성
ROWID	데이터 블록 주소 + 로우 번호
데이터 블록 주소	데이터 파일 번호 + 블록 번호
블록 번호	데이터 파일 내에서 부여한 상대적 순번
로우 번호	블록 내 순번

 

랜덤 엑세스의 최소화 

인덱스 스캔 후 추가 정보를 가져오기 위해서, Table Random Access를 수행하게 됩니다. 이 때 인덱스 스캔의 비효율이 발생하면 Table Random Access의 횟수도 많아지게 되면서 성능이 나빠지게 됩니다.

 

Single Block I/O가 발생하는 상황

Single Block I/O가 발생하는 상황	인덱스 루트 블록을 읽을 때
	인덱스 루트 블록에서 얻은 주소로 브랜치 블록을 읽을 때
	인덱스 브랜치 블록에서 읽은 주소로 리프 블록을 읽을 때
	인덱스 리프 블록에서 읽은 주소로 테이블 블록을 읽을 때

 

테이블 풀 스캔과 인덱스 스캔

스캔 유형	설명
테이블 풀 스캔	테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식입니다.
	HIGH WATER MARK는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치로써, 테이블 풀 스캔시 HWM 까지의 블록에 있는 모든 데이터를 읽어야 하기 때문에 시간이 오래 걸릴 수 있습니다.
	테이블 풀 스캔으로 읽은 블록은 재사용성이 낮다고 보고 메모리 버퍼 캐시에서 빠르게 제거될 수 있도록 관리합니다. (LRU 리스트의 끝으로 가서 금방 제거됩니다.)
	옵티마이저가 테이블 풀 스캔을 선택하는 경우	SQL 문에 조건이 존재하지 않는 경우
		SQL 문의 조건을 기준으로 사용 가능한 인덱스가 없는 경우
		옵티마이저의 판단으로 테이블 풀 스캔이 유리하다고 판단하는 경우
		전체 테이블 스캔을 하도록 강제로 힌트를 지정한 경우
인덱스 스캔	인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출하는 엑세스 기법입니다.
	인덱스 리프 블록은 인덱스를 구성하는 컬럼과 ROWID로 구성됩니다.
	인덱스를 읽어서 대상 ROWID를 찾으면 해당 ROWID로 다시 테이블을 찾아가야 합니다.(Table Random Access가 발생합니다.) 하지만 SQL 문에서 필요로 하는 컬럼이 모두 인덱스 구성 컬럼이라면 테이블을 찾아갈 필요는 없습니다.
	일반적으로 인덱스 스캔을 통해 데이터를 추출하면 해당 결과는 인덱스의 컬럼의 순서로 정렬된 상태로 반환됩니다.
	인덱스 스캔으로 읽은 블록은 테이블 풀 스캔에 의해 읽은 블록에 비해 버퍼 캐시에 더욱 오래 남아 있습니다.

 

테이블 풀 스캔 vs 인덱스 스캔

인덱스 스캔은 부하가 큰 작업입니다. 따라서 인덱스 스캔이 테이블 풀 스캔보다 성능이 반드시 좋다고 할 수 없습니다. 인덱스 스캔은 데이터의 건수가 많은 테이블에서 소량의 데이터를 스캔할 때 사용해야 합니다. 즉, 인덱스 스캔의 효율을 높여서 절대적인 논리적 I/O를 줄이는 노력을 해야합니다.

테이블 풀 스캔	인덱스 스캔
항상 이용 가능합니다.	인덱스가 존재 해야지만 이용 가능합니다.
한 번에 여러 개의 BLOCK을 읽습니다.	한 번에 한 개의 BLOCK을 읽습니다.
많은 데이터를 조회 시 성능상 유리합니다.	극히 일부분의 데이터를 조회할 시 유리합니다.
Table Random Access의 부하가 없습니다.	Table Random Access에 의한 부하가 발생합니다.
읽었던 블록을 반복해서 읽는 경우는 없습니다.	읽었던 블록을 반복해서 읽는 비효율이 발생할 수 있습니다. (논리적인 블록 I/O의 개수도 많아집니다.)

 

 

---

인덱스 범위 스캔

인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식입니다. 인덱스 스캔으로 특정 범위를 스캔하면서 대상 레코드를 하나하나 리턴하는 방식입니다.

 

인덱스 유일 스캔

인덱스를 사용하여 단 하나의 데이터를 추출하는 방식입니다. 유일 인덱스는 중복 레코드를 허용하지 않습니다. 유일 인덱스는 반드시 '=' 조건으로 조회해야 합니다. 

 

인덱스 전체 스캔

인덱스를 처음부터 끝까지 전체를 읽으면서 조건에 맞는 데이터를 추출합니다. 데이터를 추출 시 리프 블록에 있는 ROWID로 테이블의 레코드를 찾아가서 조건에 부합하는지 판단하고, 조건에 부합하면 해당 행을 리턴합니다.

 

인덱스 스킵 스캔

인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 스캔방식입니다. 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value의 개수가 적고, 후행 컬럼의 Distinct Value의 개수가 많을 때 유용합니다. 루트 또는 브랜치에서 읽은 컬럼 값 정보를 이용해서 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 엑세스합니다.

 

인덱스 고속 전체 스캔

인덱스 고속 전체 스캔(Index Fast Full Scan)은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 Multi Block I/O 방식으로 읽어들입니다. 병렬 인덱스 스캔도 가능합니다. 인덱스 리프 노드가 갖는 연결 구조를 무시하면서 데이터를 읽기 때문에 인덱스 정렬 순서의 보장을 받지 못합니다.

 

인덱스 역순 범위 스캔

인덱스 리프 블록은 Doubly Linked List 자료구조 방식으로 저장되어 있습니다. 이 자료구조의 특성을 이용하여 인덱스를 역순으로 읽을 수 있습니다. 인덱스를 뒤에서부터 앞쪽으로 스캔하므로 내림차순으로 정렬된 결과 집합을 얻을 수 있습니다. (스캔 순서를 제외하고는 범위 스캔과 동일합니다.)