|
안녕하세요. 김대우 입니다. 더운 여름 잘 지내고 계신지요?
이번에 소개해 드릴 내용은 김연욱님이 이끌어 주시는 Inside Query Performance 입니다.
아주 좋은 SQL 쿼리 성능에 대한 연재글을 올려 주고 계시며 SQLER에 많은 도움을 주시는 분이지요.
이하 내용은 김연욱님이 SQLER 자유게시판에 올려주신 항목을 정리한 내용입니다.
언제나 많은 도움 주시는 김연욱님 감사 드리구요. 앞으로도 좋은... 더운 여름 더더욱 열나게 하는 ^_^;;;
좋은 글 부탁 드립니다. 감사합니다.
이하 김연욱님의 글입니다.
|
일반적으로 이력관리 모델에서는 시작일자와 종료일자컬럼을 가진다.
--> 이부분에 대한것은 워낙에 내용이 길기때문에 생략.
--> 뭐 그냥 시작일자나 종료일자 하나만 가지는 설계를 사용하신다면 할 말 없음
이력모델에서 시작일자 + 종료일자 인덱스를 사용하는것이 좋을지
아니면 종료일자 + 시작일자 인덱스를 사용하는것이 좋을지
한번 테스트 해보자.
|
선행지식을 위해 between을 생각해보자.
일반적으로 우리가 사용하는 between은 컬럼 between 상수값1 and 상수값2 의 형태이다.
하지만, 조금 변형하여 상수값 between 컬럼1 and 컬럼2 의 형태로도 사용이 가능하다.
간단하게 예를 들어보면
벙개를 위한 장소를 몰색하던 00군이 인터넷으로 벙개용 좌석예약을 할려구 한다.
|
--drop table 좌석예약
create table 좌석예약(좌석등급 char(1), 시작번호 int, 종료번호 int, 좌석수 int, 구분 varchar(10))
go
insert into 좌석예약 values('A', 1, 100, 100, '공석')
insert into 좌석예약 values('B', 1, 100, 100, '공석')
go
|
마침 00주점에 이러한 시스템이 있었다.
사무실에서 pc로 단가가 적은 B석 51번부터 10개의 좌석을 예약한다고 해보자.
이럴때 어떻게 쿼리해야할까 ?
|
--1. 좌석상태확인
select * from 좌석예약
where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
and 51 between 시작번호 and 종료번호
좌석 등급 시작번호 종료번호 좌석수 구분
---- ----------- ----------- ----------- ----------
B 1 100 100 빈자리
(1개 행 적용됨) |
이제 between 사용에 감이 잡히겠지요.
기왕 하는김에 좌석예약을 해보자.
|
--2. 좌석예약
set nocount on
declare @좌석등급 char(1), @좌석수 int, @시작번호 int, @종료번호 int, @구분 varchar(10)
declare @좌석위치 int
set @좌석등급 = 'B'
set @좌석수 = 10
set @좌석위치 = 51
set @구분 = 'SQLER 벙개'
set xact_abort on
begin tran
if exists(select * from 좌석예약 where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
and @좌석위치 between 시작번호 and 종료번호)
begin
update 좌석예약
set 시작번호 = @좌석위치,
종료번호 = @좌석위치 + @좌석수 - 1,
좌석수 = @좌석수,
구분 = @구분,
@시작번호 = 시작번호,
@종료번호 = 종료번호
where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
and @좌석위치 between 시작번호 and 종료번호
if @시작번호 < @좌석위치
begin
insert into 좌석예약 values (@좌석등급, @시작번호, @좌석위치-1,
@좌석위치-@시작번호, '빈자리')
end
if @종료번호 > (@좌석위치 + @좌석수)
begin
insert into 좌석예약 values (@좌석등급, @좌석위치+@좌석수, @종료번호,
@종료번호-@좌석위치-@좌석수, '빈자리')
end
end
commit tran
set nocount off
go
|
예약을 잘되었는지 확인해보면...
|
select * from 좌석예약 where 좌석등급 = 'B' order by 시작번호
좌석 등급 시작번호 종료번호 좌석수 구분
---- ----------- ----------- ----------- ----------
B 1 50 50 빈자리
B 51 60 10 SQLER 벙개
B 61 100 39 빈자리
(3개 행 적용됨) |
51번부터 60번까지 예약이 잘되있다... 근데 난 언제나 벙개에 함 나갈볼런지...
|
/*
선행지식
1. 재미없는 긴글을 끝까지 읽어줄 강인한 인내력 (필수)
2. sql서버는 복합컬럼인덱스일경우 첫번째 컬럼의 통계정보만을 사용함. (중요)
3. 인덱스에 대한 기본지식
4. between에 대한 기본지식
일반적으로 between을 이용할경우는 변수 between 시작값 and 종료값 의 형식으로
사용하지만 상수값 between 시작변수 and 종료변수 와 같이 사용할 수 도 있다.
(좀더 자세한 내용은 관련 서적 참고)
해당인덱스를 clustered index 로 할것인지 nonclustered index 로 할것인지는 별개의
문제이므로 논외로 합니다.
*/
--언제나 테스트는 tempdb에서
use tempdb
go
--역시나 테스트를 위해서 테이블 하나 만들구
--drop table a
create table a(edt int not null, sdt int not null, num int identity)
go
--테스트를 편하게 하고자 날짜형 대신에 숫자형으로 테스트
/* ========================================================================== */
--켜져있으면 끄놓구
set statistics io off
set statistics profile off
--테스트용 샘플 넣구
set nocount on
declare @i int
set @i = 0
while (@i < 100000)
begin
insert a values(@i+1, @i)
set @i = @i + 2
end
set nocount off
select count(*) from a --50000건
--테스트를 위해 켜두고
set statistics io on
set statistics profile on
go
|
|
--아무런 제약조건, 인덱스 없는 상태에서 그냥
select num from a
|--Table Scan(OBJECT:([tempdb].[dbo].[A]))
--논리적 읽기 수 137 (전체 페이지수가 137page임)
--먼저 시작일 + 종료일자로 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(sdt, edt)
--저 앞에 있는놈을 보면
select num from a where 90 between sdt and edt
|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
-- 논리적 읽기 수 137
-- 저 뒤에 있는 놈을 보면
select num from a where 90000 between sdt and edt
|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
-- 강제로 pk를 타라고 힌트를 주면 어떨까 ?
select num from a (index=pk_a) where 90000 between sdt and edt
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90000),
WHERE:(90000<=[A].[EDT])
--논리적 읽기 수 108
--그럼, top 1 을 주고 인덱스를 타라고 힌트를 주면 어떨까 ?
select top 1 num from a (index=pk_a) where 90000 between sdt and edt
|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90000),
WHERE:(9000<=[A].[EDT]) ORDERED FORWARD)
--논리적 읽기 수 108
--일단, pk 지우고
alter table a drop constraint pk_a
--이번에는 종료일 + 시작일루 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(edt, sdt)
--저 앞에 있는놈을 보면
select num from a where 90 between sdt and edt
|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
--그럼, pk를 타라구 힌트를 주면
select num from a (index(pk_a))
where 90 between sdt and edt
select num from a (index(pk_a)) where 90 between sdt and edt
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90),
WHERE:(90>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 120
--요번에는 top 1 까지 주면
select top 1 num from a (index(pk_a))
where 90 between sdt and edt
|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90),
WHERE:(90>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 3
-- 요번엔 저뒤에 있는놈을 보면
select num from a where 90000 between sdt and edt
|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
--이번에두 힌트사용
select num from a (index(pk_a)) where 90000 between sdt and edt
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000),
WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 15
--그럼, top 1 을 주면
select top 1 num from a (index(pk_a)) where 90000 between sdt and edt
|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000),
WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 3
-- 통계정보를 만들어 주면 좀더 낳지 않을까(역시 동일한 결과) - 각자 해보세요...
CREATE STATISTICS ust_a_edt
ON test..a (edt)
WITH FULLSCAN
CREATE STATISTICS ust_a_sdt
ON test..a (sdt)
WITH FULLSCAN
UPDATE STATISTICS a(pk_a)
WITH SAMPLE 100 PERCENT
|
결론 : sql서버에서 --> 상수값 between 시작값 and 종료값 조인을 할경우
1. 시작일 + 종료일 인덱스일경우
1) 시작일만을 사용하므로 해당조건의 선택도가 항상동일함에도 불구하고,
시작일 <= 상수값 의 조건만을 활용한다.
|--Index Seek(OBJECT:([test].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90),
WHERE:(90<=[A].[EDT]) ORDERED FORWARD)
2) 시작일 <= 상수값의 조건으로 인덱스를 스캔하면서 종료일 >= 상수값의 조건으로
필터처리한다.(무식한놈)
2. 종료일 + 시작일 인덱스일경우
1) 종료일만을 사용하므로 해당조건의 선택도가 항상동일함에도 불구하고,
종료일 >= 상수값 의 조건만을 활용한다.
|--Index Seek(OBJECT:([test].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000),
WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
2) 종료일 >= 상수값의 조건으로 인덱스를 스캔하면서 시작일 <= 상수값의 조건으로
필터처리한다.(무식한놈)
3. 결론
1) 종료일 + 시작일 순으로 인덱스를 만들고나서
2) SELECT 절에 TOP 1 을 추가하고
3) INDEX(인덱스명) 힌트를 사용하면 항상 동일한 결과를 나타낸다.
※ 시작일 + 종료일 순으로 인덱스를 만들면 위와같이(1번에서 3번까지) 해도 비효율적인 스캔을 한다.
누군가가 딴지를 걸어주기를 기다렸는데 아무두 안걸어주어서 내가 딴지건다.
딴지) 뭐땜시, 복잡하게 종료일자 + 시작일자로 인덱스를 만드나, 그냥 시작일자 하나만 있어두 되던데...
|
--켜져있으면 끄놓구
set statistics io off
set statistics profile off
go
--일단, pk 지우고
alter table a
drop constraint pk_a
--이번에는 종료일 + 시작일루 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(sdt)
--테스트를 위해 다시 켜두고
set statistics io on
set statistics profile on
go
select top 1 num from a where sdt <= 90 order by sdt desc
|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
SEEK:([a].[sdt] <= 90) ORDERED BACKWARD)
--논리적 읽기 수 3
select top 1 num from a where sdt <= 9000 order by sdt desc
|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
SEEK:([a].[sdt] <= 9000) ORDERED BACKWARD)
--논리적 읽기 수 3
|
두경우 모두 논리적 읽기 수가 3이다. 이것이 더 좋지 않느냐, 쿼리도 깔끔하구..
그럼, sample을 조금 바꿔서 다른경우를 살펴보자.
|
--켜져있으면 끄놓구
set statistics io off
set statistics profile off
--drop table k1
create table k1(상품코드 int not null, 상품명 varchar(100))
go
alter table k1
add constraint pk_k1 primary key nonclustered(상품코드)
go
set nocount on
declare @i int
set @i = 1
while(@i <= 1000)
begin
insert into k1 values(@i, 'HDD ' +convert(varchar(10),@i)+'G')
set @i = @i + 1
end
set nocount off
go
--drop table k2
create table k2(적용일자 datetime not null, 종료일자 datetime not null, 단가 int)
go
set nocount on
declare @i datetime
set @i = '2000-01-01'
while(@i <= '2003-04-30')
begin
insert into k2 values(@i, dateadd(dd,1,@i), convert(int, rand()*1000))
set @i = dateadd(dd, 1, @i)
end
set nocount off
--drop table k3
create table k3(상품코드 int not null, 적용일자 datetime not null,
종료일자 datetime not null, 단가 int)
go
alter table k3
add constraint pk_k3 primary key nonclustered(상품코드, 적용일자)
go
set nocount on
insert into k3
select k1.상품코드, k2.적용일자, k2.종료일자, k2.단가
from k1 cross join k2
set nocount off
--drop table k4
create table k4(상품코드 int not null, 적용일자 datetime not null,
종료일자 datetime not null, 단가 int)
go
alter table k4
add constraint pk_k4 primary key nonclustered(상품코드, 종료일자, 적용일자)
go
set nocount on
insert into k4
select k1.상품코드, k2.적용일자, k2.종료일자, k2.단가
from k1 cross join k2
set nocount off
|
|
--다시 테스트를 위하여 켜놓구
set statistics io on
set statistics profile on
go
select count(*) from k3 --1216000
'k3' 테이블. 스캔 수 1, 논리적 읽기 수 3458
select count(*) from k4 --1216000
'k4' 테이블. 스캔 수 1, 논리적 읽기 수 4662
|
인덱스size 차이로 인하여 k4가 page수가 더 많다.
|
--1. 1번 상품의 '2003-03'월 단가변동내역이 필요하다면 ?
select 적용일자, 단가
from k3
where 적용일자 <= '2003-03-31' and 종료일자 >= '2003-03-01'
and 상품코드=1
|--Table Scan(OBJECT:([tempdb].[dbo].[k3]),
WHERE:(([k3].[적용일자]<=Convert([@1]) AND [k3].[종료일자]>=Convert([@2]))
AND [k3].[상품코드]=Convert([@3])))
'k3' 테이블. 스캔 수 1, 논리적 읽기 수 5197
|
적용일자가 2003-03-31일 보다 작은것이 대부분이기 때문에 table scan을 선택했다.
|
/* ========================================================================== */
select 적용일자, 단가
from k4
where 적용일자 <= '2003-03-31' and 종료일자 >= '2003-03-01'
and 상품코드=1
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[k4]))
|--Index Seek(OBJECT:([tempdb].[dbo].[k4].[pk_k4]),
SEEK:([k4].[상품코드]=Convert([@3]) AND [k4].[종료일자] >= Convert([@2])),
WHERE:([k4].[적용일자]<=Convert([@1])) ORDERED FORWARD)
'k4' 테이블. 스캔 수 1, 논리적 읽기 수 36
|
인덱스만을 읽어서 범위를 줄인후에 테이블을 읽어서 단가를 가져왔다.
논리적읽기수를 비교해봐라 엄청난 차이다.
물론, 상황에 따라서 적절한 인덱스 설계를 해야하겠지만
일반적으로 이력모델에서는 종료일자 + 시작일자 의 복합인덱스가 유리한 경우가 많다.
▶ 김연욱(Vichang)님의 Inside Query Performance - (2)
|
