1 / 27

B*TREE 인덱스 구조

B*TREE 인덱스 구조. 강사 : 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트. 목차. 인덱스 개요 B*TREE 인덱스 구조 B*TREE 인덱스 생성 과정 B*TREE 인덱스 행의 구조 SQL Server 인덱스 인덱스 페이지 조각화 인덱스 검색 프로세스 이해. 인덱스 개요. 장점 - 성능 개선 : 데이터 검색 시 IO 및 CPU 사용량 감소로 검색 속도 개선 - 유일성 보장 : 데이터의 유일성을 보장하는 유일한 수단

affrica
Download Presentation

B*TREE 인덱스 구조

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. B*TREE 인덱스 구조 강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트

  2. 목차 • 인덱스 개요 • B*TREE 인덱스 구조 • B*TREE 인덱스 생성 과정 • B*TREE 인덱스 행의 구조 • SQL Server 인덱스 • 인덱스 페이지 조각화 • 인덱스 검색 프로세스 이해

  3. 인덱스 개요 • 장점 - 성능 개선 : 데이터 검색 시 IO 및 CPU 사용량 감소로 검색 속도 개선 - 유일성 보장 : 데이터의 유일성을 보장하는 유일한 수단 (PK, UNIQUE 제약 조건도 내부적으로는 고유 (unique) 인덱스생성) • 단점 - 저장 공간 소요 : sysindexes, sp_spaceused 구문으로 사용 공간 확인 - DML 작업 시 추가 부하 발생

  4. -------------------------- Root Page --------------- Intermediate Page --- Leaf Page B*TREE 인덱스 구조 • Tree 구조로 구성됨 ( 나무를 뒤집어 놓은 구조 ) - 상위 : Root Page - 중간 : Intermediate Page - 하위 : Leaf Page 인덱스 시작점 리프 레벨 포인터 인덱스 키 + 포인터 (ROWID / Clustering Key)

  5. B*TREE 인덱스 생성 과정 • Leaf Level을 먼저 구성하고 Leaf Page가 2개 이상으로 분할 시 새로운 Root Level 추가 • Root Page가 2개 이상으로 분할 시 새로운 Root Page 추가 기존의 Root는 Intermediate Page가 된다. • 인덱스 페이지는 이중 링크로 구성되어 페이지간 연결 정보 유지 Root (single page) Root Root  Intermediate … Root Leaf Root  Leaf

  6. B*TREE 인덱스 행의 구조 • 인덱스 페이지 행 (리프 페이지가 아닌 경우) • 인덱스 페이지 행 (힙 테이블 상의 리프 페이지) • 인덱스 페이지 행 (클러스터 형 인덱스 상의 리프 페이지)

  7. --- --------- ---- Root Page ---- ----- ---- -- ----- Intermediate Page ---- --- Leaf Page --- SQL Server 인덱스 • NON-CLUSTERED INDEX - 리프 레벨 • CLUSTERED INDEX - 리프 레벨 인덱스 키 + 포인터 (ROWID / Clustering Key) 데이터 페이지 DATA ( CLUSTERED INDEX ) DATA ( HEAP )

  8. 이소진 3:320:1 이소민 이소진 이소정 3:137:1 3:137:1 3:137:1 이소영 이소진 3:320:1 3:145:1 이소정 이수희 3:137:2 3:137:2 이수희 3:137:3 이소민 이소진 3:137:1 3:137:1 이소영 3:145:1 인덱스 페이지 조각화 insert emp (name,title…) values (‘이소진’, ‘대리’ …) Non-Leaf Level < =이소진 Leaf Level (key Value) Page 305 Page 310 Page 298 Page 302

  9. 클러스터드 인덱스의 • 레벨수 –1 만큼의 I/O가 • 추가 • 새로운 데이터 삽입시 • NON-Clustered INDEX의 • 추가 부하 발생이 없다 • 단점 • 장점 DATA ( CLUSTERED INDEX ) 비클러스터형 인덱스의 구조 변화 • 클러스터형 인덱스가 있는 상태에서의 비클러스터형 인덱스 구조 인덱스 키 + 포인터 (Clustering Key)

  10. Demo : 인덱스 포인터 정보 확인 • 포인터 정보 (ROWID or 클러스터형 인덱스 키) 확인

  11. Page 301 Page 305 비클러스터형 인덱스를 사용한 데이터 검색 select * from emp where name=‘이소정’ sysindexes Page 400 Root < =’이소정’ Leaf Level (key Value) Page 302 Page 310 HEAP BOOK MARK NAVIGATION Page 101 Page 102 Page 120 Page 137 Page 205 … …

  12. 클러스터형 인덱스를 사용한 데이터 검색 select * from emp where empno = 10006 sysindexes Root Page 100 < =10006 Leaf Page 101 Page 102 Page 120 Page 137 Page 205 … …

  13. Page 301 Page 305 sysindexes Indid=1 Root = 100 클러스터형 인덱스가 있는 테이블에서 비클러스터형 인덱스를 사용한 데이터 검색 select * from emp where name=‘이소정’ sysindexes Page 400 Root < =’이소정’ Leaf Level (key Value) Page 302 Page 310 BOOK MARK NAVIGATION (10026) Clustered Index ROOT

  14. 클러스터형 인덱스가 있는 테이블에서 비클러스터형 인덱스를 사용한 데이터 검색 (2) BOOK MARK NAVIGATION (10026) Root Page 100 < =10026 Leaf Level (DATA) Page 101 Page 102 Page 120 Page 137 Page 205 … …

  15. Demo : 인덱스 사용 시 IO 분석 • 인덱스 사용 시 IO 분석

  16. 요약 • B*TREE 인덱스의 구조 - Bottom-Up 방식으로 생성 되며 루트 페이지 하나를 유지함 - 루트-중간-리프 페이지로 구성 - 리프페이지는 인덱스 키 컬럼 +포인터로 구성됨 • 비클러스터형 인덱스 - 단일 행 또는 적은 범위의 검색에 효율적인 구조 - 클러스터형 인덱스의 존재 여부에 따라서 포인터 정보가 행 구별자(ROWID) 또는 클러스터형 인덱스 키러 구성됨 • 클러스터형 인덱스 - 일반 B*TREE 인덱스와 구조는 동일하나 리프 페이지가 데이터인 구조 - 범위 검색 / 집계 연산 등에 효율적

  17. B*TREE 인덱스 정보 확인하기 강사: 이종인 다우 교육원 전임강사 / 온디멘드 수석 컨설턴트 / FMG 수석 컨설턴트

  18. 목차 • B*TREE 인덱스 기본 정보 확인 • B*TREE 인덱스 통계 정보 확인 • B*TREE 인덱스 조각화 확인

  19. statblob rowmodctr dpages rows id indid first root first IAM 0: HEAP 1: 클러스터형 인덱스 2~250: 비클러스터형 인덱스 255 : BLOB page B*TREE 인덱스 기본 정보 확인 sysindexes 시스템 테이블 • Id : 기반 오브젝트 ID ( table / view) • Indid : index ID • First : Leaf-level 의 시작 페이지 주소 • Root : 인덱스의 시작점 주소 • First IAM : 첫 IAM 페이지 주소 • Dpages : leaf-level 페이지의 개수 • Statblob : 통계페이지 (image 로 저장) • Rows : 인덱스의 행 수 • Rowmodcrt : 통계 업데이트 기준 / 최근 통계 업데이트 후 변경된 행의 개수

  20. Demo : 인덱스 기본 정보 확인 • sysindexes 시스템 테이블 정보 확인

  21. B*TREE 인덱스 통계 정보 확인 / 갱신 • SP_HELPSTATS ‘테이블 이름’, ‘ALL’ : 테이블의 모든 통계의 이름 반환 • DBCC SHOW_STATISTICS (‘테이블_이름’, ‘인덱스_이름’) : 해당 통계 페이지 정보 반환 • update statistics 테이블_이름 인덱스_이름 update statistics employees pk_employees

  22. 통계 페이지 정보 • Updated • Rows • Rows Sampled • Steps : Max 200 • Density • All Density • Range_Hi_Key • Range_Rows • EQ_Rows • Distinct_Range_Rows • Avg_Range_Rows

  23. Demo : 인덱스 통계정보 확인 • 인덱스 통계 정보 확인

  24. B*TREE 인덱스 조각화 정보 확인 • 인덱스 조각화의 영향 1) DML (insert/update/delete) : 페이지 할당 / 조각화 부하 감소  OLTP 성 효율적 2) select : range-query, group by , order by 등 seek time 증가  DW, DSS, Report 용 비효율적 • 조각화 여부 확인 : DBCC SHOWCONTIG

  25. DBCC SHOWCONTIG (조각화의 경우) dbcc showcontig ('tb_m_mmodel_cost_act_past','PK_tb_m_mmodel_cost_act_past') DBCC SHOWCONTIG이(가) 'tb_m_mmodel_cost_act_Past' 테이블을 스캔하는 중... 테이블: 'tb_m_mmodel_cost_act_Past' (165575628); 인덱스 ID: 1, 데이터베이스 ID: 10 TABLE 수준 스캔이 수행되었습니다. - 스캔한 페이지................................: 1888100 - 스캔한 익스텐트..............................: 244450 - 전환된 익스텐트..............................: 671143 - 익스텐트 당 평균 페이지 수........................: 7.7 - 스캔 밀도[최적:실제].......: 35.17% [236013:671144] - 논리 스캔 조각화 상태 ..................: 2.35% - 익스텐트 스캔 조각화 상태 ...................: 35.57% - 페이지 당 사용 가능한 평균 바이트 수.....................: 76.7 - 평균 페이지 밀도(전체).....................: 99.05% DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오. select count(*) from tb_m_mmodel_cost_act_past where work_yymm='200410' CPU 시간 = 7189ms, 경과 시간 = 30181ms.

  26. DBCC SHOWCONTIG (재구성 후의 경우) dbcc showcontig ('tb_m_mmodel_cost_act_past','PK_tb_m_mmodel_cost_act_past') DBCC SHOWCONTIG이(가) 'tb_m_mmodel_cost_act_Past' 테이블을 스캔하는 중... 테이블: 'tb_m_mmodel_cost_act_Past' (165575628); 인덱스 ID: 1, 데이터베이스 ID: 10 TABLE 수준 스캔이 수행되었습니다. - 스캔한 페이지................................: 1888097 - 스캔한 익스텐트..............................: 240140 - 전환된 익스텐트..............................: 240139 - 익스텐트 당 평균 페이지 수........................: 7.9 - 스캔 밀도[최적:실제].......: 98.28% [236013:240140] - 논리 스캔 조각화 상태 ..................: 0.42% - 익스텐트 스캔 조각화 상태 ...................: 0.11% - 페이지 당 사용 가능한 평균 바이트 수.....................: 76.7 - 평균 페이지 밀도(전체).....................: 99.05% DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오. select count(*) from tb_m_mmodel_cost_act_past where work_yymm='200410' CPU 시간 = 6267ms, 경과 시간 = 14510ms.

  27. 요약 • B*TREE 인덱스 기본 정보 확인 - sysindexes 시스템 테이블을 통해서 다음 내용 확인 ( 인덱스 종류/ 행 숫자 / 통계 정보 갱신 임계값 / 사용중인 페이지 숫자 등) • B*TREE 인덱스 통계정보 확인 (DBCC SHOW_STATISTICS ) - 정기 적인 통계 정보 확인을 통한 갱신 주기 결정 - 올바른 실행 계획 생성 여부 확인 후 샘플링 비율 결정 • B*TREE 인덱스 조각화정보 확인 (DBCC SHOWCONTIG) - 정기 적인 조각화 정보 확인을 통한 인덱스 재구성 주기 결정 - 적절한 채우기 비율 (fillfactor) 결정

More Related