물리적 데이터 모델링과 역정규화
물리적 데이터 모델링은 논리적 데이터 모델링 단계까지 설계한 데이터 모델을 실제로 구현하는 단계를 의미한다. 이제부터는 실제 데이터베이스의 성능이 중요해진다.
데이터베이스의 성능을 확인할 수 있는 지표는 다양하지만, 그 중 하나는 ‘Slow Query’가 발생하는 지점을 찾는 것이다. ‘MySQL find slow query’ 등으로 검색하면, 다양한 슬로우 쿼리를 찾기 위한 도구들과 방법들이 나타난다. 슬로우 쿼리를 발견했다면 이를 개선시킬 방법을 찾아야 한다. 여기서는 ‘역정규화’, 즉 정규화됐던 표를 성능적 이점을 얻기 위해 역정규화하는 방법을 소개한다.
그러나, 역정규화는 기본적으로 혹독한 대가를 치르게 한다. 역정규화를 함으로써 치르게 되는 대가를, 지불할 만 한가 고민해야 한다. 역정규화하기 이전에, 미리 다양한 데이터베이스 성능을 개선시킬 방법들을 찾아야 한다.
가장 먼저 고민해야 할 수단은 바로 인덱스(Index)이다. 인덱스는 읽기 성능을 비약적으로 향상시킨다. 그러나 인덱스는 쓰기 성능을 비관적으로 악화시킨다. 보통은 데이터베이스에서 쓰기 작업을 하는 테이블보다, 읽기 작업을 하는 테이블의 경우가 훨씬 더 많기 때문에, 많은 읽기에 관한 성능적 문제들은 인덱스를 통해서 해결된다.
그러나 인덱스는 쓰기 작업에서의 속도를 희생시키고, 인덱스를 저장할 공간까지 추가로 필요하기 때문에 신중히 고려해야 한다.
데이터베이스가 아닌 어플리케이션 관점에서는, 캐시(Cache)를 고려해볼 수 있다. 미리 필요한 데이터를 캐싱해두어서, 데이터베이스를 거치지 않고도 바로 클라이언트에게 즉각적인 응답을 보낼 수 있도록 하는 기술이다. 데이터베이스에 걸리는 부하를 획기적으로 줄여줄 수 있다.
여러 방법을 시도해봤음에도 성능 향상의 폭이 크지 않다면, 이때는 역정규화(Denormalization)를 고민해봐야 한다.
역정규화(Denormalization)란
정규화는 대체로, 쓰기의 편리함을 위해서 읽기의 성능을 희생하는 행동이다. 정규화를 통해서 테이블을 쪼개면, 읽기를 위해서는 JOIN을 통해서 테이블을 합쳐야 하기 때문이다. 그러나 JOIN은 비싼 연산이다.
데이터는 대부분의 상황에서 쓸 때보다 읽을 때가 더 많다. 여러 가지 시도를 해보고서, 읽기 성능이 개선되지 않는다면 최후의 수단으로 테이블의 구조를 다시 바꾸는 역정규화를 하게 된다.
역정규화는 신중히 고려해야 하지만, 그러나 필요할 때는 매우 과감히 역정규화를 선택하는 것도 중요하다.
컬럼의 역정규화 - 컬럼 중복하기 : JOIN 줄이기
JOIN을 줄이기 위한 방법이다. 성능을 높이는데 가장 먼저 생각해볼 방법은 비싼 연산 중 하나인 JOIN 을 줄이는 것이다.
아래와 같은 3NF를 만족하는 정규형 테이블이 있다고 해보자.
tag
| id | name |
|---|---|
| 1 | rdb |
| 2 | free |
| 3 | commercial |
topic_tag_relation
| topic_title | tag_id |
|---|---|
| MySQL | 1 |
| MySQL | 2 |
| ORACLE | 1 |
| ORACLE | 3 |
topic_tag_relation에서, topic_title이 “MySQL”인 태그의 name을 알고 싶다고 가정하자. 위와 같이 정규화가 돼있을 때, 우리는 아래와 같은 쿼리문으로 데이터를 요청할 수 있다.
SELECT
tag.name
FROM topic_tag_relation AS TTR
LEFT JOIN tag
ON TTR.tag_id = tag.id
WHERE TTR.topic_title = 'MySQL';| name |
|---|
| rdb |
| free |
일반적인 방법이긴 하나, 위 방법의 문제점은 JOIN의 사용에 있다. JOIN은 SQL 연산 중에서도 상당히 비싼 연산에 속한다. 만약에 위 단계에서 JOIN을 없앨 수 있다면, 성능은 더 올라갈 것이다. 즉 tag_id에 해당되는 tag.name을 그냥 한 표에 집어넣는 방법이 있다. 이때 중복은 허용하게 하지만, JOIN을 하지 않고 바로 표 내에서 원하는 정보를 검색할 수 있다.
-- 역정규화를 만드는 쿼리
ALTER TABLE `topic_tag_relation` ADD COLUMN `tag_name` VARCHAR(45) NULL AFTER `tag_id`;
UPDATE `topic_tag_relation` SET `tag_name` = 'rdb' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `tag_name` = 'free' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '2');
UPDATE `topic_tag_relation` SET `tag_name` = 'rdb' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `tag_name` = 'commercial' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '3');topic_tag_relation
| topic_title | tag_id | tag_name |
|---|---|---|
| MySQL | 1 | rdb |
| MySQL | 2 | free |
| ORACLE | 1 | rdb |
| ORACLE | 3 | commercial |
-- 역정규화 이후 JOIN이 사라진 쿼리
SELECT tag_name FROM topic_tag_relation WHERE topic_title = 'MySQL';컬럼의 역정규화 - 파생 컬럼의 형성 : 계산 작업 줄이기
아래와 같은 3NF를 만족하는 테이블에서, 각 author_id 별로 몇 개의 글을 작성했는지 목록으로 표현한다고 해보자.
topic
| title | description | created | author_id |
|---|---|---|---|
| MySQL | MySQL is ... | 2011 | 1 |
| ORACLE | ORACLE is ... | 2012 | 1 |
| SQL SERVER | SQL SERVER is ... | 2013 | 2 |
SELECT
author_id,
COUNT(author_id) AS "게시물 개수"
FROM topic
GROUP BY author_id;결과:
| author_id | 게시물 개수 |
|---|---|
| 1 | 2 |
| 2 | 1 |
위 방법의 문제점은, 만약에 게시물의 개수가 상당히 많을 때, 그리고 위 쿼리문을 상당히 자주 호출해야 할 때 발생하게 된다. 매번 쿼리를 요청할 때마다 게시물 개수를 연산하기 위한 COUNT 연산이 계속 발생하니깐, 게시물의 수가 많다면 분명 성능적인 면에서 문제가 발생할 것이다.
이를 효율적으로 처리하기 위해서, author 테이블에 파생 컬럼으로 topic_count를 추가하여, COUNT 연산을 거치지 않고도 바로 총 게시물의 수를 저장하는 방법이 있다.
author
| id | name | profile | topic_count |
|---|---|---|---|
| 1 | kim | developer | 2 |
| 2 | lee | dba | 1 |
그렇게 되면 표 안에서 바로 몇 개의 게시물을 작성했는지 추적할 수 있다.
SELECT
id,
topic_count AS "게시물 개수"
FROM author;
-- GROUP BY 연산을 생략하기에 매우 빠른 쿼리 성능테이블의 역정규화 - 컬럼을 기준으로 테이블을 분리하기(수직 파티셔닝과 샤딩)
topic
| title | description | created | author_id |
|---|---|---|---|
| MySQL | MySQL is ... | 2011 | 1 |
| ORACLE | ORACLE is ... | 2012 | 1 |
| SQL SERVER | SQL SERVER is ... | 2013 | 2 |
topic 테이블에서, description의 용량이 매우 크다고 가정해보자. 그리고 description을 제외한 컬럼을 조회하는 연산, description을 포함하여 조회하는 연산이 둘 다 많다고 가정해보자.
이때는 과감히 용량이 큰 description 테이블을 분리해내는 것도 좋은 방법이다.
| title | created | author_id |
|---|---|---|
| MySQL | 2011 | 1 |
| ORACLE | 2012 | 1 |
| SQL SERVER | 2013 | 2 |
| title | description |
|---|---|
| MySQL | MySQL is ... |
| ORACLE | ORACLE is ... |
| SQL SERVER | SQL SERVER is ... |
일반적으로 한 행에 들어갈 수 있는 최대 용량은 65,535 바이트이다. description의 사이즈가 이와 근접해간다면, 다른 행들의 정보 저장에 영향을 주지 않기 위해서 별도의 테이블로 분리하는 것도 좋은 방법이다(TEXT 자료형을 쓰지 않는다고 가정)
샤딩 (Sharding)
한편 이렇게 테이블을 개별로 분리하면, 테이블을 별도의 연산 컴퓨터로 분리해서 저장할 수 있다. 그렇게 되면 description만을 조회하는 작업, created와 author_id 만 조회하는 작업을 각각 다른 머신에서 수행할 수 있으니, 이에 따른 최적화도 기대할 수 있다고 한다. 이를 샤딩(Sharding)이라고 한다.
테이블의 역정규화 - 행을 기준으로 테이블을 분리하기(수평 파티셔닝)
topic
| title | description | created | author_id |
|---|---|---|---|
| MySQL | MySQL is ... | 2011 | 1 |
| ORACLE | ORACLE is ... | 2012 | 1 |
| SQL SERVER | SQL SERVER is ... | 2013 | 2 |
topic 테이블의 행이 1억 개, 10억 개 정도 된다고 가정하자. author_id도 1억 명, 10억 명 이렇게 무수히 많다고 가정해보자. 이때는 행을 기준으로 분리하는 것도 성능을 최적화할 수 있는 하나의 방법이 된다.
예를 들어 author_id가 1번부터 1000번까지는 topic_to_1000 테이블에 저장해놓고, author_id가 1001번부터 2000번까지는 topic_to_2000 테이블에 저장해놓는다고 가정해보자.
topic_to_1000
| title | description | created | author_id |
|---|---|---|---|
| MySQL | MySQL is ... | 2011 | 1 |
| ORACLE | ORACLE is ... | 2012 | 1 |
| SQL SERVER | SQL SERVER is ... | 2013 | 2 |
topic_to_2000
| title | description | created | author_id |
|---|---|---|---|
| MySQL | MySQL is ... | 2011 | 1001 |
| SQL SERVER | SQL SERVER is ... | 2013 | 1999 |
관계의 역정규화
JOIN을 줄이기 위해 지름길을 만드는 것을 의미한다. 처음에 나온 Column의 역정규화와 비슷한데, FK를 추가해서 JOIN을 줄이는 테크닉이다.
topic
| title | description | created | author_id |
|---|---|---|---|
| MySQL | MySQL is ... | 2011 | 1 |
| ORACLE | ORACLE is ... | 2012 | 1 |
| SQL SERVER | SQL SERVER is ... | 2013 | 2 |
tag
| id | name |
|---|---|
| 1 | rdb |
| 2 | free |
| 3 | commercial |
topic_tag_relation
| topic_title | tag_id |
|---|---|
| MySQL | 1 |
| MySQL | 2 |
| ORACLE | 1 |
| ORACLE | 3 |
위 3NF를 만족하는 테이블에서, author_id가 1인 저자의 태그 아이디와 태그명을 조회한다고 하자.
이때 문제는, author_id는 topic 테이블에,tag_id는 topic_tag_relation에, 그리고 tag.name은 tag 테이블에 있기 때문에, topic, tag, topic_tag_relation 이 세 테이블을 모두 JOIN 해야 하는 문제가 발생한다.
SELECT
tag.id,
tag.name
FROM topic_tag_relation AS TTR
LEFT JOIN tag ON TTR.tag_id = tag.id
LEFT JOIN topic ON TTR.topic_title = topic.title
WHERE author_id = 1;| id | name |
|---|---|
| 1 | rdb |
| 2 | free |
| 1 | rdb |
| 3 | commercial |
만약 이 JOIN으로 인해서 빈번히 성능 저하 문제가 발생하면, JOIN 을 한 번 줄이기 위해 topic_tag_relation에 FK로 author_id에 대한 정보를 추가할 수도 있다.
topic_tag_relation
| topic_title | tag_id | author_id |
|---|---|---|
| MySQL | 1 | 1 |
| MySQL | 2 | 1 |
| ORACLE | 1 | 1 |
| ORACLE | 3 | 1 |
SELECT
tag.id, tag.name
FROM topic_tag_relation AS TTR
LEFT JOIN tag ON TTR.tag_id = tag.id
WHERE TTR.author_id = 1위와 같이 JOIN이 한 번 줄어든 것을 볼 수 있다.
📚 핵심 정리
슬로우 쿼리 등 성능 문제를 해결하기 위해, 정규화된 테이블을 다시 바꾸는 과정을 역정규화라 합니다. 비싼 연산인 JOIN, GROUP BY 등을 줄이기 위해 중복을 허용하고 테이블을 합친다든지, FK를 넣는다든지, 혹은 파생 컬럼을 생성한다든지의 방법들이 있습니다. 또한 샤딩, 파티셔닝을 위해 테이블을 쪼개는 방법도 있습니다.
Reference
- 관계형 데이터 모델링, 생활코딩, 2019