상호작용 데이터모델링

데이터운영팀은 데이터모델링과 DBMS에 관련된 주제를 기술적 깊이에 상관없이, 정기적은 아니지만 지속적으로 공유할 예정입니다. 첫 번째 주제는 상호작용 데이터모델링이라고 제목을 붙여봤습니다. 때늦은 감이 없지 않지만, 앞으로 더 많이 고민하고 다루어져야 될 주제이기에 다루어 보겠습니다.

오늘의 아침에 일어나서 출근 전까지 저의 행동들을 가만히 돌아보면...

아침에 일어나서 벌써 전쟁터인 거실을 둘러보고 두 아이에게 잘 잤냐고 인사를 합니다.

두 아이는 마음속으로는 엄청 반가워하면서도 애써 시크하게 눈길 한번 주지 않네요... (참으면 병 생기는데... 이그 아빠가 니네 마음 다 알어...)

아내에게도 인사를 하려했지만 전쟁터 속에서도 곤히 자고 계십니다.

출근을 위해 기다리는 버스정류장에서 스마트폰 게임을 하며 친구들에게 게임 지원을 해줍니다. 이놈에게는 고블린 두마리를... 저놈에게는 감전 마법을....

그리고 다른 놈에게는 눈물나게 아까운 마법사를 몇 번 고민하다가 이런 걸 왜 요청하냐고 속으로 욕하면서 지원합니다.

의도했든 의도하지 않았든 아침 출근하는 그 짧은 시간에도 우리는 타인과 관계를 맺고 상호 작용을 하게 됩니다.

오늘의 주제는 나와 상대방의 상호작용을 어떻게 데이터모델에 표현하고 DB로 구현할 수 있는가 입니다.

[그림 1] 우리는 끊임없이 타인과 상호작용을 합니다. [저작자] freebie.photography [이미지출처] http://www.freeimageslive.co.uk/free_stock_image/network-concept-jpg

쉽게 설명하기 위해 여대생과 이쁜 언니들이 난무하여 부끄럽긴 하지만 네이버 쪽지 서비스를 예로 들겠습니다.

한게임 쪽지를 예로 들려고 했으나 수년간 주고받은 쪽지가 2013년에 받은 공지사항 외엔 없네요 ㅠㅠ

[그림 2] 예로 활용하기엔 내용이 부족한 한게임 받은 쪽지함 화면

[그림 3]은 네이버 받은 쪽지함의 내용입니다. 캡쳐해서 올리니 더 부끄럽네요... 왜 이런 쪽지들만 오는 건지...

제 마음을 어떻게 읽고 이런 맞춤형 쪽지를 보내주는 것인지... 쪽지 알파고라도 개발한 건지... (농담이니 불쾌하시다고 신고하시기 없습니다 불안불안... 사실 사례를 만들기 위해 5년간 수집한 겁니다..)

[그림 3] 네이버 받은쪽지함 UI 화면

[그림 3] 화면을 ERD로 표현하면 아마 [그림 4]와 같은 엔티티가 도출될 겁니다. (편의를 위해 개인쪽지/카페단체 등의 구분은 생략했으며 또 ERD에는 표현하지 않았지만 auto_increment 일련번호로 pk를 생성했다고 가정하겠습니다.)

[그림 4] 받은쪽지함 화면으로부터 설계한 ERD

목록을 표시하기 위한 쿼리를 한번 날려봅시다. 각각 받은쪽지함 / 보낸쪽지함 쿼리를 작성해 보겠습니다.

[그림 5] 받은쪽지함 데이터를 가져오는 쿼리

인덱스는 (받은사람ID, 등록일시)를 사용하겠네요.

한 편 보낸쪽지함에는 목록에 받은날짜라는 항목이 하나 더 있습니다.

[그림 6] 보낸쪽지함 UI 화면

모델을 다음과 같이 수정했고 [그림 5]의 받은쪽지함 쿼리도 변경했습니다.

[그림 7] 보낸쪽지함 화면을 보고 수정한 ERD

[그림 8] 보낸쪽지함 데이터를 가져오는 쿼리

인덱스는 (보낸사람ID, 보낸일시)를 사용하겠네요.

여기까지는 아무런 문제가 없습니다.

---

더 좋은 서비스들을 내놓기 위하여 기획자들은 늘 고민합니다. 그러다가 문득 이런 생각을 하게 됩니다. 받은쪽지함, 보낸쪽지함을 구분하지 말고 주고받은쪽지함과 같이 한 통에 보여주면 어떨까?

나중에 메일, 블로그랑 통합해서 내 소식이란 서비스를 만들어야지...

[그림 9] 아이디어가 반짝 [저작자] Freepik [이미지출처] http://www.freepik.com/free-vector/light-bulb-icon_766476.htm

