MySQL InnoDB 테이블 charset 변경시 주의사항

<서론>

최근 프로젝트에서 기존의 utf8 문자 집합을 가진 테이블을 utf8mb4로 변환하는 작업을 진행했습니다.

다들 아시다시피 utf8 3바이트 문자체계로는 이모지 등의 특정 문자를 삽입할 수 없습니다. (utf8mb3)

이번 작업도 레거시 아키텍처인 utf8(mb3) 기반의 테이블에 이모지 등의 문자가 삽입되어야하는 요구사항이 생겨서 캐릭터셋 컨버팅(확장)이 필요한 상황이었습니다.

 

문자 집합 확장 작업은 비손실 작업이기 때문에 데이터 손실에 대한 부담없이 작업할 수 있었습니다.

 

그래서 개발 DB 환경에서 먼저 성공적으로 작업 후 퇴근했는데, 개발팀에서 컬럼을 바꿨냐는 문의 메세지가 왔습니다.

당연히 바꾸지 않았다고 말씀드렸고, 다음 날 출근해서 문제를 파악하기 시작했습니다.

 

컬럼 타입 묵시적 변경 이슈는 데이터베이스의 저장 공간과 성능에 민감한 상황이었고,

타입 불일치로 인해 application 동작이 불가능한 문제가 실제로 발생했습니다.

캐릭터셋을 변환할 때 발생하는 문제와 이를 해결하는 방법에 대해 다루겠습니다.

 

<문제 상황>

기존 테이블 구조는 다음과 같았습니다:

CREATE TABLE `test_convert` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

이 테이블을 utf8mb4로 변환하기 위해 다음 명령어를 사용했습니다:

ALTER TABLE `dba`.`test_convert` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

 

 

변환 후 테이블 구조는 다음과 같았습니다:

CREATE TABLE `test_convert` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

 

보시는 것 처럼 TEXT 컬럼이 MEDIUMTEXT로 변경되었습니다.

 

명시적으로 MODIFY 하지 않았는데 뭐가 문제일까요?

 

 

<원인 분석>

utf8 문자 집합은 각 문자당 최대 3바이트를 사용합니다.

반면 utf8mb4 문자 집합은 각 문자당 최대 4바이트를 사용합니다.

TEXT 타입은 최대 65,535바이트를 저장할 수 있는데, 문자 수가 아니라 바이트 크기를 의미합니다

즉, utf8에서는 최대 약 21,844자의 문자를 저장할 수 있지만, utf8mb4에서는 최대 약 16,383자의 문자를 저장할 수 있습니다. 이는 utf8mb4로 변환 시 동일한 문자를 저장하기 위해 더 많은 저장 공간이 필요하다는 것을 의미합니다.

MySQL은 데이터 손실을 방지하기 위해 변환 과정에서 저장 공간이 충분하지 않을 경우 데이터 타입을 자동으로 확장합니다. TEXT 타입의 컬럼이 utf8mb4로 변환될 때 데이터 손실을 방지하기 위해 MEDIUMTEXT로 확장됩니다. MEDIUMTEXT는 최대 16MB를 저장할 수 있으며, 이는 utf8mb4 문자 집합으로 약 4,194,304자를 저장할 수 있습니다.

 

<해결 방법>

기존 데이터 타입을 유지하면서 utf8mb4로 변환하려면, 컬럼을 먼저 명시적으로 변환해야 합니다.
예를 들어, 데이터 손실이 없는 범위 내에서 TEXT 타입을 보장하려면 다음과 같은 방법을 사용할 수 있습니다:

1. ALTER TABLE ... MODIFY COLUMN ... 명령어를 사용하여 TEXT 컬럼의 캐릭터셋을 변경합니다.
2. 테이블의 캐릭터셋을 utf8mb4로 변경합니다.

