ERD 설계 이후 개발하면서 반정규화 하기

ERD 설계 시, 최대한 정규화를 지키려고 노력하고 함께 많이 조회될 데이터라면 반정규화를 하는 편인 것 같다. 대부분 조인을 통해 계산을 해야 하는 경우, 칼럼을 하나 추가하여 미리 계산하고 데이터를 저장하고, 이후 해당 계산된 값을 조인 없이 조회하여 사용했다.

 

따라서 마이브러리 프로젝트 ERD를 설계 할때 아래 데이터는 반정규화 하여 ERD를 설계하고 코드를 작성하였다.

• holder_count : 해당 도서를 소장한 사람의 수
• read_count : 해당 도서를 완독한 사람의 수
• review_count : 해당 도서의 리뷰 수
• 등등

따라서, 조회를 할 때는 조인 없이 데이터를 조회할 수 있었지만 리뷰를 쓰거나, 소장/완독 처리 시에는 추가 로직을 작성했었다.

예를 들어, 아래 코드와 같이 하나의 도서를 소장 도서로 등록할 때나 소장 도서에서 삭제할 때, Book 엔티티 내 holder_count를 수정하는 추가 로직이 필요로 했다.

 

public MyBook create(MyBookCreateServiceRequest request) {

    Book book = bookReadService.getRegisteredBookByISBN13(request.getIsbn13());
    checkBookAlreadyRegisteredAsMyBook(request.getUserId(), book);

    book.increaseHolderCount(); // 반정규화로 인한 추가 로직
    MyBook myBook = MyBook.of(book, request.getUserId());
    return myBookRepository.save(myBook);
}

public void deleteMyBook(MyBookDeleteServiceRequest request) {

    MyBook myBook = myBookRepository.getMyBookWithBookAndReviewUsingFetchJoin(request.getMybookId())
            .orElseThrow(MyBookNotFoundException::new);

    if (isOwnerSameAsRequester(myBook.getUserId(), request.getLoginId())) {
        throw new MyBookAccessDeniedException();
    }

    adjustBookPropertyWhenMyBookDeleted(myBook); // 반정규화로 인한 추가 로직
    myBookRepository.delete(myBook);
}

 

하지만, 조회를 할 때는 조인 없이 간편하게 데이터를 조회할 수 있었다.

 

위처럼 많은 고민을 하면서 ERD를 설계 하였지만, 설계 이후에 개발을 하면서 추가로 반정규화 했던 경험에 대해서 나누고 싶다. 

마이브러리 첫 배포 이후에 추천 피드라는 기능을 추가하는 과정에서 한 추천 피드를 조회할 때 생각 보다 총 7개의 테이블을 조인해서 가져와야 했다. (UI는 매우 괜찮죠..?)

 

* 추천 피드는 추천하고 싶은 도서를 선택해서 추천하고 싶은 대상과 추천글과 함께 피드를 올릴 수 있는 기능입니다.

 

 

 그 때 가장 골치 아팠던 데이터가 저자에 대한 데이터였다. 도서가 우리 서비스를 통해 검색이 될 때, 해당 도서의 대한 정보가 알리딘 API를 통해 아래와 같이 저장이 된다. 이때 저자와 번역가는 연관 관계 테이블을 중간에 두고 저장이 된다. 다음과 같이 ERD를 설계한 이유는 "저자와 번역가를 통해 도서의 정보를 보여주는 기능이 있을 수도 있겠다."라는 생각 때문이었다.

 

따라서 도서 정보를 보여줄 때, 저자와 번역가를 자주 보여주는 경우가 많아 매번 조인을 하도록 코드를 작성했다. 매우 번거롭고 SQL를 많이 나가는 것도 신경이 많이 쓰였다.

 

 

 

따라서 추천 피드를 개발하고, 이번 기회에 저자와 번역가 데이터를 반정규화하였다. 아래와 같이 authors와 translators 칼럼을 추가하였고, 한 도서에 저자와 번역가가 여러 명일 경우 , 를 구분자로 두고 저장했다. 

 

 

 

