MS-SQL Server

데이터를 선분으로 관리하는 테이블 조회성능 최적화 사례 (IP 관리 예제)

임현수 2011. 1. 13. 20:16

사용중인 IP리스트를 DB를 사용하여 관리하고 싶을 때, 보통 2가지 방식으로 관리가 가능합니다.

 

1. 사용중인 IP를 개별적 저장하여 관리

2. 사용중인 IP영역을 선분으로 저장하여 관리

 

1. 컬럼 리스트 : (IP)

2. 데이터 예

(192.168.1.1)

(192.168.1.2)

(192.168.1.125)

(192.168.20.1)

1. 컬럼 리스트 (IPFrom, IPTo)

2. 데이터 예.

(192.168.1.1, 192.168.1.125)

(192.168.20.1, 192.168.20.1)

 

대상 IP를 바로 확인 가능하므로 직관적이다?

대상 IP가 많을 경우도 빠른 등록이 가능합니다.

 

IP 개수는 4,228,250,625개입니다. 대상 IP가 많아질수록 디스크 공간을 많이 차지하게 됩니다.

더욱이, 대량 IP를 등록할 때 서버부하 상승 및 작업시간도 많이 소요됩니다.

조회 쿼리 작성시 주의를 요합니다.


이제 제목이 적혀있는데로, [2안] 방식에서 조회 쿼리 작성시 주의가 필요한 부분에 대한 테스트를 진행해보도록 하겠습니다.

--@@ 임시테이블생성(IP 리스트테이블)
create table IPList (
       IPFrom char(12) not null
,      IPTo   char(12) not null
)
create unique clustered index idx_IPList_IP_From on IPList (IPFrom, IPto)

Result>
명령이 완료되었습니다.

-- @@ 테스트 데이터 입력 (D class 1~125까지 영역을 가지는 데이터 20만건 생성)
set nocount on 

declare @i   int = 1

while (@i <= 200000)
begin
    
  insert into IPList (IPFrom, IPTo)
       select 
             right('00' + cast((@i/65025)%255  + 1 as varchar(3)),3)
       +      right('00' + cast((@i/255)%255 + 1 as varchar(3)),3)
       +      right('00' + cast(@i%255 + 1 as varchar(3)),3)
       +      '001' as IPFrom
       ,
             right('00' + cast((@i/65025)%255  + 1 as varchar(3)),3)
       +      right('00' + cast((@i/255)%255 + 1 as varchar(3)),3)
       +      right('00' + cast(@i%255 + 1 as varchar(3)),3)
       +      '125' as IPTo

       set @i = @i + 1
end

Result> 44
명령이 완료되었습니다.

-- 아래는 문제가 있는 쿼리인가?
select top 1 *
from dbo.IPList with ( nolock )
where '002255001124' between IPFrom and IPto

 
Result> 실행계획만 보면 seek 풀린다. 하지만!!! 논리적 읽기수가 엄청나다. 그저 IP 하나 확인했을 뿐인데.


 
테이블 'IPList'. 검색 1, 논리적 읽기 537, 물리적 읽기 0, 미리 읽기 0, LOB 논리적 읽기 0,


-- 쿼리를 조금만 바꾸면....

select top 1 *

from dbo.IPList with ( nolock )

where '002255001124' between IPFrom and IPto

order by IPFrom desc

 

Result> 이제 실행계획도 그렇고, 우리가 원하는데로 수행되어지는 것을 볼 수 있습니다.

 


테이블
'IPList'. 검색 1, 논리적 읽기 3, 물리적 읽기 0, 미리 읽기 0, LOB 논리적 읽기 0,


실행계획도 정상적인데, 왜 논리적 읽기 수가 100배이상 차이가 나는 걸까요? 원인은 아주 간단합니다.

인덱스가 (IPFrom) 으로 잡혀있는 경우 IPFrom값이 낮은 순으로 정렬되어져 있기 때문에, IPFrom <= '002255001124' 조건을 해당 데이터 위에 있는 값들이 모두 만족하기 때문입니다.
Order by IPFrom DESC를 주었을 경우 IPFrom 값 비교를 큰 값부터 찾기 때문에, 처음으로 조건을 만족하는 값이 실제 찾고자 하는 값을 됩니다.


다른 방법으로도 위와 같은 현상을 해결할 수 있습니다. 인덱스를 (IPTo, IPFrom)으로 만드는 것입니다. 역시 테스트 해보겠습니다.


--
인덱스추가하기
create unique index idx_IPList_IP_To on IPList (IPto, IPFrom)
 

select top 1 *

from dbo.IPList with ( nolock )

where '002255001001' between IPFrom and IPto
 

Result>


테이블
'IPList'. 검색 1, 논리적 읽기 3, 물리적 읽기 0, 미리 읽기 0, LOB 논리적 읽기 0



결론!!!!!!!
    1. 선분으로 데이터를 보관하는 경우 일반적인 equal 비교를 통하여 접근하는 방식에서 생각했던 것과 조금 다르게 동작할 수 있으니 주의가 필요합니다.
    2. 실행계획에 index seek가 보여도 성능상의 문제가 되어질 수 있습니다.