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

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

– 테이블 및 인덱스 구성

 

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

 

SQL Server 데이터베이스의 개체는 8KB 페이지의 컬렉션으로 저장된다. 테이블 미 및 인덱스 페이지가 구성되고 저장, 액세스 되는 방법에 대해서 4장에 걸쳐 다루어 본다.

 

[테이블 구성]

테이블은 하나 이상의 파티션에 포함되어 있으며 각 파티션에는 데이터 행이 힙 또는 클러스터형 인덱스 구조로 포함되어 있다. 힙 또는 클러스터형 인덱스 페이지는 데이터 행의 열 유형에 따라 하나 이상의 할당 단위로 관리 된다.

 

페이지 할당 관리 관련 : http://sqlmvp.kr/140186842239

 

아래 그림은 테이블의 구성을 나타낸다.

 

 

[파티션]

테이블 및 인덱스 페이지는 하나 이상의 파티션에 포함되어 있다. 파티션은 사용자 정의된 데이터 구성 단위이다. 기본적으로 테이블이나 인덱스는 하나의 파티션을 갖는다. 파티션은 단일 파일 그룹에 포함되어 있다.

 

테이블 또는 인덱스에 파티션이 여러 개 사용되면 지정된 열을 기준으로 행 그룹이 개별 파티션에 매핑 되도록 데이터가 행 분할 된다. 이러한 파티션은 데이터베이스에 있는 하나 이상의 파일 그룹에 포함 될 수 있다. 데이터에서 쿼리나 업데이트가 수행되면 테이블이나 인덱스는 단일 논리적 엔터티로 처리 된다.

 

[클러스터형 테이블, 힙 테이블, 인덱스]

SQL Server 테이블은 다음 두 방법 중 하나를 사용하여 파티션 내에 있는 데이트 페이지를 구성 한다.

• 클러스터형 테이블 : 클러스터형 인덱스를 갖고 있는 테이블. 데이터 행은 클러스터형 인덱스 키에 기반한 순서대로 저장. B-tree 구조. 리프 수준의 데이터 페이지를 포함하여 각 인덱스 수준에 있는 페이지는 이중 연결 리스트로 연결. 한 수준에서 다른 수준으로 이동하는 경우 키 값을 사용

 

• 힙 테이블 : 클러스터형 인덱스가 없는 테이블. 데이터 행은 특정한 순서로 저장되지 않음. 페이지 시퀀스에 대한 특별한 순서 없음. 데이터 페이지는 연결 리스트로 연결되지 않음.

 

힙 또는 클러스터형 테이블의 파티션이 여러 개인 경우 각 파티션에는 해당 파티션의 행 그룹이 포함된 힙 또는 B-tree구조가 있다. (파티션이 4개인 경우 4개의 B-tree가 있다.)

 

인덱싱된 뷰는 클러스터형 테이블과 동일한 저장소 구조를 갖는다.

 

[비클러스터형 인덱스]

비클러스터형 인덱스는 클러스터형 인덱스와 유사한 B-tree 구조를 가지고 있다. 클러스터형 인덱스와 차이점은 비클러스터형 인덱스는 행의 순서에 영향을 주지 않는다. 리프 수준에는 인덱스 행이 포함되어 있다. 각 인덱스 행에는 비클러스터형 키 값, 행 포인터 및 포괄 열이나 키가 아닌 열이 포함 된다. 포인터 값은 키 값이 있는 데이터 행을 가리킨다.

 

[XML 인덱스]

테이블의 각 XML열에는 기본 XML 인덱스 하나와 보조 XML 인덱스 여러 개를 만들 수 있다. XML 인덱스는 XML 데이터 형식 열의 XML BLO(Binary Large Object)를 영구적인 단편 형태로 표현한 것이다. XML 인덱스는 내부 테이블로 저장 된다.

 

[할당 단위]

할당 단위는 힙 또는 B-tree 내에서 해당 페이지 유형에 따라 데이터를 관리하는데 사용되는 페이지 컬렉션이다. 아래 표는 테이블 및 인덱스 데이터를 관리하는데 사용되는 할당 단위 유형이다. 힙 또는 B-tree는 특정 파티션에서 각 유형별로 하나의 할당 단위만 가질 수 있다.

할당 단위 유형	설명
IN_ROW_DATA	LB(Large Object)데이터를 제외한 모든 데이터가 포함되어 있는 데이터 또는 인덱스 행. 페이지 또는 인덱스 유형
LOB_DATA	Text, ntext, image,xml, varchar(max),nvarchar(max), varbinary(max) 또는 CLR UDT 형식의 큰 개체. 페이지는 텍스트 또는 이미지 유형
ROW_OVERFLOW_DATA	8060바이트 행 크기 제한을 초과한 varchar, nvarchar, varbinary 또는 sql_variant 열에 저장된 가변 길이 데이터. 페이지는 텍스트 또는 이미지 유형.

 

 

[파티션 및 할당 단위 예제]

다음 스크립트를 실행 하면 DatabaseLog 테이블과 Currency 테이블의 파티션 할당 단위를 확인 할 수 있다.

USE AdventureWorks2008R2; GO SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.partition_id JOIN sys.objects AS o ON p.object_id = o.object_id JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id WHERE o.name = N'DatabaseLog' OR o.name = N'Currency' ORDER BY o.name, p.index_id;

 

DatabaseLog 테이블에는 텍스트/이미지 페이지 유형이 포함되어있기 때문에 세가지 할당 유형이 모두 사용 된다. Currency 테이블에는 LOB 데이터가 없지만 데이터 페이지 관리에 필요한 할당 단위가 있다. Currency 테이블이 나중에 LOB 데이터 형식 열을 포함하도록 수정되면 해당 데이터를 관리 하기 위해 LOB_DATA 할당 단위가 생성 된다.

 

 

[참고자료]

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

http://sqlmvp.kr/140186842239

 

 

2013-04-22 / 강성욱 / http://sqlmvp.kr