[sql-server] 클러스터형 및 비 클러스터형 인덱스는 실제로 무엇을 의미합니까?

SQL Server 행 지향 스토리지에서 클러스터 및 비 클러스터형 인덱스는 모두 B 트리로 구성됩니다.

( 이미지 소스 )

클러스터 인덱스와 비 클러스터 인덱스의 주요 차이점은 클러스터 인덱스의 리프 레벨이 있다는 것입니다 이다 테이블. 이것은 두 가지 의미가 있습니다.

1. 클러스터 된 인덱스 리프 페이지의 행은 항상 포함 뭔가 테이블에있는 (비 스파 스)의 각 열 (값 또는 실제 값에 대한 포인터 중 하나를).
2. 클러스터형 인덱스는 테이블의 기본 복사본입니다.

클러스터되지 않은 인덱스는 INCLUDE키가 아닌 모든 열을 명시 적으로 포함하기 위해 SQL Server 2005 이후 절 을 사용하여 포인트 1을 수행 할 수 있지만 2 차 표현이며 항상 다른 데이터 복사본 (테이블 자체)이 있습니다.

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

위의 두 지수는 거의 동일합니다. 키 열에 대한 값이 포함 된 상위 수준 색인 페이지 A,B와 다음을 포함하는 리프 수준 페이지A,B,C,D

데이터 행 자체는 한 순서로만 정렬 될 수 있으므로 테이블 당 하나의 클러스터형 인덱스 만있을 수 있습니다.

SQL Server 온라인 설명서의 위 인용문은 많은 혼란을 야기합니다.

내 의견으로는 그것은 훨씬 더 나은 표현이 될 것입니다.

클러스터 된 인덱스의 리프 수준 행이 있기 때문에 테이블 당 하나의 클러스터 된 인덱스가있을 수 있습니다 테이블 행.

온라인 설명서는 정확하지 않지만 클러스터되지 않은 인덱스와 클러스터 된 인덱스의 “정렬”은 논리적이지 않은 것이 분명합니다. 링크 된 목록을 따라 리프 레벨에서 페이지를 읽고 슬롯 배열 순서로 페이지의 행을 읽는 경우 색인 행을 정렬 된 순서대로 읽지 만 실제로는 페이지가 정렬되지 않을 수 있습니다. 일반적으로 클러스터 된 인덱스를 사용하면 인덱스 키 가 false 인 순서대로 행이 디스크에 물리적으로 항상 저장됩니다 .

이것은 터무니없는 구현입니다. 예를 들어 행이 4GB 테이블 가운데에 삽입 된 경우 SQL Server는 새로 삽입 된 행을위한 공간을 만들기 위해 파일에 2GB의 데이터를 복사 할 필요 가 없습니다 .

대신 페이지 분할이 발생합니다. 클러스터형 및 비 클러스터형 인덱스의 리프 수준에있는 각 페이지 File:Page에는 다음 및 이전 페이지 의 주소 ( )가 논리 키 순서로 있습니다. 이 페이지는 연속적이거나 키 순서 일 필요는 없습니다.

예를 들어 링크 된 페이지 체인은 1:2000 <-> 1:157 <-> 1:7053

페이지 분할이 발생하면 새 페이지가 파일 그룹의 어느 위치에서나 (소형 테이블의 경우 혼합 범위 또는 해당 오브젝트에 속하는 비어 있지 않은 균일 범위 또는 새로 할당 된 균일 범위에서) 할당됩니다. 파일 그룹에 둘 이상의 파일이 포함되어 있으면 동일한 파일에 없을 수도 있습니다.

논리적 순서와 연속성이 이상적인 물리적 버전과 다른 정도는 논리적 조각화 정도입니다.

단일 파일로 새로 만든 데이터베이스에서 다음을 실행했습니다.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

그런 다음 페이지 레이아웃을 확인했습니다.

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

결과는 도처에 있었다. 키 순서의 첫 번째 행 (값 1-아래 화살표로 강조 표시됨)은 거의 실제 페이지에있었습니다.

논리적 순서와 물리적 순서 사이의 상관 관계를 증가시키기 위해 인덱스를 재구성하거나 재구성하여 조각화를 줄이거 나 제거 할 수 있습니다.

실행 후

ALTER INDEX ix ON T REBUILD;

나는 다음을 얻었다

테이블에 클러스터형 인덱스가 없으면 힙이라고합니다.

비 클러스터형 인덱스는 힙 또는 클러스터형 인덱스에 구축 할 수 있습니다. 그들은 항상 기본 테이블로 돌아가는 행 로케이터를 포함합니다. 힙의 경우 이것은 물리적 행 식별자 (rid)이며 3 개의 구성 요소 (File : Page : Slot)로 구성됩니다. 클러스터형 인덱스의 경우 행 로케이터는 논리적입니다 (클러스터형 인덱스 키).

후자의 경우 비 클러스터형 인덱스에 이미 CI 키 열이 NCI 키 열 또는 INCLUDE-d 열로 이미 포함되어 있으면 아무 것도 추가되지 않습니다. 그렇지 않으면 누락 된 CI 키 열이 자동으로 NCI에 추가됩니다.

SQL Server는 항상 키 열이 두 유형의 인덱스에 대해 고유한지 확인합니다. 그러나 고유하게 선언되지 않은 인덱스에 대해 적용되는 메커니즘은 두 인덱스 유형마다 다릅니다.

클러스터 된 인덱스 uniquifier는 기존 행을 복제하는 키 값이있는 행에 대해 추가됩니다. 이것은 오름차순 정수입니다.

고유 한 SQL Server로 선언되지 않은 비 클러스터형 인덱스의 경우 행 로케이터를 비 클러스터형 인덱스 키에 자동으로 추가합니다. 이것은 실제로 중복되는 행뿐만 아니라 모든 행에 적용됩니다.

클러스터형 및 비 클러스터 명명법은 열 저장소 인덱스에도 사용됩니다. SQL Server 열 저장소에 대한 종이 향상 상태

열 저장소 데이터는 실제로 어떤 키에서도 “클러스터”되지는 않지만 기본 인덱스를 클러스터형 인덱스라고하는 전통적인 SQL Server 규칙을 유지하기로 결정했습니다.

---

답변