1. 다중값 속성 저장 : 쉼표로 구분된 목록에 저장

cwchoiit 2024. 12. 30. 13:46

2024. 12. 30. 13:46

728x90

SMALL

SQL 관련 안티패턴을 공부하고자 마음먹고 새로 산 책인 [SQL AntiPatterns]을 보면서 배운 내용을 정리하려고 한다. 이 책에서 여러 안티 패턴을 설명하고 이 방식이 왜 잘못된건지, 어떤 문제를 야기하는지, 그 해결 방법은 무엇인지 자세하게 설명해주고 있는데 정말 재밌다.

참고 저서: [SQL Antipatterns]

목표: 다중 값 속성 저장

테이블의 칼럼이 하나의 값을 가질 땐 설계가 쉽다. 그러나 관련된 값의 집합은 어떻게 한 칼럼에 저장할 수 있을까? 예를 들면, 제품과 담당자가 있을 때 한 제품에 여러 담당자가 있을 수 있다. 최초에는 한 제품에는 한명의 담당자만으로 충분했는데 프로젝트가 성숙해가면서 제품의 담당자가 여러 명일 수 있다는 사실을 깨닫는다.

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

데이터베이스 구조의 변경을 최소화하기 위해, account_id 칼럼을 VARCHAR로 바꾸고, 여기에 여러 개의 계정 아아디를 쉼표로 구분해 나열하기로 했다. 아래와 같이 테이블을 정의한다.

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

그리고 이렇게 데이터를 넣어본다.

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

성공한 것 같다. 테이블을 새로 만들지도 않았고, 칼럼을 추가하지도 않았기 때문이다. 단지 칼럼 하나의 데이터 타입만 바꿨을 뿐이다. 그러나 이 테이블 설계로부터 겪어야 할 성능 문제와 데이터 정합성 문제를 살펴보자.

문제1 - 특정 계정에 대한 제품 조회

모든 FK가 하나의 필드에 결합되어 있으면 쿼리가 어려워진다. 더 이상 같은지를 비교할 수 없다. 대신 어떤 패턴에 맞는지를 검사해야 한다. 예를 들어, MySQL에서는 계정 '12'에 대한 제품을 찾기 위해 다음과 같은 쿼리를 사용할 수 있다.

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

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

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

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

내가 어떤 제품의 ID를 알고 있을 때 이 제품이 가지고 있는 계정 ID를 통해 조인하는 쿼리를 아래와 같이 기괴하게 작성해야 한다.

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

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

문제3 - 집계 쿼리 만들기

집계 쿼리는 COUNT(), SUM(), AVG()와 같은 함수를 사용한다. 그러나 이런 함수는 행의 그룹에 대해 사용하도록 설계되었지, 쉼표로 구분된 목록에 대해 사용하도록 설계된 것이 아니다. 따라서 다음과 같은 기교에 의지해야 한다.

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

이런 기교는 교묘하긴 하지만 명확하지 않다. 솔직히 쿼리를 딱 봤을때 이게 뭔지 한번에 알 수 있는 사람이 몇이나 될까? 이런 해법은 개발하는 데 시간도 오래 걸리고 디버깅하기도 어렵다. 심지어, 어떤 집계 쿼리는 이런 기교로도 만들어낼 수 없다.

문제4 - 특정 제품에 대한 계정 갱신

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

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

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

문제5 - 제품 아이디 유효성 검증

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

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

사용자들은 유효하지 않은 값을 입력하는 방법을 찾아낼 것이고, 데이터베이스는 쓰레기 더미가 될 것이다. 데이터베이스에서 에러가 발생하지는 않지만, 데이터는 의미 없는 것이 될 것이다.

문제6 - 구분자 문자 선택

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

문제7 - 목록 길이 제한

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;

VARCHAR(30)이 미래에 필요한 가장 긴 목록을 지원할 수 있는지 어떻게 알 수 있겠는가? 얼마나 길게 하면 충분할까? 이런 길이 제한에 대한 이유를 상사나 고객에게 설명해보기 바란다.

안티패턴 인식 방법

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

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

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

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

반정규화를 사용하기로 결정할 때는 보수적이어야 한다. 데이터베이스를 정규화하는 것이 먼저다. 정규화는 애플리케이션 코드를 좀 더 융통성 있게 하고, 데이터베이스의 정합성을 유지할 수 있게 한다.

해법: 교차 테이블 생성

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

CREATE TABLE Contacts
(
    product_id BIGINT NOT NULL,
    account_id BIGINT 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로 두 테이블을 참조할 때 이를 교차 테이블이라 한다. 교차 테이블은 참조되는 두 테이블 사이의 다대다 관계를 구현한다. 즉 각 제품은 교차 테이블을 통해 여러 개의 계정과 연관되며, 마찬가지로 각 계정은 여러 개의 제품과 연관된다. 안티패턴의 문제가 이 교차 테이블을 사용하면 어떻게 해결될 수 있는지 살펴보자.

문제1, 2 타파 - 계정으로 제품 조회하기와 제품으로 계정 조회하기

주어진 계정에 대한 모든 제품의 속성을 조회하려면, 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.products_id = 123;

문제3 타파 - 집계 쿼리 만들기

다음 쿼리는 제품당 계정 수를 리턴한다.

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

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

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

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

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

문제4 타파 - 특정 제품에 대한 계정 갱신

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

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

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

문제5 타파 - 제품 아이디 유효성 검증

어떤 항목이 다른 테이블에 있는 합당한 값에 대해 유효한지를 확인하기 위해 FK를 사용할 수 있다. Contacts.account_id가 Accounts.account_id를 참조하도록 선언해, 참조 정합성을 데이터베이스가 강제하도록 할 수 있다. 이렇게 하면 교차 테이블에는 실제로 존재하는 계정 아이디만 들어있음을 확신할 수 있다.

항목을 제한하는 데 SQL 데이터 타입을 사용할 수도 있다. 예를 들어, 목록에 들어갈 항목이 유효한 INTEGER 또는 DATE 값이어야 하고, 해당 칼럼이 이 데이터 타입을 사용하도록 선언했다면, 모든 항목이 해당 타입의 유효한 값이라 확신할 수 있다. 즉, `banana`와 같은 무의미한 값이 없다고 확신할 수 있다.

문제6 타파 - 구분자 문자 선택

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

문제7 타파 - 목록 길이 제한

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

교차 테이블의 다른 장점

Contacts.account_id에 걸린 인덱스를 활용하면 쉼표로 구분된 목록에서 부분 문자열 매칭하는 것보다 성능이 좋아진다. 칼럼에 FK를 선언하면 많은 데이터베이스가 내부적으로 해당 칼럼에 대한 인덱스를 생성한다. (그러나 확실한 건 해당 데이터베이스 문서를 확인)

또한, 교차 테이블에 칼럼을 추가해 각 항목에 추가 속성을 넣을 수 있다. 예를 들어, 주어진 제품에 담당자가 할당된 날짜를 저장하거나, 누가 주 담당자이고 누가 부 담당자인지를 표시하는 속성을 추가할 수 있다.

728x90

LIST

저작자표시

'SQL AntiPatterns' 카테고리의 다른 글

2. PK 관례 확립: 만능키 (6)	2024.12.30

programmer.cwchoiit