[설계] 확장가능한 데이터 모델링 (Extensible Data Modeling)

[yeoseon]

20200130 회사 기술세션에서 진행한 세미나 내용을 정리하고, 더 알고싶은 것들도 정리한다.

[yeoseon]

확장가능한 데이터 모델링(Extensible Data Modeling)

Reference

• 내가 정리한 내용
• https://martinfowler.com/

개요

테이블에 추가로 열이 필요한데, ALTER TABLE을 사용하면 Table에 Lock이 걸리는 문제를 어떻게 해결할 수 있을까?

유연하게 설계하여 ALTER TABLE을 사용하지 말자 !

MySQL의 ALTER TABLE 과정

다른 Database도 세부적인 것에서 차이는 있지만 이와 크게 다르지 않을 것이다.

1. 테이블 Lock
2. 원본 테이블과 같은 빈 신규 테이블 생성
3. 신규 테이블에 대해 컬럼 추가(변경)
4. 원본 테이블로부터 모든 데이터 행을 신규 테이블로 복사 (시간이 얼마나 걸리든 간에 그냥 복사한다. = 테이블이 클 수록 Lock 걸리는 시간이 길어진다.)
5. 기존 테이블과 신규 테이블 Swap
6. 테이블 Lock을 해제하고, 기존의 테이블을 삭제

해결방안 1 :: Extra Columns

프로젝트 시작 시점에 예비 컬럼을 여러 개 만들어두는 방법 (예비데이터1, 예비데이터2 ...)

장점

• 새로운 속성에 사용할 컬럼이 있을 때, ALTER TABLE 명령 없이 프로젝트에서 결정하여 해당 예비 컬럼을 사용하기만 하면 된다.
• Single Table Inheritance(STI)와 관련이 있다.
• 테이블 제약사항(Foreign Key, Unique Key 등)에 대한 활용이 가능하다.

단점

• 예비 컬럼을 모두 쓰면 결국 ALTER TABLE을 다시 해야한다.
• 누구나 컬럼에 데이터를 넣을 수 있다. 이처럼 정형화된 규칙이 없이 데이터가 쌓이기 때문에 일관성이 없다.
• 어떤 데이터유형이 들어올지 모르니, 단지 매우 큰 VARCHAR로 설정해두므로, 정확한 데이터 타입을 구분하지 못한다.
• 논리명도 예비데이터1과 같이 의미없는 이름이기 때문에, 이 컬럼이 어떤 속성을 나타내는지 알 수 없다.

해결방안 2 :: EAV (Entity - Attribute - Value)

확장테이블과 같은 테이블을 하나 더 만들어서 관리하는 것

장점

• ALTER TABLE을 쓸 필요가 없다.
• 최고의 유연성을 가지게 된다.
• 모든 Row가 한 논리 단위의 개별 속성 Set을 가질 수 있다.

단점

• SQL 연산이 복잡해진다.
• RDBMS에서 이미 제공하고 있는 기능을 어플리케이션단에서 다시 작성해야한다.
  RDBMS에서 제공하는 데이터 유형이나 정렬 등을 사용할 수 없으므로, 이러한 기능을 직접 어플리케이션에 작성해서 사용해야 한다.
• PIVOT 으로 풀기때문에, Scale-up(확장)을 하고 싶을 때, 추가 속성에 대해서 계속 Join을 걸어야 한다.
• 테이블 제약사항(FK, UK)를 활용할 수 없다. 단지 값으로만 관리되므로.
• 데이터 유형 역시 사용하지 못한다.
• 확장 검색이 어렵다. 이 Key에 대해 검색을 하려면, 이 Key를 정확히 알고 하드코딩을 해야한다.
• Index와 저장이 비효율 적이다. (여러 행을 계속적으로 저장해야 하므로)
  많은 행을 몇 개의 속성명으로 구분한다. (Index cardinality 부족)
  엔티티와 속성 컬럼은 모든 ROW에 추가 공간을 사용한다.
  (전통적인 테이블 : 엔티티는 주요 키로 ROW당 하나만 저장한다. 테이블의 속성명은 테이블에 한 번만 저장된다.)
