안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 12-5. 인덱스 생성과 관리 - 비클러스터형 인덱스를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 인덱스 생성과 관리 - 비클러스터형 인덱스입니다.
TL;DR
비클러스터형(Non-Clustered) 인덱스 개념을 다루며, 클러스터형 인덱스와 다른 데이터 저장 및 탐색 방식을 설명합니다. 비클러스터형 인덱스는 테이블 데이터에 대한 인덱싱을 제공하며, RID(로우 식별자)를 이용해 데이터 페이지를 참조합니다.
비클러스터형(Non-Clustered) 인덱스
실제 테이블의 데이터가 항상 순차적으로 입력되어 있는 것은 아닙니다. 관계형 데이터베이스에서 순차라는 것은 큰 의미가 없습니다. 클러스터형 인덱스로 물리적 로우와 페이지를 재배열해도 잠시 뿐입니다. 데이터 삭제 / 삽입 등이 발생하면 다시 파편화(fragmentation)가 발생할 수 있습니다. 클러스터형 인덱스를 업데이트하거나, 인덱스를 리빌드 하기 전까지 파편화 상태가 유지되며, 관계형 데이터베이스에서는 항상 ORDER BY를 이용해 쿼리 결과셋을 정렬해야 합니다.
☑️ 챗GPT 활용: 인덱스 파편화(Index Fragmentation)에 대해서 알려줘
예를 들어, 실제 SQL Server에 들어가는 테이블 데이터 순서는 일반적으로 아래처럼 구성됩니다.
|
회원번호
|
이름 | 나이 |
| 4 | 박서준 | 34 |
| 7 | 이정재 | 50 |
| 9 | 한소희 | 28 |
| 11 | 김혜수 | 52 |
| 6 | 송중기 | 37 |
| 5 | 구교환 | 40 |
| 1 | 김대우 | 19 |
| 8 | 김태리 | 33 |
| 2 | 손석구 | 40 |
| 10 | 송혜교 | 41 |
| 3 | 박은빈 | 30 |
| 13 | 유연석 | 39 |
| 12 | 이하늬 | 40 |
이런 형태로 데이터가 들어가 있습니다. 그렇다면 페이지 상태는 어떨까요?
|
데이터 페이지 1
|
||
| 4 | 박서준 | 34 |
| 7 | 이정재 | 50 |
| 9 | 한소희 | 28 |
|
데이터 페이지 2
|
||
| 11 | 김혜수 | 52 |
| 6 | 송중기 | 37 |
| 5 | 구교환 | 40 |
|
데이터 페이지 3
|
||
| 1 | 김대우 | 19 |
| 8 | 김태리 | 33 |
| 2 | 손석구 | 40 |
|
데이터 페이지 4
|
||
| 10 | 송혜교 | 41 |
| 3 | 박은빈 | 30 |
| 13 | 유연석 | 39 |
|
데이터 페이지 5
|
||
| 12 | 이하늬 | 40 |
대략적으로 위의 형태로 페이지 내부에 데이터가 위치하게 됩니다. 실제 데이터 페이지입니다.
비클러스터형 인덱스 RID
이런 데이터에 비클러스터형 인덱스를 만약 생성한다면 어떻게 될까요? 이럴 경우는 RID라는 정보가 필요하게 됩니다. 간단히 RID는 로우를 구별하는 특수한 내부 값입니다. 비 클러스터형 인덱스는 바로 이 RID로 데이터 페이지를 포인팅 합니다.
RID 형식 및 데이터
|
번호
|
RID |
| 1 | 1-3-1 |
| 2 | 1-3-3 |
| 3 | 1-4-2 |
| 4 | 1-1-1 |
| 5 | 1-2-3 |
| 6 | 1-3-3 |
| 7 | 1-1-2 |
| 8 | 1-3-2 |
| 9 | 1-1-3 |
| 10 | 1-4-1 |
| 11 | 1-2-1 |
| 12 | 1-5-1 |
| 13 | 1-4-3 |
여기서 RID의 첫 번째 1은 파일그룹(Filegroup)입니다. 그다음 숫자는 데이터 페이지 번호이며 마지막 세 번째 숫자는 페이지 오프셋(offset) 번호로 정확히 페이지의 한 로우를 포인팅(Pointing) 합니다.
비클러스터형 인덱스 페이지 구조
비클러스터형 인덱스 페이지 역시 다음과 같은 패턴이 인덱스 페이지로 분할됩니다.
|
인덱스 페이지 1
|
|
| 1 | 1-3-1 |
| 2 | 1-3-3 |
| 3 | 1-4-2 |
| 4 | 1-1-1 |
|
인덱스 페이지 2
|
|
| 5 | 1-2-3 |
| 6 | 1-3-3 |
| 7 | 1-1-2 |
| 8 | 1-3-2 |
|
인덱스 페이지 3
|
|
| 9 | 1-1-3 |
| 10 | 1-4-1 |
| 11 | 1-2-1 |
| 12 | 1-5-1 |
|
인덱스 페이지 4
|
|
| 13 | 1-4-3 |
이런 구조의 비클러스터형 인덱스가 과연 어떻게 사용될까요? 인덱스와 데이터 페이지 구조를 먼저 그려 보겠습니다.
이미지 - 비클러스터형 인덱스의 데이터 페이지 구조
이렇게 인덱스와 데이터 페이지가 생성됩니다. 이전 강좌에서 공부한 B트리를 떠올려주세요. 루트 레벨은 인덱스 페이지 7이고 중간 레벨은 인덱스 페이지 1, 2, 3, 4이며 실제 데이터페이지는 1, 2, 3, 4, 5 데이터 페이지가 됩니다.
그렇다면, 비클러스터형 인덱스에서 어떻게 값을 탐색할 수 있을까요?
이번에는 회원번호 3번 박은빈 님을 찾아보겠습니다.
이미지 - 비클러스터형 인덱스 데이터 페이지 탐색
1 < 3 < 5 이므로 인덱스 페이지 1로 찾아갑니다. 인덱스 페이지 1로 가니, RID가 있고, 3은 데이터 페이지 4번의 2번 로우에 있다고 합니다. RID가 포인팅 하고 있는 데이터 페이지 4로 가서 두 번째 로우로 가보면 회원번호 3번 박은빈 데이터가 있습니다. 이렇게 비클러스터형 인덱스를 탐색합니다.
만약 비클러스터형 인덱스에서 회원번호 8번 김태리를 찾으려면 어떻게 탐색할까요? 인덱스 페이지 7 - B트리를 보면 5 < 8 < 9 이므로 인덱스페이지 2로 가서 8번 RID를 보면 1-3-2라고 적혀있습니다. RID를 따라 데이터 페이지 3번의 2번째 로우를 찾으면 원하는 김태리를 찾을 수 있습니다. 이런 구조와 탐색 방식이 일반적인 SQL Server의 비클러스터형 인덱스 패턴입니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
