MySQL 5.7 테이블 리팩토링중 varchar 길이 제한 문제 해결
Column length too big for column ‘content’ (max = 16383); use BLOB or TEXT instead
현재 상황과 정규화의 중요성
현재 운영 중인 TF에 참여하면서 백오피스(혹은 콘솔 또는 어드민)을 개선하고 있는 애플리케이션에 대해 데이터베이스 읽기에 대한 요구사항이 대부분입니다. 우리 회사의 레거시 시스템도 데이터베이스에서 시작하며(MySQL 5.7을 사용하고 있습니다), 현재 우리의 데이터베이스에는 큰 문제가 있습니다. 그 문제는 대부분의 테이블이 제1 정규화도 제대로 되어있지 않다는 것입니다. 예를 들어 고객, 주문, 결제, 결제내역이라는 도메인이 있다고 가정해봅시다. 이 모든 도메인을 하나의 테이블 A에 몰아넣고, type이라는 열을 추가하고 0(고객), 1(주문), 2(결제), 3(결제내역)과 같이 정의합니다. 그리고 하나의 열에 여러 개의 데이터가 파이프문자(|)로 구분되어 저장됩니다. 이런 상황에서는 인덱스를 제대로 활용할 수 없어 대부분의 쿼리가 비효율적일 수밖에 없게 됩니다. 특히, 고객 데이터를 조회하기 위해서는 다음과 같은 쿼리를 작성해야 합니다.
SELECT *
FROM A
WHERE type = 0;
이런 상황에서는 type 열에 인덱스를 걸어도 의미가 없습니다. 왜냐하면 type 열에는 0, 1, 2, 3 이렇게 4개의 값만 들어가기 때문에, 인덱스를 걸어 정렬을 해두어도 type이 0인 모든 데이터를 조회하려면 결국 테이블의 25%를 반드시 읽어야 하기 때문입니다. 이러면 옵티마이저는 테이블 풀스캔이나 인덱스 풀스캔을 선택할 수밖에 없게 됩니다.
API의 지연시간을 최소화하기 위해서는 테이블에 액세스했을 때 가져오는 데이터가 최소화되어야 합니다. 그러나 원하는 데이터를 조회하기 위해서는 모든 데이터가 짬뽕되어 있는 특정 테이블을 반드시 조회해야 하고, 액세스 조건이 테이블 풀스캔 또는 인덱스 풀스캔으로 시작되어버리면 가져오는 데이터를 최소화시킬 수 없게 됩니다.
예를 들어, 가입일이 2020-12-31 이후인 고객의 목록을 조회하고 싶다면 다음과 같은 쿼리를 작성해야 합니다.
SELECT *
FROM A
WHERE type = 0
AND created_at > '2020-12-31';
위 쿼리는 액세스 조건인 type = 0이 테이블 풀스캔이나 인덱스 풀스캔으로 시작되어버리기 때문에, 결국 테이블의 25%를 읽어야 합니다. (상황에 따라 옵티마이저가 created_at을 액세스 조건으로 변경할 수도 있습니다.) 이후 created_at 조건이 평가되며, 가입일이 2020-12-31 이후인 데이터를 분류하게 됩니다.
반면, 테이블이 고객, 주문, 결제, 결제내역으로 나뉘어 있었다면(정규화 되어 있었다면), 위 쿼리는 다음과 같이 작성되었을 것이며 성능이 훨씬 더 좋았을 것입니다.
SELECT *
FROM 고객
WHERE created_at > '2020-12-31';
이러한 방식의 테이블 설계는 현재 우리 회사에서 다음과 같은 문제들을 일으키고 있습니다.
- 인덱스를 활용한 고성능 쿼리 작성이 어렵습니다. (API의 지연 시간이 느려지는 가장 큰 이유 중 하나)
- 정규화가 되어 있지 않기 때문에 각 테이블의 행렬이 비대해집니다. (코드가 더러워지는 가장 큰 이유 중 하나)
확장이 어렵습니다. 예를 들면 고객이 여러 개의 권한을 가질 필요가 있을 때, 고객과 권한이 하나의 테이블로 구성되어 있다면 새로운 권한을 추가하는 것이 불가능합니다. (이런 문제로 파이프문자( )로 여러 개의 데이터가 들어있거나, JSON 열을 추가한 히스토리가 있음)
TF 상황
현재 운영 중인 TF에서는 위의 상황으로 인해 단순한 데이터베이스 읽기 요구사항들이 굉장히 어려움을 겪고 있습니다. 단순한 목록을 출력하는 데에도 5초 이상의 시간이 소요되는 경우가 많습니다.
기존 테이블 구조를 변경하지 않고도 쿼리 튜닝으로 해결할 수 있는 부분들은 해결하고 있지만, 도저히 해결할 수 없는 경우도 발생하고 있습니다.
회사 내에는 message라는 이름의 테이블이 있는데, 이 테이블에는 이메일, SMS, 알림톡 등 각종 타입의 코멘트들이 혼재되어 있습니다. 즉, 문자열로 이루어진 콘텐츠가 대부분 혼재되어 있는 행의 수가 4천만 건 이상인 테이블입니다.
특정 데이터를 조회하여 목록에 같이 보여주어야 하는데, 어떻게 해도 만족스러운 성능을 얻을 수 없었습니다.
테이블 정규화
문제가 너무 커지기 때문에 정말로 손대고 싶지 않았지만, 결국 message 테이블에서 관리자들이 작성한 코멘트들을 분리해 별도의 테이블로 정규화하기로 결정했습니다.
새로운 테이블의 DDL은 다음과 같습니다. (이름들이 마음에 들지는 않지만, 기존 시스템과 위화감이 없도록 최대한 그대로 가져갔습니다.)
CREATE TABLE recommendation_admin_memo (
id INT AUTO_INCREMENT PRIMARY KEY,
recommendation_id VARCHAR(36) NOT NULL,
writer_id VARCHAR(36) NOT NULL,
content VARCHAR(8000) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT recommendation_admin_memo_ibfk_1 FOREIGN KEY (recommendation_id) REFERENCES recommendation (sid),
CONSTRAINT recommendation_admin_memo_ibfk_2 FOREIGN KEY (writer_id) REFERENCES account (sid)
) COMMENT '관리자들이 정보를 공유하기 위해 수업신청서에 작성한 메모';
CREATE INDEX idx_created_at ON recommendation_admin_memo (created_at);
CREATE INDEX recommendation_id ON recommendation_admin_memo (recommendation_id);
CREATE INDEX writer ON recommendation_admin_memo (writer_id);
그리고 데이터 이관을 위해 다음 쿼리를 실행했습니다.
INSERT INTO recommendation_admin_memo (recommendation_id, writer_id, content, created_at)
SELECT m.requested_system_sid, m.sender_account_sid, m.content, m.sent_at
FROM message m
JOIN account a ON a.sid = m.sender_account_sid
JOIN recommendation r ON r.sid = m.requested_system_sid
WHERE m.message_type = 2;
content의 길이가 너무 길어 varchar(500)에 넣지 못한다는 에러가 발생했습니다.
대체 메모가 얼마나 길길래 500자를 넘어서는지 궁금하여 아래의 쿼리를 실행해보았습니다.
SELECT MAX(LENGTH(m.content))
FROM message m
JOIN account a ON a.sid = m.sender_account_sid
JOIN recommendation r ON r.sid = m.requested_system_sid
WHERE m.message_type = 2;
많이 놀랐으나 어쨋든 데이터 이관은 해야하니 DDL의 varchar(500)을 varchar(18000)으로 변경하려 하니 이번엔 다음과 같은 에러가 발생했습니다.
[42000][1074] Column length too big for column 'content' (max = 16383); use BLOB or TEXT instead
varchar의 최대 길이는 65,536 정도라고 알고 있었는데, 이러한 결과가 나와서 결국 MySQL 5.7의 공식 문서를 찾아봤습니다.
문서를 대충 요약하자면, varchar 타입은 65,535 바이트까지 허용되며, length 함수는 문자열의 바이트를 반환한다는 내용입니다.
우리 테이블의 collation은 utf8mb4로 설정되어 있으므로 문자 한개당 4 바이트로 계산됩니다. 에러 메시지의 max값인 16,383이라는 수치에 4를 곱하면 65,532 바이트가 나오죠. 하지만 16,383을 초과하는 16,384라는 수치에 4를 곱하면 65,536 바이트이기 때문에 varchar 타입이 허용하는 65,535 바이트를 넘어가게 됩니다. 이쯤에서 varchar 뒤 소괄호에 넣는 값은 단순히 문자열의 길이(문자의 수)를 의미함을 알 수 있었습니다. 즉, varchar(16383)이라는 의미는 16383개의 문자를 넣을 수 있다는 의미죠.
이제 위 length 함수를 사용한 쿼리의 결과인 17,995는 17,995 바이트를 의미함을 알 수 있습니다. 여기서 단순히 4로 나눠 나온 값인 약 4500을 varchar에 적용해 varchar(4500)으로 설정하려고 할 수 있는데요, 맹점이 하나 있습니다. length 함수는 한글, 이모지등의 멀티바이트 문자에 대해서는 정확한 바이트 계산이 되지 않는다는 겁니다. (공식문서) 관리자들이 작성한 메모는 대부분이 한글이기 때문에, varchar(4500)으로 해도 역시 똑같은 에러가 발생하며 insert 쿼리가 실패하게 됩니다.
관리자들이 작성한 메모들중 가장 긴 메모의 진짜 길이를 알기 위해서는 다음과 같은 쿼리를 작성해야 했습니다.
SELECT MAX(CHARACTER_LENGTH(m.content))
FROM message m
JOIN account a ON a.sid = m.sender_account_sid
JOIN recommendation r ON r.sid = m.requested_system_sid
WHERE m.message_type = 2;
함수가 length에서 character_length로 바뀌었습니다. 이 함수가 문자의 수를 알려주는 함수입니다. 이제 content의 타입을 varchar(500)에서 varchar(7691) 이상으로 변경하고 insert 쿼리를 실행해보니 성공적으로 데이터 이관이 완료되었음을 확인할 수 있었습니다.
최종적으로 실행된 DDL은 다음과 같습니다.
CREATE TABLE recommendation_admin_memo (
id INT AUTO_INCREMENT PRIMARY KEY,
recommendation_id VARCHAR(36) NOT NULL,
writer_id VARCHAR(36) NOT NULL,
content VARCHAR(8000) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT recommendation_admin_memo_ibfk_1 FOREIGN KEY (recommendation_id) REFERENCES recommendation (sid),
CONSTRAINT recommendation_admin_memo_ibfk_2 FOREIGN KEY (writer_id) REFERENCES account (sid)
) COMMENT '관리자들이 정보를 공유하기 위해 수업신청서에 작성한 메모';
CREATE INDEX idx_created_at ON recommendation_admin_memo (created_at);
CREATE INDEX recommendation_id ON recommendation_admin_memo (recommendation_id);
CREATE INDEX writer ON recommendation_admin_memo (writer_id);
최초에 생각했던 varchar(500)과는 큰 차이가 있는 수치이지만, 기존의 비정상적으로 긴 관리자 메모들을 제거하자니 혹시 모를 꺼림칙함이 있었습니다. 또한, text 타입을 사용하자니 문제가 발생했습니다. text 타입은 기본적으로 char(16383)과 같기 때문에(중간과 큰 크기의 text는 더 큽니다) 실제 문자가 10개라면 이후를 모두 패딩처리하여 저장하므로 메모리가 과도하게 낭비될 수 있는 문제가 있고, 인덱싱이 제한되는 등의 문제가 발생할 수 있습니다. 그리고 데이터가 과하게 커지게 되면 off-page라고 하는 외부 공간에 저장할수도 있는 위험이 생기게 됩니다.
결국 다른 선택지를 고를 수 없어, 난생 처음으로 varchar에 이렇게 큰 수치의 값을 적용해보는 것을 선택하게 되었습니다. 처음에는 정말로 이렇게 해도 되는 건지 의심스러웠지만, 결국 수행하게 되었습니다.
또한, varchar 숫자의 의미와 length 함수의 맹점에 대해서 더 알아보고, 잘못 알고 있던 정보를 업데이트하게 된 계기가 되었습니다.
