꿈꾸는 개발자, DBA 커뮤니티 구루비

2.1 목표: 다중 값 속성 저장

버그 추적 데이터베이스 예제에서, 우리는 Products테이블의 정수 칼럼을 사용해 제품과 담당자를 연관시켰음
각 계정은 많은 제품에 대응되고, 각 프로젝트는 담당자를 하나만 참조하므로, 제품과 계정은 다대일 관계다.


CREATE TABLE Products (
  product_id   SERIAL PRIMARY KEY,
  product_name VARCHAR(1000),
  account_id   BIGINT UNSIGNED,
  -- . . .
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder', 12);

프로젝트가 성속해가면서, 제품의 담당자가 여러 명일수도 있다는 사실을 깨닫는다.
제품과 계정의 관계에서 다대일 관계뿐 아니라 일대다 관계도 지원해야 하는 것임.
Product테이블에서의 한 행이 하나 또는 이상의 담당자를 가질 수 있어야 함

2.2 안티패턴: 쉼표로 구분된 목록에 저장

데이터베이슥 구조의 변경을 최소화하기위해, account_id 칼럼을 VARCHAR로 바꾸로 여기에 여러개의 계정 아이디를 쉼표로 구분해 나열하기 했음


CREATE TABLE Products (
  product_id   SERIAL PRIMARY KEY,
  product_name VARCHAR(1000),
  account_id   VARCHAR(100), -- 쉼표로 구분된 목록
  -- . . .
);

INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder', '12,34');

새로 만들지도 않았고, 칼럼을 추가하지도 않았으며, 단자 컬럼 하나의 데이터 타입만 바뀌어 성공함
그러나 이 테이블 설계로부터 겪어야할 성능 문제와 데이터 정합성 문제를 살펴보자.

특정계정에 대한 제품 조회

모든 FK가 하나의 필드에 결합되어 있으면 쿠러기 어려워짐
더이상 같은지를 비교할수 없으므로, 대신 어떤 패턴에 맞는지를 검사해야함

예를 들어, MySQL에서는 계정 12에 대한 제품을 찾기 위해 다음과 같은 쿼리를 사용할수 있음


SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]';

패턴 매칭을 사용하면 잘못된 결과가 리턴될수 있고 인덱스도 활용하지 못함
패턴 매칭 문법은 데이터베이스 제품에 따라 다르기 때문에 이렇게 작성한 SQL은 밴더 중립적이지도 않음

주어진 제품에 대한 계정정보 조회

쉼표로 구분된 목록을 참조하는 테이블의 대응되는 행과 조인하기도 불편해지고 비용이 많이듬


SELECT * FROM Products AS p JOIN Accounts AS a
    ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.product_id = 123;

이런 식으로 표현을 사용해 두 테이블을 조인하면 인덱스를 활용할 기회가 사라짐
이 쿼리는 두테이블을 모두 읽어 카테시안곱(Cartesian product)을 생성한 다음, 모든행의 조합에 대한 정규표현식을 평가해야함

집계쿼리 만들기

집계쿼리는 COUNT(),SUM(),AVG()와 같은 함수를 사용해야함
그러나 이러한 함수는 행의 그룹에 대해 설계되었지, 쉼표로 구분된 목록에 대해 사용하로고 설계된 것이 아님


SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1
    AS contacts_per_product
FROM Products;

특정 제품에 대한 계정갱신

목록의 마지막에 문장열 연결을 통해 새로운 아이디를 추가할 수있지만, 이렇게 하면 목록이 정렬된 상태로 유지되지 않음


UPDATE Products
SET account_id = account_id || ',' || 56
WHERE product_id = 123;

.
목록에서 항목을 삭제하려면 두개의 SQL쿼리를 실행해야 한다
하나는 예전 목록을 불러오는데, 다른 하나는 목록을 갱신하기 위해 필요함


<?php

$stmt = $pdo->query(
  "SELECT account_id FROM Products WHERE product_id = 123");
$row = $stmt->fetch();
$contact_list = $row['account_id'];

// change list in PHP code
$value_to_remove = "34";
$contact_list = split(",", $contact_list);
$key_to_remove = array_search($value_to_remove, $contact_list);
unset($contact_list[$key_to_remove]);
$contact_list = join(",", $contact_list);

