Clustered Index & Non Clustered Index

클러스터형 인덱스 & 비클러스터형 인덱스

클러스터 인덱스와 비클러스터 인덱스에 대해서 정리를 해보려고 한다. MySQL, SQL Server 관련 자료를 중심으로 작성했다.

Page

클러스터형, 비클러스터형 인덱스는 B-tree 자료구조를 바탕으로 저장된다. B-tree 자료구조의 각 노드들은 페이지 단위로 관리된다. 페이지는 MySQL 의 경우 16KB 가 디폴트 크기다. MySQL 5.6 버전 이상부터는 페이지 크기를 innodb_page_size 변수 설정으로 변경할 수 있다.

page 는 SQL Server 에서 데이터 저장의 가장 기본 단위다. MySQL 에서는 InnoDB 엔진이 디스크와 메모리 간의 데이터를 전달할 수 있는 최소 단위다.

디스크 저장공간은 file 에 할당되는데 file 은 논리적으로 page 단위로 구분된다.

예시 테이블 정보

아래 이미지로 나타낼 데이터들의 가상 테이블 정보는 다음과 같다.

테이블명은 Company 이며 컬럼은 name 과 founder 로 구성된다. 10개의 데이터가 들어있다.

클러스터형 인덱스란?

데이터가 저장된 페이지 자체를 정렬하는 기준이 되는 인덱스다. 테이블 당 하나만 설정할 수 있다.

PRIMARY KEY 를 지정하면 MySQL (의 InnoDB 엔진)이나 SQL Server 는 자동으로 해당 키를 클러스터형 인덱스로 지정한다.

테이블에 PRIMARY KEY 가 없으면 UNIQUE 제약 조건(MySQL 은 모든 컬럼들 중 NOT NULL 로 정의된 첫번째 UNIQUE 컬럼) 이 지정된 컬럼을 클러스터형 인덱스로 설정한다.

만약에 PRIMARY KEY, UNIQUE 제약 조건 모두 없다면 MySQL 은 GEN_CLUST_INDEX 라는 row ID 값을 갖는 가상의 6 바이트 크기의 컬럼을 생성하여 클러스터형 인덱스로 설정한다. row ID 는 데이터 행이 insert 된 순서대로 증가한다.

위는 Company 테이블의 PRIMARY KEY 로 name 컬럼이 지정된 경우다.

클러스터형 인덱스는 B-tree 의 루트 페이지와 리프 페이지를 중심으로 살펴볼 수 있다. PRIMARY KEY 를 기준으로 물리적으로 데이터가 저장된 데이터 페이지 자체를 정렬한다.

루트 페이지에는 리프 페이지의 번호와 각 리프 페이지의 첫번째 데이터 정보를 갖는다. 리프 페이지는 실제로 데이터가 저장되어 있는 데이터 페이지와 같다.

비클러스터형 인덱스

위는 Company 테이블의 인덱스로 founder 가 지정된 경우다. PRIMARY KEY 는 없다.

비클러스터형 인덱스 SECONDARY INDEX(보조 인덱스) 라고도 하며 클러스터형 인덱스와 달리 물리적으로 저장된 데이터를 정렬하지 않는다.

비클러스터형 인덱스의 리프 페이지는 데이터 페이지와 다르다. 클러스터형 인덱스와 달리 데이터 페이지 자체도 정렬되어 있지 않은 힙 형태로 구성된다.

비클러스터형 인덱스는 한 테이블에 인덱스를 여러개 설정할 수 있고, 설정한 인덱스를 기준으로 정렬한 인덱스 정보를 갖는다.

루트 페이지는 리프 페이지의 페이지 번호와 각 페이지의 첫번째 데이터 정보를 갖는다. 리프 페이지는 인덱스로 설정한 키와 해당 키를 포함한 데이터가 저장된 데이터 페이지의 위치 정보를 갖는다. 이 위치 정보는 RID 라고 하며 파일(file) 번호, 데이터 페이지(page) 번호, 행(row) 번호로 구성된다.

클러스터형 인덱스 + 비클러스터형 인덱스

위는 Company 테이블의 name 컬럼이 PRIMARY KEY 로 지정 되었고, founder 컬럼이 SECONDARY INDEX 로 지정된 경우다.

클러스터형 인덱스와 비클러스터형 인덱스가 함꼐 구성된 경우는 클러스터형 인덱스로 사용되는 PRIMARY KEY 와 함께 별도로 비클러스터형 인덱스를 보조 인덱스로 설정한 경우다.

MySQL 에서 모든 비클러스터형 인덱스는 클러스터형 인덱스의 정보를 갖는다. 그래서 클러스터형 인덱스의 크기가 커지면 비클러스터형 인덱스의 크기도 커지게 된다.

비클러스터형 인덱스의 인덱스 행에서 데이터 행으로의 포인터를 행 로케이터라고 합니다. 행 로케이터의 구조는 데이터 페이지가 힙에 저장되는지 아니면 클러스터형 테이블에 저장되는지에 따라 다릅니다. 힙의 경우 행 로케이터는 행에 대한 포인터입니다. 클러스터형 테이블의 경우 행 로케이터는 클러스터형 인덱스 키입니다.

테이블에 비클러스터형 인덱스만 있을 경우에는 비클러스터형 인덱스의 리프 페이지는 RID 정보를 갖고 있고 이 RID 를 기준으로 데이터 페이지를 조회한다. 테이블에 비클러스터형 인덱스 뿐만 아니라 클러스터형 인덱스도 있을 경우에는 비클러스터형 인덱스는 클러스터형 인덱스 (컬럼) 값을 갖고 있고 클러스터형 인덱스를 기준으로 데이터 페이지에 접근한다.

<참고>

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

https://learn.microsoft.com/ko-kr/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16

https://learn.microsoft.com/ko-kr/sql/relational-databases/indexes/create-clustered-indexes?source=recommendations&view=sql-server-ver16

https://hudi.blog/db-clustered-and-non-clustered-index/

https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index

https://pangtrue.tistory.com/286

https://dev.mysql.com/doc/refman/8.0/en/innodb-physical-structure.html

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_page

https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16

https://ask.sqlservercentral.com/questions/39281/what-is-a-rid-lookup.html