해외 판매 사이트의 스키마를 인계받았는데 테이블이 쉰 개가 넘고 정의가 꼼꼼했다. 모든 컬럼에 주석이 달려 있고 이름 규칙도 일관돼 있었는데 맨 아래 한 줄이 걸렸다.
Table of contents
Open Table of contents
증상 — 맨 아래 한 줄
테이블의 기본 문자셋이 DEFAULT CHARSET=utf8 로 잡혀 있었다. 이름이 일반적인 유니코드 표기와 같아서 문제없어 보이지만 MySQL 의 utf8 은 실제로 담을 수 있는 범위가 좁다. MySQL 5.7 참고 매뉴얼은 이 utf8 을 문자당 최대 세 바이트에 보충 문자 미지원(BMP 만)으로 적는다.
해외 판매라면 여러 언어의 글자와 이모지가 들어올 수 있다. 그런 문자는 네 바이트를 쓰므로 이 설정에서는 저장할 때 오류가 나거나 잘린다.
원인 — 3바이트가 담지 못하는 것
실제로 어떤 글자가 안 들어가는지를 INSERT 로 확인해 보니 이모지와 일부 한자가 Incorrect string value 로 걸렸다. 상품명이나 후기에 그런 글자가 들어올 가능성이 충분히 있었다.
그래서 utf8 을 두는 것 대신 네 바이트를 담는 utf8mb4 로 바꾸는 쪽을 골랐다. 이유는 해외 판매라 그런 글자가 들어올 가능성이 충분히 있어서다. 이름이 비슷해서 지나치기 쉬운 자리라 왜 그렇게 했는지를 함께 남겼다.
판단 기준 — 비어 있을 때 가장 싸다
이 변경을 지금 하는 것과 나중에 하는 것의 차이가 컸는데 자료가 없을 때는 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 한 줄이면 끝난다. 자료가 쌓인 뒤에는 전체를 다시 쓰는 작업이 되고 서비스도 멈춰야 한다.
거기에 varchar(255) 에 인덱스가 걸린 컬럼은 767바이트 길이 제한에 걸려서 인덱스 정의까지 함께 손봐야 한다. 지금 손대면 한 줄이고 나중에 손대면 계획이 필요한 작업이었다.
주의 — 인덱스와 외래키의 부재
같은 이유로 인덱스도 SHOW INDEX 로 지금 봤는데 PRIMARY 말고는 하나도 없었다. 다른 테이블을 가리키는 컬럼들은 조회 패턴이 뻔한데도 인덱스가 안 걸려 있었다.
FOREIGN KEY 도 하나도 없었는데 안 걸기로 한 것인지 빠뜨린 것인지가 문서에 없었다. 의도적으로 안 건 것이면 그 결정을 적어 둬야 다음 사람이 판단할 수 있다.
조치 — 세 가지 상태를 만드는 기본값
tinyint(1) 플래그 컬럼 몇 개가 기본값이 NULL 로 돼 있는 것도 있었다. 참과 거짓만 있어야 할 자리에 빈 값이 더해져서 상태가 셋이 된다.
빈 값인 행은 참을 찾는 조회에도 거짓을 찾는 조회에도 안 걸린다. 어느 목록에도 안 나오는 행이 생기는 것이므로 NOT NULL DEFAULT 0 으로 기본값이 명시적으로 채워지도록 손봤다.
정리
- 이름이 같아 보여도 담을 수 있는 문자 범위가 다르다
- 세 바이트 문자셋은 이모지와 일부 한자를 못 담는다
- 문자셋 변경은 자료가 비어 있을 때 가장 싸다
- 나중에는 인덱스 길이 제한까지 함께 걸린다
- 조회 패턴이 명확한 참조 컬럼에는 인덱스를 미리 넣는다
- 제약을 의도적으로 안 건 것과 빠뜨린 것은 다르다
- 플래그의 빈 기본값은 세 가지 상태를 만든다
- 어느 조회에도 안 잡히는 행이 생긴다