인덱스 칼럼순서

인덱스 칼럼순서

• 여러가지 칼럼에 인덱스를 걸 때 검색할 때 어떤 순서로 하냐에 따라서 성능에 큰 영향을 미친다 이유를 살펴보자

실험

더미 데이터 늘리고 인덱스 걸기

USE Northwind;



select *
INTO TestOrders
From Orders


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


-- 더미 데이터 늘리기
SELECT *
FROM TestOrders
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

• 처음 더미 데이터를 걸때 반복문을1000을 거는데 왜 데이터가 *로 늘어났는지 의아했는데 반복문 하나에 한 테이블 데이터라고 생각하면 되었다..

범위로 실험

-- 1
SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate = '19970101'

-- 2
SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate = '19970101'

-- 1과 2는 실행계획과, 논리 읽기 수 , 실행 속도 일치

-- 직접 보는 과정 =  and = 은 순서가 영향을 미치지 않는다 
SELECT *
FROM TestOrders
ORDER BY EmployeeID, OrderDate


-- 범위로 찾는다면? 
-- 3
SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103'

-- 4  orderDate가 먼저걸렸다 
SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103'

-- 3이 4보다 훨씬 빠르다.  

• 범위로 찾을 때 3번은 employee를 먼저 찾고 후행에 between을 하니 더 빨랐다 이유를 생각해보면 between을 먼저한다면 해당 범위를 먼저 쫙 ~ 다 찾고 뒤에 또다시 employeeID를 찾아야하지만, employeeID를 먼저 찾은 상태면 해당 범위의 날짜를 다 찾을 필요가없다.

BETWEEN 대시 IN 사용시

-- between 범위가 작을 때 -> In List로 대체하는 것을 고려 
set statistics PROFILE ON

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

-- PROFILE을 통해서 봤을 때 19970101이면서 EmployeeID = 1것을 찾고 OR를 하는 것을 볼 수 있다 여기서 차이점은 BETWEEN은 모든 것을 찾고 난뒤에 다시 EmployeeID = 1를 찾미나 여기서는
-- 하나하나 매칭한다는 것이 차이점이다.

• BETWEEN 대신 IN을 사용하면 PROFILE을 통해서 봤을 때 19970101이면서 EmployeeID = 1것을 찾고 OR를 하는 것을 볼 수 있다 여기서 차이점은 BETWEEN은 모든 것을 찾고 난뒤에 다시 EmployeeID = 1를 찾미나 여기서는 하나하나 매칭한다는 것이 차이점이다.

• 결론

• index(A, b, c,)로 구성되엇을 대 선행에 between 사용하면 후행 인덱스 기능을 상실하는 느낌이다.

• 이글 은 루키스님의 DB강의를 학습 후 기록하였습니다.