TOP 1 이면 SEEK 해야 하나요? SCAN 해야 하나요?
REMARK 1 START
데이터 생성 스크립트
USE TEMPDB
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST
GO
CREATE TABLE TEST(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, T INT NOT NULL
, DUMMY CHAR(1000) NOT NULL DEFAULT('A')
)
GO
SET NOCOUNT ON
GO
DECLARE @I INT = 1
WHILE @I < 100000 -- 이 숫자만 효과를 극대화 하기 위해 크게 만들었음
BEGIN
INSERT INTO TEST(T) VALUES((@I%3) *2 )
SELECT @I = @I+1
END
GO
CREATE INDEX T ON TEST(T)
GO
아래 쿼리는 SEEK 할까? SCAN 할까?
SELECT TOP(1) * FROM TEST WHERE T = 4 -- 위 쿼리가 왜 SEEK 를 하지 않고 SCAN 하는가? 가 질문이다.
테이블 'TEST'. 검색 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
그런데, 위와같은 경우는 SCAN 이 실제로 유리하다. 왜냐하면, HISTOGRAM 에서 4가 존재할 확율을 구하게 되는데 4는 통계를 보면 다음과 같다.
전체 행수가 99999 에서 33333 개가 있다고 나와 있다. 그러니 첫번째 PAGE 에서 4를 만날 확율이 높을수 있다. 그러니 SQL 은 SEEK 하지 않고 SCAN 하게 된다. 행수가 제한되는것은 HISTOGRAM 에 있는 행의 수와 TOP 의 두 조건이 모두 동작한다. 자 이번에는 SEEK 하도록 해보겠다.
쿼리의 인자를 다음과 같이 바꾸어 보자
SELECT TOP(1) * FROM TEST WHERE T = 5
잘 SEEK 한것을 볼 수 있다. 왜냐하면, 전체 행수에서 5가 있을 확률은 없기 때문이다. 이 경우 SCAN 으로 찾을 확율은 전체 테이블을 모두 다 읽어야 찾을 수 있기 때문에 SEEK 해서 찾는다. 자 이번에는 통계를 보고 판단해도 비 합리적으로 동작하도록 해보겠다. 통계는 행의 순서정보를 압축을 통해 볼 수 없게되므로, 순서정보를 다르게 넣으면 SQL 통계기반 판단에서 더 비 효율적인 선택을 할 수 있는 기회가 커지게 된다. 만약 다음과 같은 데이터를 준비 한다면 통계를 이용한 옵티마이징의 문제를 좀 더 쉽게 모니터링 할 수 있다.
HISTOGRAM 은 같으나 문제가 될 수 있는 데이터 생성
SET STATISTICS IO OFF
SET NOCOUNT ON
GO
USE TEMPDB
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST
GO
CREATE TABLE TEST(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, T INT NOT NULL
, DUMMY CHAR(1000) NOT NULL DEFAULT('A')
)
GO
INSERT INTO TEST (T) VALUES (0);
GO 33333
INSERT INTO TEST (T) VALUES (2);
GO 33333
INSERT INTO TEST (T) VALUES (4);
GO 33333
CREATE INDEX T ON TEST(T)
GO
SET STATISTICS IO ON
GO
-- 아까와 동일 쿼리 실행
SELECT TOP(1) * FROM TEST WHERE T = 4
테이블 'TEST'. 검색 수 1, 논리적 읽기 수 9578, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
위 데이터도 역시 통계 데이터는 같다 99999 행 중33333 개가 있지만 024024024... 로 들어 있을지 000...222...444... 식으로 데이터가 들어 있는지 통계에서 알 수 없다. 이때는 앞에 있는것 처럼 잘 혼합되어 있다고 생각하는것 같다. 그러나, 데이터를 임으로 2번째 경우와 같이 넣게되면 SQL SERVER의 옵티마이저 기준에서는 효율적이다. 라고 선택한 SCAN 이 더 비효율적인 경우가 된다. 이때는 아래와 같이 힌트를 명시하는것이 좋다.
인덱스 힌트를 사용한 쿼리
SELECT TOP(1) * FROM TEST WITH(INDEX(T)) WHERE T = 4
테이블 'TEST'. 검색 수 1, 논리적 읽기 수 7, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
이렇게 설명하는게 더 좋지 않았을까 싶다. INDEX SEEK 가 좋다 SCAN 이 좋다. 가 아니라 통계의 한계와 데이터의 이해가 선행되어야 하지 않을까 싶다. 위 설명이 아마 강사가 설명중 "난 SEEK 하는게 더 빠른 경우를 본적 있어요 SEEK 하도록 해주세요" 라고 설문에 답한 시나리오의 재현이 아닐까 생각한다. 사실 이런 부분이 비즈니스에서 발견되면 일반적인 통계기반 옵티마이저 보다 데이터를 잘 알고 있는 인간이 힌트를 통해 성능을 고정시켜야 한다. 힌트가 필요한가? 필요하지 않은가? 만약 플랜이 캐시라도 된다면...
그런데, 강사가 이 부분을 HISTOGRAM 설명없이 SCAN SEEK 를 설명하고 있는데, 상당히 혼란스러웠다. 이걸 어떻게 HISTOGRAM 설명없이 설명 할 수 있는지 아직도 이해가 가지 않고 혹시 위 현상을 HISTOGRAM 없이 설명할 수 있다거나, 오류내용이 있다면 언제든지 누구든지 알려주길 바란다. 강의자는 실행계획상 SCAN 과 SEEK 의 비용을 비교해 보고 SCAN 이 싸기 때문에 비용기반 옵티마이저가 SCAN 한다라고 설명하고 있다. 여기서 왜 비용이 쌀까? 에 주목해야 한다. 통계에 중복도가 상당히 높기 때문에 SCAN으로 읽을때 첫 번째 페이지나 두 번째 페이지에서 원하는 4를 만날 확율이 매우 높다. SEEK 의 경우도 B-TREE 의 두 번째 페이지 + LOOKUP 의 두 번째 페이지 정도에서 데이터를 찾을 수 있으나, SEEK 연산자의 기본비용과 LOOKUP 의 기본기비용을 더하면 단 한단계에서 처리하는 SCAN 보다 비용이 비싸게 된다.
이것이 5를 넣어 비용을 비교하고 이것이 통계의 영향인지 TOP 의 영향인지를 설명한 의도이다. SEEK 와 SCAN 의 기본 이터레이터 비용 계산은 UnPlugged Level 400 병렬처리 세션을 보기 바란다. (발표 : 김민석)
REMARK 1 END