6_인덱스_칼럼_순서.md

인덱스 칼럼 순서

USE Northwind;

-- 복합 인덱스 칼럼 순서
-- Index(A, B, C)

-- NonClustered
--   1
-- 2 3 4

-- Clustered
--   1
-- 2 3 4

-- Heap Table [ {Page} {Page} {Page}]

-- 북마크 룩업을 최소화 하는 것만으로 최적화가 끝나는 것을까?
-- No, Leaf Page 탐색은 여전히 존재하기 때문
-- [레벨, 종족] 인덱스 => (lv 56 휴먼 Search)

SELECT *
INTO TestOrders
FROM Orders;

DECLARE @i INT = 1;
DECLARE @emp INT;
SELECT @emp = MAX(EmployeeID) FROM Orders;

-- 더미 데이터를 엄청 늘린다(830 * 1000)

WHILE (@i < 1000)
BEGIN
		INSERT INTO TestOrders(CustomerID, EmployeeID, OrderDate)
		SELECT CustomerID, @emp + @i, OrderDate
		FROM Orders;
		SET @i = @i + 1;
END

CREATE NONCLUSTERED INDEX idx_emp_ord
ON TestOrders(EmployeeID, OrderDate)

CREATE NONCLUSTERED INDEX idx_ord_emp
ON TestOrders(OrderDate, EmployeeID)

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- 두 개 인덱스를 비교
SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate = CONVERT(DATETIME, '19970101');

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate = CONVERT(DATETIME, '19970101');

-- 두 개의 결과가 완벽하게 동일하게 나왔다
-- 스캔 수, 논리적 읽기, 실행 계획까지 모두 동일

-- 직접 살펴보자
SELECT *
FROM TestOrders
ORDER BY EmployeeID, OrderDate;

SELECT *
FROM TestOrders
ORDER BY OrderDate, EmployeeID;

-- 범위로 찾는다면?
-- ex. 이벤트로 아이템을 뿌리는 데 최근에 접속한 유저들에게만 아이템을 뿌려야할 때
SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';
-- OrderDate >= '19970101' AND OrderDate <= '19970103'

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';

-- 1번의 경우 논리적 읽기 : 5
-- 2번의 경우 논리적 읽기 : 16

-- 직접 살펴보자
SELECT *
FROM TestOrders
ORDER BY EmployeeID, OrderDate;
-- idx_emp_ord 인덱스로 서치를 하면 
-- 이미 EmployeeID로 정렬한 후에  OrderDate로 정렬한 데이터를 서치를 하기 때문에
-- EmployeeID가 1일 때 일정 범위의 OrderDate의 값을 찾는 과정이 조금 더 수월하다.

SELECT *
FROM TestOrders
ORDER BY OrderDate, EmployeeID;
-- idx_ord_emp 인덱스로 서치를 하면
-- OrderDate로 먼저 정렬이 되어 있기 때문에 19970101 ~ 19970103까지 범위를 찾고
-- 거기서 EmployeeID가 1일 때를 찾아야 하는 과정을 거치기 때문에 좀더 많은 범위의 데이터를 찾게 된다.

-- 결론 Index(a, b, c)로 구성되었을 때, 선행에 between 사용 => 후행은 인덱스 기능 x
-- a에서 between을 사용했으면 b,c는 인덱스 기능을 사용하지 못함
-- 즉 between 기능을 활용하려면 후행에다가 between 인덱스를 사용해야 한다.

-- 근데.. between 같은 비교가 등장하면 인덱스 순서만 무조건 바꿔주면 될까? -> NO!
-- 좀 애매한 게 다양한 SQL 구문을 활용해서 해당 DB를 활용할 텐데
-- 하나의 케이스만 놓고 쿨하게 인덱스를 추가하고 수정하는 것은 위험할 수가 있다.
-- 모든 케이스를 보고 다른 쿼리에도 어떤 영향을 줄지 생각을 하고 결정을 내려야 한다.

-- 꿀팁
-- BETWEEN 범위가 작을 때는 -> IN-LIST로 대체하는 것을 고려해보자

SET STATISTICS PROFILE ON;

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate IN('19970101', '19970102', '19970103');

-- 위 구문에서 동작은
-- (EmployeeID = 1 AND 19970101) OR (EmployeeID = 1 AND 19970102) OR (EmployeeID = 1 AND 19970103)
-- 위와 같이 총 3번에 걸쳐서 값을 찾아내기 때문에 사실 상 여러번 비교 연산을 하면서 서치를 한다.

-- 오늘의 결론

-- 복합 컬럼 인덱스(선행, 후행) 순서가 성능에 영향을 줄 수 있다.
-- 특히 특정 범위 내(BETWEEN, 부등호)에서 인덱스가 선행에 들어가면, 후행은 인덱스 기능을 상실하게 된다.
-- 이를 해결할 수 있는 방법 중 하나가 만일 BETWEEN 범위가 적으면 IN-LIST로 대체하면 좋은 경우가 있다.
-- => 선행에 BETWEEN이 있을 경우에만 사용
-- 선행 =, 후행이 BETWEEN이라면 아무런 문제가 없다.