데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SQL Server 테이블 및 인덱스 구조 아키텍처(4/4)

– 비클러스터형 인덱스 구조

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

비클러스터형 인덱스는 다음 두 가지 주요 차이점을 제외하고 클러스터형 인덱스와 동일한 B-tree구조를 갖는다.

  • 기본 테이블의 데이터 행은 비클러스터형 키 기반의 순서대로 정렬되거나 저장되지 않는다.
  • 비클러스터형 인덱스의 리프계층은 데이터 페이지 대신 인덱스 페이지로 구성 된다.

 

 

 

테이블이나 뷰에 클러스터형 인덱스나 힙과 함께 비크러스터형 인덱스를 정의할 수 있다. 비클러스터형 인덱스의 각 인덱스 행에는 비클러스터형 키 값과 행 로케이터가 있다. 이 로케이터는 키 값이 포함된 힙이나 클러스터형 인덱스의 데이터 행을 가리킨다.

 

  • 테이블이 힙인 행 로케이터는 행에 대한 포인터 이다. 포인터는 파일 ID, 페이지 번호 미 및 페이지의 행 번호로 구성 된다. 전체 포인터를 RID라고 한다.
  • 테이블에 클러스터형 인덱스가 있거나 인덱스가 인덱싱된 뷰에 있는 경우 행 로케이터는 행에 대한 클러스터형 인덱스 키이다. 클러스터형 인덱스가 고유한 인덱스가 아니면 SQL Server에서는 uniqueifier라는 내부적으로 생성된 4바이트 값을 추가하여 중복 키를 고유키로 변경한다. 이 값은 사용자에게 보이지는 않는다. 비클러스터형 인덱스에서 사용하기 위해 클러스터형 키를 고유한 키로 바꿔야 할 때만 이 값이 추가 된다. SQL Server는 비클러스터형 인덱스의 리프 행에 저장된 클러스터형 인덱스를 찾아 데이터를 검색 한다.

 

 

 

비클러스터형 인덱스에는 인덱스에서 사용하는 각 파티션에 대해 index_id > 0의 sys.partitions 행을 가지고 있다. 기본적으로 비클러스터형 인덱스는 단일 파티션을 가지고 있다. 비클러스터형 인덱스가 다중 파티션을 사용하는 경우 각 파티션은 해당 특정 파티션에 대한 인덱스 행을 포함하는 B-tree 구조를 갖는다.

select * from sys.partitions where index_id > 0

 

 

 

비클러스터형 인덱스의 데이터 형식에 따라 각 비클러스터형 인덱스 구조에는 특정 파티션에 대한 데이터를 저장하고 관리하는 할당 단위가 있다. 최소한 각 비클러스터형 인덱스에는 인덱스 B-tree 페이지를 저장하는 파티션당 하나의 IN_ROW_DATA 할당 단위가 있다. 또한 비클러스터형 인덱스에는 LOB(Large Object)열이 포함된 경우 파티션당 하나의 LOB_DATA 할당 단위가 있다. 8060바이트 행 크기 제한을 초과하는 가변 길이 열이 포함된 경우 파티션당 하나의 ROW_OVERFLOW_DATA 할당 단위도 있다. B-tree에 대한 페이지 컬렉션은 sys.system_internals_acclocation_units 시스템 뷰의 root_page 포인터로 고정 된다.

select * from sys.system_internals_allocation_units

 

 

 

[비클러스터형 인덱스 찾기]

아래 스크립트를 실행하면 비클러스터형 테이블을 확인 할 수 있다.

SELECT

    B.NAME, A.TYPE_DESC, B.TYPE_DESC, B.CREATE_DATE

FROM SYS.INDEXES AS A

    INNER JOIN SYS.OBJECTS AS B

        ON A.OBJECT_ID = B.OBJECT_ID

WHERE B.TYPE_DESC = 'USER_TABLE' AND A.TYPE_DESC = 'NONCLUSTERED' ORDER BY B.NAME

 

 

 

[포괄열(INCLUDE) 인덱스]

키가 아닌 열이라는 포괄열을 추가하여 비클러스터형 인덱스의 기능을 인덱스의 리프 수준으로 확장할 수 있다. 키열은 모든 두순의 비클러스터형 인덱스에 저장되는 반면 키가 아닌 열은 리프 수준에만 저장된다.

포괄열 참고 : http://msdn.microsoft.com/ko-kr/library/ms190806(v=sql.105).aspx

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/ms177484(v=sql.105).aspx

http://suneethasdiary.wordpress.com/2011/07/08/indexes-2/

http://msdn.microsoft.com/ko-kr/library/ms190806(v=sql.105).aspx

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1337
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 973
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 933
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1487
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1172
» SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1066
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1370
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1069
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1127
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1096
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1254
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 801
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1130
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 975
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1457
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 3317
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2184





XE Login