MSSQL 디스크 기반의 인덱스 설명

데이터베이스에서 효율적으로 데이터를 검색하기 위해서는 인덱스에 대해 이해하고 있어야 합니다. 예를 들어 책을 보면 책 앞 쪽에 색인(인덱스)이 있어서 찾고자 하는 페이지를 쉽게 찾을 수 있게 목차 페이지가 있습니다. 데이터베이스에서 인덱스란 책의 목차와 유사합니다. 

MSSQL에서는 스토리지 형식에 따라 사용할 수 있는 인덱스 유형이 정의되어 있습니다. 일반적으로 사용하는 디스크 기반의 rowstore 형식은 클러스터형, 비클러스터형 인덱스를 사용할 수 있고, 메모리 최적화 테이블 기반은 hash와 비클러스터형 인덱스를 사용할 수 있습니다. 

메모리 최적화 테이블 기반은 별도의 설정을 통해 사용할 수 있기 때문에 이 글에서는 디스크 기반의 인덱스만 다루겠습니다. 

---

디스크 기반의 인덱스 종류 

• 클러스터 형
• 비클러스터형
• 고유 인덱스
  클러스터형 인덱스와 비클러스터형 인덱스 모두 고유 인덱스가 될 수 있습니다. Primary Key 또는 Unique 제약 조건을 만들면 지정된 열에는 고유 인덱스가 자동으로 생성됩니다. 
• Filtered 인덱스 
  최적환된 비클러스터형 인덱스로 비클러스터형 인덱스를 생성할 때 Where 절을 추가하여 필터링시킨 인덱스를 말합니다. 

※ 인덱스 종류는 4개로 나오지만 실질적인 인덱스는 클러스터형과 비클러스터형 두 개로 봐야 합니다. 나머지 두 개는 확장 개념으로 봐야 합니다. 

---

클러스터형(Clustered)

테이블에 데이터 행이 정렬된 순서로 지정된 인덱스로 하나의 테이블에는 하나의 클러스터형 인덱스만 존재합니다. 만약 클러스터형 인덱스가 없는 테이블이 있다면 그 테이블은 힙 테이블(정렬이 되지 않은 구조) 지정됩니다.

주로 기본 키(Primary key)가 클러스터형 인덱스입니다. 

---

비클러스터형(Non Clustered)

데이터 행에 독립적인 인덱스입니다. 비클러스터형 인덱스는 인덱스 키 값이 있으며 각 키 값 항목에 키 값이 포함된 데이터 행에 대한 포인터가 있습니다. 

Unique 제약 조건을 추가하면 비클러스터형 인덱스를 생성합니다.

또한, 기본 키가 없는 테이블에 기본 키를 추가할 때 기존에 클러스터형 인덱스가 이미 있다면 비클러스형 인덱스를 사용하여 기본 키를 생성합니다. 

---

인덱스의 생성

● 클러스터형 

 
CREATE CLUSTERED INDEX IX_TestTable_TestCol1   
    ON dbo.TestTable (TestCol1);   
 

 

● 비클러스터 형

 
CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID   
    ON Purchasing.ProductVendor (BusinessEntityID);  
 

 

● 고유 인덱스 

 
CREATE UNIQUE INDEX AK_UnitMeasure_Name   
   ON Production.UnitMeasure (Name);   
 

 

● 필터링된 인덱스 

 
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
 

 

● 포괄 열이 있는 인덱스 

 
CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
 

※ 포괄 열이 있는 인덱스는 select 문에서 조건과 조회 열을 따로 구분하여 인덱스를 지정하는 방법으로 where 절에 있는 컬럼은 인덱스 컬럼으로 select 절에 나오는 컬럼은 include 컬럼으로 지정합니다. 

---

인덱스의 삭제    

 
DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor; 
 

---

인덱스의 수정

인덱스 열을 추가 또는 제거하거나 그 위치를 변경할 때는 인덱스를 삭제하고 다시 만들어야 합니다. 

다만, 인덱스의 옵션을 변경할 수는 있습니다. 

 
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON)
;
 
--위 인덱스를 변경할 때는 아래와 같이 작성합니다. 
 
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    )
;
 

 

 

자료출처) https://learn.microsoft.com/