[Database] Clustered vs Non-Clustered Index
Database의 Index에 대한 이해
Index
💡 데이터베이스의 검색 속도 향상을 위해 유지, 관리하는 자료구조
인덱스가 없는 테이블의 경우 Full scan이 발생함
인덱스의 장점과 단점
- 장점
- 검색 속도 향상
- 시스템 부하를 줄여, 시스템 전체 성능향상에 기여 가능
- 단점
- 인덱스를 위한 추가 공간 필요
- 생성에 시간이 소요될 수 있음
- Insert, Upate, Delete가 자주 발생한다면 성능 하락 발생할 수 있음
Index의 종류
1. Clustered Index
- 테이블 당 한 개만 생성 가능
- 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬
- 데이터를 인덱스로 지정한 컬럼에 맞춰서 물리적으로 재배치
- 물리적 재배치로 인해 리프노드에 해당 레코드 자체를 저장
- 테이블 구조에 영향을 미치는 인덱스
- CUD가 일어날 때 물리적 재배치를 해야 하기 때문에 CUD 성능이 느림
- 테이블 생성 시 PK 컬럼에 자동 생성
Clusted Index 생성 및 구조
- 클러스터형 인덱스를 구성하기 위해 행 데이터를 해당 열로 정렬한 후, 루트페이지 생성
- 클러스터형 인덱스는 루트 페이지와 리프 페이지로 구성되며, 리프 페이지는 데이터 그 자체
- 클러스터형 인덱스는 검색 속도가 비클러스터형 인덱스보다 빠름
장점
- 비클러스터형 보다
검색 속도빠름
단점
- 데이터
CUD 속도느림 - 대용량의 데이터가 입력된 상태에서, 클러스터형 인덱스를 생성할 경우 시스템 부하를 줄 수 있음
- Inno DB
- primary key는 항상 clustered index
- unique index는 정의된 컬럼 중 하나
- 보이지 않는 컬럼을 내부적으로 추가해 사용
2. Non-Clustered Index
- 테이블 당 여러개 생성 가능(최대 249개)
- 인덱스 키 값에는 해당 데이터에 대한 포인터가 존재
- 설정한 인덱스를 루트노드로 정렬. 루트노드 페이지 하위에 있는 리프노드 페이지는 해당 인덱스에 해당하는 레코드의 위치 포인터(RID, Row Identifiers) 정보 저장
- 인덱스의 구조는 데이터 행과 독립적
- 한 테이블에 여러 개 생성 가능
장점
- 클러스터형 보다
CUD 속도가 더 빠름 여러 개생성 가능- 단, 남용할 경우 오히려 성능을 떨어뜨릴 수 있음
단점
- 리프 페이지가 데이터가 아닌, 데이터가 위치한 포인터임. 클러스터형 보다
검색 속도가 느림
- Inno DB
- 힙 파일(로우가 저장된 곳)의 위치가 아닌 기본키를 참조
Non-Clustered Index 생성 및 구조
- 비클러스터형 인덱스는 데이터 페이지를 건들지 않고, 별도의 장소에 인덱스 페이지를 생성
- 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬한 후 위치 포인터를 생성
생각
프로젝트에서 테이블 설계를 진행한 경험이 있고, 해당 테이블로 애플리케이션을 개발까지 한 경험이 있다. 설계한 테이블은 AS-IS 기준으로 사용하지 않는 칼럼을 삭제하는 방향으로 진행되었으며, 테이블에 대한 primary key는 엔티티에 대한 key 한 개였던 As-Is 기준이 그대로 적용되었다.
다만 다른 점이 있다면, 과거에는 Oracle의 시퀀스를 통해 id를 부여했다면, To-Be 프로젝트 표준은 문자열의 UUID 적용이었다. MySql의 InnoDB 전략으로 생성된 테이블이었기 때문에 UUID 값으로 CUD가 발생할 경우, 난수로 생성된 id로 인해 매번 데이터가 재정렬되는 문제가 발생할 수 있다 생각이 들었다.
기존 데이터가 수 억건에 이르렀기 때문에 성능 이슈가 있을 수 있음에도 뚜렷한 해결책을 제시하기엔 업무 배경지식도, 경험도 부족하기 때문에 자신있게 얘기할 수 없는 상황이 그저 슬플 뿐이다.
