[MSSQL] 복합 인덱스

가장 중요한 개념이 있다.

 

인덱스는 여러개의 칼럼에 설정할 수 있다.

단일 인덱스를 여러개 생성할 수 있고, 여러 칼럼을 묶은 복합 인덱스를 설정할 수도 있다.

다만 인덱스는 디비의 메모리를 사용하여 테이블 형태로 저장하므로 개수와 저장되는 공간은 비례하기에

where절에 자주 사용되며 고유한 값 위주로 인덱스를 설정하는게 옳다.

 

select *
INTO TestTable2
FROM [Order Details] -- NorthWind db의 테이블

-- 복합 인덱스 추가
CREATE INDEX INDEX_TESTINDEX ON TestTable2(OrderID,ProductID); -- 두 칼럼을 대상으로 인덱스를 걸음.

결론만 말하면 두 칼럼을 대상으로 인덱스가 걸려있을 때 where절을 통해 각 칼럼(OrderID는 A ProductID는 B라고 지칭)

A,B를 조건으로 SELECT 시 인덱스의 덕을본다. A 칼럼만을 조건으로 넣어도 동일하다.

단! B만을 가지고 WHERE절 조건을 넣어 검색을하면 풀스캔하는 방식과 다른게 없다. (즉 인덱스 덕을 보지못한다)

 -> 복합 인덱스를 볼 때 가장 중요한 것. 즉 복합 인덱스의 칼럼을 어떤 순서로 걸었는지가 매우 중요.

 -> 그 이유는 간단히 말하면 실제 인덱스(A,B)를 걸 때 A를 기준으로 데이터를 정렬하고 A가 동일한 경우에만 B를 이용해 둘 사이의 우선순위를 결정하게 됨. 

 

실제로 WHERE절 조건을 각 3가지로 나누어서 성능을 확인해보면 (Ctrl + L)

 

1. Where절 조건에 OrderID,ProductID 모두. (참고로 둘 다 조건으로 둘어오는 경우 WHERE절 조건에 순서가 ProductID가 먼저와도 상관X)

-- INDEX SEEK가 빠르다고 보면됨. SCAN은 느린 것.
SELECT *
	FROM TestTable2
	WHERE OrderID = 10252 AND ProductID = 455

2. Where절 조건에 OrderID만

-- INDEX SEEK가 빠르다고 보면됨. SCAN은 느린 것.
SELECT *
	FROM TestTable2
	WHERE OrderID = 10252

3. Where절 조건에 ProductID만

-- INDEX SEEK가 빠르다고 보면됨. SCAN은 느린 것.
SELECT *
	FROM TestTable2
	WHERE ProductID = 455

 

만약 ProductID만을 대상으로 검색이 자주 일어나야한다면 해당 칼럼을 대상으로 따로 인덱스를 걸어줘야한다.

 -> 다만 위에서 A,B칼럼이 복합인덱스인데 A칼럼을 하나 더 인덱스걸어준다던지 하는짓은 하면 안된다. (공간낭비)

 

참고로 위의 내용은 인덱스가 Clustered/Non-Clustered 상관없이 모두 해당 되는 내용.

 -> Non-Clustered의 경우 정렬방식은 아니고 따로 테이블이 만들어지는 형식.

 

마지막으로, 인덱스를 걸었다해서 (앞에서 a,b순서에서 a를 걸었다하더라도) where절 조건에서 온전한 값으로 조건을 걸어 조회하는게 아니면 INDEX Seek이 아닌 Index Scan을 볼 수 있다.

 

대표적인 예로 문자열 칼럼을 대상으로 인덱스를 걸을 때.

WHERE SUBSTRING(NickName,1,4) = 'abcd';

위와 같이 인덱스의 값을 가공해서 조건을 달 경우 인덱스 Seek이 되지 않을 수 있음.