[Admin] MySQL/MariaDB Clustered vs Non-Clustered Index

1. 인덱스란?

 

인덱스는 테이블 또는 View에서 행을 가져오는 속도를 높이기 위해서 데이터베이스의 하나 이상의 열에서 작성된 키입니다. 이는 Oracle, SQL Server, MySQL, MariaDB 등과 같은 데이터베이스가 키 값과 관련된 행을 신속하게 찾을 수 있도록 합니다. Clustered Index, Non-Clustered Index 두 가지 형태로 나눠지게 됩니다. MySQL/MariaDB에서 Clustered Index를 사용할 수 있는 엔진은 InnoDB 뿐입니다. MyISAM, Memory, Archive NDB 엔진은 사용할 수 없습니다. InnoDB Buffer Pool 메모리에 존재하지 않을 경우 디스크에서 페이지를 가져오게 됩니다. 한번 가져온 페이지는  대부분은 InnoDB Buffer Pool에 존재합니다. 속도 관점에서는 최대한 디스크에서 페이지를 가져오는 것보다는 메모리에서 가져오는 게 좋을 수밖에 없습니다.

 

B+Tree Structure of a Clustered Index

2. Clustered Index 란?

 

클러스터 인덱스는 키 값에 대한 테이블의 데이터 행을 정렬하는 인덱스의 한 유형입니다. 데이터베이스에는 테이블당 클러스터 인덱스가 하나만 존재해야 합니다.

 

클러스터 인덱스는 테이블의 데이터를 정렬해서 저장되는 순서를 정의합니다. 따라서 테이블마다 클러스터링된 인덱스가 하나만 있습니다. RDBMS에서는 일반적으로 Primary Key를 사용하여 특정 열을 기반으로 클러스터링 된 인덱스를 만들 수 있습니다.

 

3. Non-Clustered Index 란?

 

Non-Clustered Index는 데이터와 인덱스를 각각 다른 위치에 저장합니다. 인덱스는 해당 데이터의 위치에 대한 포인터를 포함하고 있습니다. Non-Clustered Index의 인덱스가 서로 다른 위치에 저장되므로 단일 테이블에는 많은 Non-Clustered Index가 있을 수 있습니다.

 

예를 들면, 책 한권은 내용을 빠르게 찾기 위해서 보통 두 가지 이상의 색인을 가지게 됩니다. 앞쪽에는 "목차" 기준으로 정렬되어 있고, 뒤쪽에는 "찾아보기"와 같은 용어에 대한 알파벳 순으로 표시하는 것을 볼 수 있습니다. 책을 테이블이라고 하고 목차, 찾아보기를 Non-Clustered Index로 생각하면 이해가 될 수 있습니다.

 

4. Clustered Index와 Non-Clustered Index 차이점

 

Parameters	Clustered Index	Non-Clustered Index
Use For	물리적으로 행을 재배열	논리적으로 행을 재배열(물리적으로 데이터 파일 포인터 활용 하며 재배열은 안함)
Storing method	인덱스의 리프 노드에 데이터 페이지를 저장	인덱스의 리프 노드에 데이터 페이지 저장 안함
Size	용량 작다	용량 크다
Data accessing	Faster(빠름)	Clustered Index에 비해 느림
Additional  disk space	필요하지 않음	인덱스를 별도로 지정하는 데 필요
Type of Key	기본적으로 테이블 Primary Key는 Clustered Index 사용	복합키 역할을 하는 데이블의 고유한 제약조건과 함께 사용
Main Feature	데이터 검색 속도 향상	조인에 사용되는 열에 생성
N per TABLE	테이블 당 1개	테이블 당 249개
engine	InnoDB	InnoDB

 

5. Clustered Index 장점/단점

5.1. 장점

• Primary Key로 검색할 경우 성능 빠름
• 테이블의 모든 보조 인덱스가 Primary Key를 가지고 있기 때문에 인덱스만으로 처리 가능(커버링 인덱스)
• MIN, MAX, COUNT 쿼리를 사용 하는 범위 또는 그룹에 성능 최적화
• 데이터의 특정 지점으로 바로 이동하거나 그 위치부터 순차적으로 읽기 가능
• 페이지 전송 최소화, 캐시 히트 최적화

5.2.  단점

• 정렬 되지 않은 데이터를 적재할 경우(PK를 컬럼을 선정할 때 Auto_increment와 유사한 정렬 순서를 보장하면 좋음)
• INSERT, UPDATE, DELETE(DML) 시 추가 작업 증가
• Clustered Index 필드가 변경 될 때 업데이트하는 데 시간 오래 걸림
• 리프 노드는 대부분 Clustered Index의 데이터 페이지를 포함

6. Non-Clustered Index 장점/단점

6.1. 장점

• 테이블에 여러 개의 인덱스를 생성 가능
• Clustered Index Overhead 비용 최소화

6,2. 단점

• 논리적 순서로 데이터를 저장하는 데 도움을 주지만, 데이터 행을 물리적으로 정렬하지 안 함(물리적으로 I/O 발생)
• 검색할 때 많은 비용 발생
• 불필요한 디스크 사용량 증가

7. 주의 사항

• Clustered Index 생성 시 Key에 대한 크기를 작게 해야 좋습니다. 따라서 인덱스 설계할 때 데이터 타입과 사이즈를 적절하게 사용하는 것을 권장 합니다.
• 테이블에 Primary Key를 반드시 명시합니다. 이는 HA 동기화를 할 때 내부적으로는 Primary Key로 Master Node에서 Slave Node로 데이터를 이관하게 됩니다. Clustered Index가 아니더라도 꼭 필요한 부분입니다.
• 테이블에 Non-Clustered Index를 여러 개 생성하게 되면, DML 작업에 취약하며, 시스템 성능 저하가 발생합니다. 꼭 필요한 인덱스를 추가해야 하며, 주기적으로 운영 DB에서 사용하지 않은 인덱스를 찾아서 제거하시기 바랍니다.

8. 참고

• www.guru99.com/clustered-vs-non-clustered-index.html
• umumble.com/blogs/mysql/mysql-(innodb)-clustered-and-non_clustered-indexes-/
• 12bme.tistory.com/149
• lng1982.tistory.com/144
• m.blog.naver.com/PostView.nhn?blogId=jevida&logNo=221139771581&proxyReferer=https:%2F%2Fwww.google.com%2F
• mongyang.tistory.com/75
• dev.mysql.com/doc/refman/8.0/en/storage-engines.html