1 상황

2 상황만들기

3 적절한 솔루션

4 결론

상황은 이렇다. test테이블에 3백만건이 있다. 하루에 5천건씩 데이터가 쌓인다. 그러므로 하루치 데이터를 접근하려면 5천건을 읽어야 한다. 다음날은 전날 하루치에 데이터는 액세스가 매우 빈번하게 일어난다. 인덱스는 다음과 같다.

• seq -> clustered index
  
• dt -> non-clustered index
  
  

우리의 개발자는 다음과 같이 처리하였다. 테스트에는 2초 이내로 모든 결과를 뿌려줄 수 있었다. 하지만 실제 서비스 환경에서 빈번하게 액세스하다보니 서버에 많은 부하를 주게 되었다. CPU는 80%까지 올라갔다.

--2~3회 실행한다. 왜? 메모리에 데이터가 올라와져 있다는 가정을 하기 위해..
set statistics io on
set nocount on
declare @dt char(8)
set @dt = '20080115'

select *
from test 
where dt = @dt
--테이블 'test'. 검색 수 1, 논리적 읽기 수 15589, 물리적 읽기 수 32, 미리 읽기 수 4

결과를 모두 뿌리는데 1초 이내. 실행 계획을 보면 non-clustered index seek를 한 것을 알 수 있다. 하지만 I/O가 매우 많다. 하지만 인덱스나 다른 변경은 할 수 없는 상황이다. 어떻게 해결할까? (고민해 보고 적절한 솔루션 부분을 참고한다.)

if object_id('test') is not null
	drop table test;
go

with temp1(num)
as
(
        select 1 num
        union all
        select num + 1 from temp1
        where num + 1 <= 5000
),
temp2(num)
as
(
        select 1 num
        union all
        select num + 1 from temp2
        where num + 1 <= 600
)
select 
	identity(int,1,1) seq
,	convert(char(8), getdate() + b.num, 112) dt
,	left(newid(), 12) dumy1-- 데이터를 채워넣기 위함
,	newid() dumy2-- 데이터를 채워넣기 위함
into test
from temp1 a, temp2 b
option (maxrecursion 0);

create clustered index cix_seq
on test(seq);
--테이블 'test'. 검색 수 1, 논리적 읽기 수 19738, 물리적 읽기 수 0, 미리 읽기 수 906

create index nix_dt
on test(dt);
--테이블 'test'. 검색 수 1, 논리적 읽기 수 20443, 물리적 읽기 수 0, 미리 읽기 수 3953

--논클러스터드 인덱스는 구조상 랜덤액세스가 발생하고, 범위검색에 유리하지 않음
--클러스터드 인덱스를 사용하도록 유도한다. 

--2~3회 실행한다. 왜? 메모리에 데이터가 올라와져 있다는 가정을 하기 위해..
set statistics io on
set nocount on
declare 
	@dt char(8)
,	@min_seq int
,	@max_seq int
set @dt = '20080115'

select 
	@min_seq = min(seq)
,	@max_seq = max(seq)
from test 
where dt = @dt

select * from test
where seq between @min_seq and @max_seq
--테이블 'test'. 검색 수 2, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0
--테이블 'test'. 검색 수 1, 논리적 읽기 수 38, 물리적 읽기 수 0, 미리 읽기 수 0
--select 15589/(6+38) --354

I/O만 무려 354배나 향상되었다. 단순히 I/O만 보았을 때가 그렇다. 매우 빈번하게 액세스가 일어나는 환경이라면 성능향상의 효과는 정말 환상적일 것이다. 이 문제는 min(), max()가 mssql server에서 어떻게 동작하는 알고 있고, clustered index와 non-clustered index의 구조와 원리에 대해서 알고 있어야만 해결 할 수 있다. (소가 뒷걸음질 치다가 쥐 잡을 수도 있기는 하다.)

mssql server는 min(), max()의 대상이 되는 컬럼에 인덱스가 있다면 적절히 앞/뒤로 읽는다. 만약 dt 컬럼에 인덱스가 desc로 생성되어 있다면 min()은 인덱스의 맨 끝에서 1건을 읽고 읽기를 멈춘다.(실행계획을 보면 top을 볼 수 있다.) 마찬가지 원리로 max()는 인덱스의 맨 처음에서 1건을 읽고 읽기를 멈춘다. 그러므로 아주 적은 비용으로 변수에 입력한 날짜(여기에서는 '20080115')에 seq의 범위를 알 수 있다. seq는 clustered index가 잡혀 있으므로 아주 적은 비용으로 처리 할 수 있게 된다.

사실 이 문제는 적절한 인덱스 설계를 하지 않은 것은 근본적인 원인이다. 아마도 아무런 생각없이 또는 unique라는 기본키의 기본속성을 만족시키기 위해서 일련번호(identity(1,1))를 생성하고, 아무 생각없이 clustered index를 생성했을 것이다. 앞에서 '인덱스 설계를 변경하지 못하는 환경'이라는 조건을 달았는데, 조금 어거지 같지만 실제로 서비스되는 환경이라면 DBMS 종류나 버전에 따라서 서비스를 잠시 내리지 않는 한은 인덱스 설계 변경을 할 수 없는 환경이 되기도 한다. 역시 문제는 설계문제!!