제 홈페이지의 모든 글은 anti-nhn license에 따릅니다.



대용량 서비스 - 5. 디비 비정규화

지난 글에서 디비의 인덱스가 어떻게 작동하는 지 대충 정리했었습니다. 일반적으로 데이터는 정규화를 시키는 게 좋습니다. 디비 정규화의 핵심 아이디어는 사람이 보기 좋게 정리하고 데이터 중복을 방지하는 것입니다. 디비 정규화에 대한 문서는 여기저기 널렸습니다. 먼저 정규화를 이해하신 후에 이 글을 보시기 바랍니다. 


나중에 봐야지 .. 라고 생각하시는 분들은... 얼렁 생각을 바꿔서 정규화를 먼저 보고 오세요!!! 정규화를 모르고 비정규화만 시켰다가는 재앙이 일어납니다.



1. 비정규화 주의 사항

디비 비정규화란 말 그대로 디비의 정규화를 깨는 것입니다. 정규화를 깨는 유일한 이유는 속도 향상입니다. 정규화가 잘 된 테이블이 index를 잘 타는 테이블은 아니니까요.

정규화가 데이터 중복을 막는 것인데, 비정규화를 시키게 되면 중복된 데이터가 들어갈 수 있습니다. 한 테이블에 정규화된 데이터와 비정규화된 데이터를 한 꺼번에 넣으면 문제가 생겼을 때 처리가 매우 곤란합니다. 따라서 정규화를 잘 시킨 테이블과 그 것을 비정규화 시킨 데이블을 분리해서 쓰는 것이 안전합니다. 비정규화된 테이블은 정규화된 테이블의 데이터를 이용해서 복원할 수 있는 구조를 가지는 것이 좋습니다.

비정규화는 트리거로 구성하는 것이 좋습니다. 트리거란 어떤 쿼리가 실행되었을 때 다른 쿼리도 실행시켜라라는 것입니다. 정규화 테이블에 insert,update,delete 가 일어나면, 비정규화 테이블에서도 어떤 작업을 하라는 식으로 하면 됩니다. 이는 프로그램에서 직접 2개의 쿼리를 돌리는 것 보다 훨씬 안전합니다. 첫번째 쿼리가 성공하고 나서 두 째 쿼리가 실패하거나 하는 등의 사태가 있을 수 있기 때문입니다. 

비정규화 테이블에 대한 insert , update , delete는 오로지 trigger를 통해서만 하고 select 만 비정규화 테이블에 바로 하는 게 좋습니다. 그래야 개발자가 딱 봐도 인지 가능한 정규화된 테이블에만 insert, update, delete를 하고, 복잡하고 어려운 select 대신 쉬운 select 로 처리할 수 있습니다.


2. 비정규화 예제 

2.1 count , max 등 grouping 관련 처리

아래와 같이 글 내용을 저장하고 있는 테이블이 있을 때,

msg_id(글 아이디. 순차적으로 증가, int 값) , 
writer(글 쓴 사람. 일단 편의상 varchar) , 
cont (글 내용, varchar 또는 text 등),
written_dt (글 쓴 날짜)

사용자 별 글 쓴 갯 수를 뽑아내는 쿼리는 다음과 같습니다.

select count(*) from msg where writer='홍길동';

글 갯수가 많을 경우 전에 설명했던 것처럼 부하가 걸릴 수 있습니다. 따라서 글을 쓰는 순간에 글 갯수를 저장하는 별도의 테이블에 trigger를 만드는 게 좋습니다.

그 별도의 테이블의 구조는 아래와 같습니다.

writer(글 쓴 사람. 일단 편의상 varchar) ,
cnt (글 갯수)

사용자가 가입하는 순간에 msg_stat 에 writer = 새로 가입한 사용자, cnt =0 으로 데이터를 삽입해 놓습니다.( 이부분도 트리거로 하는 게 좋습니다. 여기서는 테이블 구조를 조금이라도 단순화 시키고자 회원 테이블은 만들지 않았습니다.) 그리고 글을 쓸 때마다 아래와 같은 방식으로 msg_stat 테이블의 데이터를 수정합니다.

