Database Index

인덱스

데이터베이스 테이블에서 원하는 데이터를 빠르게 검색하기 위한 자료구조

테이블의 특정 컬럼에 인덱스를 설정하면 데이터 조회 시 디스크의 I/O와 검색 범위를 줄여 검색 성능을 최적화할 수 있다

내부 구조

일반적으로 B-트리(Balanced Tree) 구조로 구성됨

B-트리
다진 트리(Multi-way Tree)로, 각 노드가 여러 개의 자식 노드를 가질 수 있도록 설계된 균형 트리
항상 균형 상태를 유지하여 검색, 삽입, 삭제 연산의 시간 복잡도를 일정하게 유지한다
- 루트 노드(Root Node): 트리의 최상단 노드. 데이터를 탐색하는 시작점
- 브랜치 노드(Branch Node): 루트와 리프 노드 사이에 위치하며 탐색을 위한 중간 경로를 제공
- 리프 노드(Leaf Node): 트리의 최하단 노드. 실제 인덱스 키와 해당 데이터 위치가 저장됨

장점과 단점

장점

• 검색 속도 향상: 인덱스를 사용하면 테이블의 전체 데이터를 조회하지 않고, 인덱스를 통해 필요한 데이터만 빠르게 검색할 수 있음
• 디스크 I/O 감소: 필요한 데이터에만 접근하기 때문에 디스크 접근 횟수가 줄어들어 성능이 향상됨
• 정렬 속도 향상: 정렬된 상태로 유지되므로 ORDER BY 절을 사용할 때 성능이 향상됨

단점

• 쓰기 성능 저하: 데이터를 삽입, 수정, 삭제할 때 인덱스도 함께 수정해야 하므로 쓰기 작업의 성능이 저하될 수 있음
• 메모리와 디스크 공간 사용 증가: 인덱스를 생성하면 추가적인 저장 공간이 필요하며, 많은 인덱스를 생성하면 메모리 사용량이 증가함
• 잘못된 인덱스 사용시 오히려 성능 저하: 인덱스를 잘못 설계하거나 선택도가 낮은 컬럼에 인덱스를 설정하면 풀 테이블 스캔이 더 효율적일 수 있음

트리의 노드 & 페이지
- 페이지: 디스크와 메모리(버퍼풀) 사이에서 데이터를 읽고 쓰는 최소 단위
- 테이블 데이터와 인덱스 데이터는 모두 페이지 단위로 관리되며, 페이지 크기는 데이터베이스 설정에 따라 다름
- 페이지에 저장되는 데이터가 많아지면 필요한 데이터를 가져오기위해 디스크 접근이 증가하여 디스크의 I/O가 많아질 수 있고, 메모리에 적재할 수 있는 페이지 수가 줄어들어 메모리 캐신 효율이 감소함
* 디스크에서 데이터를 읽어올 때는 버퍼풀(Buffer Pool)이라는 메모리에 페이지를 캐싱하여 성능을 향상시킴

구성과 동작

• 인덱스는 페이지 단위로 저장되며 인덱스 키를 기준으로 항상 정렬된 상태를 유지
• 인덱스는 B-트리 인덱스 영역과 클러스터형 인덱스(Primary Key)로 나뉘며 모든 페이지는 키 값을 기준으로 정렬되어 있음
• PK 값이 클수록 인덱스 효율이 떨어질 수 있음. PK가 너무 크면 페이지당 저장할 수 있는 인덱스 키 수가 줄어들어 메모리 사용량이 증가하고 디스크 I/O가 많아질 수 있음
• PK의 선택도(Cardinality)가 인덱스 성능에 더 큰 영향을 미침. 선택도가 높은 열에 인덱스를 생성하면 검색 성능이 향상됨

종류

분류	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를 식별하기 위해 갖는 물리적인 주소 정보

인덱스와 테이블의 관계

• 클러스터형 인덱스 (Primary Key Index)
  • 리프 노드: 인덱스 키 + 실제 테이블 레코드
  • 테이블 정렬 여부: 테이블이 PK 기준으로 물리적으로 정렬됨
  • 인덱스 수: 테이블당 하나만 생성 가능
  • 탐색 시 I/O: 추가적인 I/O가 적음 (테이블 데이터 포함)
  • 사용 사례: Primary Key, 고유한 값으로 정렬이 필요한 경우
  • 테이블 데이터 자체가 인덱스에 포함되므로 테이블이 인덱스와 동일한 저장 구조를 가짐
  • 데이터 삽입/수정 시 물리적 재정렬 필요
  • 사용하기 좋은 경우
    • 테이블 데이터가 자주 업데이트 되지 않는 경우
    • 항상 정렬된 방식으로 데이터를 반환해야 하는 경우
    • 읽기 작업이 월등히 많은 경우
• 비클러스터형 인덱스
  • 인덱스와 테이블 데이터가 별도의 저장 공간에 저장됨
  • 리프 노드: 인덱스 키 + PK
  • 테이블 정렬 여부: 테이블이 정렬되지 않음
  • 인덱스 수: 테이블당 여러 개 생성 가능
  • 탐색 시 I/O: 추가적인 I/O 발생 (PK를 통해 테이블 데이터 참조)
  • 사용 사례: 보조 인덱스, 자주 조회되는 컬럼에 인덱스를 생성할 때
  • 비클러스터형 인덱스는 (인덱스 키, PK) 쌍으로 구성되며, 인덱스를 통해 PK를 찾은 후 테이블 데이터를 조회함
  • 데이터 변경 시 상대적으로 작은 부하
  • 사용하기 좋은 경우
    • where 절이나 join 절과 같이 조건문을 활용하여 테이블을 필터링하는 경우
    • 데이터가 자주 업데이트 되는 경우
    • 특정 컬럼이 쿼리에서 자주 사용되는 경우
• 유니크 인덱스
  • 컬럼의 값이 고유한 값이 되도록 보장하는 인덱스
  • Primary Key 또는 Unique 제약 조건이 설정된 컬럼에 자동으로 생성됨

주의사항

• 인덱스 생성은 중복도가 낮고 선택도가 높은 컬럼에 적용해야 효과적
  • 중복된 값이 많은 컬럼에 인덱스를 생성하면 성능 향상이 거의 없음
  • 선택도가 높다: 컬럼 값이 고유한 값일 확률이 높다
• 읽어야 할 레코드의 수가 전체 테이블 레코드의 20-25%를 넘어서면 인덱스 사용이 비효율적임. 풀 테이블 스캔이 더 효율적일 수 있음
• 범위 조건이 포함된 쿼리는 인덱스의 두 번째 컬럼 이후를 사용할 수 없는 경우가 많음. 이로 인해 멀티 컬럼 인덱스를 사용할 때는 범위 조건 위치를 주의해야 함