[MSSQL] 인덱스(INDEX) - 파편화, 리빌드, 페이지정리(Reorganize)

 

인덱스(INDEX)

 

데이터베이스 테이블에서 검색 속도를 높이기 위한 구조. 책의 목차나 색인과 비슷한 개념

 

➡️ 속도 차이 수십~수천 배

 

 

인덱스와 기본키(PK)의 차이점

	기본 키(PK)	인덱스(INDEX)
목적	데이터 무결성 보장 (식별자)	조회 성능 향상
중복 허용	❌ 불가 (항상 UNIQUE)	⭕ 가능 (선택 가능)
NULL 허용	❌ 불가	⭕ 가능
갯수	테이블당 1개만 가능	여러 개 가능
자동 인덱스 생성	⭕ YES (UNIQUE + CLUSTERED)	⭕ 수동으로 생성
삭제 시	PK 제거하면 인덱스도 사라짐	인덱스만 개별 삭제 가능

 

 

단점

❌ 쓰기 성능 저하 → INSERT, UPDATE, DELETE 시 인덱스도 갱신됨

❌ 공간 사용 → 인덱스도 저장공간 차지함

❌ 너무 많으면 역효과 → 쿼리 최적화기가 잘못된 인덱스를 쓸 수도 있음

 

 

 

인덱스 파편화(Fragmentation)

 

데이터가 자주 삽입/삭제/수정되면서, 인덱스 페이지에 빈 공간이나 순서가 꼬임 발생

원래는 정렬돼 있던 데이터가 조각나서 비효율적으로 배치되는 현상

 

 

내부 파편화 : 인덱스 페이지 안에 공간 낭비가 많음

외부 파편화 : 페이지들이 순차적으로 연결되지 않음 (랜덤 접근 증가)

 

전체 테이블 파편화 율 확인 쿼리

SELECT
    dbschemas.[name] AS 'Schema',
    dbtables.[name] AS 'Table',
    dbindexes.[name] AS 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
JOIN
    sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN
    sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                              AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
    AND indexstats.page_count > 100
ORDER BY
    indexstats.avg_fragmentation_in_percent DESC;

 

 

인덱스 리빌드 (REBUILD)

인덱스를 완전히 새로 생성해서 정리. 조각난 페이지들을 물리적으로 재배치 + 통합

" 전체 재작성 "

 

 

➡️ 내부/외부 파편화 모두 해결

➡️ 통계 자동 업데이트

➡️ 리소스 소모 큼 (대규모 DB에서는 시간 오래 걸림)

 

ALTER INDEX [인덱스이름] ON [테이블이름] REBUILD;

-- 모든 인덱스
ALTER INDEX ALL ON [테이블이름] REBUILD;

 

 

인덱스 페이지 정리 (REORGANIZE)

인덱스를 순차적으로 정리해서 최소한의 리소스로 조각 제거

" 페이지 순서 정리 "

 

➡️ 느리지만 부드럽게 작동

➡️ 외부 파편화는 많이 개선 안 됨

➡️ 내부 파편화만 약간 개선됨

ALTER INDEX [인덱스이름] ON [테이블이름] REORGANIZE;

-- 모든 인덱스
ALTER INDEX ALL ON [테이블이름] REORGANIZE;