1 개요

2 int

2.1 int형으로 IP 연산에 활용할 수 있을까?

2.2 날짜(YYYYMMDD)를 int형으로?

3 가변길이

4 bigint 와 binary

5 float

테이블의 컬럼에 대한 데이터형의 선택은 데이터베이스 전체의 성능에 영향을 끼친다. 특히, 기본키의 데이터형의 선택은 매우 중요하다. 왜냐하면 관계형 DBMS에서는 테이블의 기본키가 외부키로 쓰이기 때문에 파급효과(일종의 side-effect)가 있기 때문이다. 또한 저장공간을 얼마만큼 차지하는지를 결정하는 일이므로 직접적으로 성능을 결정하기도 한다. 또한 데이터형은 기본적인 제약사항을 나타낸다. 필자의 지인이 테스트해 본 결과 SQL Server의 tinyint와 smallint는 2byte의 같은 저장 공간을 사용하는 것으로 나타났었다. (현상) tinyint는 0 ~ 255까지만 사용한다는 제약의 차이다.

int형은 가장 기본적인 데이터 타입이다. 32bit의 경우 4byte다. 그럼 64bit에서는? 다음의 URL을 참고.
유닉스/리눅스는 LP, 윈도우는 LLP 를 사용한다. 즉, 유닉스/리눅스는 LP64, 윈도우즈는 LLP64를 사용한다. 결론적으로 유닉스/리눅스/윈도우즈는 모두 32bit를 사용하고 있다.

Data Type	LP32	ILP32	ILP64	LLP64	LP64
char	8	8	8	8	8
short	16	16	16	16	16
int32			32
int	16	32	64	32	32
long	32	32	64	32	64
long long (int64)				64
pointer	32	32	64	64	64

자, 그럼 int형은 어찌어찌 쓰이는지 그 쓰임새를 알아보도록 하자.

IP는 기본적으로 "255.255.255.255"와 같은 형태이다. 255라는 것은 1byte로 표현할 수 있는 최대 숫자이다. 마침표(".")를 제외하면 4byte로 해결할 수 있다. 그래서 일반적인 서버 프로그래머들은 이러한 형태의 문자열 IP를 int형으로 변경하여 사용하고는 한다. 하지만 SQL Server의 경우 int형은 4byte의 크기를 가지지만 unsigned가 아닌 signed int이므로 "255.255.255.255"를 모두 표현할 수 없다. 그렇다고 8byte의 bigint를 사용하는 것도 아깝다. 그러므로 다음과 같이 binary(4)로 관리하면 된다. 이렇게 관리하면 IP에 대한 범위연산도 할 수 있으므로 성능과 관리라는 2마리 토끼를 잡을 수 있다. IP는 다음과 같이 변경하면 된다.

declare @ip varchar(15)
set @ip = '121.153.53.57'
--121.168.96.235

select
	ip_binary
,	convert(varchar, convert(smallint, substring(ip_binary, 1, 1))) + '.' +
	convert(varchar, convert(smallint, substring(ip_binary, 2, 1))) + '.' +
	convert(varchar, convert(smallint, substring(ip_binary, 3, 1))) + '.' +
	convert(varchar, convert(smallint, substring(ip_binary, 4, 1))) ip_string
from(
	select
		CONVERT(binary(1), CAST(PARSENAME(@ip, 4) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@ip, 3) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@ip, 2) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@ip, 1) AS tinyint)) ip_binary
) t

날짜형 데이터 타입의 경우는 년, 월, 일 뿐만 아니라 시, 분, 초, 밀리초 단위 이하까지도 표현할 수 있다. SQL Server의 datetime 형식은 8byte이며, smalldatetime의 경우는 분 단위까지 표현이 가능하다. 날짜 연산은 일반적인 10진 연산과는 다르게 복잡한 계산을 해야 한다. 그 만큼 CPU자원을 많이 소모한다는 뜻이다. 만약 분 단위까지만 필요하다면 smalldatetime을 사용하면 된다. 또는 숫자형 날짜(날짜 포멧에 맞아야 함)라는 보장이 있고, 2100년 정도까지만 사용한다면 int형도 '2100123124'와 같이 시간정도는 표현이 가능하다. 년, 월, 일만 필요하더라도 int형을 쓰는 것으 매우 좋은 일이다. 요즘은 CPU자원이 매우 풍부하므로 CPU를 더 쓰고, I/O를 줄이는 방법도 생각해 볼 만하다. 날짜형에 대한 부분은 날짜 데이터형식 문서를 참고하기 바한다.

