1 Clustered Index Seek가 정말 니가 원하는 Seek인가?

2 Clustered Index가 Non-Clustered Index보다 빠르다?

3 페이지(최소 입/출력 단위)의 효율적인 사용 방법

4 update

5 DESC? ASC?

실행계획에 눈을 뜨기 시작하면 Seek, Scan이 인덱스를 사용하여 원하는 결과셋을 가져왔는지 보게 된다. 그래도 이제 눈을 뜬 초보는 Clustered Index Scan이라고 나오면 인덱스가 잘 쓰이는 줄 안다. (물론 때에 따라서 잘 쓰인 경우도 있다.) 조금 시간이 지나면 Clustered Index Seek를 했다고 좋아라 한다. 하지만 중요한 것을 놓쳤다. 바로 원하는 만큼만을 조회했는지 아니면 전체를 조회했는지 신경쓰지 않는다는 것이다. 이부분은 심지어 2~3년차 DB쟁이들도 잘 모르는 경우가 있으므로 잘 봐두기 바란다. 다음과 같은 경우가 그렇다.

--준비
WITH temp(num)
AS
(
        SELECT 0 num
        UNION ALL
        SELECT num + 1 FROM temp
        WHERE num + 1 <= 10000
)
SELECT  
	num
,	newid() guid_col
,	REPLICATE('0', 5000) chr_data
INTO #temp
FROM temp
OPTION (MAXRECURSION 0);

CREATE CLUSTERED INDEX cix
ON #temp(num);

--실행
SET STATISTICS IO ON
SELECT * FROM #temp
WHERE num > 0;

우리의 개발자는 마우스를 그래픽 실행계획에 가져다 대고 Clustered Index Seek라고 좋아할 것이다. 하지만 이게 진짜 당신이 원하는 Clustered Index Seek인가? 아니다. 이건 Clustered Index Scan과 같은 것이다. SET STATISTICS IO ON을 했다면 I/O을 볼 수 있을 것이다. 필자의 경우 다음과 같다.

/*
SET STATISTICS IO ON
SELECT * FROM #temp
WHERE num > 0;
--Scan count 1, logical reads 10023, physical reads 0, read-ahead reads 6
*/

SELECT * FROM #temp
--Scan count 1, logical reads 10023, physical reads 0, read-ahead reads 6

보다시피 I/O의 양이 똑같다. 비용을 비교해보면 다음과 같이 Clustered Index Seek와 Clustered Index Scan이 동일하다는 것을 알 수 있다. 그러므로 여러분은 Clustered Index Seek를 했다고 하여 안심하면 안 된다. 반드시 I/O의 양도 봐야 한다.

SELECT * FROM #temp
WHERE num > 0

SELECT * FROM #temp;

또 한가지 주의해야 할 사항은 이렇게 2개 이상의 SQL문을 실행할 때에 실행계획이 모두 나타나면 그때서야 실행계획 탭으로 마우스를 클릭하여 옮겨가야 정상적인 실행계획을 볼 수 있다는 것이다. 모든 SQL에 대한 실행계획이 생성되기 전에 실행계획 탭으로 옮기면 다음과 같이 전체 실행계획이 150%가 되는 일종의 버그를 볼 수 있다. 참고로 필자는 SQL Server 2008버전이다.

물론 이런 현상은 GUI자체의 문제다. 탭을 옮겼다가 다시 실행계획탭으로 가거나 최소화 후 최대화하면 화면이 갱신되는데, 어쨌든 바로 갱신되지 않으므로 주의해야 한다. 별거 아니다.

MS-SQL Server나 Oracle Server와 같은 DBMS는 최소 입/출력 단위를 가진다. MS-SQL Server7.0버전 이상부터는 8KB의 페이지가 최소 입/출력 단위이다. 또한 페이지 헤더를 빼면 한 페이지에는 8060 Byte까지만 저장된다. 그러므로 위의 예제로 만들었던 테이블의 1Row는 5000 Byte가 넘으므로 1페이지당 1Row만 저장될 수 있다. 또한 약 3000 Byte는 사용하지 못하는 비효율적인 상황이 발생한다. 이런 경우 Clustered Index를 만들면 100Row를 접근하는데 적어도 100페이지 이상을 읽어야 하는 현상이 발생한다.