create trigger  msg_insert after insert on msg
    for each row begin
        UPDATE msg_stat SET cnt = cnt+1 where writer = NEW.writer;
    end

여기에는 index를 writer 에만 걸어놓으면, 사용자가 쓴 글 갯수를 간단하고 뽑아올 수 있습니다. 데이터 삭제에 대한 trigger는 생략합니다. 


만약 굳이 비정규화를 시키지는 않고 어느 정도 이상의 값은 무시하겠다면 아래와 같은 쿼리도 가능합니다.

select count(*) from (select 1 from msg where writer='홍길동' limit 1000);

이렇게 하면 1000개가 넘는 것은 전부 1000개로 나올 것이고, 그 이하는 갯수가 정확히 나올 겁니다. 서비스에 따라 다르겠지만 일반적으로 이 정도면 큰 부하가 되진 않습니다.



최근 글 쓴 사용자 10명을 뽑아오려면, msg_stat 테이블을 좀 수정하면 됩니다. msg_stat에 1개 컬럼을 더 추가합니다.

writer(글 쓴 사람. 일단 편의상 varchar) ,
cnt (글 갯수)
last_msg_Id (최근에 쓴 글 번호) 

trigger는 아래와 같이 바뀝니다.


create trigger  msg_insert after insert on msg
    for each row begin
        UPDATE msg_stat SET cnt = cnt+1 , last_msg_id = NEW.msg_id where writer = NEW.writer;
    end

여기에 last_msg_id 컬럼에 대해서 인덱스를 걸면 최근에 글 쓴 사용자 10명을 뽑아오는 것이 별 부하 없이 가능합니다. 다만 이런 경우 삭제 trigger가 복잡해 질 수 있습니다. 제일 최근 글이 삭제될 경우에는 msg_stat의 last_msg_id가 그 이전 글을 찾아서 넣어야 할 겁니다. 이 데이터를 뽑아오기 위해서는 msg 테이블에 (writer, msg_id) 인덱스가 있어야 합니다.



2.2 특정 부서 사람이 쓴 글을 시간 순으로 보기

직원 테이블(emp)
emp_id : 직원 아이디
dept_code : 직원의 소속 부서를 나타내는 부서코드

글테이블(emp_msg)
msg_id : 글 아이디
writer : 직원테이블의 emp_id
cont : 글 내용.

와 같은 테이블이 있을 때 영업부(dept_code 가 1 이라 칩시다.)에서 쓴 글을 최근 순으로 정렬하면 아래와 같이 될 겁니다.

SELECT a.emp_id , b.cont FROM emp a INNER JOIN emp_msg  b ON a.emp_id = b.writer WHERE a.dept_code = 1 ORDER BY b.msg_id

이 쿼리의 문제는 where 절에서 쓰는 테이블과 ordering에서 쓰는 테이블이 다르다는 것입니다.

정리를 하면 a 테이블에서 조건(dept_code = 1) 을 이용해서 데이터를 뽑은후 (emp.emp_id) 그 데이터를 바탕으로 b 테이블에 조인(a.emp_id = b.writer) 해서 뽑은 데이터를 b테이블의 컬럼(msg.msg_id) 로 정렬을 하려고 하니 정렬이 빠르게 될 수 없습니다. ( 우연찮게 emp_id가 1개만 나오면 인덱스를 잘 탈 수도 있겠지만, 지극히 예외적인 상황입니다.)

b 테이블 입장에서 보면 아래와 같은 쿼리와 유사합니다.

select b.cont from msg b where b.writer in (a의 dept_code가 1인 모든 사용자) order by b.msg_id;

이것도 별도의 테이블을 만들면 됩니다.

부서별 글번호 (emp_dept_msg)
dept_code : 부서코드
msg_id : 글 아이디


create trigger  emp_msg_insert after insert on emp_msg
    for each row begin
        INSERT INTO emp_dept_msg  SET dept_code  = (select dept_code from emp where emp_id = NEW.writer) , msg_id  = NEW.msg_id  ;
    end

