강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트 B*TREE 인덱스 생성 및 관리 강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트

목차 인덱스 생성 / 삭제 인덱스 재구성 복합 인덱스 검색 복합 인덱스 컬럼 순서 결정 인덱스 생성 지침

인덱스 생성 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX 인덱스_이름 ON <테이블/뷰> ( 컬럼_이름 [ ASC | DESC ] [ ,...n ] )        [ WITH ( 옵션 [ ,...n ] ) ]      [ ON 파일 그룹 이름 ] CREATE CLUSTERED INDEX IDX_EMP_01 ON EMP (DEPTNO ASC, TITLE DESC ) WITH SORT_IN_TEMPDB ON HR_FILEGROUP Heap 에서 Data 검색

인덱스 생성 옵션 PAD_INDEX : fillfactor로 지정된 사용 가능한 공간의 비율이 인덱스의 중간 수준 페이지에도 적용 FILLFACTOR : 인덱스 페이지의 리프 페이지를 어느 정도 채울지 나타내는 비율을 지정 SORT_IN_TEMPDB : tempdb에 임시 정렬 결과를 저장 IGNORE_DUP_KEY : 고유 인덱스를 위반하는 행만 실패 처리 STATISTICS_NORECOMPUTE : 통계는 자동으로 갱신하지 않음 DROP_EXISTING : 인덱스 정의 수정 Heap 에서 Data 검색

인덱스 삭제 DROP INDEX 테이블_이름.인덱스_이름 - 자주 사용하지 않는 인덱스는 삭제 검토 - 자주 사용하지 않는 인덱스는 삭제 검토 - 인덱스 삭제 작업도 대량의 작업이므로 BULK_LOGGED 모델 사용을 검토 - 트랜잭션 로그 파일의 빈 공간 정보 점검 DROP INDEX EMP.IDX_EMP_01 Heap 에서 Data 검색

인덱스 재구성 WITH DROP_EXISTING WITH DROP_EXISTING DBCC DBREINDEX - 페이지 압축 - 컬럼 변경 - FILLFACTOR, PAD_INDEX 변경 - 비클러스터형 인덱스를 클러스터형 인덱스로 변경 DBCC DBREINDEX - FILLFACTOR 변경 - 테이블의 모든 인덱스 일괄 적용 가능 DBCC INDEXDEFRAG - 물리적인 리프 페이지의 순서를 논리적인 순서로 재정렬 - 기존에 지정된 FILLFACTOR 적용 DBCC DBREINDEX Heap 에서 Data 검색 DBCC INDEXDEFRAG

인덱스 재구성 비교 인덱스 재구성과 잠금 인덱스 재구성과 트랜잭션 병렬 작업 : DBCC INDEXDEFRAG는 불가 비클러스터형 인덱스 재구성 시 S-Lock 클러스터형 인덱스 재구성 시 X-Lock 작업중인 페이지 X-Lock (ON-LINE) DBCC INDEXDEFRAG DBCC DBREINDEX create index ~ WITH DROP_EXISTING 인덱스 재구성과 트랜잭션 Mass – One transaction 작업중인 페이지 수정 DBCC INDEXDEFRAG DBCC DBREINDEX create index ~ WITH DROP_EXISTING Heap 에서 Data 검색 병렬 작업 : DBCC INDEXDEFRAG는 불가

인덱스 조각화와 재구성 1 2 3 4 5 6 7 8 9 10 데이터 대량 로딩 (조각화 발생) 1 2 3 4 5 6 7 8 9 10 데이터 대량 로딩 (조각화 발생) 1 2 4 6 8 9 10 11 13 14 3 5 7 12 DBCC INDEXDEFRAG 1 2 3 4 5 6 7 8 9 10 11 12 제약 조건 유형 DBCC DBREINDEX / CREATE INDEX ~ WITH DROP EXISTING 1 2 3 4 5 6 7 8 9 10 11 12

인덱스 재구성 구문 WITH DROP_EXISTING DBCC DBREINDEX DBCC INDEXDEFRAG CREATE CLUSTERED INDEX IDX_EMP_01 ON EMP (DEPTNO ASC, TITLE DESC, SAL DESC ) WITH DROP_EXISTING ON HR_FILEGROUP DBCC DBREINDEX (‘EMP’,’IDX_EMP_01’, 80) Heap 에서 Data 검색 DBCC INDEXDEFRAG (‘NORTHWIND’,‘EMP’,’IDX_EMP_01’)

Demo : 인덱스 재구성 with drop_existing DBCC DBREINDEX DBCC INDEXDEFRAG

복합 인덱스 복합 인덱스 효과 - 책갈피 조회(BOOKMARK NAVIGATION)를 생략하여 IO 감소 - 다중의 단일 컬럼 인덱스를 단일의 복합 인덱스로 유도하여 DML 수행 시 부하 감소 - 테이블의 인덱스 숫자를 줄여서 저장 공간 효율화

