1 Surrogate Key 란?

2 OLTP 시스템에서의 Surrogate Key(옛날 마소원고의 일부다)

3 개똥을 약에 쓰자(데이터 추출시 사용하면 좋다)

4 결론

Surrogate Key(대리키)란, Natural Key(자연키)를 대체하는 키를 말한다. Surrogate Key는 다음과 같은 3가지 목적으로 사용된다.

• 자연키의 저장 공간을 줄여 성능을 향상시키기 위해 사용된다.
  
• 계좌번호와 같은 중요한 자료를 대체하여 보안상의 이득을 얻기 위해 사용된다.
  
• 변경에 영향을 덜 받기 위해 사용.
  
  

이를 설명하기 위해 다음의 그림을 보자.

* 2009-11-23일 ERD를 다시보니 현실에 맞지 않아서 ERD를 대충 수정했습니다. 고객ID를 설명하기 위한 것이므로..

자연키의 저장 공간을 줄여 성능을 향상시키기 위해 사용된다
위의 그림에서 자연키는 '고객ID'이다. 일반적으로 12Byte ~ 20Byte 정도로 사용된다. 어떤 곳은 e메일 주소를 고객의 ID로 사용하는 곳도 있다. 고객이 1천 만 명이고, 평균 주문건수는 10건(총 10억건), 배송건수 2천만건, 고객ID가 12Byte일 경우와 Surrogate Key를 사용하여 4Byte 숫자를 사용하는 경우의 데이터 크기의 차는 다음과 같다. (데이터 저장을 위한 군더더기들은 전혀 계산하지 않았다. 인덱스 크기까지 산정하면 훨씬 더 많은 차이가 날 것이다.)

구분/고객ID	12Byte (varchar(12))	4Byte (int)
고객(1천만건)	144MB	38MB
주문(1억건)	1440MB	380MB
배송(2천만건)	228MB	76MB
총합	1812MB	494MB

데이터의 크기를 줄이면 성능이 좋아지는 것은 절대 깨질 수 없는 진리다. 같은 결과를 얻는데 적게 읽는다면 디스크 사용량이 줄어든다. 그러면 자연스럽게 사용되는 메모리의 양도 줄어들 것이고, CPU가 처리해야 하는 데이터도 적어지기 때문이다. 아주 당연한 원리다. 고객의 경우는 Key Entity(or 마스터 데이터 or 기준정보)이기 때문에 고객ID 12Byte는 그 영향력이 매우 막강하다. 고객이 1천 만 명의 회사에서 고객ID를 Surrogate Key로만 바꾸어도 5%이상의 부하가 줄어들 것이다.

계좌번호와 같은 중요한 자료를 대체하여 보안상의 이득을 얻기 위해 사용된다
위의 그림에서 고객ID는 3군데에 있다. 저 3군데 중에 어떤 곳을 가도 고객ID는 노출이 된다. 요즘 같은 시기에는 ID만 알아도 마음만 먹으면 신상정보를 추적해 낼 가능성이 매우 많다. 만약 Surrogate Key를 사용하였다면 고객ID는 1군데만 존재하므로 아무래도 노출의 위험성이 적다.

변경에 영향을 덜 받기 위해 사용
Surrogate Key 단독으로는 아무런 의미가 없기 때문에, Surrogate Key에 물려있는 개체가 바뀐다고 해도 크게 무리가 없다. 예를 들어, 이 데이터 모델에서 고객ID는 식별자이다. 만약 고객이 고객ID를 변경하기를 원한다면 영향받는 개체는 몇 개인가? 만약, 고객ID를 'databaser' -> 'dwa'로 변경을 원한다면 3개의 테이블에서 변경이 일어나야 한다. 만약 주문건수가 100건이라면 적어도 200건 이상의 갱신이 이루어져야 한다. 만약 고객ID 대신 Surrogate Key를 사용하였다면 1건의 갱신으로 작업은 끝난다. 또한 이런 경우 Surrogate Key를 사용하면 이력관리도 매우 용이해진다. 만약 Surrogate Key를 사용하지 않는다면 이력관리 테이블이 3개가 더 있어야 할지도 모른다. 참고로 DW에서는 차원의 이력관리 및 운영 시스템과의 독립성 유지를 위해서 차원의 기본키는 대리키를 사용해야 한다.

