안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 12-7. 인덱스 생성과 관리 - 인덱스 옵션을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 인덱스 생성과 관리 - 인덱스 옵션입니다.

TL;DR

인덱스 생성 옵션에 대해 설명합니다. 고유 인덱스(Unique Index)는 중복 값을 허용하지 않는 기능을 제공하며, 채우기 비율(Fill Factor)은 인덱스 구조 조정과 데이터 수정 발생 시, 성능 개선을 위해 활용됩니다.

인덱스를 생성하면서 여러 옵션을 이용할 수 있습니다. 색인의 구조와 탐색 패턴을 살펴보셨다면, 이런 옵션들을 이용해 더 상세하게 제어할 수 있습니다.

그럼, 인덱스를 SQL 구문으로 생성하면서 자세히 살펴보겠습니다.

클러스터형 인덱스와 비클러스터형드 인덱스 생성은 앞에서 진행했으니, 고유 인덱스(Unique Index)를 짧게 살펴보고, 채우기 비율(Fill Factor)을 진행하겠습니다.

고유 인덱스(Unique Index)

먼저 SQL 구문을 실행해 생성하고 살펴보겠습니다. 이 예제는 이전 강좌에서 생성한 SQLERTestDB와 IndexDummy 테이블을 재사용합니다.

USE SQLERTestDB;
GO

--데이터 조회
SELECT TOP 100 * FROM IndexDummy;
SELECT COUNT(*) FROM IndexDummy;

--고유 인덱스 생성
CREATE UNIQUE INDEX idx_dummy_str ON IndexDummy (dummy_str);

--중복 데이터 삽입 시도- 에러
INSERT INTO IndexDummy(idx, dummy_str) VALUES(10001, '복사한_dummy_str_데이터');
--INSERT INTO IndexDummy(idx, dummy_str) VALUES(10001, '2C579027-F972-4F7D-BCA6-3669575F83FE');

결과
메시지 2601, 수준 14, 상태 1, 줄 13
Cannot insert duplicate key row in object 'dbo.IndexDummy' with unique index 'idx_dummy_str'. The duplicate key value is (2C579027-F972-4F7D-BCA6-3669575F83FE).
The statement has been terminated.

고유 인덱스는 인덱스 기능 + 고윳값 제약 설정입니다. 고유 인덱스를 설정하면 테이블에 중복 값을 넣으려 할 경우 에러가 발생합니다. IGNORE_DUP_KEY 옵션을 이용하면, 에러를 발생시키지 않고 INSERT 구문을 무시할 수 있습니다.

고유 인덱스에 WITH IGNORE_DUP_KEY 옵션 적용

--인덱스 삭제
DROP INDEX IndexDummy.idx_dummy_str;

--유니크 인덱스 생성 - WITH IGNORE_DUP_KEY
CREATE UNIQUE INDEX idx_dummy_str ON IndexDummy (dummy_str)
WITH IGNORE_DUP_KEY;
GO

--데이터 조회
SELECT TOP 100 * FROM IndexDummy;

-- 다시 중복 데이터 삽입 시도
INSERT INTO IndexDummy(idx, dummy_str) VALUES(10001, '복사한_dummy_str_데이터');
--INSERT INTO IndexDummy(idx, dummy_str) VALUES(10001, '2C579027-F972-4F7D-BCA6-3669575F83FE');

결과
Duplicate key was ignored.

이렇게 오류가 발생하지 않고, 결과는 무시됩니다. 대량 데이터를 추가하거나, 중복 값을 입력할 필요가 없을 경우 이렇게 IGNORE_DUP_KEY 옵션을 이용합니다.

테이블에 이미 기본 키(Primary Key)가 있더라도 고윳값을 식별할 수 있는 고유 인덱스로 테이블에 추가되는 값의 고유성과 인덱스 성능을 보장받을 수 있습니다.

채우기 비율(Fill Factor)

다음은 인덱스 성능의 중요 옵션 - 채우기 비율을 간략히 말씀드리겠습니다. 인덱스를 생성하고 데이터가 변경될 경우, 인덱스는 일정 조건에 맞춰 함께 변경됩니다.

특히, 이미 인덱스의 B트리 구조가 완전히 밸런싱 되어 모든 노드 값이 채워져 있을 때, 대량의 데이터 삽입이 발생하면, B트리 구조 리밸런싱을 위해 노드를 추가하고 인덱스를 재정렬 하고 변경하면서 시스템에 부하를 줄 수 있습니다. 이런 과정을 SQL Server “페이지 분할(Page Split)”이라고 부릅니다. 이런 경우를 대비해 Fill Factor를 조절하면, 데이터 추가나 변경에 대비해 인덱스 구조에 여유를 둘 수 있습니다.