select TITLE from emp where name between ‘공대열’ and ‘ 남수중’ 복합 인덱스 범위 스캔 select TITLE from emp where name between ‘공대열’ and ‘ 남수중’ Index Page Non-Leaf Level 가기순 3:301 김태훈 3:302 이소민 3:305 임세령 3:310 Leaf Level (key Value) Page 301 Page 302 Page 305 Page 310 가기순 사원 공국진 공대열 대리 … 가기순 사원 김태훈 과장 남수중 남현주 사원 … 김태훈 과장 이소민 사원 이소영 이소정 … 임세령 대리 임용찬 사원 임희찬 … 공국진 사원 남수중 과장 공대열 대리 남현주 사원 Clustered 되지 않은 Index가 있는 Heap에서 Data 검색 … … Page 101 Page 102 Page 120 Page 137 Page 205 10000 이종인 … 10001 황희정 10002 류경석 10003 최철원 10004 남현주 … 10005 이동희 10006 김태훈 10007 정원혁 10008 최인규 … 10009 전복희 10010 임무호 10011 송원석 10024 이수희 … 10025 이소민 10026 이소정 10027 황인숙 10068 홍태영 … … …

select TITLE,NAME from emp where TITLE=‘대리’ 복합 인덱스 전체 스캔 1 select TITLE,NAME from emp where TITLE=‘대리’ Index Page Non-Leaf Level 가기순 3:301 김태훈 3:302 이소민 3:305 임세령 3:310 Leaf Level (key Value) Page 301 Page 302 Page 305 Page 310 가기순 가기순 사원 공국진 공대열 대리 … 사원 김태훈 과장 남수중 남현주 사원 … 김태훈 과장 이소민 사원 이소영 이소정 … 이소민 사원 이소영 이소정 … 임세령 대리 임용찬 사원 임희찬 … 임세령 대리 임용찬 사원 임희찬 … 공국진 사원 남수중 과장 공대열 대리 남현주 사원 Clustered 되지 않은 Index가 있는 Heap에서 Data 검색 … … Page 101 Page 102 Page 120 Page 137 Page 205 10000 이종인 … 10001 황희정 10002 류경석 10003 최철원 10004 남현주 … 10005 이동희 10006 김태훈 10007 정원혁 10008 최인규 … 10009 전복희 10010 임무호 10011 송원석 10024 이수희 … 10025 이소민 10026 이소정 10027 황인숙 10068 홍태영 … … …

select TITLE,NAME from emp 복합 인덱스 전체 스캔 2 select TITLE,NAME from emp Index Page Non-Leaf Level 가기순 3:301 김태훈 3:302 이소민 3:305 임세령 3:310 Leaf Level (key Value) Page 301 Page 302 Page 305 Page 310 가기순 가기순 사원 공국진 공대열 대리 … 사원 김태훈 과장 남수중 남현주 사원 … 김태훈 과장 이소민 사원 이소영 이소정 … 이소민 사원 이소영 이소정 … 임세령 대리 임용찬 사원 임희찬 … 임세령 대리 임용찬 사원 임희찬 … 공국진 사원 남수중 과장 공대열 대리 남현주 사원 Clustered 되지 않은 Index가 있는 Heap에서 Data 검색 … … Page 101 Page 102 Page 120 Page 137 Page 205 10000 이종인 … 10001 황희정 10002 류경석 10003 최철원 10004 남현주 … 10005 이동희 10006 김태훈 10007 정원혁 10008 최인규 … 10009 전복희 10010 임무호 10011 송원석 10024 이수희 … 10025 이소민 10026 이소정 10027 황인숙 10068 홍태영 … … …

복합 인덱스 컬럼 순서 지정 Where A=? and B=? and C=? and D=? Where A=? and B=? ~ Where A=? and B=? and C=? and D=? Where A=? and B=? and C=? INDEX SEEK FILTER Where A=? and B=? and D=? Where A=? and B=?

인덱스 생성 지침 1 Clustered Index 의 선정 범위 검색 정렬된 결과 출력 집계 생성 주의 사항 1) 인덱스 컬럼의 개수 : 16 2) data type 선정 : 900바이트 3) 추가적인 비클러스터형 인덱스 존재 시 유의 (비클러스터형 인덱스의 포인터=클러스터형 인덱스 키)

인덱스 생성 지침 2 where 절에 자주 사용되는 컬럼 Join 의 연결고리로 사용되는 컬럼 복합인덱스의 컬럼 순서는 선택도가 높은 컬럼 우선, 범위 검색 조건이 아닌 컬럼 우선 순 인덱스 컬럼 가공 지양  인덱스 컬럼 가공은 인덱스 전체 스캔

인덱스 생성 지침 여러 개의 단일 컬럼 인덱스와 하나의 차선 인덱스가 효율성 비교 인덱스 존재 시 활용 여부 점검 주기적인 통계 페이지 UPDATE 관리 정기 적인 인덱스 재구성

요약 인덱스 재구성 방법 차이 이해 - WITH DROP_EXISTING - DBCC DBREINDEX - DBCC INDEXDEFRAG FILLFACTOR 비율 결정 요소 - 재구성 작업 가능 시간 산정 - 산정된 시간에 따라 테이터 인서트량 분석 - 시스템 성능 모니터를 통항 페이지 분할 정도 확인

요약 복합 인덱스 사용 시 장점 복합 인덱스 컬럼 순서 결정 - 책갈피 조회 생량을 통한 IO 감소 - 인덱스 숫자를 줄여서 저장 공간 효율화 및 DML 부하 감소 효과 복합 인덱스 컬럼 순서 결정 - where 절에 자주 사용되는 컬럼 우선 - 선택도가 높은 컬럼 우선 - 범위 검색이 아닌 컬럼 우선 인덱스 생성 지침 - where 절, join 조건에 사용되는 컬럼 - 활용도가 낮은 인덱스 삭제 여부 검토 - 테이블에서 유지 관리할 인덱스 숫자 검토 및 저장공간 검토 등