• 속성을 추가하게 되면 매번 Join을 추가해줘야 한다.
• 엉뚱한 데이터를 저장하는 것을 막을 수가 없다.

해결방안 3 :: CTI (Class Table Inheritance)

주제별로 추가 테이블을 만드는 방식
CTI(Concrete Table Inheritance)와 반대되는 개념

장점

• 생성 후 변경할 일이 없는 서브타입에 최적으로 동작한다.
• 데이터유형, 제약조건 모두 정상 동작한다.
• 서브타입 테이블 생성 및 제거가 쉽다.
  어차피 Master 원장이 있으므로 우리가 필요한 추가 테이블이 있으면 생성하고, 너네가 필요없으면 삭제하면 된다.
• 모든 서브타입의 공통속성 조회는 마스터원장과 1:1로 되어있이 때문에 마스터 원장만 보면 된다. -> 조회가 쉽다.
• 서브타입 테이블은 보통 크기가 작고, 인덱스는 더 작다.

단점

• 엔트리를 추가할 때 INSERT를 + 서브타입 테이블 만큼 수행해야 한다.
• 서브타입 속성 조회시 JOIN을 무조건 사용해야 한다.
• 모든 유형의 서브타입을 조회할 때 다중 JOIN이 필요하다.
  마스터 원장에 대해 서브타입 테이블이 3개 있는데, 3개를 다보고 싶으면 JOIN을 다 걸어야 한다.
• FK를 설정했을 경우, 마스터 원장을 수정하면 서브타입 테이블까지 모두 LOCK 된다.

해결방안 4 :: Serialized LOB

• Serializing
  객체를 직렬화된 형태로 나타낼 수 있다.
  직렬화된 데이터로 저장을 하고 다시 객체로 로드할 수 있는 스칼라 문자열(XML, JSON 등)로 객체를 변환할 수 있다.
• LOB
  긴 바이트 배열이나 문자열을 저장할 수 있는 Large Object
  BLOB이나 TEXT 데이터 유형
  문자열로 변환한 객체는 하나의 BLOB 또는 TEXT 컬럼으로 저장할 수 있다.
  -> 객체의 각 필드를 저장하기 위한 SQL 컬럼을 정의하지 않아도 된다.

MySQL에서 XML를 사용한 Serialized LOB

다음과 같은 테이블이 있다.

CREATE TABLE 'customer' (
   'CUST_NUM'  VARCHAR(15) NOT NULL COMMENT '고객 번호',
   'CUST_AGE'    VARCHAR(2) COMMENT '고객 나이',
   'XTN_INFO'   BLOB NULL DEFAULT NULL COMMENT '확장정보'
);

데이터를 INSERT 할 때 다음과 같이 XML를 이용한다.

INSERT INTO customerVALUES 
   ('1', '28', 
   '<CUST_ADDRESS>서울시 관악구</CUST_ADDRESS>
   <CUST_NAME>윤여선</CUST_NAME>'
   );

이렇게 XML 태그를 통해 INSERT된 값을 조회하고 싶을 때 MySQL에서는 ExtractValue()를 제공한다.

SELECT 
ExtractValue(XTN_INFO, 'CUST_NAME' AS CHAR) AS CUST_NAME
FROM customer

'XML을 사용한 Element의 값을 빼주세요' 의 기능
단, Index를 쓸 수 없어 풀스캔을 통해 찾아야 한다.

MariaDB에서 제공하는 Dynamic Column

자료 참조.
Binary로 저장하고, Column_get()을 통해 꺼낸다.
MariaDB에서 지원하는 여러 데이터 타입으로 조회할 수 있다. (금액은 Decimal로 꺼내줌)

장점

• 언제든지 새로운 필드를 추가하고 객체를 저장할 수 있다.
• ALTER TABLE이 필요 없다.

단점

• Attribute같은 개념이 아니기 때문에 Index를 사용할 수 없다.
• 개별 필드가 아닌 전체 객체를 반환한다.
• 단일 필드 갱신에 객체 전체를 기록한다.
  개별, 단일 필드를 다룰 때 객체를 다 다시 만들어서 집어넣는 방식으로 동작한다.
• DB의 제약조건, 데이터유형을 지원하지 않는다.
• 사용자 필드는 WHERE, GROUP BY, ORDER BY에서 쓰기 어렵다.

해결방안 5 :: Inverted Index

4 방안에서 Index를 사용하지 못하는 것을 해결하기 위해 XML을 사용해서 넣을 때, Key로 사용하고 싶은 컬럼을 따로 뽑아서 Table을 하나 더 생성해서 관리한다.
후에 값을 꺼낼 때 해당 테이블과 JOIN를 통해 찾을 수 있다.

장점

• Serialized LOB의 장점을 그대로 받는다.
• 별도 테이블에 생성하기 때문에, 마스터 테이블은 LOCK 걸릴 일이 없이 사용자 필드에 Index를 생성할 수 있다.
• 데이터유형과 제약조건 기능을 지원한다.

단점

• 별도에 테이블에 중복 저장을 한다.
• 속성 테이블의 수동 동기화(또는 트리커 연동)을 통해 지속적으로 동기화하는 작업들은 사람이 직접 수동으로 해야한다.
• 마스터행 Fetch에 JOIN이 필요하다.
• 어떤 열에 대해 인덱스를 생성할지에 대한 계획이 필요하다.
• NOT NULL의 제약사항은 여전히 지원하지 못한다.

해결방안 6 :: OSC (ONLINE SCHEMA CHANGE)

오픈소스 데이터 베이스 툴 Percona Toolkit의 pt-online-schema-change 사용

앞의 5가지 방법이 DB 내에서 ALTER TABLE을 안쓰는 방법이었다면,
OSC는 외부에서 지원하는 오픈소스 DB 툴의 ALTER TABLE을 이용하는 방법이다.

OSC의 ALTER TABLE 과정

Table LOCK을 걸지 않고 수행한다.
1. 테이블 Lock
2. 원본 테이블과 같은 빈 신규 테이블 생성
3. 신규 테이블에 대해 컬럼 추가(변경)
4. 원본 테이블로부터 모든 데이터 행을 신규 테이블로 복사
PK 순서대로 행을 Chunk 단위로 일부를 묶어서 새 테이블에 넣는다.
트리거를 원본테이블에 걸어 변경 사항을 지속적으로 Capture 하고 신규테이블을 적용한다.
-> 기존 테이블에 PK가 없거나, 이미 Trigger가 걸려있는 경우 사용하지 못한다.
5. 기존 테이블과 신규 테이블 Swap
6. 기존의 테이블을 삭제
7. 테이블 LOCK 해제

장점

• Lock 없이 ALTER TABLE을 쓸 수 있다.

단점

Batch 처럼 Chunk 단위로 데이터를 조금씩 반영하기 때문에, 시간이 4배 이상 걸릴 수 있다.

해결방안 7 :: No-SQL 쓰자.

장점

• 위반할 규칙이 없다.
  관계형은 테이블의 모든 행에서 고정열이 있어야 하지만, 비 관계형모델은 규칙 없이 레코드별 개별 필드 집합을 저장할 수 있다.
• 스키마가 없어서 유연하다.
• 문서기반 데이터베이스는 문서마다 고유 속성 정의를 지원하도록 설계되어, 신규 속성 추가시 좋다.

단점

• 관계형 DB의 장점을 모두 잃는다.(데이터타입, 제약사항, 균일 구조 레코드...)

결론

이런 방식이나 명칭을 결정할 때, 세계적으로 어떤 방식이나 명칭을 사용하는 지 충분히 알아보는 작업도 같이 수행하자!