골드에그

[문제코드]

테이블에 적재하는 데이터의 양이 점차 많아질수록 Index 에 대한 부하 뿐만 아니라 I/O 에 대한 부하가 걸리게 된다. 테이블의 양이 점점 많아지고 있을 때, 아래와 같이 데이터를 분리할 칼럼을 선택해 테이블을 분리할 수 있다.

CREATE TABLE Bugs_2008 ( ... );
CREATE TABLE Bugs_2009 ( ... );
CREATE TABLE Bugs_2010 ( ... );

해당 구조는 아래와 같이 여러 단점을 가지고 있다.

1 . 데이터 정합성 관리

데이터베이스 행에 데이터를 삽입할 때 테이블을 선택하는 것은 오로지 사용자 책임이다.

INSERT INTO Bugs_2010 (..., date_reported, ...)
	VALUES (..., '2010-06-01', ...);

INSERT INTO Bugs_2011 (..., date_reported, ...)
	VALUES (..., '2011-02-20', ...);

날짜연도에 따라서 테이블을 생성해야 하는데 테이블 생성하는 것을 잊어버렸다면 에러가 발생할 수 있다.

또한, 한 해 동안 버그 개수를 세보려고 하는데 통계 정합성이 맞지 않을 수 있다. 2010 년 관련 값이 Bugs_2009 테이블에 삽입되었던 것이다. 잘못된 어플리케이션의 로직을 방어하기 위해 데이터베이스 기준으로 제약할 수 있는 방법이 있긴 하다. 아래와 같이 CEHCK 조건을 필드에 적용하면 된다.

CREATE TABLE Bugs_2009 (
	...
    date_reported DATE_CHECK (EXTRACT(YEAR FROM date_reported) = 2009)
)

2 . 데이터 수정 불편

UPDATE 구문으로 단순히 데이터를 변경하고 싶지만, 테이블을 나눈 기준 열 칼럼의 값이 바뀌면 해당 테이블에서 삭제하고 다른 테이블에 데이터를 옮겨야한다.

INSERT INTO Bugs_2009 (bug_id, date_reported, ...)
	SELECT bug_id, date_reported, ...
    FROM Bugs_2010
    WHERE bug_id = 1234;
    
DELETE FROM Bugs_2010 WHERE bug_id = 1234;

3 . 유일성 보장

테이블을 나누는 기준인 PK 칼럼은 유일함이 보장되어야 한다. 한 테이블에서 다른 테이블로 행을 옮겼을 때 PK 값이 다른 행과 충돌하지 않는다는 확신이 있어야 한다.

4 . 여러 테이블에 걸쳐 조회

여러 테이블에 걸쳐 조회할 필요가 생길 때 분리된 모든 테이블을 UNION 으로 묶어서 재구성한 다음 쿼리를 실행해야 한다.

SELECT b.status COUNT(*) AS count_per_status FROM (
	SELECT * FROM Bugs_2008
    	UNION ALL
    SELECT * FROM Bugs_2009
    	UNION ALL
    SELECT * FROM Bugs_2010 ) AS b
GROUP BY b.status;

5 . 테이블에 새로운 칼럼 추가 시 모두 변경

테이블에 새로운 칼럼 추가 시 모든 분리된 테이블에 똑같은 칼럼을 추가해야 한다.

[해결방법]

수평 분할 사용과 수직 분할 사용을 고려할 수 있다.

1 . 수평 분할

행을 여러 파티션으로 분리하는 규칙과 함께 논리적 테이블을 생성하는 것만으로도 충분하다. 물리적으로는 테이블이 분리되었지만, 논리적으로는 하나의 테이블처럼 사용할 수 있다.

CREATE TABLE Bugs (
	bug_id SERIAL PRIMARY KEY,
    ...
    date_reported DATE
) PARTITION BY HASH (YEAR(date_reported))
PARTITIONS 4;

테이블을 직접 분리했을 때랑 다르게 잘못된 데이터가 분리된 테이블로 들어갈 위험이 없다는 장점이 있다. 분리 기준이 되었던 칼럼의 값을 업데이트해도 문제가 없고 분리 테이블을 모두 접근해서 쿼리를 할 필요도 없다.

다만, 위 예제에서 4년 이상이 된 데이터가 있다면, 파티션 중 하나에는 두 연도의 데이터가 들어갈 수 있다.

2 . 수직 분할

수직 분할은 테이블에 있는 칼럼 중 크기가 아주 큰 칼럼이거나 거의 사용되지 않는 칼럼이 있을 경우 고려할 수 있는 방법이다. BLOB 이나 TEXT 칼럼은 크기가 가변적이고 매우 커질 수 있는 칼럼이라서 같은 테이블에서 조회 & 수정 & 삭제 시 성능 저하를 초래한다. 거의 사용되지 않는 칼럼도 리소스 낭비가 있을 뿐이다. 이렇게 가변적이거나 크기가 아주 크거나 자주 사용되지 않는 칼럼들만 모아서 별도의 종속 테이블로 분리하는 것이 좋다.

