[DB] 데이터베이스 인덱스(Index) 정의, 종류, 설계 : SQL 검색성능 향상

 

 

인덱스(Index)란?

인덱스(Index)는 데이터 검색속도 향상을 위하여 테이블에 저장된 로우(row)를 식별가능하도록 저장한 데이터베이스 오브젝트입니다.

 

일반적으로 책을 볼 때 목차, 차례가 존재하는데 이것은 데이터베이스의 인덱스와 유사한 개념입니다.

목차부터 찾으면 원하는 정보가 몇 페이지에 있는지 금방 알 수 있고 어느 정보가 어떻게 구성되어 있는지 전체적으로 파악하기도 용이합니다.

 

B* Tree 기반 인덱스(Index)

인덱스(Index)는 Balanced Tree 기반 인덱스로 Root 블록, Branch 블록, Leaf 블록으로 구성되어 있습니다.

Balanced Tree는 모든 Leaf 블록이 동일한 Depth를 유지하도록 하여 균형을 유지합니다.

그러면 데이터 검색을 최대 Depth 이하로 하기 때문에 검색속도를 보장받을 수 있습니다.

그래서 RDBMS에서 B* Tree를 채용하였습니다.

 

Leaf Block에서 주황색 선 부분이 있어서 검색을 옆으로 할 수 있어서 매번 Root에서 검색해서 내려올 필요가 없기 때문에 인덱스가 존재하면 그만큼 속도가 더 빠릅니다.

 

---

 

인덱스(Index)의 종류

인덱스는 데이터의 고유성을 확보하여 검색성능을 향상시키는 객체입니다.

해당 컬럼의 데이터들을 정렬하여 별도의 공간에 데이터의 ROWID와 함께 저장합니다.

인덱스에 어떤 것들이 있는지 알아보도록 하겠습니다.

분류	Index 종류	설 명
유일성	Unique Index	Index 키는 연관된 테이블의 하나의 행만 가리킴
	Non-Unique Index	Index 키는 연관된 테이블의 여러 행을 가리킬 수 있음
구성컬럼 수	Single Index	하나의 컬럼으로 구성된 Index
	Composite Index	여러 개의 컬럼 조합으로 생성된 Index(32개까지 조합)
클러스터	Clustered Index	데이터의 레코드 순서가 Index 페이지의 정렬순서와 동일하거나 비슷하게 만들어진 index
	Non-Clustered Index	데이터 레코드의 물리적 순서가 Index의 엔트리 순서와 상관없이 저장되도록 구성된 Index
함수	Function-Based Index	함수나 표현식의 계산값으로 인덱스 생성
기타	Bitmap Index	비트를 이용하여 컬럼값을 저장하고 ROWID를 자동으로 생성
	Reverse key Index	Index 컬럼의 순서는 유지하면서 해당 컬럼 값의 각 바이트 위치를 거꾸로 저장 ex) 컬럼값 1234 -> 인덱스 4321

- ROWID : row를 식별하기 위해 갖는 물리적인 주소 정보

 

Index Scan의 종류

Query를 수행할 때 인덱스(Index)를 이용한 Scan에 어떤 종류가 있는지 알아보도록 하겠습니다.

 

Index Full Scan

- Index 전체를 탐색하는 방식

- 사용컬럼들이 모두 하나의 인덱스에 존재

- LIKE, IS NOT NULL 연산자 등 조건에서 가능(정렬 보장)

- INDEX 선두컬럼이 조건에 없으면 Table Full scan을 고려하지만 Table Full Scan보다 I/o를 줄일 수 있거나 정렬된 결과를 쉽게 없을 경우 선택

 

Index Fast Full Scan

- Index 블록만을 스캔하여 원하는 데이터를 검색하는 방식

- SELECT 절, 조건절에 사용한 컬럼이 모두 인덱스로 구성

- 데이터 정렬 미보장

- 병렬 수행 가능

 

Index Skip Scan

- 결합인덱스의 선행조건에 대한 조건이 없고 후행컬럼에 대한 조건만 있는 경우

 

함수 기반 인덱스

- 컬럼에 함수를 적용한 상태로 생성한 인덱스

- 장점 : 어플리케이션 수정 없이 성능향상 가능

- 주의사항 : 데이터변경 시 처리비용이 크므로 자주 변경되는 컬럼 적용하지 않는 것이 좋음

 

비트맵 인덱스

- bit 값을 이용하여 컬럼값을 저장하고 ROWID를 자동 생성하는 인덱스

- 많은 행을 가지며 Key 열은 적은 분포도(5 이하)를 가질 때 유리 (예: 성별, 결혼여부 등)

- OR 연산자를 포함하는 여러 개의 조건을 자주 사용할 때 유리

 

 

---

 

인덱스(Index) 설계

인덱스 선택 및 순서에 따라 인덱스를 이용한 스캔을 하므로

인덱스를 설계할 때부터 순서를 유의해서 만들어야 합니다.

- 조건절에 항상 또는 자주 사용되는 컬럼 선택

- '='조건으로 자주 조회되는 컬럼들을 앞쪽 순서에 위치

- 분포도가 좋은 컬럼 선정 (분포도 15% 이내)

설정기준	설 명	정 도
카디널리티 (Cardinality)	한 컬럼이 갖고 있는 값의 중복 정도가 낮을수록 좋음	높은수록 적합
선택도 (Selectivity)	특정 값을 얼마나 잘 선택하는지에 대한 지표 선택도 = 컬럼 특정값 row수 / 총 row수 X 100 선택도 = 컬럼 평균값 row수 / 총 row수 X 100	낮을수록 적합 (5~10% 적정)
활용도	해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값	높을수록 적합
중복도	중복 인덱스 여부에 대한 값	없을수록 적합

인덱스(Index) 설계절차

인덱스 설계절차는 크게 3 부분으로 이루어져 있습니다.

 

1. 접근경로 수집

- 반복 수행되는 접근경로 : Join 컬럼

- 분포도가 양호한 컬럼

- 조회조건에 사용되는 컬럼

- 자주 결합되어 사용되는 컬럼

- 데이터 정렬 순서와 그룹핑 컬럼

- 일련번호를 부여한 컬럼

- 통계 자료 추출 조건

- 조회 조건이나 조인조건 연산자

 

2. 분포도에 의한 후보 컬럼 선정

- 분포도 10~15% 선정 추천

분포도(%) =  데이터별 평균 row 수 / 테이블의 총 row수 X 100

 

3. 컬럼 조합 및 순서 결정

- 항상 사용되는 컬럼을 선두컬럼 선정

- 등치(=) 조건으로 사용되는 컬럼을 선행컬럼으로 선정

- ORDER BY, GROUP BY 순서 적용