SQL Server 인덱스를 고려한 쿼리문 작성

 쿼리문이 느릴 때 인덱스를 만들어 빠르게 하는 방법과 쿼리문을 수정해 기존 인덱스를 효율적으로 사용하는 방법이 있다. 전자는 인덱스 재구성하는 것은 많은 리소스를 소모하기 때문에 자원낭비입니다. 그래서 후자를 통해 해결하는 것이 전문가의 자세입니다.

 

쿼리문 작성 팁

 

1) 열 변경하지 않기

-- 인덱스 만들기
CREATE NONCLUSTERED INDEX NCL_OrderDate_DueDate_ShipDate
ON dbo.SalesOrderHeader(OrderDate, DueDate, ShipDate)
--문제의 쿼리

SELECT SalesOrderID, Status, SalesOrderNumber, AccountNumber,
   CreditCardApprovalCode, TotalDue
FROM dbo.SalesOrderHeader
WHERE CONVERT(char(8), OrderDate, 112) = '20140602'
--해결 방안
SELECT SalesOrderID, Status, SalesOrderNumber, AccountNumber, CreditCardApprovalCode, TotalDue
FROM dbo.SalesOrderHeader
WHERE OrderDate = '20140602'
 

 문제의 쿼리를 보면, OrderDate 속성의 열 변경이 일어나 인덱스가 있음에도 불구하고 테이블 스캔이 일어납니다. 그래서 인덱스를 효율적으로 사용하지 못합니다.

 해결 방안은 열 변경을 일으키지 않게 하여 테이블 스캔을 막아야 합니다. 

 

2) 서로 비교되는 데이터 형식 일치시키기

-- 인덱스 만들기
CREATE NONCLUSTERED INDEX NCL_CreditCardApprovalCode
ON dbo.SalesOrderHeader(CreditCardApprovalCode)
--문제의 쿼리
SELECT SalesOrderID, Status, SalesOrderNumber, OrderDate, AccountNumber, TotalDue
FROM dbo.SalesOrderHeader
WHERE CreditCardApprovalCode = N'88031Vi9920'
--해결 방안
SELECT SalesOrderID, Status, SalesOrderNumber, OrderDate, AccountNumber, TotalDue
FROM dbo.SalesOrderHeader
WHERE CreditCardApprovalCode = '88031Vi9920'

 

 문제의 쿼리를 보면, CreditCardApprovalCode 속성이 varchar이지만, nvarchar 데이터 타입과 비교하여 인덱스 테이블의 모든 레코드를 nvarchar로 바꿔줘야하기 때문에 인덱스 스캔이 일어납니다. 그래서 인덱스를 효율적으로 사용하지 못합니다. 

 해결 방안은 비교하는 두 데이터 타입을 맞춰서 인덱스 스캔을 막아야 합니다. 

 

3) 불필요한 구문 삭제하기

-- 인덱스 만들기
CREATE NONCLUSTERED INDEX NCL_PurchaseOrderNumber
ON dbo.SalesOrderHeader(PurchaseOrderNumber)
--문제의 쿼리
SELECT SalesOrderID, Status, SalesOrderNumber, OrderDate, AccountNumber, TotalDue
FROM dbo.SalesOrderHeader
WHERE ISNULL(PurchaseOrderNumber, '') = 'PO3828168588'
--해결 방안
SELEC SalesOrderID, Status, SalesOrderNumber, OrderDate, AccountNumber, TotalDue
FROM dbo.SalesOrderHeader
WHERE PurchaseOrderNumber = 'PO3828168588'

 

 문제의 쿼리를 보면, CONVERT나 ISNULL함수를 인덱스가 있는 속성에게 사용하면 인덱스 스캔이 일어납니다. 그래서 인덱스를 효율적으로 사용하지 못합니다. 

 해결 방안은 불필요한 함수 사용을 자제하면서 인덱스 스캔을 막아야 합니다. 

 

4) 첫 번째 인덱스 키 열 고려하기

-- 인덱스 만들기
CREATE UNIQUE CLUSTERED INDEX CL_SalesOrderDetail
ON dbo.SalesOrderDetail(SalesOrderID, SalesOrderDetailID)
--문제의 쿼리
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, LineTotal
FROM dbo.SalesOrderDetail
WHERE SalesOrderDetailID = 887788
--해결 방안
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, LineTotal
FROM dbo.SalesOrderDetail
WHERE SalesOrderID = 333724 AND SalesOrderDetailID = 887788

 

 문제의 쿼리를 보면, WHERE절에 첫번째 인덱스 키 열을 사용하지 않아 인덱스 스캔이 일어납니다. 그래서 인덱스를 효율적으로 사용하지 못합니다. 

 해결 방안은 WHERE 절에 첫번째 인덱스 키 열을 포함시켜서 인덱스 스캔을 막아야 합니다. 

 

5) 필요한 열만 SELECT 절에 나열하기

-- 인덱스 만들기
CREATE UNIQUE CLUSTERED INDEX UCL_SalesOrderID
ON dbo.SalesOrderHeader(SalesOrderID)
GO
CREATE NONCLUSTERED INDEX NCL_OrderDate_DueDate_ShipDate
ON dbo.SalesOrderHeader(OrderDate, DueDate, ShipDate)
--문제의 쿼리
SELECT SalesOrderID, OrderDate, DueDate, ShipDate, Status
FROM dbo.SalesOrderHeader
WHERE OrderDate BETWEEN '2014-06-02' AND '2014-06-05'
--해결 방안
SELECT SalesOrderID, OrderDate, DueDate, ShipDate
FROM dbo.SalesOrderHeader
WHERE OrderDate BETWEEN '2014-06-02' AND '2014-06-05'

 

 문제의 쿼리를 보면, 인덱스 키 열 이외에 다른 속성도 SELECT하고 있어서 Index Seek가 일어나고 없는 속성을 찾으러 Key LookUP이 일어납니다. 그래서 인덱스를 효율적으로 사용하지 못합니다. 

 해결 방안은 인덱스에 있는 속성만 사용하면 Index Seek만 일어나서 성능 효율이 높아집니다.