'가변길이'의 '가변'은 저장되는 데이터의 크기가 랜덤임을 이야기한다. 그래도 어디까지가 해당 컬럼의 데이터인지 알기 위해서 명시적인 무언가가 필요하다. 그러므로 DBMS는 내부적으로 가변길이 데이터형의 크기를 저장하는 군더더기(redundancy)를 만든다. sql server의 경우는 varchar(8000)까지 이므로 8000을 1byte는 설명할 수 없으므로 2byte다. 다른 DBMS의 varchar형이 255까지 지원한다는 의미는 군더더기가 1byte붙는 것을 의미한다. 여기까지가 스토리지 엔진이야기고, 논리적으로 varchar(n)에서 n은 2가지 관점에서 봐야한다. (varbinary와 같은 데이터형도 있지만, 가변길이에 대한 설명이므로 varchar 데이터형으로 설명하겠다)

1. 도메인
   
2. 성능
   
   

1. 도메인 문제
성능차이가 발생하지 않건 발생하건 n의 크기는 현실을 반영해야 한다. 도메인 무결성을 지키기 위해서는 현실적으로 예측가능한 만큼만 크기를 늘려야 한다. 데이터 품질저하는 데이터베이스의 존재 자체를 위협한다. 물론 데이터 품질문제를 해결하기 위해 어디에선가 더 처리해야 하므로 성능문제도 유발시킨다.

2. 성능 문제
n에따라 성능차이가 발생하는가? 그렇다. 성능차이 발생한다. n은 확정되겠지만, 데이터의 값은 미확정이다. 이러한 불확실성을 해결해나가는 인간의 도구가 확률이다. varchar(10)은 공간 사용 범위(최대치 - 최소치)는 9 다. 하지만 varchar(1000)은 999 다. varchar(1000)일때 옵티마이저는 어떻게 예측해야 할까? 컬럼의 값이 1byte만 사용될 때와 1000byte를 사용할 때 읽는 page수는? 당연히 1000byte일 경우 훨씬 더 많은 page를 읽어야 할 것이다. 즉, n의 크기가 커짐에 따라서 옵티마이저가 제대로 예측할 확률이 적어진다는 뜻이다. 그래도 성능이 제대로 나올수 있게 옵티마이저는 예측을 해야 한다. 그래야 사람들이 쓸테니까 말이다.

옵티마이저는 어떤 기준을 마련해야 하는데, 기준이 없다. 데이터의 확률분포, 평균, 표준편차를 알 수 있는 것도 아니다. 그나마 상황에서는 써먹을 수 있는 기술통계량이 평균도 아니고, 최빈값(mode)도 아니고 중앙값(median)이다. 또한 분포를 모르므로 체비셰프 정리를 이용해야 할 것이다. 중앙값은 짝수냐 홀수냐에 따라 공식이 좀 다른데, 약간의 차이때문에 if를 한 번 태우는 것은 비용낭비다. 그래서 sqL server는 그냥 n/2 다. 즉, varchar(1000)이면 500을 예상한다. 499나 501로 예상해도 별반 차이가 없을 것이다. 하지만 재수없게 1,2의 차이로 예상된 비용이 병렬처리를 할거냐 말거냐의 비용(기본값=5)의 경계에 설수도 있다. 결론은 n은 sql server가 처리해야 할 데이터의 양을 예상되는데 사용된다. 테스트 해보자.

테스트데이터 만들기

;with dummy
as
(
        select 0 seq
        union all
        select seq + 1 from dummy
        where seq + 1 <= 10000
)
select 
	seq 
,	convert(varchar(100), newid()) var100
,	convert(varchar(7000), newid()) var7000 
into #temp
from dummy
option (maxrecursion 0);

테스트 데이터를 만들고 아래의 sql을 실행하여 실행계획을 보자.

select 
	seq
,	var100
from #temp
order by var100

"예상 행 크기"와 "메모리 부여" 부분을 보자. 그리고 다시 varchar(n)에서 n의 크기를 크게 잡은 sql을 실행시켜보자.

select 
	seq
,	var7000
from #temp
order by var7000

"예상 행 크기"와 "메모리 부여" 부분을 보자. varchar(100)인 경우와 varchar(7000)인 경우 sql server는 다르게 보고 있다는 것을 확인 할 수 있다.

아.. 씨벌.. 결과가 이럴 줄이야... ㅋ

declare
    @big_i bigint
set @big_i = 432631175994903702

select
    convert(binary(8), @big_i)
,   convert(binary(8), 432631175994903702)

/* 결과..
------------------ ------------------
0x060103C32BEF8C96 0x12000001968CEF2B
*/

• http://slame.tistory.com/2
  
• http://www.valken.net/473