CREATE TABLE `dba`.`test_convert2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 컬럼 캐릭터셋 변경
ALTER TABLE `dba`.`test_convert2` MODIFY COLUMN `name` TEXT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 테이블 캐릭터셋 변경
ALTER TABLE `dba`.`test_convert2` CONVERT TO CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI;

 

 

이렇게 단계를 나눠 수행하면 묵시적인 형 변환 없이 작업할 수 있습니다.

 

<추가 고려 사항>

 

1. 인덱스 크기 제한:

1. utf8mb4 문자 집합은 문자당 최대 4바이트를 사용하므로, 인덱스 제한도 검토해야 합니다.
   MySQL의 InnoDB 스토리지 엔진에서 인덱스의 최대 길이는 3072바이트입니다.
2. utf8에서는 최대 varchar(1024) 를 인덱스로 사용할 수 있지만
   utf8mb4에서는 최대 varchar(768) 까지만 문자만 인덱스로 사용할 수 있습니다.
   이를 해결하기 위해 인덱스 길이를 줄이거나, prefix indexing을 사용해야 합니다.
   관련 포스팅: https://leezzangmin.tistory.com/36
   

-- utf8mb4
CREATE TABLE `test_convert3` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `col1` varchar(768) NOT NULL,
  `col2` varchar(769) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

alter table dba.test_convert3 add index test1(col1);
-- 정상 처리 completed in 332 ms
alter table dba.test_convert3 add index test2(col2);
-- 정상 처리[42000][1071] Specified key was too long; max key length is 3072 bytes

==========================================================================

-- utf8mb3
CREATE TABLE `test_convert4` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `col1` varchar(1024) NOT NULL,
  `col2` varchar(1025) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table dba.test_convert4 add index test1(col1);
-- 정상 처리 completed in 410 ms
alter table dba.test_convert4 add index test2(col2);
-- 정상 처리 [42000][1071] Specified key was too long; max key length is 3072 bytes

 

2. 문자열 함수 및 비교:

• utf8mb4로 변환한 후에는 문자열 함수 및 비교 작업에서도 변경된 문자 집합의 특성을 고려해야 합니다. 예를 들어, LIKE 연산자나 정렬 순서 등이 영향을 받을 수 있습니다. 적절한 COLLATE를 사용하여 이러한 작업을 처리해야 합니다.
• 각 문자열 집합 별로 default collation이 정의되어 있습니다. 다른 테이블과 통일하는 것이 비교작업 시 의도치 않은 동작을 방지할 수 있습니다.

 

3. 성능 이슈:

• utf8mb4는 utf8보다 더 많은 저장 공간을 사용할 수도 있습니다. 이로 인해 디스크 I/O 성능에 영향을 미칩니다. 대량의 데이터가 저장된 테이블에서는 성능 저하를 유발할 수 있으므로, 적절한 인덱스 설계와 최적화를 통해 성능 문제를 해결해야 합니다.
• 기존 타입을 유지하는 경우에 내부 메모리 버퍼링 시에도 추가적인 공간이 필요해지므로 고려가 필요합니다.

  

결론

이런 자동 타입 변경은 데이터 손실을 방지하기 위한 MySQL의 친절한 배려에서 비롯된 문제입니다.

문제 해결을 위해서는 컬럼의 최대 크기를 조정하거나, 인덱스 크기 제한을 검토하는 등의 접근이 필요합니다.

변환 작업을 진행하기 전에 테스트를 통해 예상치 못한 문제를 방지하시기 바라며,

저와 같은 문제를 겪지 않으시기 위해서는 검증 스크립트도 작성하시는 것을 추천드립니다. 저는 검증 스크립트가 있었는데도 문제 없을 거라고 생각해서 돌리지 않았다가 이슈를 뒤늦게 발견한 케이스입니다.... ^^;;

쉬운 작업이지만 고려해서 이 글을 보시는 모두 변경 작업 잘 하시길 바랍니다~~

 

 

 

 

 

---

 

 

 

 

 

 

레퍼런스:

 

https://stackoverflow.com/questions/37356693/mysql-convert-to-utf8-without-structure-change

MySQL convert to UTF8 without structure change

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html

MySQL :: MySQL 8.0 Reference Manual :: 12.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets