❤️🩹 정규화
ERD 내에서 중복 요소를 찾아 제거해 나가는 과정입니다. (중복된 데이터는 많은 문제를 일으키기 때문)
🃏 제 1 정규화
같은 성격과 내용의 데이터가 연속적으로 나타나는 컬럼이 존재할 때, 해당 컬럼을 제거하고 기본테이블의 PK를 추가하여 새로운 테이블을 생성하고, 기존의 테이블과 1:N 관계를 형성합니다.
tag 필드를 보면 값들이 여러 개 있기 때문에 1정규화를 한 것입니다.
"title"과 "tag"는 두 개의 테이블로 분리했을 때 M:N 관계가 됩니다. 하나의 title은 여러 개의 tag를 가질 수 있고, 하나의 tag는 여러 개의 title을 가질 수 있습니다.
M:N이므로 테이블 3개를 만들어서 1:N으로 만들어줘야 합니다. topic_tag_relation을 만들어 각 테이블을 이어줄 새로운 tag_id를 만듭니다.
🃏 제 2정규화
PK가 여러 키로 구성된 복합키로 구성된 경우가 2차 정규화의 대상이 됩니다.
복합키 전체에 의존하지 않고 복합키의 일부분에만 종속되는 속성들이 존재할 경우(부분적 함수 종속 관계) 이를 분리합니다.
1 정규화가 된 상태에서 보면 type 컬럼에서 "paper", "online"으로 다르고 price에서 각각 필드 값이 각기 달라 중복되는 레코드가 보입니다.
각기 다른 부분을 테이블로 나누고 중복 부분을 하나로 표현되게 하면 다음과 같이 됩니다.
type와 price 필드를 따로 빼서 topic_type 테이블을 생성한 것입니다.
🃏 제 3정규화
테이블의 키가 아닌 컬럼들은 기본키에 의존해야 하는데 실제로는 기본키가 아닌 다른 일반 컬럼에 의존하는 컬럼들이 있을 수 있습니다.
이를 이행적 함수 종속 관계라고 합니다.
author_id에 의존하는 컬럼들로 title에 의존하는 것처럼 보이지만 아닌 것.
그래서 PK(title)에 의존하지 않는 컬럼들을 분단합니다.
author 테이블을 만들고 중복되는 컬럼들 3개는 그대로 가져옵니다. 그러면 author_id가 외래키 관계로 맺어집니다.
❤️🩹 반정규화 (역정규화)
정규화를 통해 만든 표를 개발적 측면에서 성능이나 편의성을 위해서 되돌아가는 작업을 말합니다.
🃏 Join 줄이기
topic_tag_relation 테이블의 topic_title의 값이 MySQL이 태그의 이름을 알고 싶다고 해보자. 그러면 JOIN을 할 수 밖에 없습니다. (topic_tag_relation테이블과 tag테이블을 JOIN)
SELECT tag.name
FROM topic_tag_relation AS TTR
LEFT JOIN tag
ON TTR.tag_id = tag.id
WHERE topic_title = "MySQL";이런 쿼리가 서버 내에서 많이 일어난다면, join은 많은 부하가 일어나게 됩니다.
그래서 테이블을 하나로 합쳐버리면 됩니다. -> 정규화 하기 전 상태로 돌아가기.
SELECT tag_name
FROM topic_tag_relation
WHERE topic_title = "MySQL";그러면 조인없이 쿼리가 작성될 수 있습니다.
🃏 계산 작업 줄이기
count값이 필요해서 group by를 통해 컬럼을 묶고 조회한다고 해보자.
SELECT author-id, COUNT(author_id)
FROM topic
GROUP BY author_id;이러한 쿼리도 빈번하게 일어난다면 역시 부하가 일어날 수 있습니다. 따라서 그룹핑해서 내장함수를 사용하는 것이 아닌 topic_count 데이터를 컬럼 추가하는 것입니다.
UPDATE author SET topic_count = 2 WHERE id = 1;
UPDATE author SET topic_count = 1 WHERE id = 2;이렇게 역정규화된 표를 만들 수 있습니다. SELECT id, topic_count FROM author; 로 계산을 줄여서 쿼리를 작성할 수 있습니다.
유효한 데이터를 유지하기 위해서는 글이 추가될 때마다 지속적으로 데이터를 업데이트 해야 하는 관리를 해야합니다. 즉 저장된 데이터가 항상 정확하고 최신 상태여야 한다는 점입니다.
트리거를 사용하던지 서버 사이드에서 처리할 수 있습니다.
🃏 테이블 분리
이와 같이 행 or 컬럼을 기준으로 테이블을 분리할 수 있습니다.
만일 topic 테이블에 description의 용량이 엄청 많다면 topic 테이블을 자주 조회했을 때 약간 부하가 올 수 있습니다.
그래서 표의 성능을 위해 용량이 큰 테이블을 따로 분리하는 것입니다. 테이블을 나누면 여러 대의 서버에서 각기 다르게 접근해서 처리할 수 있으니 성능이 좋을 수 있습니다.
행을 기준으로 분리 하는 것은 author_id 회원이 몇만명으로 많을 때, 하나의 테이블에 접근해서 처리하는 것보다 테이블을 분담해서 author_id 가 1000이하일 경우 1500 이상일 경우 각기 다른 테이블로 접근해서 부하를 줄일 수 있습니다.
이것이 파티션 기법입니다.
🃏 외래키 줄이기
저자의 태그 아이디와 태그명을 조회한다고 해봅니다.
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;이렇게 2번의 조인으로 조회를 해야 합니다.
이것도 Join을 줄여서 지름길을 만듭니다. 외래키를 통해서 줄이는 방법입니다.
어느 저자의 tag.id와 tag.name을 조회한다고 가정했을 때, 3가지 필드 중 어느 저자인지 가 필요합니다. 이들을 조회하기 위해선 3개의 테이블을 Join해서 불러와야 하지만 join을 많이 사용하면 성능 저하의 가능성 때문에 topic_tag_relation 테이블에 author_id 컬럼을 추가하면 join을 한 번만 할 수 있습니다.
topic_tag_relation 테이블 외래키를 통해서 join을 줄여 역정규화 하는 쿼리
-- 테이블에 컬럼 추가하기, NULL 허용하고 author_id 컬럼이 tag_name 컬럼 뒤에 위치하도록 설정
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 ~
UPDATE topic_tag_relation SET ~
UPDATE topic_tag_relation SET ~역정규화 이후 쿼리는 다음과 같이 join이 하나 줄어듭니다.
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;