개발자는 No problem이라고 말하지만 곧 절규에 빠지게 됩니다.

[그림 10] 「개발자 - 절규」 (2016) 156x193 px, 캔버스에 합성

개발자가 고통스러워하는 이유를 한번 살펴볼까요? 이해를 쉽게 하기 위해 쿼리를 작성해 보겠습니다. 위 [그림 5], [그림 8]에서 작성한 받은쪽지함/보낸쪽지함의 쿼리를 UNION ALL 한다면 쉽게 답이 나옵니다. 아마 시행착오 끝에 다음 쿼리가 나오게 될 겁니다.

SELECT 상대방ID, 쪽지내용, 보낸일시, 읽은일시
FROM (
    SELECT 보낸사람ID AS 상대방ID, 쪽지내용, 보낸일시, 읽은일시
    FROM 쪽지
    WHERE 받은사람ID = '나'
    -- ORDER BY 받은일시 DESC -- (밖에서 ORDER BY를 하므로 오버헤드)
    
    UNION ALL
    
    SELECT 받는사람ID AS 상대방ID, 쪽지내용, 보낸일시, 읽은일시
    FROM 쪽지
    WHERE 보낸사람ID = '나'
    -- ORDER BY 보낸일시 DESC -- (밖에서 ORDER BY를 하므로 오버헤드)
)
ORDER BY 보낸일시 DESC

그러나 결과는 나오겠지만 데이터가 늘어나면 늘어날수록 위 쿼리가 느려지는 걸 경험할 수 있을 겁니다.

이유는 인덱스 때문입니다.

UNION ALL 윗부분의 쿼리는 (받은사람ID, 보낸일시) 인덱스를 선택합니다. UNION ALL 아랫부분의 쿼리는 (보낸사람ID, 보낸일시) 인덱스를 선택합니다.

UNION ALL 바깥부분, 즉, FROM 절의 inline view 형태로 구성된 테이블을 ORDER BY하는 경우 인덱스를 사용할 수 없으므로 full scan을 하고 sort를 해야 합니다. 플랜을 떠 보면 file sort, temp table 등의 구문을 확인할 수 있습니다.

서비스가 잘 되어 불특정 다수의 사용자가 주고받은 쪽지 목록 쿼리가 계속 들어온다면 FILE SORT가 많아지므로 CPU가 증가하고 IOWAIT이 발생하고 전체 DB가 느려지면서... 아마 장애로 이어질 겁니다.

더군다나 보낸쪽지 / 받은쪽지가 많은 heavy user일수록 인덱스를 타지 않고 file sort를 해야 하는 row 건수가 많아지므로 더 느려지는 구조임을 쉽게 예측할 수 있습니다.

성능 문제로 모델 구조를 바꾸지 않고서는 답이 없는 쿼리입니다. 아니면 기획자의 의지를 꺾으시거나...

---

모델을 다음과 같이 변경하면 어떨까요?

[그림 11] 개선한 ERD

보낸사람ID / 받는사람ID 대신 나와 상대방ID 개념을 도입하여 보내고 받는 행위를 수발신구분코드로 변경하면 [그림 12]와 같은 깔끔한 쿼리를 얻을 수 있습니다.

[그림 12] 개선한 ERD를 토대로 작성한 받은쪽지함, 보낸쪽지함, 주고받은쪽지함 데이터 선택 쿼리

이때 중요한 것은 데이터를 항상 pair로 유지해야 한다는 점입니다.

내가 쪽지 한번 보낼 때마다 (나, 상대방, 발신), (나, 상대방, 수신) 두 건의 데이터를 넣어야 합니다.

두 건씩 넣으면 쪽지 내용이 긴 경우 너무 낭비가 심한 것이 아니냐 하는 질문이 나올 수 있습니다.

이를 위하여 쪽지 테이블을 쪽지와 쪽지 내용으로 분리하고 쪽지에는 pair로 두 건, 쪽지 내용에는 내용만 분리하여 1건만 넣는 것을 고려할 수 있습니다.

[그림 13] 쪽지와 쪽지 내용을 분리한 ERD

이런 형태의 모델은 주변에서 쉽게 찾을 수 있습니다.

가족관계증명서, SMS 메세지, 메일, 카톡/라인 등 SNS메세지, 선물함, FILE 공유, 친구관리, 블로그 이웃관리 등

나와 상대방이 인터랙션 하는 구조에서는 언제나 나타날 수 있는 형태이므로 한번 이해한다면 두고두고 써먹을 수 있을 것 같네요.

이상으로 상호작용 데이터모델링 글을 마칩니다.

다음에 기회가 되면 상호작용 모델에서 샤딩을 어떻게 해야 하는지에 대한 고민을 공유해 보겠습니다.

긴 글 읽어주셔서 감사합니다.