$stmt = $pdo->prepare(
  "UPDATE Products SET account_id = ?
   WHERE product_id = 123");
$stmt->execute(array($contact_list));

제품 아이디 유효성 검증

사용자가 banana와 같은 유효하지 않는 항목을 입력하는 것을 어떻게 방지할수 있을까?


INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder', '12,34,banana');

사용자들은 유효하지 않는 값을 입력하는 방법을 찾아낼것이고, 데이터베이스는 쓰레익 더미가 될것임

구분자 문자 선택

정수 목록 대신 문자열 목록을 저장하는 경우 목록의 일부 항목이 구분자 문자를 포함할수 있음.
항목간의 구분자로 쉼표를 사용하면 모호해질수 있음.
구분자로 다른 문자를 사용할 수도 있으나, 이 새로운 구분자가 항목에 절대 안 나온다고 보장할 수 있을까?

목록 길이 제한

VARCHAR(30) 칼럼에 얼마나 많은 목록 항목을 저장할수 있을까?
각 항목의 길이에 따라 다름.
각 항목의 길이가 2라면 (쉼표를 포함해서) 항목을 열개 저장할수 있음
그러나 각 항목의 길이가 6이라면 항목을 네개 저장할수 있을 뿐임


UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46'
WHERE product_id = 123;

UPDATE Products SET account_id = '101418,222630,343842,467790'
WHERE product_id = 123;

2.3 안티패턴 인식방법

프로젝트 팀에서 다음과 같은 말이 나온다면, 무단횡단 안티패턴이 사용되고 있음을 나타내는 단서로 간주할수 있음

"이 목록이 지원해야 하는 최대 항목수는 얼마나 될까?"
- VARCHAR칼럼의 최대 길이를 선정하려 할때 이런 질문이 나옴
"SQL에서 단어의 경계를 어떻게 알아내는지알아?"
- 문자열의 일부를 찾아내기 위해 정규표현식을 사용한다면, 이런 부분을 별도로 저장해야 함을 뜻하는 단서일수 있음
"이 목록에서 절대 나오지 않을 문자가 어떤게 있을까?"
- 모호하지 않는 문자를 구분자로 사용하고 싶겠지만, 어떤구분자를 쓰든 언제가는 그 문자가 목록의 값에 나타날 것이라 예상해야한다

2.4 안티패턴 사용이 합당한 경우

어떤 종류의 쿼리는 데이터베이스에 반정규화(denormalization)를 적용해 성능을 향상시킬수 있음
목록을 쉼표로 구분된 문자열로 저장하는 것도 반정규화의 예임
애플리케이션에서 쉼표로 구분된 형식의 데이터를 필요로 하고, 목록안의 개별항목에는 접근할 필요가 없을수 있음
비슷하게 애플리케이션이 다른 출처에서 쉼표로 구분된 형식으로 데이터를 받아 데이터베이스에 그대로 저장하고 나중에 동일한 형식으로 불러내야하며,
목록안의 개별 값을 분리할 필요가 없다면 안티패턴을 사용할수 있음.

2.5 해법: 교차테이블 생성

account_id를 Products 테이블에 저장하는 대신, 별도의 테이블에 저장해 account_id가 별도의 행을 차지하도록 하는것이 좋음
이 새로 만든 Contracts테이블은 Products와 Accounts사이의 다대다 관계를 구현함


CREATE TABLE Contacts (
  product_id  BIGINT UNSIGNED NOT NULL,
  account_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

INSERT INTO Contacts (product_id, account_id)
VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);

어떤 테이블이 FK로 두 테이블을 참조할때 이를 교차 테이블이라고 함
교차 테이블은 참조되는 두 테이블 사이의 다대다 관계를 구현함
즉 각 제품은 교차 테이블을 통해 여러 개의 계정과 연과되면, 마찬가지로 각 계정은 여러개의 제품과 연과됨

그림 2.1의 ERD를 보기바람

안티패턴절에서 보았던 문제가 교차테이블을 사용하면 어떻게 해결될수 있는지 살펴봄

계정으로 제품조회하기와 제품으로 계정 조회하기

주어진 계정에 대한 모든 제품의 속성을 조회하려면, Products 테이블과 Contacts테이블을 조인하면 됨


SELECT p.*
FROM  Products AS p JOIN Contacts AS c ON (p.product_id = c.product_id)
WHERE c.account_id = 34;

마찬가지로 계정 상세정보를 조회하는 쿼리도 읽기 쉽고 최적화하기도 쉬움
조인을 위해 난해한 정규표현식을 사용하는 대신 효율적인 인덱스를 사용함


SELECT a.*
FROM   Accounts AS a JOIN Contacts AS c ON (a.account_id = c.account_id)
WHERE c.product_id = 123;

집계 쿼리 만들기

다음 쿼리는 제품당 계정수를 리텀함


SELECT product_id, COUNT(*) AS accounts_per_product
FROM Contacts
GROUP BY product_id;

계정당 제품수를 구하는것도 마찬가지로 간단함


SELECT account_id, COUNT(*) AS products_per_account
FROM Contacts
GROUP BY account_id;

가장 많은 담당자를 할당 받은 제품을 구하는 것과 같이 좀더 복잡한 리포트를 만드는 것도 가능함


SELECT c.product_id, c.contacts_per_product
FROM (
  SELECT product_id, COUNT(*) AS accounts_per_product
  FROM Contacts
  GROUP BY product_id
) AS c
ORDER BY c.contacts_per_product DESC LIMIT 1

특정제품에 대한 계정 갱신

목록에 항목을 추가하거나 삭제하는 것은 교차테이블에 행을 삽입하거나 삭제하는 방법으로 할수 있음
각제품에 대한 참조는 Contacts테이블에 별도릐 행으로 저장되므로, 한번에 하나씩 추가또는 삭제할수 있음


INSERT INTO Contacts (product_id, account_id) VALUES (456, 34);

DELETE FROM Contacts WHERE product_id = 456 AND account_id = 34;

제품 아이디 유효성 검증

어떤 항목이 다른 테이블에 있는 합당한 값에 대해 유효한지를 확인하기 위해 FK를 사용할수 있음
항목을 제한하는데 SQL타입을 사용할수 있음
예를 들어, 목록에 들어갈 항목이 유효한 INTEGER또는 DATE값이어야하고 해당 칼럼이 이 데이터 타입을 사용하도록 선언했다면,
모든항목이 해당 타입의 유효한 값이라(즉 banana와 같은 무의미한 항목이 없다고) 확신할수 있음

구분자 문자선택

각 항목을 별도의 행으로 저장하므로 구분자를 사용하지 않음
쉼표나 구분자로 사용하는 다른 문자가 항목에 포함되어 있을지 걱정할 필요가 없음.

목록길이제한

각 항목이 교차테이블에 별도 행으로 존재하기 때문에, 한 테이블에 물리적으로 저장할수 있느느 행수에만 제한을 받음
항목 수를 제한하는것이 적당하다면, 목록의 항목을 한한 길이를 보는 것보다 애플리케이션에서 항목수를 세어 이 정책을 강제해야함

교차테이블의 다른 장점

Contacts.accountid에 걸린 인덱스를 활용하면 쉼표로 구분된 목록에서 부분 문자열 매칭하는것보다 성능이 좋아짐
칼럼에 FK를 선언하면 많은 데이터베이스가 내분적으로 해당 컬럼에 대한 인덱스를 생성함 (그러나 문서를 확인하기 바람)
또한 교차테이블에 칼럼을 추가해 각 항목에 추가 속성을 넣을수 있음
- 예를 들어, 주어진제품에 담당자가 할당된 날짜를 저장하거나, 누가 주 담당자고 누가 부 담당자인지를 표시하는 속성을 추가할수 있음

SQL Antipatterns Tip

각 값은 자신의 칼럼과 행에 저장하라.

문서에 대하여

최초작성일 : 2011년 11월 22일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.*

2장. 무단횡단 (by onsider) [2011.11.26]

2.1 목표: 다중 값 속성 저장

2.2 안티패턴: 쉼표로 구분된 목록에 저장

특정계정에 대한 제품 조회

주어진 제품에 대한 계정정보 조회

집계쿼리 만들기

특정 제품에 대한 계정갱신

제품 아이디 유효성 검증

구분자 문자 선택

목록 길이 제한

2.3 안티패턴 인식방법

2.4 안티패턴 사용이 합당한 경우

2.5 해법: 교차테이블 생성

계정으로 제품조회하기와 제품으로 계정 조회하기

집계 쿼리 만들기

특정제품에 대한 계정 갱신

제품 아이디 유효성 검증

구분자 문자선택

목록길이제한

교차테이블의 다른 장점

문서에 대하여