복합인덱스

MSSQL에서 복합 인덱스 만들때

예를 들어,

SELECT 		* 
FROM  		ex_table 
WHERE 		a = 1 AND b = 2 
ORDER BY 	c DESC

라는 쿼리문을 자주 사용하고 해당 조건문들에 대해서 PK, UK가 아니라고 가정했을때

Index (a, b, c) 와 Index (a, b) 가 적절할까?

결론부터 말하자면 전자인 Index (a, b, c) 를 사용하는 것이 정렬 포함 쿼리 성능 최적화에 더 유리하다.

CREATE INDEX idx_mytable_abc ON my_table (a, b, c DESC);

CREATE INDEX idx_mytable_abc ON my_table (a, b);

추가적으로 SELECT 절에 사용되는 컬럼들까지 명시해서 인덱스를 생성하게 된다면 효율이 증가한다.

CREATE INDEX idx_mytable_abc_cover ON my_table (a, b, c DESC)
INCLUDE (d, e); -- SELECT절에 필요한 다른 컬럼들

하지만 복합 인덱스 또는 인덱스가 많게 되면 Insert 성능 문제가 발생한다는 단점이 있다.

그 외로도 하드웨어 성능, 디스크 성능, 트랜잭션 크기, DB 옵션 등이 있다.

현실적인 기준으로는 초당 트랜잭션 갯수가

Index (a, b) 는 10000건~50000건 Index (a, b, c) 는 5000건 ~ 30000건 정도 이므로

디스크 I/O, sys.dm_exec_requests 및 sys.dm_tran_active_transactions 뷰 활용 또는 초당 TPS를 확인하여 인덱스를 생성한다.