DB Index

index 인덱스

• 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터 구조
• 데이터를 빠르게 찾을 수 있는 수단으로, 테이블에 대한 조회 속도를 높여 주는 자료 구조
• 인덱스를 통해 전체 데이터의 검색 없이 필요한 정보에 대해 신속한 조회 가능
• DB 사용 시 데이터의 양(row)에 따라 실행 결과의 속도가 차이가 남
• 데이터의 양이 증가할수록 실행 속도는 느려지고, JOIN이나 서브 쿼리 사용 시 곱 연산이 일어나 데이터 양이 증가하기 때문에 WHERE 조건에서 필요한 데이터만 추출 후 사용하는 것이 좋음, 쿼리의 성능을 높이는 데 중요한 것은 인덱스를 적재적소로 활용하는 것
• index는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조로 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됨
• 인덱스 생성 컬럼을 WHERE 조건으로 거는 등 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있음
• 인덱스 생성 시 데이터를 오름차순으로 정렬하기 때문에 정렬된 주소체계

옵티마이저: 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진, 컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저

인덱스 개념

---

인덱스 특징

• 기본키(PK) 컬럼은 자동으로 인덱스 생성됨
• 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음
• Table Full Scan: 테이블의 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색
• Index Range Scan: 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있음
• 조건절에 =로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음

---

인덱스 종류

유형	설명
순서 인덱스 (Ordered Index)	- 데이터가 정렬된 순서로 생성되는 인덱스 - B-Tree 알고리즘 활용(오름차순/내림차순 지정가능)
해시 인덱스 (Hash Index)	- 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스 - 데이터 접근 비용 균일, 튜플(Row) 양에 무관
비트맵 인덱스 (Bitmap Index)	- 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스 - 수정 변경이 적을 경우 유용(생년월일, 상품번호 등)
함수기반 인덱스 (Functional Index)	- 수식이나 함수를 적용하여 만든 인덱스
단일 인덱스 (Singled Index)	- 하나의 컬럼으로만 구성한 인덱스 - 주 사용 컬럼이 하나일 경우 사용
결합 인덱스 (Concatenated Index)	- 두 개 이상의 컬럼으로 구성한 인덱스 - WHERE 조건으로 사용하는 빈도가 높은 경우 사용
클러스터드 인덱스 (Clutered Index)	- PK 기준으로 레코드를 묶어서 저장하는 인덱스 - 저장 데이터의 물리적 순서에 따라 인덱스가 생성 - 특정 범위 검색 시 유리함

---

인덱스 스캔 방식

구분	설명	개념도
인덱스 범위 스캔	인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후 리프 블록에 필요한 범위만 스캔
인덱스 전체 스캔	수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
인덱스 단일 스캔	수직적 탐색만으로 데이터를 찾는 스캔 방식
인덱스 생략 스캔	선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔 방식

루트 블록: 가장 상위 블록

리프 블록: 가장 아래 단계에 존재하는 블록

---

인덱스 적용 기준

인덱스 분포도가 10~15% 이내인 경우 사용

분포도 = (1 / (컬럼 값의 종류)) X 100
분포도 = (컬럼 값의 평균 Row 수) / (테이블의 총 Row 수) X 100

• 분포도가 범위 이상이더라도 부분처리를 목적으로 하는 경우 적용
• 조회 및 출력 조건으로 사용되는 컴럼인 경우 적용
• 인덱스 자동생성 기본키와 Unique키의 제약조건을 사용할 경우 적용

분포도(Selectivity): 특정 컬럼의 데이터가 테이블에 평균적으로 분포되어 있는 정도

---

인덱스 컬럼 선정

• 분포도가 좋은 컬럼은 단독적으로 생성
• 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성
• 결합 인덱스는 구성되는 컬럼 순서 선정(사용빈도, 유일성, 정렬 등)에 유의함
• 가능한 한 수정이 빈번하지 않은 컬럼 선정

---

인덱스 사용 이유

• 데이터들이 정렬이 되어있다는 점: 조건 검색이라는 영역에서 굉장한 장점

 

조건 검색 Where절의 효율성

• 테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 뒤죽박죽으로 저장됨
• -> 풀 테이블 스캔 (Full Table Scan): Where절에 특정 조건에 맞는 데이터들을 찾아낼때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 함
• 인덱스 테이블: 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건 (Where)에 맞는 데이터들을 빠르게 찾아낼 수 있음

 

정렬 Order by절의 효율성

• 인덱스(Index)를 사용하면 Order by에 의한 Sort과정을 피할 수 있음
• Order by는 굉장히 부하가 많이 걸리는 작업: 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생됨
• 인덱스를 사용: 이미 정렬이 되어 있어 가져오기만 하면 되기 때문에 전반적인 자원의 소모를 하지 않아도됨

 

MIN, MAX의 효율적인 처리 가능

• 데이터가 정렬되어 있기에 얻을 수 있는 장점
• MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 됨
• 테이블을 다 뒤져서 작업하는 것(Full Table Scan)보다 훨씬 효율적으로 찾을 수 있음

---

인덱스 단점

• 정렬된 상태를 계속 유지 시켜줘야 한다는 점: 레코드 내에 데이터값이 바뀌는 부분이라면 악영향을 미침
• INSERT / UPDATE / DELETE를 통한 데이터 수정: INDEX 테이블 내에 있는 값들을 다시 정렬
• INDEX 테이블 / 원본 테이블 이렇게 두 군데에 데이터 수정 작업해줘야함
• 검색시에도 인덱스가 무조건 좋은 것이 아님
• 지나치게 많은 인덱스는 오버헤드(Overhead) 작용
• 추가적인 저장 공간이 필요함을 고려해야 함
• 넓은 범위를 인덱스 처리 시 오히려 전체 처리보다 많은 오버헤드를 발생시킬 수 있음
• 인덱스와 테이블의 저장 공간을 적절히 분리될 수 있도록 설계해야 함 
  • 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적
  • 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 나음
  • 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요함

Overhead: 어떤 처리를 하기 위해 들어가는 간접적인 처리 시간 · 메모리 등

---

인덱스 관리

• INSERT: 새로운 데이터에 대한 인덱스를 추가
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가

• 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있음
• 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생함
• -> 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는 작업으로 이를 대신함

 

 

---

참고자료

수제비 정보처리기사 실기 3-27, 7-9

코딩팩토리, [DB] 데이터베이스 인덱스(Index) 란 무엇인가?