// 고정 크기의 타입만 정의
CREATE TABLE Bugs (
	bug_id SERIAL PRIMARY KEY,
    summary CHAR(80),
    date_reported DATE,
    reported_by BIGINT UNSIGNED,
	FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
)
// 가변 크기의 타입만 정의
CREATE TABLE BugDescriptions (
	bug_id BIGINT UNSIGNED PRIMARY KEY,
    description VARCHAR(1000),
    resolution VARCHAR(1000),
    FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
)

[문제코드]

어떤 사람에 대한 정보를 DB 에 저장할 때 취미가 여러 개라면 아래와 같이 여러 칼럼에 나누어 저장하는 안티패턴을 생각할 수 있다.

CREATE TABLE Person (
	person_number INT PRIMARY KEY,
    name VARCHAR(10),
    hobby1 VARCHAR(20),
    hobby2 VARCHAR(20),
    hobby3 VARCHAR(20),
)

해당 구조는 아래와 같이 여러 단점을 가지고 있다.

1 . 검색 문제

원하는 정보가 어느 칼럼에 있는지 모르기 때문에 모든 필드들을 확인해야 한다.

hobby1, hobby2, hobby3 이 모두 null 로 초기화되어 있는 상태에서 하나씩 hobby 를 추가하기 때문에 찾으려고 하는 값이 어느 위치에 있는지 확인하기 어렵다.

SELECT * 
FROM Person
WHERE hobby1 = 'soccer'
   OR hobby2 = 'soccer'
   OR hobby3 = 'soccer';

취미가 축구인 사람을 찾기 위해 모든 다중 속성 칼럼들에 대해 찾는 것을 볼 수 있다.

2 . 수정 문제

마찬가지로 여러 칼럼 중 어떤 칼럼을 수정해야 할지 먼저 검색을 해야한다는 것이 단점이다. 그런데 이마저도 동시성 문제가 존재하여 둘 중 하나는 충돌로 인해 업데이트에 실패하거나 변경 내용을 덮어쓸 수 있다. 그래서 아래와 같이 NULLIF 함수를 통해 칼럼 값이 특정 값과 같으면 NULL 로 만드는 작업을 해야해서 번거롭다.

먼저 삭제 코드는 다음과 같다.

UPDATE Person 
SET hobby1 = NULLIF(hobby1, 'soccer'),
    hobby2 = NULLIF(hobby2, 'soccer'),
    hobby3 = NULLIF(hobby3, 'soccer')
WHERE person_number = '~';

만약에 첫 번째 NULL 인 칼럼에 추가하는 작업을 하는 코드를 만들어 본다고 생각해보자. 각 칼럼마다 NULL 이 아니면 아무런 변경도 가하지 않고 새 태그 값은 기록하지 않는 과정을 해야할 것이다.

3 . 일관성 문제

일관성도 문제다. 여러 칼럼에 중복되는 값이 입력될 수 있다.

4 . 확장 문제

취미가 하나 더 필요하다면, 해당 테이블 칼럼을 확장해야 하는데 이 때 테이블 전체를 잠금 설정하고 모든 클라이언트의 접근을 차단하는 과정이 필요하게 된다. 예전 구조의 데이터들을 마이그레이션해야하고, 그 양도 많다면 작업 시간이 많이 걸릴 수 있다. 또한 해당 테이블을 사용하는 모든 애플리케이션의 SQL 구문을 변경해야 한다.

[해결방법]

다중 속성 칼럼들을 종속 테이블로 변환 생성해서 사용하면 된다.

CREATE TABLE person (
  person_number INT PRIMARY KEY,
  name VARCHAR(10),
);

CREATE TABLE Hobby (
  person_number INT FOREIGN KEY REFERENCES person(person_number)
  hobby_name VARCHAR(10)
);

모든 문제를 해결할 수 있다.

1 . 검색 해결

SELECT * 
FROM Person JOIN Hobby USING (hobby_id)
WHERE hobby_name = 'soccer';

두 개의 취미를 가진 사람도 손쉽게 찾을 수 있다.

SELECT * FROM Person
	JOIN Hobby AS h1 USING (hobby_id)
	JOIN Hobby AS h2 USING (hobby_id)
WHERE h1.hobby_name = 'soccer' AND h2.hobby_name = 'sing';

2 . 수정 해결

수정 문제도 쉽게 해결 할 수 있다. 단순히 종속 테이블에 행을 추가하거나 삭제하면 된다.

--- 수정
INSERT INTO Hobby (member_id, hobby_name) VALUES (1, 'soccer');

--- 삭제
DELETE FROM Hobby WHERE member_id = 1 AND hobby_name = 'soccer';