16. [MS-SQL] 논클러스터 인덱스(Non-Clustered Index)

논클러스터 인덱스(Non-Clustered Index)

NON CLUSTERED INDEX를 사용하는 상황은 주로 특정 쿼리 성능을 향상시키기 위해 필요합니다.

논클러스터 인덱스의 특징과 사용해야 하는 일반적인 상황은 다음과 같습니다

논클러스터 인덱스의 주요 특징

1. 물리적 순서와 무관:
   • 논클러스터 인덱스는 테이블의 물리적 순서에 영향을 미치지 않습니다.
     이는 클러스터 인덱스와의 주요 차이점입니다.
     클러스터 인덱스는 테이블의 실제 데이터가 인덱스 키 순서에 따라 물리적으로 정렬되도록 합니다.
2. 별도의 저장 구조:
   • 논클러스터 인덱스는 데이터베이스 테이블의 실제 데이터와는 별도로 저장됩니다.
     인덱스는 인덱스 키 값과 해당 데이터 행의 위치 정보를 포함하는 구조로 구성됩니다.
3. 데이터 행의 주소 저장:
   • 논클러스터 인덱스는 인덱스 키 값과 해당 데이터 행의 주소(RID, Row Identifier)를 저장합니다.
     이를 통해 인덱스를 사용하여 데이터를 빠르게 찾을 수 있습니다.
4. 다중 논클러스터 인덱스 지원:
   • 하나의 테이블에 여러 개의 논클러스터 인덱스를 생성할 수 있습니다.
     이는 다양한 검색 조건에 대해 각각의 인덱스를 사용할 수 있게 합니다.

---

논클러스터 인덱스를 사용해야 하는 상황

1. 빈번한 검색 작업:
   • 특정 컬럼에 대한 검색이 빈번히 일어나는 경우, 해당 컬럼에 논클러스터 인덱스를 생성하면 검색 속도가 빨라질 수 있습니다.
2. WHERE 조건에 자주 사용되는 컬럼:
   • WHERE 절에서 자주 사용되는 컬럼에 논클러스터 인덱스를 생성하면 조건에 맞는 데이터를 빠르게 찾을 수 있습니다.
3. JOIN 연산 최적화:
   • 다른 테이블과의 조인에서 자주 사용되는 컬럼에 논클러스터 인덱스를 생성하면 조인 성능이 향상될 수 있습니다.
4. ORDER BY 및 GROUP BY:
   • ORDER BY 또는 GROUP BY 절에서 자주 사용되는 컬럼에 논클러스터 인덱스를 생성하면 정렬 및 그룹화 작업이 더 빠르게 수행될 수 있습니다.
5. SELECT 문에서 자주 사용되는 2개 이상의 컬럼 (복합 인덱스):
   • 특정 컬럼만을 선택하여 조회하는 경우, 해당 컬럼에 논클러스터 인덱스를 생성하면 전체 테이블 스캔을 피할 수 있습니다.

---

예시 쿼리

1. 자주 검색되는 칼럼

-- 고객 테이블에서 이름으로 자주 검색하는 경우
CREATE NONCLUSTERED INDEX idx_customer_name
ON Customers (CustomerName);

2. WHERE 조건에 자주 사용되는 컬럼

-- 주문 테이블에서 주문 날짜로 자주 검색하는 경우
CREATE NONCLUSTERED INDEX idx_order_date
ON Orders (OrderDate);

-- 사용 예시
SELECT *
FROM Orders
WHERE OrderDate = '2024-05-31';

3. JOIN 연산에 자주 사용되는 칼럼

-- 주문 테이블과 고객 테이블을 고객 ID로 자주 조인하는 경우
CREATE NONCLUSTERED INDEX idx_order_customer_id
ON Orders (CustomerID);

-- 사용 예시
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

4. ORDER BY 및 GROUP BY 에 자주 사용되는 칼럼

-- 제품 테이블에서 가격으로 정렬하는 경우
CREATE NONCLUSTERED INDEX idx_product_price
ON Products (Price);

-- 사용 예시
SELECT *
FROM Products
ORDER BY Price DESC;

5. SELECT 문에서 자주 사용되는 2개 이상의 컬럼 (복합 인덱스)

*복합인덱스를 설정할 때의 칼럼 순서대로 정렬됨 아래처럼 Name,Title이면, Name부터 정렬된 후, Title로 정렬됨

따라서, 순서가 중요함

-- 직원 테이블에서 이름과 직책을 자주 조회하는 경우
CREATE NONCLUSTERED INDEX idx_employee_name_title
ON Employees (Name, Title);

-- 사용 예시
SELECT Name, Title
FROM Employees;

요약

논클러스터 인덱스는 주로 검색 성능을 최적화하고, 특정 컬럼에 대한 접근을 빠르게 하기 위해 사용됩니다. 테이블의 사용 패턴을 분석하고, 쿼리에서 자주 사용되는 컬럼에 대해 논클러스터 인덱스를 생성하면 전체적인 쿼리 성능을 크게 향상시킬 수 있습니다.

---

하지만, 논클러스터드 인덱스가 많은 경우 다음과 같은 문제가 발생할 수 있습니다

1. 저장 공간 소비:
   • 많은 인덱스가 많은 저장 공간을 차지하게 됩니다. 각 인덱스는 추가적인 디스크 공간을 요구하며, 데이터베이스의 전체 용량을 증가시킬 수 있습니다.
2. 쓰기 성능 저하:
   1. 인덱스를 생성하면 데이터를 삽입, 수정, 삭제할 때마다 인덱스도 갱신되어야 합니다. 논클러스터드 인덱스가 많을수록 쓰기 작업의 성능이 저하될 수 있습니다.
3. 인덱스 관리 오버헤드:
   • 많은 인덱스는 데이터베이스 관리 오버헤드를 증가시킵니다. 인덱스가 많을수록 쿼리 옵티마이저가 적절한 인덱스를 선택하는데 더 많은 시간이 걸리고, 통계 정보를 유지하는 데도 더 많은 리소스가 소요됩니다.
4. 쿼리 성능 저하:
   • 인덱스가 많으면 오히려 쿼리의 성능이 저하될 수 있습니다. 쿼리 옵티마이저가 적절한 인덱스를 선택하기 어려워지고, 인덱스의 활용이 최적화되지 않을 수 있습니다.
5. 인덱스 키 크기 제한:
   • 논클러스터드 인덱스의 키 크기가 제한되어 있습니다. 많은 컬럼을 포함하는 복합 인덱스를 생성하면 키 크기 제한에 도달할 수 있으며, 이로 인해 인덱스 생성이 실패할 수 있습니다.
6. 인덱스의 불필요한 중복:
   • 너무 많은 인덱스를 생성하면 비슷한 컬럼을 기준으로한 중복 인덱스가 생길 수 있습니다. 이 경우 인덱스가 중복되어 관리하기 어려워지며, 유지보수 비용이 증가할 수 있습니다.

따라서 적절한 인덱스를 설계할 때는 쿼리 패턴과 데이터의 특성을 고려하여 필요한 인덱스만 생성하는 것이 중요합니다. 불필요한 인덱스는 성능 저하와 유지보수 비용을 증가시킬 수 있으므로 피해야 합니다.