SQL (21) - Index Scan vs Index Seek

이번 포스트에서는 인덱스의 동작 방식인 Scan과 Seek에 대해서 알아보겠습니다.

두 개의 동작방식을 간단하게 얘기하면 다음과 같이 얘기할 수 있습니다.

 

Scan의 경우, 인덱스를 처음부터 끝까지 순회하면서 데이터를 찾는 동작방식입니다.

Seek에 비해 비효율적이지만, 적은 양의 데이터에서는 높은 효율을 가질 수 있습니다.

 

Seek는 인덱스를 통해 필요한 데이터를 빠르게 찾는 동작방식입니다.

일반적으로 Scan에 비해 효율적이지만, 상황에 따라 Scan보다 효율이 떨어질 수 있습니다.

그러나 많은 양의 데이터에서 높은 효율을 가집니다.

 

이제 예제를 통해 Index의 Scan과 Seek의 동작 방식을 알아보겠습니다.

 

1) 예제

USE Northwind;

-- 임시 테이블
CREATE TABLE TestAccess
(
	id INT NOT NULL,
	name NCHAR(50) NOT NULL,
	dummy NCHAR(1000) NULL
);
GO

-- Clustered 인덱스 생성
CREATE CLUSTERED INDEX TestAccess_CI
ON TestAccess(id);

-- Non-Clustered 인덱스 생성
CREATE NONCLUSTERED INDEX TestAccess_NCI
ON TestAccess(name);

DECLARE @i INT;
SET @i = 1;

-- 1부터 500까지의 TestAccess 테이블의 데이터를 만들고 
-- id는 1 ~ 500 / name은 Name1 ~ Name500 / dummy는 Hello World1 ~ Hello World500 
-- 위와 같은 데이터를 가짐
WHILE(@i <= 500)
BEGIN
	INSERT INTO TestAccess
	VALUES (@i, 'Name' + CONVERT(VARCHAR, @i), 'Hello World' + CONVERT(VARCHAR, @i));
	SET @i = @i + 1;
END

-- 인덱스 정보
EXEC sp_helpindex 'TestAccess';

-- 인덱스 번호
SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('TestAccess');

-- 인덱스 조회
DBCC IND('Northwind', 'TestAccess', 1);
DBCC IND('Northwind', 'TestAccess', 2);

 

위와 같이 임시 테이블과 인덱스 2개를 생성하였습니다.

인덱스 정보는 다음과 같습니다.

 

인덱스 번호는 다음과 같습니다.

 

각 인덱스의 조회결과는 다음과 같습니다.

 

이제 위의 예제에서 인덱스의 동작방식을 알아보겠습니다.

이를 위해 쿼리를 추가하겠습니다.

-- 쿼리의 작업정보를 알려주는 함수
-- 이어지는 순서대로 각 문을 구문 분석, 컴파일 및 실행하는 데 필요한 시간(밀리초)을 표시 
SET STATISTICS TIME ON;
-- 물리적 및 논리적 IO 작업 양에 대한 정보 표시
SET STATISTICS IO ON;

 

위 쿼리를 실행한 후 이후의 쿼리에서는 메세지 창에서 정보를 확인할 수 있습니다.

 

1 - 1) 조건없는 검색

SELECT *
FROM TestAccess;

 

위 쿼리의 경우 다음과 결과가 나왔습니다.

 

 

 

Clustered Index Scan이 나오는 것을 보면 현재 인덱스 Scan으로 동작하는 것을 알 수 있습니다.

또한, 논리적 읽기가 169개가 찍힌 것으로 보아 이진 트리를 전부 순회하였음을 알 수 있습니다.

 

1 - 2) 조건있는 검색(Clustered)

SELECT *
FROM TestAccess
WHERE id = 104;

 

이번에는 조건을 걸어 검색하되, Clustered Index를 조건에 사용하여 검색해보겠습니다.

위 쿼리의 경우 다음과 같은 결과를 얻을 수 있었습니다.

 

 

 

쿼리가 Index Scan으로 동작하였음을 알 수 있습니다.

논리적 읽기가 2번 발생한 이유는 현재의 Clustered Index 방식을 사용하므로

루트 노드에서 id를 통해 질의하여 데이터를 바로 찾아오기 때문입니다.

 

1 - 3) 조건있는 검색(Non-Clustered)

SELECT *
FROM TestAccess
WHERE name = 'name5';

 

이번에도 조건을 걸어 검색하되, 이번에는 Non-Clustered 인덱스를 통해 검색해보겠습니다.

다음과 같은 결과를 얻을 수 있습니다.

 

 

쿼리의 동작 방식은 Index Seek + Key LookUp으로 동작하는 것을 확인할 수 있습니다.

논리적 읽기가 4번으로 나타나는 이유는

루트 노드에서 name에 해당하는 id값을 통해 / 질의하여 해당 노드로 이동 /

해당 노드에서 name에 해당하는 값을 통해 / 질의하여 name에 맞는 데이터를 찾아오는

4가지로 구성된 과정을 거치기 때문입니다.

 

여기서 앞의 2가지 과정이 Index Seek이고,

뒤의 2가지 과정이 Key LookUp입니다.

 

1 - 4) 정렬하여 검색(Non-Clustered)

SELECT TOP 5 *
FROM TestAccess
ORDER BY name;

 

마지막으로 검색을 하되 Non-Clustered 인덱스로 정렬하여 상위 5개를 가져오는 방식을 사용해보겠습니다.

쿼리로부터 얻은 결과는 다음과 같습니다.

 

 

 

쿼리는 Index Scan + Key Look Up의 방식으로 동작하는 것을 확인할 수 있습니다.

이때 논리적 읽기가 17번 발생했다는 것을 볼 수 있는데,

위와 같이 적은 작업량의 Index Scan이 발생한 이유는 

Order By의 대상이 Non-Clustered된 Index를 사용하기 때문입니다.

 

위 쿼리는 Non-Clustere된 이진 트리에서 자식 노드의 최상위 5개만 가져오면 되기 때문에  
데이터를 가져오기 위해 부모 노드부터 자식 노드까지 2번 왕복합니다.
따라서 연산식의 경우 가져오는 데이터의 개수 * 2 + n의 수치라고 생각하면 됩니다.

 

그러므로 적은 양의 논리적 읽기가 발생합니다.

그리고 위와 같은 어느 정도 의도된 Index Scan의 경우는 충분히 감안해도 되는 연산입니다.