대부분 비어 있는 컬럼이 있었다

주문 테이블에 컬럼이 47개였다. 그중 열 몇 개가 거의 비어 있었다.

SELECT
  COUNT(*) AS total,
  COUNT(pg_approval_no) AS pg,
  COUNT(mobile_carrier) AS carrier,
  COUNT(gift_message) AS gift
FROM orders;

total   pg      carrier  gift
82041   82041     1204    312

COUNT(컬럼) 은 NULL 을 안 세니 그 값이 곧 채워진 비율이다. mobile_carrier 는 1,204건이고 gift_message 는 312건에만 있다.

Open Table of contents

특정 경우에만 쓰는 값이었다
무엇이 문제인가
분리를 검토했다
나누는 기준을 정했다
검증 — 유효 조건과 확인 쿼리
정리

특정 경우에만 쓰는 값이었다

mobile_carrier 는 휴대폰 결제일 때만 채워지고 전체 주문의 1.5%다. gift_message 는 선물 주문일 때만이다.

이런 컬럼이 열두 개였는데 결제 수단별과 주문 유형별로 필요한 값이 다르기 때문이다.

한 경우에만 쓰는 컬럼을 공용 표에 두면 나머지 행에서는 전부 비어 있다.

무엇이 문제인가

당장 큰 문제는 아니었고 저장 공간도 NULL 이면 거의 안 쓴다. 다만 몇 가지가 걸렸다.

먼저 어느 컬럼이 언제 유효한지 모른다. mobile_carrier 가 비어 있으면 휴대폰 결제가 아닌 것인지 휴대폰 결제인데 값이 안 들어온 것인지 구분이 안 된다. 앞의 것은 정상이고 뒤의 것은 문제인데 pay_type 과 함께 봐야 갈리고 그 조합을 매번 기억해야 한다.

다음으로 컬럼이 계속 는다. 결제 수단을 하나 추가하면 그 수단 전용 컬럼이 또 붙어서 47개가 50개가 된다. 몇 년 지나면 어떤 컬럼이 어느 경우에 쓰이는지 아는 사람이 없어지고 실제로 grep -rn 으로 찾아도 쓰는 코드가 없는 것이 몇 개 있었다. 행이 길어지면 InnoDB 가 한 페이지에 담는 행 수가 주는 것도 있다.

마지막으로 화면 코드가 조건투성이다.

if ($order->pay_type == 'mobile') {
    echo $order->mobile_carrier;
}

이 조건이 여러 화면에 흩어진다.

분리를 검토했다

결제 수단별 정보를 별도 표로 뺄지 봤다.

CREATE TABLE order_payment_detail (
  order_no  INT NOT NULL,
  meta_key  VARCHAR(50) NOT NULL,
  meta_value VARCHAR(255) NULL,
  PRIMARY KEY (order_no, meta_key)
);

meta_key 와 meta_value 로 두는 키-값 방식이라 새 항목이 생겨도 컬럼을 안 늘린다. 대신 조회가 번거로워지는데 주문 목록에 결제 수단별 정보를 같이 보여 주려면 LEFT JOIN 이 여러 번이다.

나누는 기준을 정했다

전부 옮기지 않고 기준을 정했다.

성격	어디에
모든 주문에 있는 값	주문 표
조회·정렬·집계에 쓰는 값	주문 표 (비어 있어도)
상세 화면에서만 보는 값	별도 표

mobile_carrier 는 통계에 쓰고 있었다. 통신사별 결제 비중을 보므로 주문 표에 남겼다. gift_message 는 상세에서만 보니 별도 표로 옮겼다.

열두 개 중 다섯 개를 옮겼다. 어떤 값이 자주 쓰이는지는 grep -rn 과 슬로우 로그에서 셌는데 짐작으로 나누면 자주 쓰는 것이 밖으로 나가서 조회가 느려진다.

검증 — 유효 조건과 확인 쿼리

남긴 컬럼에는 언제 값이 있는지 주석을 달았다.

ALTER TABLE orders
  MODIFY COLUMN mobile_carrier VARCHAR(20) NULL
  COMMENT '휴대폰 결제 시에만. pay_type=mobile 인 행에서 필수';

COLUMN_COMMENT 는 SHOW CREATE TABLE 에 함께 나오니 별도 문서보다 안 낡는다.

조건이 맞는데 값이 없는 행을 찾는 쿼리도 뒀다.

-- 휴대폰 결제인데 통신사가 없는 주문
SELECT COUNT(*) FROM orders
WHERE pay_type = 'mobile' AND (mobile_carrier IS NULL OR mobile_carrier = '');

-- 휴대폰 결제가 아닌데 통신사가 있는 주문
SELECT COUNT(*) FROM orders
WHERE pay_type != 'mobile' AND mobile_carrier IS NOT NULL;

양쪽을 다 봤는데 첫 번째가 8건이고 두 번째가 0건이었다.

8건은 결제 응답에서 통신사 정보를 못 받은 것으로 상대 응답이 항상 그 필드를 주는 것은 아니었다. 있어야 하는데 없는 것과 없어야 하는데 있는 것을 둘 다 본다.

앞으로 컬럼을 늘릴 때 물어볼 것도 정했다.

1. 모든 행에 값이 있나? → 아니면 왜 없는지 적는다
2. 조회·집계에 쓰나? → 안 쓰면 별도 표를 검토한다
3. 이 값이 유효한 조건이 있나? → 있으면 주석에 적는다
4. 조건과 값이 어긋난 행을 찾는 쿼리를 만든다

정리

한 경우에만 쓰는 컬럼을 공용 표에 두면 나머지가 전부 비어 있다
COUNT(컬럼) 은 NULL 을 안 세니 그 값이 곧 채워진 비율이다
비어 있는 것이 해당 없음인지 누락인지 구분이 안 된다
경우가 늘 때마다 컬럼이 는다
행이 길어지면 InnoDB 가 한 페이지에 담는 행 수가 준다
전부 옮기지 않고 조회·집계에 쓰는지로 남길지 정한다
자주 쓰는지는 짐작 말고 grep -rn 과 슬로우 로그에서 센다
유효 조건을 컬럼 주석에 적으면 스키마와 같이 살아 안 낡는다
조건과 값이 어긋난 행을 양방향으로 찾는다