따라서 추천 피드를 조회할 때 조인하는 테이블의 갯수도 줄고 실제 쿼리 개수도 줄일 수 있었다.

하지만 칼럼 추가시, 기존에 저장되어 있던 도서들에 대해서는 authors와 translators가 빈값으로 저장되어 있다.

따라서 데이터를 보정해야 하는 쿼리를 작성해야 했다.

(당연히 AutoCommit 설정을 끄고 디비 보정을 했습니다!)

 

-- AutoCommit 해제
SET AUTOCOMMIT = 0;

-- authors 디비 보정
UPDATE books C, (SELECT A.book_id as book_id, GROUP_CONCAT(B.name SEPARATOR ', ') AS authors
FROM books_authors A RIGHT OUTER JOIN authors B
ON A.author_id = B.id
GROUP BY A.book_id) D
SET C.authors = D.authors
WHERE C.id = D.book_id;

-- translators 디비 보정
UPDATE books C, (SELECT A.book_id as book_id, GROUP_CONCAT(B.name SEPARATOR ', ') AS translators
FROM books_translators A RIGHT OUTER JOIN translators B
ON A.translator_id = B.id
GROUP BY A.book_id) D
SET C.translators = D.translators
WHERE C.id = D.book_id;

-- 데이터 확인 후 Commit
Commit;

 

짜라라란 ~

 

 

 

+ 정규화 / 반정규화 간단한 개념 정리

정규화?

• 데이타의 일관성과 최소한의 데이터 중복을 위해 테이블을 분해하는 과정
• 테이블 간에 중복된 데이터를 허용 하지 않음으로써 무결성을 유지하고 DB의 저장 용량을 줄일 수 있는 장점이 있다.

제1 정규화

• 테이블의 칼럼이 원자값을 갖도록 테이블을 분해하는 것.

제2 정규화

• 제1 정규화를 진행한 테이블에 대해서 완전 함수 종속을 만족하도록 테이블을 분해하는 과정
• 완전 함수 종속 : 기본키의 부분집합이 결정자가 되어선 안된다.

제3 정규화

• 제2 정규화를 진행한 테이블에 대해서 이행적 종속을 없애도록 테이블을 분해하는 과정
• 이행적 종속 : A → B, B → C 가 성립할 때 A → C가 성립되는 것.

BCNF 정규화

• 제3 정규화를 진행한 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해하는 것

 

정규화를 통해 데이터의 중복을 최소화하고 무결성을 유지할 수 있지만, 데이터 조회 시의 조인을 유발하는 문제점이 존재한다. 이는 조회 성능을 저하하는 원인이 되기도 한다. 따라서 쓰기 성능보다 조회 성능이 더 중요한 로직이라면 반정규화를 통해 조회 성능을 높일 수 있다.

 

반정규화?

• 정규화된 엔터티, 속성, 관계에 대해 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법
• 반정규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행
• 조회 성능은 향상시킬 수 있지만, 데이터의 정합성을 맞추기 위해서는 추가적인 쓰기 로직이 있어야 한다.

반정규화 기법

• 테이블 반정규화 : 테이블 병합, 테이블 분할, 테이블 추가
• 칼럼 반정규화 : 중복 칼럼 추가, 파생칼럼 추가, 이력 테이블 칼럼 추가, PK에 의한 칼럼 추가  
• 관계 반정규화 : 중복관계 추가

참고 : https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=333

 

ERD 설계 시, 정규화라는 개념은 은연중에 떠올리고 데이터의 일관성에 집중해서 설계하는 편이고, 정규화 단계를 매번 외우지만 매번 까먹었는 개념이라.. 이번에 한번 더 간단하게라도 정리할 수 있었고, 매번 반정규화는 칼럼 추가로만 생각했다가 위 레퍼런스를 읽어보면서 반정규화 기법이 매우 다양하다는 사실도 알게 되었다 ~~