Index Scan vs Index Seek

1. 인덱스 성능

Index Scan이 뜨는 경우 테이블의 처음부터 다 비교해보면서 데이터를 찾기 때문에 성능이 안좋다.

Index Seek가 뜨는 경우는 인덱스를 통해 효율적으로 데이터를 찾기 때문에 성능이 좋다.

그러나 꼭 Index Scan이 뜬다고 해서 마냥 나쁜 것은 아니다. 상황에 따라 Index Scan이 떠도 성능이 좋은 경우가 있다.

 

2. 분석

USE Northwind;

-- 인덱스 접근 방식
-- Index Scan VS Index Seek

CREATE TABLE TestAccess
(	
	id INT NOT NULL,
	name NCHAR(50) NOT NULL,
	dummy NCHAR(1000) NOT NULL
);
GO

CREATE CLUSTERED INDEX TestAccess_CI
ON TestAccess(id);
GO

CREATE NONCLUSTERED INDEX TestAccess_NCI
ON TestAccess(name);
GO

DECLARE @i INT;
SET @i = 1;

WHILE( @i < 500 )
BEGIN
	INSERT INTO TestAccess
	VALUES(@i, 'Name' + CONVERT(VARCHAR, @i), 'Hello World ' + CONVERT(VARCHAR, @i));
	SET @i = @i + 1;
END
GO

-- 인덱스 정보
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);

먼저 테이블을 생성하고, 테이블에 데이터를 500개를 반복문을 통해 넣어주었으며, Clustered Index와 Non-Clustered Index를 하나씩 생성하였다.

 

인덱스들의 내용은 다음과 같다.

Clustered Index(1) : id

            864
 832 865 872 ~ 1031 (0레벨은 167개)

NON-CLUSTERED(2) : Name
                                  841
840 845 846 847 976 977 979 978 844 980 981 843 842 (0레벨은 13개) 

 

SET STATISTICS TIME ON; -- 실행 시간 알려줌
SET STATISTICS IO ON; -- 논리적 읽기를 알려줌

논리적 읽기란 실제 데이터를 찾기 위해 읽은 페이지 수이다.

읽은 페이지 수가 적을수록 탐색 시간이 빠르다.

위의 명령어를 통해 논리적 읽기와 실행 시간을 볼 수 있다.

 

CASE1) INDEX SCAN

SELECT *
FROM TestAccess;

논리적 읽기 169

경과 시간 59ms

대부분의 SCAN처럼 속도가 경과 시간 59ms로 느리다.

 

CASE2) INDEX SEEK

SELECT *
FROM TestAccess
WHERE id = 104;

논리적 읽기 2

경과시간 0ms

 

CASE3) INDEX SEEK + KEY LOOKUP

SELECT *
FROM TestAccess
WHERE name = 'Name5'

 

논리적 읽기 4

경과시간 0ms

논리적 읽기가 4번인 이유가 논클러스티드 2번 -> 클러스티드 2번의 과정이기 때문에 2번+2번=4번이 된다

 

CASE4)  INDEX SCAN + KEY LOOKUP

SELECT TOP 5 *
FROM TestAccess
ORDER BY name; -- 데이터가 정렬 되어있고 5개만 추출하면되어서 SCAN임에도 불구하고 매우 빠른속도

논리적 읽기 16

경과 시간 0ms

 

대부분의 경우 SCAN이 느리고 SEEK가 빠르지만 CASE4같은 경우에는 SCAN임에도 불구하고 경과 시간이 0ms이다.

그 이유는 name인덱스가 ORDER BY로 인해 정렬되어 있고 TOP 5로 인해서 5개의 데이터만 찾으면 되기 때문에 속도가 빠르다.

 

이처럼 SCAN이라고 꼭 모두 나쁜것은 아니다.