[MSSQL] 복합 인덱스

📌 준비하기

• 'northwind database' 를 활용하여 실습하였습니다.
• 아래의 링크에서 확인하거나 [여기]를 클릭하여 쿼리문을 볼 수 있습니다.

GitHub - microsoft/sql-server-samples: Azure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for

 

• 얻은 쿼리문을 mssql에서 실행하여 새로운 데이터베이스를 생성합니다.

 

• 임시 테이블을 생성하여 실습합니다.
• 복합 인덱스를 사용하기위해 복합인덱스를 추가합니다.
• 아래의 쿼리문을 실행합니다.

select * into TestOrderDetails
from [Order Details]

create index Index_TestOrderDetails
on TestOrderDetails(orderID, ProductID);

 

 

📌 복합인덱스

• Index Scan과 Index Seek의 차이점을 간단하게 알아두면 다음과 같습니다.

-- index scan > bad (index full scan!, 모든 요소를 하나씩 검색)
-- index seek > good (해당 요소로 바로 찾아감)

 

📌 인덱스 탐색

• 각 케이스별로 mssql이 어떻게 인덱스를 탐색하여 결과값을 나타내는지 확인합니다.

· case 1

-- case 1: search orderID with productID
select *
from testorderdetails
where orderid = 10248 and productid = 11;

• 복합인덱스를 선언한 순서대로 orderid, productid 두개를 조건으로 사용하여 검색하면 Index Seek으로 단번에 대상을 찾은것을 확인할 수 있습니다.
• 일반적인 복합 인덱스를 사용하는 방법으로 크게 문제없이 seek 검색을 하였습니다.

 

· case 2

-- case 2: productID with search orderID 
select *
from testorderdetails
where orderid = 10248 and productid = 11;

• 복합인덱스를 선언한 순서와 반대로 productid, orderid 두개를 조건으로 사용해도 검색하면 Index Seek으로 단번에 대상을 찾은것을 확인할 수 있습니다.

 

· case 3

-- case 3: search orderID only
select *
from testorderdetails
where orderid = 10248

• 복합인덱스를 사용하지만, 검색조건에서는 복합인덱스 중 첫번째 인덱스인 orderID만 사용하여 검색을 합니다.
• Index Seek를 사용한것을 볼 수 있으며, Rows to be Read는 3이 된것을 볼  수 있습니다.

 

· case 4

-- case 4: search productID only
select *
from testorderdetails
where productid = 11

• 복합인덱스를 사용하지만, 검색조건에서는 복합인덱스 중 두번째 인덱스인 productID만 사용하여 검색을 합니다.
• 다른 세가지 케이스와 달리 Index Scan을 사용한것을 볼 수 있으며 Rows to be Read가 '2155'개나 되는것을 볼 수 있습니다.

 

✅ 살펴보기

• case 4의 경우 Index Scan을 사용하는데, 간단히 그 이유를 알아봅시다.

 

• 아래 사진과같이 인덱스 정보를 조회하고, 인덱스 페이지를 조회합니다.
• 인덱스 페이지를 보면 인덱스 페이지에 정렬되는 순서는 OrderID가 먼저 정렬되고나서 ProductID가 정렬되는것을 볼 수 있습니다. 이것은 OrderID는 독립적으로 인덱스가 될 수 있지만, ProductID는 OrderID에 의해 1차로 정렬된 후 2차로 스스로 정렬됩니다.
• 결론적으로, ProductID는 OrderID에 의해 정렬되기때문에 독립적인 ProductID만 보았을때는 아무런 규칙성이 없어 Index Scan을 사용하는것 입니다.

 

👍 결론

• 복합 인덱스에서 모든 인덱스를 사용할때 순서는 상관 없습니다.
• 복합 인덱스에서 첫번째 인덱스만을 사용하여 검색을할때는 Index Seek를 사용하여 인덱스를 잘 활용하는것을 볼 수 있습니다.
• 하지만, 첫번째 인덱스가 아닌 하위 인덱스만을 사용할때에는 Index Seek이 아닌 Index Scan 방법을 사용하여 효율적이지 못한 검색을 보여줍니다.
• 하위 인덱스만을 사용하여 검색을 해야할경우, 또다른 인덱스를 하위인덱스에 설정해줘야 합니다.

 

📌 문자열 검색

• 같은 결과를 보여주는 두 쿼리문에서 내부적으로 검색 방법에 대한 차이가 있습니다.

 

• 'Bu'로 시작하는 문자열을 찾을때 like를 사용하여 검색을하면 Index Seek를 사용합니다.

 

• 'Bu'로 시작하는 문자열을 찾을때 결과는 같지만 Substring을 사용하여 검색을하면 Index Scan를 사용합니다.