Database에서 index의 종류는 다양하지만 크게 Clustered Index와 Non-Clustered Index로 나뉜다.
Cluster 사전적 의미
- 군집화, 집속체, 무리, 밀접해있는 다수의 무언가를 총칭한다.
Clustered Index: 실제 데이터와 같은 무리의 인덱스
Non-Clustered Index == (Secondary Index,보조인덱스): 실제 데이터와 다른 무리의 별도의 인덱스
😲 우리도 모르게 인덱스가 생겼었다???
Table을 생성할 때 PK는 Clustered Index가 Unique 정보를 통해서는 Non-Clustered Index가 생성되게 됩니다.
PK가 없는 경우
- MySQL의 InnoDB는 기본적으로 데이터를 저장하고 Indexing하기 위해 Primary Key가 필요하다.
PK가 없다면 아래와 같은 순서로 데이터베이스(구체적으로는 스토리지 엔진)에서 별도의 처리를 통해 PK를 설정한다.
- 기본적으로 PK를 클러스터링 키로 선택한다.
- PK가 없다 면 NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택한다.
- 후보군이 없다면 내부적으로 자동 증가 유니크 컬럼을 추가한 후 클러스터링 키로 선택한다.
(사용자에게 보이지 않기 때문에 접근할 수 없다.)
ROW 기반의 복제나 InnoDB Cluster에서는 모든 테이블이 PK를 가져야만 하는 정상적인 복제 성능을 보장하기도 하므로 적절한 PK가 없다면 사용할 수 있는 자동 증가 값을 PK로 사용하자.
PK와 Unique 옵션을 주지 않고 테이블을 생성했다.
- 3번으로 생성되는 내부 PK는 사용자에게 노출되지 않으며, 쿼리에서 사용할 수도 없다. MySQL을 믿고 PK를 안쓰고 UNIQUE Column을 두는건 올바르지도 않고 클러스터 인덱스는 테이블 당 하나만 가질 수 있는 혜택이므로 반드시 PK를 생성해주자
insert 명령을 통해서 정렬되지 않게 데이터를 넣어줬다.
이후 name 컬럼에 not null + unique 옵션을 줬는데 name이 PK가 되면서 데이터가 정렬된 것을 볼 수 있었다.
만약 여기서 id 컬럼에 PK를 주게되면 id 값으로 정렬되면서 id 컬럼이 PK가 되고 name이 unique + not null은 unique가 된것을 볼 수 있었다. 즉 우선순위는 PK인것을 볼 수 있었다.
table을 생성할 때 위에서 이야기한 것처럼 PK 혹은 Unique 정보를 통해서 각각의 Index가 생성되는 것을 확인할 수 있었다.
email을 unique 옵션을 주니까 index가 하나 생긴것을 볼 수 있었다. 즉 Clustered Index는 테이블당 하나의 컬럼만 가능하지만 Non-Clustered Index 인덱스는 여러개의 컬럼이 올 수 있는것을 확인할 수 있었다.
이정도면 궁금증이 생겼다고 생각하고 Clustered Index와 Non-Clustered Index에 대해서 바로 알아보고 싶지만
테이블의 저장 방식을 이해하기 위해서는 페이지(또는 블럭)에 대해 알아야 하기 때문에 Page에 대해서 잠깐 알고 넘어가자
페이지(Page)란?
- 페이지란 디스크와 버퍼풀(메모리)에 데이터를 읽고 쓰는 최소 작업 단위이다.
일반적인 인덱스를 포함해 PK(클러스터 인덱스)와 테이블 등은 모두 페이지 단위로 관리된다.
- 만약 쿼리를 통해 1개의 레코드를 읽고 싶더라도 결국은 하나의 블록을 읽어야 하는 것이다.
페이지에 저장되는 개별 데이터의 크기를 최대한 작게 하여, 1개의 페이지에 많은 데이터들을 저장할 수 있도록 하는 것이 상당히 중요하다.
페이지에 저장되는 데이터의 크기가 큰 경우 어떤 문제가 있을까?
- 디스크 I/O가 많아질 수 있음
- 메모리에 캐싱할 수 있는 페이지의 수가 줄어들 수 있음
조회하는 레코드 건수가 많아서 1개의 페이지만으로 처리가 안된다면 다른 페이지를 읽어야하면 추가적으로 SSD, HDD에서 I/O 작업이 일어나게 된다.
- 즉, 읽어야 하는 페이지의 수 만큼 성능이 떨어지게 된다.
- 데이터베이스 성능 개선 혹은 쿼리 튜닝은 디스크 I/O 자체를 줄이는 것이 핵심이며 인덱스의 자료구조가 BST가 아닌 B-TREE 기반으로 사용하는 이유도 한번의 I/O 작업에서 더 많고 유용한 데이터를 가져오는 이유도 있다.
디스크 I/O를 통해 페이지를 읽어오면 버퍼풀이라는 메모리에 캐싱해두기 때문에 메모리의 효율을 위해서도 중요하다.
- 개별 데이터의 크기가 커지면 페이지 자체의 크기가 커지면서, 메모리에 캐싱해둘 수 있는 페이지 수도 줄어들게 되고 메모리 효율이 떨어지게 된다.
🤔 주의점
5개의 non-clustered 인덱스를 갖는 테이블의 PK 크기에 따라 인덱스의 크기를 비교한 표이다.
- 모든 non-clustered 인덱스는 PK를 가지고 있기 때문이다.
레코드가 증가할 때마다 50바이트씩 늘어나며 레코드 건수가 100만건이 되는 경우 PK의 크기에 따라서 인덱스의 크기가 190MB나 증가했다.
- 인덱스가 커질수록 성능을 내기 위해 그만큼의 메모리가 필요하므로 PK는 신중히 선택해야 한다.
(신경쓰기 싫다면 Auto-Increment를 사용하자)
Clustered Index
클러스터링 인덱스는 데이터가 테이블에 물리적으로 저장 되는 순서를 설정한다.
위에서 클러스터링 인덱스로 지정되었을 때 특징들을 확인할 수 있었다.
- id 컬림이 클러스터 형 인덱스로 지정이 되어서 데이터들이 id값 기준으로 정렬된것을 볼 수 있었다.
- 테이블 데이터는 오직 한 가지의 방법으로만 정렬되기 때문에 테이블 당 하나의 클러스터 형 인덱스인 id만 존재하는 것을 볼 수 있었다.
데이터 2를 추가하면 3,4를 뒤로 한칸씩 밀고 중간에 2가 삽입된 것을 볼 수 있었다.
실제 데이터들이 같은 무리를 가지기 때문인데 INSERT로 인해서 움직이는 데이터들이 많을수록 소모되는 비용이 커지게 된다. 따라서 Primary Key 값을 어떤 컬럼으로 선택하는가에 따라 Database의 성능이 달라질 수 있다.
- JPA를 사용할 때 키 생성 전략을 자동생성 방식으로 사용하는데 UUID혹은 email 같은 유니크한 값을 PK로 사용하는 경우 데이터가 중간에 삽입되어 DB 성능에 문제가 생길 수 있다.
@Entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Member {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true, nullable = false)
private String name;
private String email;
}
Clustered 인덱스의 구조는 Data Page(실제 데이터가 저장된 장소)의 데이터들이 순차적으로 정렬되어있다.
- Leaf level 과 DataPage가 동일하다고 생각하면 된다.
Insert 시 Data가 정렬되고 Index 는 Data Page의 첫 번째 레코드의 주소값을 가지게 된다.
- Index 가 곧 바로 Data Page 에 접근하기 때문에 Non-Clustered Index 보다 보통 동작이 빠르다.
- Page를 타고 들어가는 I/O 작업이 덜 발생하기 때문에 성능상 이점이 있지만 INSERT/UPDATE/DELETE에 대해서는 정렬으로 인한 데이터들의 이동이 있기 때문에 더 느리다.
- INSERT시 레코드를 추가하기 위해 PK 기반으로 레코드의 저장 위치를 탐색해야 하고 PK를 변경하는 것을 레코드가 저장된 물리적인 위치를 변경하는 작업이 수반되기 때문에 성능이 느리다.
Data가 정렬되어 저장되므로 Non-Clustered Index에 비해 범위로 질의를 하는 것에 유리하다.
기본적으로 인덱스 칼럼 기준으로 레코드들이 오름차순으로 정렬된다.
Clustered Index를 생성하면 좋을 때
- 테이블 데이터가 자주 업데이트 되지 않는 경우
- 항상 정렬 된 방식으로 데이터를 반환해야하는 경우 (ORDER BY, 범위 검색에 막강하다.)
- 읽기 작업이 월등히 많은 경우 유용하다.
일반적인 온라인 환경에서 읽기와 쓰기의 비율이 8:2, 9:1 정도이기 때문에 CREATE/UPDATE/DELETE 성능을 어느정도 감안하고 조회 성능을 향상시키기 위해서 사용한다.
Non-Clustered Index
Non-Clustered Index는 말 그대로 클러스터 형의 반대인 실제 데이터와 다른 무리의 별도의 인덱스를 뜻한다.
- 따라서 테이블에 저장 된 물리적인 순서에 따라 데이터를 정렬하지 않는다.
테이블의 레코드는 그대로두고 지정된 컬럼에 대해 정렬된 인덱스를 만든다.
- 물리적으로 레코드를 정렬하지 않기 때문에 Clustered Index보다 읽기 속도 는 성능이 떨어지지만, 추가/수정/삭제의 성능은 더 뛰어나다.
Non-Clustered Index를 생성하는 방법
- unique 제약조건 적용시 자동으로 생성되며 직접 index를 생성시 Non-Clustered Index가 생성된다.
Non-Clustered 인덱스의 구조는 Clustured 구조와는 다르게 Leaf Level과 Data Page가 구분된다.
- Data Page의 데이터는 정렬 되어있지 않다.
구조를 보고 다시 정리해보면 Non-Clustered Index 구조에서 Data Page는 따로 정렬을 하지 않기 때문에 Clustered Index보다는 CREATE, UPDATE, DELETE 영향이 덜하지만 실제 Data Page에 접근해야 하기 때문에 Clustered Index보다 READ 성능이 떨어진다.
Clustered Index는 별도의 저장공간이 필요로 하지 않지만 Non-Clustered Index는 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
- 불필요한 저장 공간 및 성능의 문제가 있을 수 있기 때문에 사용되지 않는 인덱스는 바로 제거해주자!
테이블당 여러개의 Non-Clustered Index가 존재할 수 있으며 리프 페이지에 Data Page의 주소를 담고 있다.
Non-Clustered Index를 생성하면 좋을 때
- where절이나 Join 절과 같이 조건문을 활용하여 테이블을 필터링 하고자할 때
- 데이터가 자주 업데이트 될 때
- 특정 컬럼이 쿼리에서 자주사용 될 때
Clustered Index와 Non-Clustered Index를 혼합해서 사용하는 경우
대부분 하나의 테이블에 클러스트형 인덱스와 비클러스트형 인덱스가 혼합되어 있는 경우가 많다.
- PK 는 기본적으로 존재하고, 추가로 조회가 자주 발생하는 컬럼에 대해 Non-Clustered Index를 추가한다.
- 두 개를 혼합하여 사용하는 경우Non-Clustered Index를 먼저 조회 후 클러스트형 인덱스 키를 조회하여 데이터를 검색한다.
Non-Clustered Index 에서 PK값이 아니라 Data Page의 주소 값이라면?
인덱스 컬럼인 name으로 정렬하는것 뿐만아니라 Data Page의 주소값도 변경되어야 하며 Non-Clustered Index의 이점을 얻지 못한다.
- 따라서 id 컬럼이 변경되지 않으면 Non-Clusterd Index의 Page가 변경되지 않도록 혼합해서 사용할 때는 PK값이 사용되었다.
주의할 점
Non-Clustered Index가 Clustered Index의 컬럼 값을 저장하게 된다.
- Clustered Index의 컬럼의 크기가 클 수록 Non-Clustered Index가 차지하는 공간도 커지게 되므로 PK 설정에 주의해야 한다.
📚 Reference
'DB' 카테고리의 다른 글
| MySQL 트랜잭션과 잠금 (1) | 2024.05.07 |
|---|---|
| 인덱스(Index)란? (1) | 2024.03.24 |