흔히 데이터베이스의 설계라고 하면 물리적인 설계를 의미한다. 즉, 인덱스, 테이블, 제약조건 등에 대한 정의가 포함된다. 요즘의 데이터베이스라면 코드테이블이 하나쯤은 있는 것이 당연하게 여겨지기도 한다. 예를 들면, (직업코드, 직업명)과 같은 형태이다. ERP(Enterprise Resource Planning)와 같은 시스템의 경우 ‘코드마스터’ 라는 명칭으로 코드테이블이 대부분 존재한다. 이렇게 코드테이블을 두는 이유는 뭘까? 단순히 선배들이 그렇게 해왔기 때문이나 남들이 그렇게 하기 때문에 또는 책에서 그렇게 지시했기 때문이라고 대답할 수는 없는 일이다. 코드 테이블을 두는 이유는 유지보수의 편리성과 성능 향상에 있다. 먼저 유지보수의 편리성에 대해 이야기 해보자. 코드 테이블이 있는 학교의 데이터베이스의 경우 한 학과의 명칭이 변경되었다면 코드 테이블에서 해당 학과의 코드명을 고치면 된다. 만약, 코드 테이블이 없다면 이 학교의 데이터베이스 관리자는 학과와 관련된 모든 테이블을 찾아 갱신해야 할 것이고 이 과정에서 놓친 테이블이 발생하여 에러의 원인이 되기도 한다. 그럼 성능 향상은 어떻게 되는 것일까?

• 유지보수의 편리성: 학과의 명칭이 변경되었다면 코드테이블의 해당 학과의 코드명만 고쳐주면 된다. 만약 코드 테이블이 없다면 모든 관련 테이블을 갱신해야 한다.
  
• 성능: 코드는 INT형이고, 코드명은 가변길이 문자형(최대 20) 이라면 INT형을 저장하는 것이 저장공간을 적게 잡아먹고, 만약 인덱스 등의 객체가 생성된다면 인덱스의 크기도 줄어들 것이다 . 100 만 건의 데이터가 있다고 가정하자. INT형이라면 4*1000000 바이트이며, 가변길이 문자형이라면 20 * 1000000 바이트가 된다. 예를 들면, 500페이지짜리 책을 읽는 것과 1000페이지짜리 책을 읽는 것과의 차이이다.
  
  

데이터베이스 관련 책을 보면 모델링에는 모델러의 주관적인 생각이 들어간다는 말을 쉽게 접할 수 있다. 사실 맞는 말이지만 ‘주관’이라는 의미는 좋은 의미로 해석될 수도 있지만 나쁜 의미로 해석될 수도 있다. 다시 말해 주관은 실제세계를 표현하는 방법의 차이 정도로 해석할 수도 있지만, 나쁜 의미로 해석하자면 모델러의 능력이라고 볼 수도 있기 때문이다(사실 프로젝트에 투입되는 인원 중 모델이나 설계문서를 검토하여 문제점을 지적해 낼 수 있는 사람이 거의 없기 때문에 검증된 외부업체에 검토를 의뢰하는 것이 좋다). 설계에 따라서 어떻게 다른 구현이 이루어지는지 또한 성능상의 차이가 있는지 예를 통해 알아보자.

그림과 같이 하나의 대상을 두고도 서로 다른 두 사람이 두 가지 형태의 모델을 도출했다고 가정하자. A, B 모델의 차이는 그림에서 보는 바와 같이 A모델은 비식별자 관계를 맺고 있고, B모델은 식별자 관계(외부 키가 기본 키의 일부 또는 전체가 됨)를 맺고 있다. 어떤 것이 더 좋은 모델일까? 현재로써는 알 수 없다. 단지 A모델을 만든 사람이 왜 A처럼 만들었지 왜 B처럼 만들었는지 확실하게 알고 있다면 두 모델은 문제점이 없다고 봐도 무방하다. 하지만 왜 그렇게 했는지 모른다면 그게 바로 문제이다. ERD(entity Relationship Diagram)처럼 생긴 문서를 던져주고 개발을 지시하면서 개발 시간을 재촉한다면 그 자체가 잘못인 것과 같다. 그림에서 두 모델이 실제 물리적으로도 똑같이 구현되어 있고, 두 모델의 최상위에 있는 테이블 A를 코드테이블이라고 가정하자. 만약 G 테이블에서 A 테이블에 있는 특정 컬럼의 데이터를 가져와야 한다면 두 모델에는 어떤 차이가 있을까? 실제 SQL로 만들어 본다면 아래의 코드와 같을 것이다.

--A의 경우
SELECT
	A.UID_A
,	G.UID_G
FROM G 
	INNER JOIN F ON G.UID_F = F.UID_F
	INNER JOIN C ON F.UID_C = C.UID_C
	INNER JOIN A ON C.UID_A = A.UID_A

--B의 경우
SELECT
	A.UID_A
,	G.UID_G
FROM G INNER JOIN A ON G.UID_A = A.UID_A

이 경우 두 모델에서 SQL의 실행 결과는 같다. 그러나 A 모델은 몇 번의 조인을 거쳐야만 비로소 A 테이블의 데이터를 조회할 수 있다. B의 경우는 단 한 번의 조인으로 원하는 데이터를 조회할 수 있다. A 모델은 조인한 만큼의 데이터를 처리해야 하고 원하는 데이터를 찾기 위해 많은 길을 거쳐야 한다. 데이터의 출처 또한 불명확해진다. 실제로 모델의 느낌은 A가 더 복잡함을 알 수 있다. 느낌이 오는가? 설계가 어떻게 되어 있는지에 따라 성능에 많은 차이가 나고, 데이터의 출처가 아주 명확해 지고 SQL도 단순해진다.