당연히 인덱스는 ( dept_code, msg_id) 입니다.



2.3 위치 관련 비정규화

내 근처에 있는 매장 보기는 대략 아래와 같은 쿼리로 실행될 수 있습니다.

select * from location where x between 38.12345 and 42.12345 and y between 120.12345 and 130.12345;

위경도 값이 어느 범위 내에 있는 것을 location 테이블에서 조회하는 것입니다.

여기서의 문제는 2차원 데이터이기 때문에 1차원 인덱스로는 처리가 불가능하다는 것입니다. 디비 엔진에 따라 point 타입( x,y 로 구성된 2차원 값) 에 인덱스를 걸 수 있는 경우도 있지만 역시 R tree 방식을 씁니다.

R tree는 2차원 공간은 모눈종이처럼 쪼개 놓는 것입니다. 그리고 그 모눈 종이의 각 칸에 번호를 매겨 놓습니다. 그리고 그 번호에 인덱스를 거는 것입니다.(번호는 1차원이 됩니다!)

각 칸의 변의 길이는 100m인 모눈종이를 가정하면, 특정 점에서 100m 내의 모든 점은 그 특정점이 있는 칸과 좌상, 상, 우상, 좌, 우, 좌하, 하, 우하 등 총 9개의 칸 안에 들어있습니다. 각각의 칸 번호를 계산해서 뽑아낸 데이터 중에서 실제 거리 계산을 해서 맞는 데이터만 뽑으면 됩니다. x, y가 각 100 칸이라고 했을 때, 왼쪽 위부터 1부터 번호를 매기면 (1,1) 칸의 번호는 1 , (1,2)칸의 번호는 2, (1, 100) 칸의 번호는 100, (2,1)칸의 번호는 101 과 같이 되는 방식입니다.
인덱스 번호 = (y-1)*100 + x 로 정리됩니다.

따라서 내가 있는 칸이 (10,20)이라고 했을 때 그 칸의 번호는 1920이 됩니다. 주변 100m 이내라는 것은 아래와 같이 9칸 중의 하나라는 뜻이 됩니다.

181918201821
191919201921
201920202021


전체 데이터 중에서 가능성이 조금이라도 있는 데이터들만 뽑아 낸 후에 처리하기 때문에 전체를 처리하는 것에 비해서 부하가 상당히 줄어듭니다.



2.4 현재 진행 중인 이벤트 보기

select * from event_table where open_dt > now() and close_dt < now() order by close_dt desc limit 10;

와 같은 형식으로 데이터가 있을 때는 다음과 같은 비정규화 테이블(event_stat) 을 만듭니다.

event_id (이벤트 아이디, int)
status ( enum : OPEN , CLOSED ) 
close_dt ( date : 이벤트 종료일)

현재 이벤트가 진행 중인지 진행중이 아닌 지를 체크하는 비정규화 테이블을 만들고 이 테이블을 하루에 한 번씩 cron job 등을 통해서 업데이트 하면 됩니다. 당연히 index는 (status, close_dt) 로 걸면 됩니다. 

select * from event_stat where status = 'OPEN' order by close_dt desc limit 10;

이 경우에 데이터를 업데이트 하는 동안 문제가 될 소지가 있다면 다음과 같은 방법도 가능합니다.

create table tmp like event_stat ;

event_stat과  동일한 구조를 가진 tmp 테이블을 만들고, 이 테이블에 cron job 등을 통해서 데이터를 새로 다 집어넣습니다. 데이터 삽입이 완료되는 순간에

rename table event_stat to old_event_stat , tmp to event_stat ;

으로 기존에 있던 event_stat을 old_event_stat으로 바꾸고, 새로 데이터가 들어가 tmp 테이블을 event_stat으로 바꾸면 됩니다. 이렇게 하면 데이터가 항상 정상적으로 나옵니다.  (rename이 동시에 일어나기 때문에 table이 존재하지 않는 상황은 발생하지 않습니다.) 

마지막으로 기존에 있던 table drop!

drop table old_event_stat ;


2.5 카카오톡

