RDB Modeling - 6. 물리적 데이터 모델링, 역정규화

RDB Modeling (관계형 데이터베이스 모델링)

6. 물리적 데이터 모델링, 역정규화

6.1 - 물리적 데이터 모델링 소개

6.2 - 역정규화 소개

6.3 - 역정규화 - COLUMN을 조작해서 JOIN을 줄이기

6.4 - 역정규화 - COLUMN을 조작해서 계산을 줄이기

6.5 - 역정규화 - TABLE을 쪼개기

6.6 - 역정규화 - 관계의 역정규화

---

본 포스트는

생활코딩(https://opentutorials.org/)의

Database > 관계형 데이터 모델링(https://opentutorials.org/course/3883) 수업을 바탕으로

공부한 내용의 정리입니다.

---

<6.1 - 물리적 데이터 모델링 소개>

 

 

 

물리적 데이터 모델링

 

이전 단계인, 논리적 데이터 모델링이

관계형 데이터베이스에 어울리는 이상적인 표(TABLE)를 구성하는 Schema를 만드는 과정이었다면,

 

물리적 데이터 모델링은

이러한 TABLE들을

구체적인 Database 솔루션(제품)에 맞는 현실적인 TABLE들로 만드는 것.

 

특히, 이 단계에서 중요한 것은

성능.

 

 

 

성능을 향상시키려면?

 

일단 운영을 조금이라도 해보는 것.

Just do it.

 

data가 쌓이고 처리량이 많아져야

어디가 느려지고, 어디는 괜찮은지 분별이 생기니까.

 

적당한 시점에서 각 query의 성능을 평가해보고,

병목(bottleneck)현상이 생기는 곳을 집중적으로 해결하는 것이 바람직.

 

＊find slow query

　ㆍ여러가지 query 중에 특히 느려지는 query

　ㆍ찾는 방법은 DB 제품마다 다름

　ㆍ제품명과 본 키워드로 검색하여 방법을 찾아볼 수 있음

 

 

 

Denormalization(역정규화, 반정규화)

 

여러가지 방법을 적용해보고,

최후에는

정규화했던 이상적인 TABLE에

손을 대는 것.

 

정규형을 벗어나는 것(정규화에 반하는 설계)을 알지만,

성능 등의 어떤 이득을 목적으로 감행하는 행위.

 

역정규화는 혹독한 댓가를 치뤄야 함.

(e.g., TABLE의 유연성이 줄어들어 확장하기 어려운 고정된 형태가 될 수 있다.)

이러한 댓가를 지불할만한 것인지는 이루고자 하는 목적과 상황을 파악하여 결정.

따라서, 역정규화 이전에 다른 해결책들을 먼저 찾아보는 것이 바람직함.

 

 

 

먼저 찾아볼만한 방법들

 

ㆁIndex

　＊index는 ROW에 대한 읽기 성능(해당 ROW를 찾는 것)을 비약적으로 향상시킨다.

　＊대신, 쓰기 성능은 비관적으로 희생시킴.

　　ㆍindex가 걸려있다면, 쓰기가 이뤄질 때마다 입력된 정보를 정돈하기 위한 복잡한 연산과정이 필요함.

　　ㆍ이 과정에서 시간이 더 소요되고, 저장 공간도 더욱 차지.

　＊그럼에도 불구하고, 이렇게 정돈하면 매우 빠르게 읽을 수 있기 때문.

 

ㆁApplication

　＊Database를 이용하고 있는 Application 단계에서,

　　캐시와 같은 방법을 시도하는 것

　　ㆍDB에 access를 줄여 부하를 줄이는 것.

　　ㆍ당연히 캐시와 DB의 일관성을 유지해야하니, 이런 부분도 고려.

 

---

<6.2 - 역정규화 소개>

 

 

 

Denormalization(역정규화)

 

Normalization(정규화)을 통해서 만든 이상적인 표(TABLE)를,

성능이나 개발의 편의성 등 어떤 이득을 목적으로 구조를 바꾸는 것.

 

정규형을 벗어나는 것(정규화에 반하는 설계)을 알지만,

성능 등의 어떤 이득을 목적으로 감행하는 행위.

 

 

 

write vs read

 

＊정규화는 보통, 쓰기(저장)의 편리함(효율)을 위해 읽기의 성능을 희생함.

　ㆍ정규화를 진행하면, 보통 TABLE들이 여러 개로 나뉘게 됨.

　ㆍ이들을 다시 합쳐서 사용하려면 JOIN을 사용해야 하는데,

　　JOIN은 굉장히 비싼 작업임. 읽기의 성능이 희생됨.

　ㆍApplication을 운영하다보면, DB를 읽는 작업이 굉장히 자주 일어나는데,

　　정규화로 인해 성능이 느려지는 경우가 종종 있음.

　ㆍ다른 방법들을 시도해본 후, 최후의 수단으로 역정규화로 TABLE의 구조를 다시 바꾸게 됨.

 

＊정규화를 먼저 한 다음에, 성능을 시험해보고 필요에 따라 역정규화를 시도해보는 것.

　ㆍ처음부터 정규화를 하지 않은 TABLE이 좋은 것이라고 할 수 없음.

　ㆍ정규화를 한다고 해서 반드시 성능이 떨어지는 것도 절대 아님.

 

 

 

수업용 구글 스프레드시트

http://bit.ly/2WLMCko

 

 

 

Origin

 

author TABLE

id	name	profile
1	kim	developer
2	lee	dba

 

topic TABLE

title	description	created	author_id
MySQL	MySQL is ...	2011	1
ORACLE	ORACLE is ...	2012	1
SQL SERVER	SQL SERVER is ...	2013	2

 

tag TABLE

id	name
1	rdb
2	free
3	commercial

 

topic_tag_relation TABLE

topic_title	tag_id
MySQL	1
MySQL	2
ORACLE	1
ORACLE	3

 

topic_type TABLE

title	type	price
MySQL	paper	10000
MySQL	online	0
ORACLE	online	15000

 

 

 

SQL Code (MySQL)

 

DROP TABLE IF EXISTS `author`;

 

CREATE TABLE `author` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(45) DEFAULT NULL,

  `profile` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `author` VALUES (1,'kim','developer'),(2,'lee','DBA');

 

DROP TABLE IF EXISTS `tag`;

 

CREATE TABLE `tag` (

  `id` int(11) NOT NULL,

  `name` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `tag` VALUES (1,'rdb'),(2,'free'),(3,'commercial');

 

DROP TABLE IF EXISTS `topic`;

 

CREATE TABLE `topic` (

  `title` varchar(50) NOT NULL,

  `description` text,

  `created` datetime DEFAULT NULL,

  `author_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`title`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `topic` VALUES ('MySQL','MySQL is ...','2011-01-01 00:00:00',1),('ORACLE','ORACLE is ...','2012-02-03 00:00:00',1),('SQL Server','SQL Server is ..','2013-01-04 00:00:00',2);

 

DROP TABLE IF EXISTS `topic_tag_relation`;

 

CREATE TABLE `topic_tag_relation` (

  `topic_title` varchar(50) NOT NULL,

  `tag_id` int(11) NOT NULL,

  PRIMARY KEY (`topic_title`,`tag_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `topic_tag_relation` VALUES ('MySQL',1),('MySQL',2),('ORACLE',1),('ORACLE',3);

 

DROP TABLE IF EXISTS `topic_type`;

 

CREATE TABLE `topic_type` (

  `title` varchar(45) NOT NULL,

  `type` char(6) NOT NULL,

  `price` int(11) DEFAULT NULL,

  PRIMARY KEY (`title`,`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `topic_type` VALUES ('MySQL','online',0),('MySQL','paper',10000),('ORACLE','online',15000);

 

 

 

역정규화의 규칙?

 

＊정규화는 공정을 거쳐가듯이, 순차적으로 진행해왔음.

＊하지만, 역정규화는 어떤 규칙이 정해진 것이 아님.

　ㆍ상황을 판단해서 진행.

　ㆍ엄밀하게 정해진 공정이 아니라, 여러 기법들을 소개하는 일종의 샘플들.

　ㆍ법칙으로 받아들이기 보다는, 창의력을 발휘하여 상황에 맞게, 현명하게 활용.

 

---

<6.3 - 역정규화 - COLUMN을 조작해서 JOIN을 줄이기>

 

 

 

하고자 하는 DB access

 

topic 중 MySQL의 tag를 모두 알고싶다.

즉, topic_tag_relation.topic_title의 값이 MySQL인 tag_name을 모두 알고 싶다.

 

 

 

Origin - TABLE

 

tag TABLE

id	name
1	rdb
2	free
3	commercial

 

topic_tag_relation TABLE

topic_title	tag_id
MySQL	1
MySQL	2
ORACLE	1
ORACLE	3

 

 

 

Origin - SQL read code

 

SELECT tag.name

　FROM topic_tag_relation AS TTR

　　LEFT JOIN tag

　　　ON TTR.tag_id = tag.id

　WHERE topic_title = 'MySQL';

 

 

 

문제점

 

서비스를 운영하다보면, 이러한 조회가 굉장히 많이 일어날 수 있음.

(어떤 서비스냐에 따라 다름)

그런데, 이것으로 시스템에 굉장히 부하가 많이 일어나고,

사용자의 경험이 급격히 나빠지고 있다면, 방법이 필요함.

 

 

 

해결책

 

여러가지 방법이 있겠지만,

비용이 비싼 작업인 JOIN이 생략되도록,

topic_tag_relation을 조회하는 것만으로 tag.name을 알 수 있도록.

중복을 허용.

 

 

 

TABLE 수정 SQL query

 

ALTER TABLE topic_tag_relation

　ADD COLUMN tag_name VARCHAR(5) NULL AFTER tag_id;

 

UPDATE topic_tag_relation

　SET tag_name = 'rdb'

　WHERE tag_id = 1;

UPDATE topic_tag_relation

　SET tag_name = 'free'

　WHERE tag_id = 2;

UPDATE topic_tag_relation

　SET tag_name = 'commercial'

　WHERE tag_id = 3;

 

 

 

역정규화 결과 - TABLE

 

topic_tag_relation

topic_title	tag_id	tag_name
MySQL	1	rdb
MySQL	2	free
ORACLE	1	rdb
ORACLE	3	commercial

해당 COLUMN이 추가됨.

tag TABLE의 tag_name과 중복되는 저장이 생기지만,

JOIN 과정은 생략할 수 있음.

 

 

 

역정규화 결과 - SQL read code

 

SELECT tag_name

　FROM topic_tag_relation

　WHERE topic_title = 'MySQL';

 

 

이전 Origin 상태에서 JOIN을 한 것과 동일한 결과를 얻을 수 있음.

하지만 JOIN을 하지 않기 때문에 훨씬 더 빠르게 조회가 가능함.

(저장 측면에서는 중복이라는 비효율이 발생하지만, 읽기 편의성은 증가)

 

역정규화를 하면, 정규화 이전의 문제를 자진해서 끌어들이게 됨.

중복 등을 허용하여 두므로, 시스템의 복잡도가 증가함.

프로그램의 관리는 위험해지지만, 성능을 위해 감수.

 

---

<6.4 - 역정규화 - COLUMN을 조작해서 계산을 줄이기>

 

 

 

하고자 하는 DB access

 

각각의 저자가 몇 개의 글을 작성했는지 목록으로 표현한다.

 

 

 

Origin - TABLE

 

author TABLE

id	name	profile
1	kim	developer
2	lee	dba

 

topic TABLE

title	description	created	author_id
MySQL	MySQL is ...	2011	1
ORACLE	ORACLE is ...	2012	1
SQL SERVER	SQL SERVER is ...	2013	2

 

 

 

Origin - SQL read code

 

SELECT

　author_id, COUNT(author_id)

FROM topic

GROUP BY author_id;

 

 

 

문제점

 

만약, 이러한 query가 아주 빈번하다면,

GROUP BY도 비싼 작업이 될 수 있기 때문에(data가 많을 수록),

시스템의 부하를 가져올 수도 있다.

 

 

 

해결책

 

topic TABLE에 쓰기(삽입,수정,삭제)가 일어날 때,

미리 계산하여 topic_count를 author TABLE에 반영해주자.

(읽기에서 계산이 발생하지 않는 대신, 쓰기에서 추가적인 작업이 필요해진다.

만약 쓰기가 읽기보다 덜 자주 일어난다면 고려해볼만 하다.)

 

 

 

TABLE 수정 SQL query

 

ALTER TABLE author

　ADD COLUMN topic_count INT NULL AFTER profile;

 

UPDATE author

　SET topic_count = 2

　WHERE (id = 1);

UPDATE author

　SET topic_count = 1

　WHERE (id = 2);

 

 

 

역정규화 결과 - TABLE

 

author TABLE

id	name	profile	topic_count
1	kim	developer	2
2	lee	dba	1

해당 COLUMN이 추가됨.

DB에서 계산을 통해 얻을 수 있는 자료를 굳이 저장함으로써,

저장 측면의 효율성은 떨어지지만, 계산 작업을 생략할 수 있으므로 빠른 조회가 가능.

또한, 데이터를 쓸 때(삽입,수정,삭제), topic_count 역시 맞춰서 수정해야 하는 것을 명심해야 함. 무결성을 지켜야 함.

 

 

 

역정규화 결과 - SQL read code

 

SELECT

　id, topic_count

FROM

　author;

 

 

이전 Origin 상태에서 계산을 한 것과 동일한 결과를 얻을 수 있음.

하지만 계산을 하지 않기 때문에 훨씬 더 빠르게 조회가 가능함.

(쓰기 측면에서는 중복과 추가작업이 발생하지만, 읽기 편의성은 증가)

 

역정규화를 했을 때,

우리가 얻는 것과 잃는 것의 trade-off가 있고,

어느 쪽으로 비용을 옮긴 형태를 원하는지 여러 측면에서(속도, 저장용량, 복잡성, ...) 고려해봐야 함.

 

---

<6.5 - 역정규화 - TABLE을 쪼개기>

 

성능 향상을 위해,

하나의 TABLE을 여러 TABLE로 분리

 

여러 TABLE로 분리했으니,

각각 다른 서버로 분산시켜 운영할 수도 있음.

 

 

 

Origin

 

topic TABLE

title	description	created	author_id
MySQL	MySQL is ...	2011	1
ORACLE	ORACLE is ...	2012	1
SQL SERVER	SQL SERVER is ...	2013	2

 

---

[COLUMN을 기준으로 TABLE을 분리]

 

만약, decription의 내용이 아주 용량이 크고,

description을 제외한 나머지 내용을 조회하는 경우가 빈번할 때(더 권장),

또는 description을 포함해서 조회하는 경우가 빈번해도.. 저 description의 용량이 크면 고려.

 

 

 

문제점

 

DB에서 이번에 쓰이지 않는데도 + 매번 용량이 아주 큰 값까지 함께 가져오느라 속도가 느려진다거나,

어쨌든 용량이 아주 큰 값을 매번 함께 가져오느라 속도가 느려짐.

 

 

 

해결책

 

용량이 큰 값은 별개의 TABLE로 분리한다.

식별자가 중복될 것이고,

같은 식별자임에도 TABLE이 따로 존재할 수 있지만,

DB에 요구되는 작업의 종류와 빈도에 따라 속도는 향상될 수 있다.

 

 

 

역정규화 결과

 

topic TABLE

title	created	author_id
MySQL	2011	1
ORACLE	2012	1
SQL SERVER	2013	2

description COLUMN이 분리되어 나감.

 

 

topic_description TABLE

title	description
MySQL	MySQL is ...
ORACLE	ORACLE is ...
SQL SERVER	SQL SERVER is ...

분리한 COLUMN으로 TABLE을 생성.

기존 기본키와 같은 기본키를 사용.

 

 

식별자가 중복되고,

같은 식별자임에도 TABLE이 따로 존재하지만,

DB에 요구되는 작업의 종류와 빈도에 따라 속도는 향상될 수 있다.

 

description을 제외한 다른 값들이 필요할 때 속도가 크게 향상될 것이고,

Application에서 description은 캐싱 기법을 사용한다든가..

두 TABLE을 다른 서버에 저장하여 분산시킬 수 있다든가..

(어렵고 유지하기도 힘들지만..;;)

 

---

[ROW를 기준으로 TABLE을 분리]

 

만약, 저장된 데이터(ROW)가 아주 많아서

조회에 시간이 걸린다면 고려.

 

 

 

문제점

 

ROW가 너무 많아서 조회에 시간이 오래 걸릴 수 있다.

TABLE에서 어떤 기준에 따라 일부분만 필요한데도, 어쨌든 전체 ROW 중에 조건을 걸어야 하니..

 

 

 

해결책

 

차라리 0~1000 / 1001~2000 / 2001 ~ 3000 처럼

ROW들을 어떤 기준에 따라 다른 TABLE에 저장되도록.

필요한 기준에 맞춰서 그 TABLE에서만 조회한다면 속도가 향상될 것.

 

※ 기준은 이론적으로는 한계가 없으나, 관리가 굉장히 어려움.

 

 

 

역정규화 결과

 

topic_1000

title	description	created	author_id
MySQL	MySQL is ...	2011	1
ORACLE	ORACLE is ...	2012	1

 

topic_1500

title	description	created	author_id
SQL SERVER	SQL SERVER is ...	2013	1500

 

 

ROW들을 어떤 기준에 따라 다른 TABLE에 저장되도록.

필요한 기준에 맞춰서 그 TABLE에서만 조회한다면 속도가 향상될 것.

물론, 저장 역시도 기준에 맞춰서 그 TABLE에 제대로 할 수 있도록.

 

하지만 생각해보면,

topic에 연결되는 다른 TABLE도 많은데,

기존 topic TABLE이 건재하다면, 분할 TABLE과의 공통성(무결성)이 보장되어야 하며,

기존 topic TABLE이 없이 분할 TABLE만으로 운영한다면, 연결하는 TABLE은 또 어떻게 연결될 수 있을지 고려해야 한다.

관리가 어렵다.

(거의 최후의 보루인 느낌;; 여러가지 솔루션과 방법론으로 정교하고 안전하게 처리해야 함.)

 

---

<6.6 - 역정규화 - 관계의 역정규화>

 

지름길을 만든다.

JOIN을 줄여서.

 

위의 COLUMN을 조작하여 JOIN을 줄이는 방식과의 차이점은,

위의 방식은 일반적인 COLUMN을 추가하지만,

(결과 자체를 추가해버림. JOIN을 아예 없앨 수도 있음.)

 

여기서는 외래키(FK)를 추가함으로써 JOIN을 줄이는 기술.

(FK가 존재하므로 JOIN이 아예 없진 않으나, 줄어듦.)

 

 

 

하고자 하는 DB access

 

저자의 tag_id와 tag_name을 조회한다.

 

 

 

Origin - TABLE

 

topic TABLE

title	description	created	author_id
MySQL	MySQL is ...	2011	1
ORACLE	ORACLE is ...	2012	1
SQL SERVER	SQL SERVER is ...	2013	2

 

tag TABLE

id	name
1	rdb
2	free
3	commercial

 

topic_tag_relation TABLE

topic_title	tag_id
MySQL	1
MySQL	2
ORACLE	1
ORACLE	3

 

 

 

Origin - SQL read code

 

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;

 

 

 

문제점

 

비용이 비싼 작업인 JOIN이 여러 번 행해짐.

 

 

 

해결책

 

여러 TABLE을 JOIN해야하는 것을 좀 줄이기 위해,

같은 식별자를 쓰는 TABLE의 FK COLUMN을 복사해옴.

(해당 TABLE은 이제 JOIN하지 않아도 됨)

(하지만 복사해온 FK를 사용하는 JOIN은 행해짐)

 

 

 

TABLE 수정 SQL query

 

ALTER TABLE topic_tag_relation

　ADD COLUMN author_id INT NULL AFTER tag_id;

 

ALTER TABLE topic_tag_relation

　ADD CONSTRAINT FK_author_id

　FOREIGN KEY (author_id)

　REFERENCES author (ID);

 

UPDATE topic_tag_relation

　SET author_id = 1

WHERE

　(topic_title = 'MySQL');

UPDATE topic_tag_relation

　SET author_id = 1

WHERE

　(topic_title = 'ORACLE');

 

ALTER TABLE topic_tag_relation

　MODIFY COLUMN author_id INT NOT NULL;

 

 

 

역정규화 결과 - TABLE

 

topic_tag_relation TABLE

topic_title	tag_id	author_id
MySQL	1	1
MySQL	2	1
ORACLE	1	1
ORACLE	3	1

topic TABLE의 author_id와 중복이 발생함.

저장 효율성도 떨어지고, 무결성을 지키는 데 더 신경써야 하고, 복잡해지지만,

(topic TABLE의 title-author_id와 이 TABLE의 topic_title-author_id가 동일하도록 유지해야 한다.)

JOIN을 줄일 수 있음.

 

 

 

역정규화 결과 - SQL read code

 

SELECT

　tag.id, tag.name

FROM

　topic_tag_relation AS TTR

LEFT JOIN tag

　ON TTR.tag_id = tag.id

WHERE author_id = 1;

 

JOIN을 한 단계 줄였음.