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이라고 꼭 모두 나쁜것은 아니다.