우리모두 카톡유저라고 가정하고.. 카톡용 비정규화를 해봅시다. 정규화를 잘 시킨 talk_table 이라는 테이블은 아래와 같은 구조를 가질 겁니다.

talk_id  : 메세지 1개당 생기는 순차적 아이디, primary key , int
sender : 보낸 사람 , int
receiver : 받는 사람 , int
cont : 글내용 , text
sent_dt : 보낸날짜 , datetime이나 timestamp

다음과 같은 데이터가 있을 때..

talk_idsenderreceiver
112
221
332
413
512

내가 1번이라고 가정하고..

내가 2번과 나눈 모든 대화를 최근 대화 순으로 보여주는 쿼리는 아래와 같습니다.


SELECT * FROM talk_table WHERE (sender = 1 AND receiver = 2) OR (receiver =1 AND sender =2 ) ORDER BY talk_id

결과는 맞겠지만 지난 글에서 얘기한 것처럼 이것은 좋은 성능과는 거리가 멉니다.

A와 B가 나눈 대화를 뽑아내기 위한 비정규화 테이블은 다음과 같습니다.

비정규화 테이블 (ir_talk )
smaller_peer : sender와 receiver 중에서 번호가 작은 값 
larger_peer : sender와 receiver 중에서 번호가 큰 값 
talk_id : 정규화된 테이블(kakao_talk) 과 조인하기 위한 키값

이 경우 중요한 것은 A, B  두 사람 중 누가 보내고 누가 받았는 지가 중요한 게 아니라 "A, B 사이의 대화" 라는 것이기 때문에 smaller_peer 와 larger_peer 개념을 씁니다.

이렇게 하고 ( smaller_peer , larger_peers , talk_id ) 로 인덱스를 걸면 됩니다. query는 다음과 같이 됩니다.

SELECT b.* FROM ir_talk a LEFT JOIN talk_table b ON a.`talk_id` = b.`talk_id` 
WHERE a.`smaller_peer` = 1 AND a.`larger_peer` = 2 ORDER BY a.talk_id DESC;

이렇게 하면 인덱스를 깔끔하게 탑니다.(where 절에서 반드시 작은 놈을 smaller_peer 에 맞춰줘야 합니다. 아니면 LEAST 와 같은 함수를 써도 되구요.)

trigger는 아래와 같이 하면 됩니다.

CREATE TRIGGER `talk_table_insert` AFTER INSERT ON `talk_table` 
    FOR EACH ROW BEGIN
        SET @smaller = LEAST(NEW.sender , NEW.receiver ); -- 작은 놈. LEAST 함수는 mysql에 있음. 다른 디비는 모르겠음.
        SET @larger = GREATEST(NEW.sender , NEW.receiver );  -- 큰 놈. GREATEST 도 마찬가지...
        INSERT INTO ir_talk SET smaller_peer = @smaller , larger_peer = @larger , talk_id = NEW.talk_id;
    END

smaller_peer와 larger_peer 의 구분 없이 그냥 둘을 하나의 컬럼으로 합쳐서 해도 됩니다. 합치려면 peers  = ( @smaller << 16) | @larger  와 같이 shift 연산을 쓰면 됩니다. 디비에 따라서 합치는 게 더 좋을 수도 있습니다. 여기서는 의미를 명확하게 전달하기 위해 분리시킨 케이스까지만 보고 넘어가겠습니다.

그룹톡 같은 거 빼고,.. 첫 페이지를 보면, 가장 최근에 대화한 사람 순서대로 나옵니다. 내가 그사람에게 보냈건 그 사람이 나한테 보냈건..

내 아이디를 1번이라고 했을 때 아래와 같은 쿼리가 나옵니다.

SELECT * FROM talk_test WHERE sender = 1 OR receiver = 1 ORDER BY talk_id DESC;

실행 시키면 아래와 같이 나옵니다.


talk_idsenderreceiver
512
413
221
112

