( 아직은 기본적인 설명이고 깊은 내용은 추후 보강 예정입니다 )
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
― Wikipedia
일반적으로, 쓰기(INSERT, UPDATE, DELETE) 성능을 희생해 읽기(SELECT) 성능을 높이는 방법.
읽기 성능이 좋아지는 이유
인덱스 구현에 보통 B+ 트리(자료구조)가 사용되는데,
컬럼(보통 WHERE 절에 들어가는)에 대해 인덱스를 생성하면 컬럼 값이 키 값으로 이 트리에 저장됨.
그런데 이 트리는 항상 정렬된 상태를 유지함. 그래서 키 값을 찾을 때 빠름.
그리고 잎 노드에 키 값과 함께 실제 레코드의 주소가 있음.
따라서, 인덱스를 이용하면 실제 레코드를 빠르게 찾아갈 수 있음.
B+ 트리
- B 트리의 특성에 더해서, 잎 노드들을 포인터로 순차적으로 연결해 놓음
- 실제 데이터에 대한 주소는 잎 노드에만 있음. 루트부터 탐색 시 잎에 도달할 때까지 내려가야 함
쓰기 성능이 떨어지는 이유
테이블의 레코드에 대해 쓰기 작업을 수행하면 인덱스 키 추가 또는 삭제 작업이 발생하는데,
이때 정렬(균형) 유지를 위해 노드 분리와 같은 연산이 수행됨.
이 노드 분리 연산은 최악의 경우 루트까지 전파될 수도 있음.
심지어 연산 비용의 대부분은 디스크 I/O가 차지함. (메모리와 CPU에서 처리하는 시간은 얼마 안 됨)
주의
- 인덱스는
SELECT에서만 사용되는 것은 아님.UPDATE나DELETE에서도 먼저 찾아야 하기 때문에 사용됨. - 인덱스 키 값이 커지면 페이지(또는 블록, 디스크에 데이터를 저장하는 기본 단위) 하나에 저장할 수 있는 키 개수가 줄어듦. 이때, 여러 페이지를 읽어야 하는 상황이 벌어지면 디스크 I/O가 많아지고 그만큼 느려짐.
- 읽기 위주의 테이블이라고 해도 너무 많은 컬럼에 대해 인덱스를 생성하면 인덱스의 크기가 지나치게(메모리보다 더) 커져서 문제가 될 수 있음. (-> 파티션)
함께 볼 내용들
- 체인지 버퍼
- InnoDB 스토리지 엔진에서의 레코드 잠금에 인덱스의 설계가 미치는 영향
- 검색 목적이 아닌, 정렬이나 그루핑과 같은 작업을 위한 인덱스 생성
ref.
Real MySQL 8.0, 위키북스, 2021
자료구조, 방송통신대학교, 2023
