[MSSql] index 인덱스 리빌드, 재구성(속도 향상)

어찌저찌... sqld 시험 합격 히히 근데 지금 회사에선 오라클을 안쓰네..

 

 

---

인덱스 속성

인덱스 속성을 들어가면 조각화 라는 카테고리가 있어요.

 

인덱스 조각화(Index Fragmentation)란?

: 디스크 상에 Page들이 연속적으로 위치해 있지 않고(서로 연관된 데이터들이 한 Extent로 묶이지 않는), 공간을 두고 떨어져 있는 현상을 말합니다. 인덱스를 처음 생성했을 때에는 연속해서 서로 연관된 데이터들끼리 연속된 Page들로 구성되어 있지만, 데이터의 변경(INSERT, UPDATE, DELETE)이 일어나다 보면, 나중에는 데이터들이 비연속적인 Page들에 분상되게 되어, 해당 데이터를 이용할 때 Disk I/O가 더 많이 발생하게 되고 이에 따라 DB의 성능이 떨어지게 된다.

 

페이지 밀도(Page Density)란?

: DB에서의 각 Page는 가변적인 Row 수를 가진다. Row들이 한 Page의 모든 공간을 차지하는 경우 페이지 밀도는 100%이다. Page가 데이터들 없이 비어있는 경우 페이지 밀도가 0%이다. 밀도가 100%인 한 페이지가 새 Row를 수용하기 위해서는 두 Page로 분할되는데, 이렇게 새롭게 만들어지는 두 Page의 밀도는 약 50%가 된다.

 

* 많은 Workload에서 페이지 밀도를 높이는 것이 인덱스 조각화를 줄이는 것보다 성능에 더 긍정적인 영향을 준다. 페이지 밀도를 불필요하게 낮추지 않으려면, 페이지 분할 수가 많은 인덱스(예: 비연속 GUID 값을 포함하는 선행 Column이 포함된, 수정이 빈번한 인덱스 등)와 같은 특정한 경우를 제외하고는, 채우기 비율을 100% 또는 0%이외의 값으로는 설정하지 않는 것이 바람직하다.

 

제 주관적인 생각으로는 

• 인덱스 조각화는 DML 명령어들(INSERT, UPDATE, DELETE)이 실행될때마다 인덱스가 새로 만들어지는데, 이 때 기존 인덱스가 사라지고 생성되는 것이 아닌, 사용하지 않음 처리하고 새로 생성되기 때문에 저장공간을 잡아먹을 뿐더러, B-Tree 구조로 인해 한쪽이 무거워지는 현상이다. 다시 인덱스를 재구성 또는 리빌드 함으로써 쌔삥 B-Tree 구조를 만들어준다~
• 페이지 밀도는 인덱스로 지정한 컬럼의 row가 null이나 중복값을 피해주면 된다~

 

여기 조각화에 보면 총 조각화 있는데, 이게 0퍼에 갈수록 속도가 빨라져요.

대부분 인덱스를 만든 직후는 0.01% ~ 0.1% 에요.

---

인덱스 Reorganization(재구성)

• 기존에 사용되던 Page 정보를 순서대로 다시 구성하는 작업
• Rebuilding 보다 리소스가 덜 사용되므로, 이 방법을 기본 인덱스 유지 관리 방법으로 사용하는 게 바람직함.
• 온라인 작업이기 때문에, 장기간의 object-level locks가 발생하지 않으며 Reorganization 작업중에 기본 테이블에 대한 쿼리나 업데이트 작업을 계속 진행할 수 있다.

ALTER INDEX [IndexName] ON [dbo].[TableName] REORANIZE WITH (LOB_COMPACTION = ON)
# LOB_COMPACTION = ON 옵션은 LOB 데이터 타입(대용량 데이터)에 대한 압축 작업을 진행한다는 의미.

#테이블의 모든 인덱스 재구성
ALTER INDEX ALL ON [dbo].[TableName] REORANIZE

 

인덱스 Rebuilding(재생성)

• 기존의 인덱스를 삭제하고 재생성하는 방법
• 인덱스의 모든 Row가 검사되며, 통계도 업데이트(Full-Scan) 되어 최신 상태가 되므로, 기본 샘플링된 통계 업데이트에 비해 DB의 성능이 향상되는 경우도 있다.
• 일반적으로 일정 수준 이상의 조각화(약 30% 이상)가 발생한 경우에는 인덱스 리빌딩을 진행해야 인덱스 조각화를 해결할 수 있다.
• 인덱스 유형 및 DB 엔진에 따라 온라인/오프라인으로 나뉘며, 오프라인 인덱스 리빌딩은 온라인 방식에 비해 시간은 덜 걸리지만, 리빌딩 작업 중에 object-level의 Lock을 유발함.

ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

#PAD_INDEX = OFF
#Fill Factor 설정(페이지의 어느 정도의 공간을 비워 둘 것인지 결정, 기본 값은 0)은 Leaf 노드에서만 적용되는데, 
#PAD_INDEX = ON으로 설정하는 경우에는 intermediate 노드에도 Fill Factor 설정이 적용.

#STATISTICS_NORECOMPUTE = OFF
#통계 자동 업데이트 비활성화(대용량 테이블의 경우 통계 자동 업데이트 활성화 시켜두면 성능 저하 발생 가능)

#SORT_IN_TEMPDB = OFF
#인덱스를 생성하거나 리빌드할 때 발생하는 정렬 동작의 중간 결과값을 tempdb 에 저장할 것인지 결정
#사용하는 데이터베이스와 tempdb가 다른 디스크에 위치해 있는 경우 인덱스 리빌드 시간 단축 가능

 

 

 

 

출처 : https://co-no.tistory.com/16