SELECT num FROM #temp
WHERE num BETWEEN 1 AND 100
--Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0

만약 num컬럼에 Non-Clustered Index를 생성하면, SQL Server는 위의 문제에서는 당연히 Non-Clustered Index를 선택하여 읽을 것이다. 왜냐하면 당연히 비용이 적게 들기 때문이다. Non-Clustered Index를 생성하면 몇 Page나 읽을까?

create index nix
on #temp(num);

SELECT num FROM #temp
WHERE num BETWEEN 1 AND 100
--Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0

꼴랑 2페이지 읽었다. 어떤 문서에는 무턱대고 Non-Clusterd Index보다 Clustered Index가 빠르다고 명시해 놓은 것도 있다. 혼동하기 쉽상이다. 사실 이런 종류의 쿼리는 Covered Index를 사용한 것인데, 많은 문서에서 결합 인덱스를 이야기 해 놓는 바람에 혼란을 준 것이다. 만약 Clustered Index만 있는 상황에서 위와 같은 패턴의 쿼리가 아주 빈번하게 사용되고 있다면 Clustered Index와 Non-Clustered Index는 공존해야 한다. 이런 경우는 극단적인 경우이지만 같은 컬럼에 대해 2종류의 인덱스가 생성된 상황에서 대부분은 Non-Clustered Index를 삭제하지만 이런 상황도 있다는 것을 고려[1]해야 한다.

위에서 우리는 테이블의 설계에 따라서 저장 공간을 효율적으로 사용하지 못하는 상황을 살펴보았다. char_data가 xml문서라고 가정하고, 5000 Byte라고 가정하자. 또한 num컬럼은 Primary Key(이하 PK)라고 가정하자. 만약 조건으로 num 또는 guid_col이 범위 검색되고, 그 중에 하나의 xml문서만 골라보는 사용 패턴[2]을 보인다면 어떻게 해야만 효율적인 사용을 할 수 있을까? 솔루션은 '수직분할'이다. 즉, A{num, guid_col}, B{num, char_data}로 2개의 셋(테이블)을 구성하는 것이다. 그럼 검색을 할 때는 A테이블을 이용하고, 선택된 데이터는 B에서 1개씩 사용되면 되는 것이다.

{* 대략 이런 패턴이다.}

그렇다면 '일반적인 상황에서 저장공간을 효율적으로 사용하고, 성능을 끌어 올릴 수 있는 방법은 없을까' 하고 궁금증을 가질 수도 있을 것이다. 아주 좋은 궁금증이다. 왜 방법이 없겠는가? 그 방법은 바로 '정규화'다. 그래서 정규화가 최강의 성능튜닝 도구이자 최고의 무결성 확보의 도구라고 하는 것이다. 정규화는 DBMS의 최소 입/출력 단위에 오밀조밀 데이터가 모여 있게끔 해준다. 그러므로 저장공간의 낭비를 막고, 성능을 향상시켜주면서 무결성까지 확보하는 데이터베이스의 최강의 도구다. 최강의 도구다. 최고의 도구다. 좆빠진다. 쩌러주신다. 원츄! 따봉! 또 뭐있지? 암턴.. 정규화는 (--)b

--clustered index update test

drop table test
create table test(seq int identity primary key, id varchar(8000))
go

set statistics io off
set nocount on 

insert test(id) values(replicate('a',3000))
go 10000


set statistics io on
set nocount on 

select * from test
--테이블 'test'. 검색 수 1, 논리적 읽기 수 5020, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.


select * from test
where seq = 100
--테이블 'test'. 검색 수 0, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.


update test
set id = replicate('a',6000)
where seq = 100

select * from test
--테이블 'test'. 검색 수 1, 논리적 읽기 수 5021, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.


select * from test
where seq = 100
--테이블 'test'. 검색 수 0, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

--varchar(500)으로 하고 fill_factor 조정도 해보자..
drop table test2
create table test2(
    seq int identity
,   id varchar(500)
,   constraint pk_test2 primary key clustered(seq) with(fillfactor=90)
)
go

alter table test2
drop constraint pk_test2

alter table test2
add constraint pk_test2 primary key clustered(seq)
with(fillfactor = 90)

클러스터 인덱스 생성시 DESC로 하면.... 오프셋 꺼꾸로...
아 귀찮어..