결과가 틀렸습니다. 1과 2가 대화한 내용이 3개나 떴습니다. 이 테이블을 이용해서 제대로된 데이터가 나오게 하는 것은 넘어가도록 하겠습니다. (잘 그루핑하면 되겠지만, 좋은 성능을 낼 수는 없기 때문입니다.)

여기서는 "사람"과 "또 다른 사람"이란 개념을 씁니다. 비정규화 테이블은 아래와 같습니다.

첫페이지용 비정규화 테이블 (ir_talk_main)
user : 어떤 사람
the_other : user 와 대화한 상대
talk_id : 정규화된 테이블(kakao_talk) 과 조인하기 위한 키값

CREATE
    TRIGGER `talk_table_insert` AFTER INSERT ON `talk_table` 
    FOR EACH ROW BEGIN
        SET @smaller = LEAST(NEW.sender , NEW.receiver ); -- 작은 놈. LEAST 함수는 mysql에 있음. 다른 디비는 모르겠음.
        SET @larger = GREATEST(NEW.sender , NEW.receiver );  -- 큰 놈. GREATEST 도 마찬가지...
        INSERT INTO ir_talk SET smaller_peer = @smaller , larger_peer = @larger , talk_id = NEW.talk_id;
        
        REPLACE INTO ir_talk_main SET USER = @smaller , the_other = @larger , talk_id = NEW.talk_id;
        REPLACE INTO ir_talk_main SET the_other = @smaller , USER = @larger , talk_id = NEW.talk_id; 
    END;

인덱스는 (user , talk_id) 로 걸면 됩니다. 그리고 unique index로 (one, the_other) 를 걸어야 합니다. 이 테이블에는 두 사람 간의 가장 최근 대화만 있어야 하기 때문에 기존에 한 대화가 있으면 이 테이블에서 지워야 합니다. (mysql은 replace 구문으로 깔끔하게 해결 됩니다. 다른 DB를 쓰시면 select 해봐서 있으면 지우고 insert 하는 식으로 하면 될겁니다.)

찾는 쿼리는 

SELECT b.* FROM ir_talk_main a LEFT JOIN talk_table b ON a.`talk_id` = b.`talk_id` 
WHERE a.`user` = 2 ORDER BY a.talk_id DESC;

와 같이 됩니다. 역시 깔끔하게 인덱스탑니다.


3. 마치며..

이 글이 비정규화에 대한 예찬론처럼 들릴까 걱정됩니다. 비정규화는 위험합니다. 게다가 하드 디스크도 많이 잡아먹습니다. (데이터 중복은 물론이고, 인덱스를 무지하게 걸어댄 테이블은 데이터 용량보다 인덱스 용량이 더 커지기도 합니다.)
정상적인 서비스에서 속도를 2배쯤 빠르게 하겠다고 비정규화를 하는 것은 별로 바람직한 생각이 아닙니다. 위에서 각종 trigger를 보여드린 것처럼 정규화 테이블에 변경이 일어날 때마다 비정규화 테이블에도 변화가 일어나야 하기 때문에 insert, update, delete 의 부하가 커집니다. 따라서 약간의 성능 향상을 위해서 비정규화했다가는 오히려 망가질 수도 있습니다.
수십배 이상의 속도 향상이 필요한 불가피한 경우를 제외하고는 별로 권장하진 않습니다.

by 삼실청년 | 2012/06/16 23:40 | 컴터질~ | 트랙백 | 덧글(3)

트랙백 주소 : http://iilii.egloos.com/tb/5648374
☞ 내 이글루에 이 글과 관련된 글 쓰기 (트랙백 보내기) [도움말]
Commented by 만두징 at 2012/07/14 02:07
정말 잘 봤습니다. ^^ 감사 드립니다.
Commented by 삼실청년 at 2012/07/24 18:34
보통은 다른 글들 참고해서 정리하는데, 이 글은 제가 걍 생각나는대로 쓴 거라... 틀린 거 있을까봐 무섭네요.ㅜㅜ 비정규화 문서들이 별로 없더라구요..
Commented at 2019/03/21 04:40
비공개 덧글입니다.

:         :

:

비공개 덧글

◀ 이전 페이지          다음 페이지 ▶