그렇다면 B 모델이 더 좋은 모델이라고 말 할 수 있을까? B 모델은 관리해야 할 컬럼 수가 늘어나고 그 만큼의 추가적인 저장 공간도 더 필요하다. 설계의 기준은 데이터가 어디까지 접근할 필요성이 있는지에 대한 요구사항이 정의되어 있어야 한다. 만약 F 테이블이 A 테이블에 접근해야 하고, G 테이블은 A, C, F 모두 접근해야 하고, 나머지는 접근의 필요성이 없다면 모델은 아래의 그림과 같은 A, B 모델의 혼합된 형태가 될 것이다.

어떤 것이 더 유리한지 판단하는 것은 모델링 또는 설계하는 사람의 능력에 따라 달라진다. 필자도 ‘상황’이 주어지지 않는 이상 어떤 모델이 더 좋다고는 말할 수 없다. 주의해야 할 것은 최상위 테이블인 A가 매우 단순해야 한다는 것이다. 물리적으로 보면 UID_A가 INT형인 경우와 CHAR(50)인 경우는 관계를 맺을 때와 인덱스 크기에 영향을 끼치게 된다. 즉, 최상위 테이블이 가벼우면 가벼울수록 좋다는 것이다. UID_A가 CHAR(50)인 경우 C, F, G 테이블을 보면 각 행(Row)마다 CHAR(50) 만큼의 저장 공간이 필요하다는 것을 알 수 있다. 그래서 일반적으로 코드 테이블 같은 경우는 대리키(설계속성)를 사용하여 이러한 저장 공간과 인덱스의 크기를 줄여 성능과 함께 정보의 질을 높인다.

많은 경우 OLTP 시스템에서는 정당한 이유없이 일련번호(Sequence, Surrogate Key)를 사용한다. 왜냐하면, 이렇게 하면 그래도 시스템이 대충 버티기 때문이다. 하드웨어가 워낙 좋아진 이유도 한 몫한다. 어쨌든 'Surrogate Key 남발' 패턴의 설계는 어플리케이션은 다수의 JOIN 사용으로 복잡성을 가중시킨다. 이런 경우 개발한 당사자가 아닌 사람이 유지보수를 한다면 유지보수 비용은 엄청나게 늘어난다.

일련번호를 Surrogate Key로 남발해서 사용하는 경우 대부분 Primary Key로 설정된다. 그나마 Oracle과 같은 DBMS는 Dense Index 이므로 SQL Server와 같은 1차, 2차 인덱스 구조를 가진 DBMS보다는 영향이 덜하다. SQL Server의 경우 아무런 이유없이 일련번호에 Primary Key를 설정하면 Clustered Index(Sparse Index)가 자동 생성된다. 그러므로 이후에 생성되는 인덱스는 2차 인덱스로 Dense Index가 된다. 또한, Dense Index는 Sparse Index의 유지보수에 직접적으로 영향을 받는다.

어쨌든 단점이 많기는 하지만 DW의 입장에서는 장점으로 존재할 수 있다. ETL툴을 이용하면 실시간으로 정보계로 데이터를 끌어올 수도 있다. 예를 들어 다음과 같은 경우다.

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

인덱스의 상황은 다음과 같이 정리된다.

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

만약 특정 날짜의 데이터를 가져온다고 한다면 일반적으로는 다음과 같이 SQL을 작성할 것이다.

set statistics io on
set nocount on

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

최상의 선택일까? 아니다. 우연치 않게도 Sequence가 있다. Sequence는 입력된 순서를 의미하므로 시간의 특성을 가진다. 그러므로 날짜와 연계를 하면 Non-Clustered Index가 아닌 Clustered Index를 이용할 수 있다. 개념은 다음과 같다.

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

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
and left(dt,8) = @dt --인덱스 사용못하게..

--테이블 'test'. 검색 수 2, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0
--테이블 'test'. 검색 수 1, 논리적 읽기 수 38, 물리적 읽기 수 0, 미리 읽기 수 0
--select 15589/(6+38) --I/O가 354배 차이 난다!!

어쨌든 OLTP환경에서 잘못 만들어진 것을 DW구축에서 ETL시에는 꽤 유용하게 사용될 수도 있다는 것을 보았다. DW에서 Surrogate Key를 만드는 일은 성능에 매우 큰 영향을 끼치는 일이다. 또 유연성을 위해서 매우 중요한 일이다. 물론 OLTP환경에서도 Surrogate Key를 만드는 것은 중요하다. 무엇보다도 올바른 판단을 내리기 위해서 노력하는 것이 중요하다.

주어진 환경을 개선하는 것은 매우 좋은 일이다. 하지만 시스템이나 어플리케이션들이 여러 곳에 산재해 있는 경우는 종속성 조차 100% 따지기가 매우 힘든 경우가 많이 있다. 만약 오랜 역사를 가진 기업이라면 더더욱 그렇다. 환경을 개선하기 어렵다면 환경을 이용하는 방법도 생각해보자.