MSSQL 디스크 기반의 인덱스 디자인 가이드

경험이 많은 데이터베이스 관리자는 인덱스를 잘 다자인 할 수 있습니다. 데이터베이스, 쿼리 및 데이터 열의 특성을 이해하면 최적인 인덱스를 디자인할 수 있습니다. 아래는 인덱스 디자인 시 고려하는 사항입니다. 

 

---

1. 데이터베이스 고려 사항 

• 테이블에 인덱스를 많이 만들면 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE문의 성능이 저하될 수 있습니다. 
  데이터를 수정하는 UPDATE 문을 실행하는 경우 열을 포함하는 각 인덱스뿐만 아니라 기본 테이블에 있는 열도 업데이트가 되어야 합니다.
  
• 가능한 열 수가 적은 인덱스를 만듭니다.
  
• 하지만 데이터를 수정하지 않는 경우 많은 열의 인덱스는 조회 쿼리 성능에 도움이 됩니다.

인덱스를 디자인하고자 하는 테이블의 사용 특성을 이해해야 합니다. 데이터 수정 작업의 횟수와 데이터의 용량 등을 고려해야 합니다. 만약, 인덱스를 너무 많이 만든다면 인덱스의 저장 공간이 과도하게 할당되게 되고 성능을 얻고 스토리지를 추가해야 하는 경우가 발생할 수도 있습니다. 또한 사용되는 컬럼이 잘못 지정된 인덱스는 사용되지 않는 인덱스가 될 가능성이 높습니다. 

 

---

2. 쿼리 고려 사항

• 조건자 및 조인 조건에 자주 사용되는 열에 대해 비클러스형 인덱스를 만듭니다.
• 여러 개의 쿼리를 사용하여 동일한 여러 행을 업데이트하는 대신 단일 문에 가능한 많은 행을 삽입하거나 수정하는 쿼리를 작성합니다. 문을 하나만 사용하면 최적화된 인덱스 유지 관리가 수행될 수 있습니다.

 인덱스를 생성할 때 Filtered 인덱스로 생성할 수도 있으며, 포괄 열(include)을 지정하여 생성할 수도 있습니다. 또한 인덱스에 정렬을 주어 생성할 수도 있습니다.  쿼리 분석을 통해 인덱스를 좀 더 효율적으로 생성할 수 있습니다. 정렬을 지정하면 실행계획에서 SORT 연산자 홍목이 제거되는 경우도 있습니다. 

 
-- 필터(Filtered) 인덱스 생성 
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO
 
-- 아래와 같은 조회 쿼리에 인덱스를 생성한다면 포괄 열 인덱스로 생성 
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';
 
-- 포괄 열 인덱스 생성 
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
 
 
-- 인덱스의 정렬 
CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO
 

---

3. 열 고려 사항

• 클러스터형 인덱스의 키 길이는 짧게 유지합니다. 클러스터형 인덱스는 고유하거나 NULL이 아닌 열에 만들어지는 이점을 누릴 수 있습니다. 
• 인덱스로 사용되는 열의 고유한지 확인합니다. 고유하지 않은 인덱스보다 고유한 인덱스가 최적화됩니다.
• 인덱스에 여러 열이 포함된 경우 열의 순서를 고려합니다. 
  WHERE절에서 보다 큼(=), 보다 작음 또는 BETWEEN 검색 조건에 사용되거나 조인에 참여하는 열이 먼저 배치되어야 합니다.   추가 열은 고유성 수준에 따라 가장 고유한 열에서 가장 고유하지 않은 열의 순서로 정렬되어야 합니다. 
  

---

인덱스 정렬 순서 디자인 지침

인덱스를 정의할 때 인덱스 키 열의 데이터를 오름차순으로 저장할지 또는 내림차순으로 저장할지 고려합니다. 오름차순이 기본값입니다.  인덱스의 정렬과 쿼리의 실행 계획에서의 SORT 연산자의 정렬이 동일하면 SORT 연산자를 제거하여 쿼리 계획의 효율성을 향상할 수 있습니다. 

 

---

클러스터형 인덱스 디자인 지침

클러스터형 인덱스는 B+ 트리로 구성됩니다. 
자세한 내용은 아래를 참조하세요. 

https://learn.microsoft.com/ko-kr/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver16

 
Root node - Intermediate level - Leaf nodes/data pages
 

클러스터형은 접근된 데이터를 찾는데 용이하며 순차적인 데이터나 고유한 데이터에 적합한 인덱스입니다. 
아래와 같은 쿼리를 사용할 때 적합한 인덱스입니다. 

• <=, <, >, >= 및 BETWEEN  연산자를 사용하여 값 범위를 반환합니다.
• JOIN 절을 사용하며 일반적으로 외래 키 열입니다.
• ORDER BY 또는 GROUP BY 절을 사용합니다.

---

비클러스터형 인덱스 디자인 지침

비클러스형 인덱스는 조건절에서 정확하게 일치하는 데이터를 찾을 때 적합합니다. 
비클러스형 인덱스는 다음의 두 가지 주요 차이점을 제외하고 클러스터형 인덱스와 동일한 B-트리 구조를 갖습니다. 

• 기본 테이블의 데이터 행은 비클러스터형 키에 따라 정렬되고 순서대로 저장되지 않습니다.
• 리프 수준은 데이터 페이지 대신 인덱스 페이지로 구성됩니다. 비클러스터형 인덱스의 리프 수준에 있는 인덱스 페이지에는 키 열과 포함된 열이 포함되어 있습니다.

 
Root node - Leaf nodes - data pages
 

---

데이터베이스 고려 사항

• 자주 업데이트 하지는 않지만 데이터가 많은 데이터베이스나 테이블의 경우 비클러스터형 인덱스가 많으면 쿼리 성능이 향상됩니다.
  전체 테이블 인덱스보다 유지 관리 비용을 줄이고, 스토리지 비용을 줄이고, 쿼리 성능을 향상할 수 있도록 잘 정의된 필터링된 인덱스를 만듭니다. 
  
• 자주 업데이트되는 테이블은 너무 많이 인덱싱 하지 않아야 합니다. 또한 인덱스는 가능한 적은 수의 열을 포함하는 좁은 인덱스여야 합니다. 
  테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다. 

 

참고) https://learn.microsoft.com/

Microsoft Learn: 커리어의 문을 여는 기술 구축