사용중인 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
-- 쿼리를 조금만 바꾸면....
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가 보여도 성능상의 문제가 되어질 수 있습니다.