관계형 데이터 모델링 (3)

7. 물리적 데이터 모델링

- 논리적 데이터 모델링이 관계형 데이터베이스 패러다임에 잘 맞는 이상적인 표를 만드는 것이었다면, 물리적 데이터 모델링은 선택한 데이터베이스 제품에 맞는 현실적인 고려는 하는 방법론

- 이 단계에서 가장 중요한 것은 성능입니다. 특히 역정규화(반정규화, denormalization)의 사례를 집중적으로 다룸

1) 물리적 데이터 모델링

- find slow query => 느려지는 지점 찾을 수 있음 -> 성능 향상 위한 방법 적용

- denormalization(역정규화, 반정규화)

- index : 읽기 편하나 쓰기 어려움(연산 과정 -> 시간, 저장 공간 많이 듦)

- application : 캐시 사용(입력에 따른 실행결과 저장 -> 데이터베이스 부하 감소)

- 다양한 방법 시도해본 후에 마지막으로 역정규화를 통해 표의 구조를 바꿈

 

2) 역정규화 소개

- denormalization(역정규화, 반정규화) : 정규화를 통해서 만든 이상적인 표를 성능이나 개발 편의성을 위해서 구조를 바꾸는 것

- 정규화 : 쓰기의 편리함을 위해서 읽기의 성능을 희생

- 정규화로 인해 속도가 느려지는 경우 발생 -> 최후의 수단 - 역정규화

 

http://bit.ly/2WLMCko

denormalization - http://bit.ly/2WLMCko

- 역정규화

(1) 하나의 표 안에서 column을 바꿈

(2) 하나의 표를 여러개의 표로 쪼개는 것

(3) 테이블과 테이블 사이의 관계성을 조작해서 일종의 지름길을 만듦

 

3) 역정규화 : 컬럼을 조작해서 join을 줄이기

- 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');

 

SELECT tag_name FROM topic_tag_relation WHERE topic_title = 'MySQL';

 

4) 역정규화 : 컬럼을 조작해서 계산을 줄이기

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

- 역정규화 이전 쿼리

SELECT author_id, COUNT(author_id) FROM topic GROUP BY author_id;

 

- 테이블 변경 쿼리

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');

 

SELECT id, topic_count FROM author;

 

5) 역정규화 : 표를 쪼개기

(1) 테이블의 역정규화 - 컬럼을 기준으로 테이블을 분리

topic_description

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

-> 최후의 수단으로 사용

 

(2) 테이블의 역정규화 - 행을 기준으로 분리

topic_1000

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

-> 이론적으로는 문제가 없으나 관리하기 힘듬

 

- 테이블의 역정규화는 여러대의 서버로 분산할 수 있을 때 사용

 

6) 역정규화 : 관계의 역정규화

- 지름길을 만든다

- 목표 : 저자의 태그 아이디와 태그명을 조회한다.

- 역정규화 이전 쿼리

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;

 

- 역정규화 이후 쿼리

ALTER TABLE `topic_tag_relation` ADD COLUMN `author_id` INT NULL AFTER `tag_name`;
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'MySQL') and (`tag_id` = '2');
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '1');
UPDATE `topic_tag_relation` SET `author_id` = '1' WHERE (`topic_title` = 'ORACLE') and (`tag_id` = '3');

 

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;