즉, Fill Factor가 100%라면, 현재 인덱스 리프 레벨이 100%로 가득 채워져 있는 상태입니다. 적절한 수량만큼만 채우는 80% 정도로 값을 설정하면 인덱스 리프레벨은 20% 정도의 여유 공간이 생깁니다. 하지만! 인덱스 데이터 페이지가 채우기 비율만큼 늘어나기 때문에 약간의 인덱스 쿼리 속도 저하가 있습니다.
SQL 구문 예제로 설명드리겠습니다.

USE SQLERTestDB;
GO

--데이터 테스트 조회
SELECT TOP 100 * FROM IndexDummy;
SELECT COUNT(*) FROM IndexDummy;

--인덱스 재생성 - WITH DROP_EXISTING 옵션
CREATE CLUSTERED INDEX idx_id ON IndexDummy(idx)
WITH DROP_EXISTING;
GO

--인덱스 정보 조회
EXEC sp_helpindex 'IndexDummy';

--평균 페이지 밀도 판단 - Avg. Page Density (full) 값 참조
DBCC SHOWCONTIG(IndexDummy);


결과
DBCC SHOWCONTIG scanning 'IndexDummy' table...
Table: 'IndexDummy' (1173579219); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 124
- Extents Scanned..............................: 16
- Extent Switches..............................: 15
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 100.00% [16:16]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 112.1
- Avg. Page Density (full).....................: 98.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

기본적으로 채우기 옵션 없이 인덱스를 생성하면 페이지 밀도가 100%에 가깝게 생성됩니다. 그렇다면, 데이터를 적절히 지우거나 추가해 페이지 밀도를 조절해 보겠습니다.

--하나 건너 하나씩 데이터 삭제
BEGIN TRAN;
DELETE FROM IndexDummy WHERE idx % 2 = 0;
COMMIT TRAN;

--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(IndexDummy);

결과
DBCC SHOWCONTIG scanning 'IndexDummy' table...
Table: 'IndexDummy' (1173579219); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 124
- Extents Scanned..............................: 16
- Extent Switches..............................: 15
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 100.00% [16:16]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 4104.1
- Avg. Page Density (full).....................: 49.29%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

하나 건너 하나씩 데이터를 지우는 쿼리를 수행했고, 다시 DBCC 명령을 수행해 보면 페이지 밀도가 떨어진 것을 확인할 수 있습니다.
그럼 채우기 비율을 설정해 인덱스를 구성하고 다시 페이지 밀도를 체크해 보겠습니다.

--인덱스 재생성 WITH FILLFACTOR 설정
CREATE CLUSTERED INDEX idx_id ON IndexDummy(idx)
WITH FILLFACTOR = 80, DROP_EXISTING;
GO

--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(IndexDummy);

결과
DBCC SHOWCONTIG scanning 'IndexDummy' table...
Table: 'IndexDummy' (1173579219); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 76
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 100.00% [10:10]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 10.00%
- Avg. Bytes Free per Page.....................: 1582.8
- Avg. Page Density (full).....................: 80.44%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

채우기 비율을 80으로 설정했고 확인해 보면 80%로 설정된 것을 확인 가능합니다. 채우기 비율은 현재 테이블의 데이터가 너무 적을 경우 또는 익스텐트를 사용하는 VARCHAR(MAX) 등의 데이터형이 사용될 경우 채우기 설정과 약간 다르게 생성될 수 있으니 주의하세요. 

PAD_INDEX 설정

이전 강좌에서 잠시 소개해 드렸습니다. 채우기 비율 Fill Factor와 비슷한 PAD_INDEX입니다. PAD_INDEX와 FILL_FACTOR의 차이는 PAD_INDEX는 중간레벨에도 여유 공간을 두고 생성합니다.

--PAD_INDEX 추가 - 인덱스 재생성
CREATE CLUSTERED INDEX idx_id ON IndexDummy (idx)
WITH FILLFACTOR = 80, PAD_INDEX, DROP_EXISTING;
GO

이렇게 PAD_INDEX 옵션을 추가할 수 있습니다. 

인덱스를 생성할 때 데이터 삽입과 수정, 삭제 비율을 고려해 FILL_FACTOR와 PAD_INDEX를 적절히 구성하세요. 이후 SQL 유지 관리 계획(Maintenance Plan)을 통해 주기적으로 인덱스를 유지보수 할 경우, 생성 시 설정한 FILL_FACTOR와 PAD_INDEX 값으로 인덱스를 재구성하거나 재생성합니다.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022