Bong's Log

지난 포스팅을 통해 기존 utf8mb4_unicode_ci 환경에서 첨자(ª, ²)나 특수문자가 일반 문자/숫자와 동일시되어 발생하는 정합성 문제를 해결하려면, 콜레이션을 utf8mb4_bin 또는 utf8mb4_0900_as_cs로 전환해야 한다는 점을 다루었습니다.

또한 일부 테이블만 콜레이션을 변경했을 때 JOIN 시 발생하는 Illegal mix of collations (ERROR 1267)와 인덱스 무력화 현상도 살펴보았습니다.

이론적인 해결책은 명확하지만, 이미 수백 개의 테이블이 얽혀 돌아가는 라이브 운영 환경에서 콜레이션을 일괄 변경하는 것은 마치 달리는 자동차의 바퀴를 갈아 끼우는 것과 같습니다. 섣불리 ALTER TABLE을 실행했다가는 외래키(Foreign Key) 제약조건 에러로 작업이 중단되거나, 변경 도중 메타데이터 락(MDL)과 조인 실행 계획 붕괴로 인해 서비스 전체가 마비될 수 있습니다.

이번 포스팅에서는 운영 중인 데이터베이스의 콜레이션을 외래키 충돌이나 조인 성능 저하 없이 안전하게 전환하는 내부 아키텍처 원리와 4단계 실무 마이그레이션 절차를 완벽하게 정리해 드립니다.

1. 왜 외래키(FK)가 걸린 컬럼의 Collation은 변경이 거부될까?

부모 테이블(users)과 자식 테이블(orders)이 user_code라는 문자열 컬럼으로 외래키(Foreign Key) 관계를 맺고 있다고 가정해 보겠습니다. 두 테이블 모두 utf8mb4_unicode_ci를 사용 중일 때, 부모 테이블의 콜레이션부터 utf8mb4_0900_as_cs로 변경을 시도하면 MySQL은 즉시 아래와 같은 에러를 뱉어냅니다.

-- 부모 테이블의 Collation 단독 변경 시도
ALTER TABLE users 
MODIFY COLUMN user_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL;

ERROR 3780 (HY000): Referencing column 'user_code' and referenced column 'user_code' in foreign key constraint 'fk_orders_users' are incompatible.

(MySQL 5.7 이하에서는 ERROR 1025 또는 ERROR 1215: Cannot add foreign key constraint 발생)

내부 작동 원리: InnoDB B-Tree 인덱스와 가중치(Weight) 비교 메커니즘

InnoDB 스토리지 엔진에서 외래키 제약조건은 단순한 논리적 규칙이 아니라, 부모 테이블의 인덱스 트리(B-Tree)와 자식 테이블의 인덱스 트리를 물리적으로 직접 탐색(Index Lookup)하여 정합성을 검증하는 저수준(Low-level) 메커니즘입니다.

1. 인덱스 탐색 알고리즘 불일치: 자식 테이블에 데이터가 INSERT 될 때, InnoDB는 자식 컬럼의 값을 가지고 부모 테이블의 B-Tree 인덱스를 직접 검색합니다. 만약 두 컬럼의 콜레이션이 다르면 문자열을 바이트 가중치(Weight)로 변환하는 규칙과 B-Tree 정렬 순서 자체가 달라지므로, InnoDB는 인덱스 루프업(Lookup)을 수행할 수 없습니다.
2. 참조 무결성(Referential Integrity) 붕괴: 부모 테이블(0900_as_cs)에서는 'a'와 'ª'가 서로 다른 2개의 Row로 존재할 수 있지만, 자식 테이블(unicode_ci)에서는 두 값을 구분할 수 없습니다. 만약 부모 테이블의 'ª'가 삭제(ON DELETE CASCADE)될 때, 자식 테이블에서는 'a'까지 함께 삭제되는 대참사가 벌어지므로 MySQL은 데이터 딕셔너리(Data Dictionary) 레벨에서 스키마 변경을 원천 차단합니다.

2. 한 번에 바꾸려다 발생하는 2가지 치명적 운영 장애

외래키 에러를 피하기 위해 SET foreign_key_checks = 0;을 선언하고 테이블들을 순차적으로 ALTER 하는 방식을 흔히 떠올립니다. 하지만 대용량 실무 환경에서는 다음 두 가지 장애 포인트 때문에 이 방식을 그대로 쓰면 위험합니다.

① Collation 변경은 INPLACE가 불가능한 테이블 리빌드(COPY) 작업이다

인덱스가 걸린 문자열 컬럼의 콜레이션을 변경하면 B-Tree 인덱스의 정렬 순서가 완전히 바뀌므로 인덱스를 처음부터 다시 써야 합니다. MySQL에서 이 작업은 ALGORITHM=INSTANT나 INPLACE가 지원되지 않으며, 테이블 전체를 임시 테이블로 복사하는 ALGORITHM=COPY로 동작합니다.

-- ALGORITHM=INPLACE 강제 지정 시 발생하는 에러 확인
ALTER TABLE users 
MODIFY COLUMN user_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
ALGORITHM=INPLACE;
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

• 장애 영향도: ALGORITHM=COPY가 수행되는 동안 해당 테이블에는 쓰기 작업(DML)이 모두 차단(LOCK=SHARED)되며, 끝나는 시점에 배타적 메타데이터 락(Exclusive MDL)을 획득하면서 후속 쿼리들이 도미노처럼 적체되어 커넥션 풀이 고갈됩니다.

② 변경 과도기(Transition Window) 동안의 JOIN 에러 및 Full Scan 폭발

10개의 테이블을 순차적으로 변경하는 데 총 30분이 걸린다고 가정해 보겠습니다. 1번 테이블(users)의 변경이 끝나고 2번 테이블(orders)이 변경되기 전까지의 수 분 동안, 두 테이블의 콜레이션이 서로 달라지는 공백기(Inconsistency Window)가 발생합니다. 이 짧은 순간에 애플리케이션에서 두 테이블을 조인하는 쿼리가 유입되면:

• 일반 콜레이션 간 조인 시: Illegal mix of collations (ERROR 1267) 에러가 쏟아지며 API 장애 발생
• utf8mb4_bin으로 변경 시: 에러는 안 나지만 묵시적 형변환으로 인해 후행 테이블 인덱스가 깨져 DB CPU가 100%로 폭주

3. 실무 4단계 무중단 Collation 마이그레이션 아키텍처

서비스 중단이나 조인 장애 없이 기존 utf8mb4_unicode_ci 환경을 utf8mb4_0900_as_cs(또는 utf8mb4_bin)로 안전하게 전환하는 표준 엔지니어링 절차입니다.

[Step 1] 영향도 전수 조사: 변경 대상 컬럼 및 외래키(FK) 토폴로지 추출

가장 먼저 데이터베이스 내에서 콜레이션 변경이 필요한 모든 문자열 컬럼과, 그에 얽혀 있는 외래키 제약조건의 생성/삭제 스크립트를 자동으로 추출합니다.

-- 1. 외래키(FK)로 묶여 있는 문자열 컬럼 목록 및 현재 Collation 불일치/현황 조회
SELECT 
    kcu.TABLE_NAME AS child_table,
    kcu.COLUMN_NAME AS child_column,
    c1.COLLATION_NAME AS child_collation,
    kcu.CONSTRAINT_NAME AS fk_name,
    kcu.referenced_table_name AS parent_table,
    kcu.referenced_column_name AS parent_column,
    c2.COLLATION_NAME AS parent_collation
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.COLUMNS c1 
  ON kcu.TABLE_SCHEMA = c1.TABLE_SCHEMA AND kcu.TABLE_NAME = c1.TABLE_NAME AND kcu.COLUMN_NAME = c1.COLUMN_NAME
JOIN INFORMATION_SCHEMA.COLUMNS c2 
  ON kcu.REFERENCED_TABLE_SCHEMA = c2.TABLE_SCHEMA AND kcu.REFERENCED_TABLE_NAME = c2.TABLE_NAME AND kcu.REFERENCED_COLUMN_NAME = c2.COLUMN_NAME
WHERE kcu.TABLE_SCHEMA = 'your_db_name'
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
  AND c1.DATA_TYPE IN ('varchar', 'char', 'text');

[Step 2] 사전 정합성 검증: Collation 변경 시 깨질 잠재 데이터 클렌징

기존 utf8mb4_unicode_ci에서는 'a'와 'ª'가 같은 값이라 부모 테이블에 'a'만 있어도 자식 테이블에 'ª'가 들어갈 수 있었습니다. 하지만 콜레이션을 0900_as_cs나 bin으로 바꾸는 순간, 자식 테이블의 'ª'는 부모 테이블에 존재하지 않는 고아 레코드(Orphaned Record)가 되어 외래키 재결합 시 에러를 유발합니다.

-- 부모-자식 간 unicode_ci로는 매칭되지만, 변경될 엄격한 Collation(bin/as_cs) 기준으로는 깨지는 고아 데이터 색출
SELECT 
    c.order_id, 
    c.user_code AS child_val, HEX(c.user_code) AS child_hex,
    p.user_code AS parent_val, HEX(p.user_code) AS parent_hex
FROM orders c
JOIN users p ON c.user_code = p.user_code -- 기존 unicode_ci 인덱스 활용 조인
WHERE c.user_code COLLATE utf8mb4_bin <> p.user_code COLLATE utf8mb4_bin;

• 이 쿼리로 검출된 데이터가 있다면, 마이그레이션 전에 부모 테이블에 누락된 특수문자 키를 추가하거나 자식 데이터를 보정해야 합니다.

[Step 3] 실행 계획 붕괴를 막는 브릿지 인덱스(Bridge Index) 배포 (무중단 핵심 팁!)

테이블들을 순차적으로 변경하는 동안 조인 에러와 Full Table Scan을 막기 위해, 대용량 핵심 테이블에 변경될 콜레이션 기준의 가상 컬럼 인덱스를 미리 추가하고 애플리케이션 쿼리가 과도기 동안 안전하게 동작하도록 완충 지대를 만듭니다. (소규모 점검 창구(Maintenance Window) 확보가 가능하다면 Step 4로 바로 이동해도 무방합니다.)

-- [MySQL 8.0] 메타데이터만 변경되는 빠른 작업이거나 Online DDL(INPLACE)이 가능한 가상 컬럼 인덱스 추가
ALTER TABLE orders 
ADD COLUMN user_code_new VARCHAR(50) 
    GENERATED ALWAYS AS (user_code COLLATE utf8mb4_0900_as_cs) VIRTUAL,
ADD INDEX idx_orders_user_code_new (user_code_new);

[Step 4] FK 제어 및 안전한 일괄 스키마 전환 실행

소규모 테이블이나 트래픽이 적은 시간대에는 foreign_key_checks = 0을 활용한 트랜잭션/배치 스크립트로 변경하고, 수천만 건 이상의 대용량 테이블은 gh-ost(또는 pt-online-schema-change)를 사용하여 무중단으로 스키마를 교체합니다.

방법 A: 점검 시간(Maintenance Window) 내 일괄 DDL 스크립트 실행

테이블 전체의 기본 콜레이션과 하위 모든 문자열 컬럼을 한 번에 바꾸려면 MODIFY COLUMN 대신 CONVERT TO CHARACTER SET 구문을 사용하는 것이 누락을 막는 정석입니다.

-- 1. 현재 세션에서만 외래키 검사 비활성화 (글로벌 설정 변경 절대 금지!)
SET SESSION foreign_key_checks = 0;

-- 2. 부모 테이블 및 하위 모든 컬럼 Collation 일괄 변환
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

-- 3. 자식 테이블 및 하위 모든 컬럼 Collation 일괄 변환
ALTER TABLE orders 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

-- 4. 외래키 검사 재활성화
SET SESSION foreign_key_checks = 1;

-- 5. 데이터베이스 기본 Collation 변경 (향후 생성될 신규 테이블을 위해 필수)
ALTER DATABASE your_db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_cs;

⚠️ 주의사항: ALTER DATABASE ... COLLATE 명령어는 앞으로 새로 만들어질 테이블(CREATE TABLE)의 기본값만 바꿀 뿐, 이미 존재하는 테이블들의 콜레이션은 전혀 바꾸지 않습니다. 반드시 기존 테이블들에 대해 개별적으로 CONVERT TO CHARACTER SET을 수행해야 합니다.

방법 B: 24x365 무중단 대용량 환경 — Shadow Table 스왑 전략 (gh-ost 활용)

수억 건의 테이블이라 ALTER TABLE 실행 시 수십 분간 테이블이 잠기는 것을 허용할 수 없다면, 외래키 제약조건을 잠시 제거(Drop)한 뒤 gh-ost로 무중단 마이그레이션을 수행해야 합니다.

4. 마이그레이션 직후 반드시 확인해야 할 실행 계획(Execution Plan) 검증

모든 테이블의 콜레이션을 utf8mb4_0900_as_cs(또는 utf8mb4_bin)로 성공적으로 변경했더라도, 애플리케이션 커넥션(JDBC, Python, Node.js 등)의 설정이나 스토어드 프로시저가 여전히 구형 utf8mb4_unicode_ci로 남아 있으면 또 다른 문제를 일으킵니다.

검증 포인트: 커넥션 콜레이션 불일치 여부 확인

마이그레이션 직후 핵심 조인 쿼리와 조건절 쿼리에 대해 EXPLAIN과 SHOW WARNINGS를 실행하여 옵티마이저가 내부적으로 불필요한 형변환을 수행하고 있지 않은지 반드시 점검해야 합니다.

-- 1. 핵심 조인 및 첨자('ª', '²') 검색 쿼리의 실행 계획 검증
EXPLAIN SELECT o.order_id, u.user_code
FROM users u
JOIN orders o ON u.user_code = o.user_code
WHERE u.user_code = 'TYPE_²';

-- 2. 옵티마이저가 재작성한 내부 쿼리에 CONVERT() 함수가 끼어있는지 확인!
SHOW WARNINGS\G

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `db`.`o`.`order_id` AS `order_id`,`db`.`u`.`user_code` AS `user_code` 
         from `db`.`users` `u` join `db`.`orders` `o` 
         where ((`db`.`o`.`user_code` = 'TYPE_²') and (`db`.`u`.`user_code` = 'TYPE_²'))

• 정상 판정 기준: SHOW WARNINGS 결과의 where 절에 convert( 또는 COLLATE라는 단어가 보이지 않고 깔끔하게 (db.o.user_code = 'TYPE_²') 형태로 등치 비교가 이루어진다면, 두 테이블의 인덱스와 콜레이션이 완벽하게 일치하여 최고 성능으로 동작하고 있는 것입니다!

5. 요약 

1. 외래키(FK)가 걸린 테이블의 콜레이션 변경 시 발생하는 ERROR 3780은 InnoDB가 B-Tree 인덱스의 가중치(Weight)를 직접 비교하여 참조 무결성을 검증하기 때문에 발생하는 아키텍처적 제약입니다.
2. 콜레이션 변경(CONVERT TO CHARACTER SET)은 인덱스 전체를 재정렬해야 하므로 ALGORITHM=COPY로 동작하며, 순차 변경 시 과도기 동안 조인 에러(ERROR 1267)와 Full Scan을 유발할 수 있습니다.
3. 안전한 전환을 위해서는

      ① INFORMATION_SCHEMA를 통한 영향도 파악 $\rightarrow$

      ② 첨자/대소문자로 인한 고아 데이터 사전 정합성 검증 $\rightarrow$

      ③ SET SESSION foreign_key_checks = 0 또는 gh-ost 동시 컷오버를 통한 일괄 전환 $\rightarrow$

      ④ SHOW WARNINGS를 통한 실행 계획 내 암시적 형변환 검증의 4단계 절차를 준수해야 합니다.

지난 포스팅에서 데이터 이관 시 첨자(ª, ²)나 특수문자 변질을 막기 위해 특정 테이블이나 컬럼의 콜레이션을 utf8mb4_unicode_ci에서 utf8mb4_bin이나 utf8mb4_0900_as_cs로 변경하는 전략을 다루었습니다.

하지만 실무에서 일부 테이블의 콜레이션만 부분적으로 변경하거나, 레거시 테이블(utf8mb4_unicode_ci)과 신규 테이블(utf8mb4_0900_ai_ci)을 함께 운영하다 보면 필연적으로 마주치는 치명적인 장애가 있습니다. 바로 두 테이블을 JOIN 하거나 서브쿼리로 비교할 때 발생하는 ERROR 1267 (HY000): Illegal mix of collations 에러입니다.

더 무서운 것은 에러가 나지 않고 조인이 수행되더라도, 옵티마이저가 내부적으로 인덱스를 포기하고 Full Table Scan을 선택해 DB CPU를 100%로 치솟게 만드는 현상입니다. 이번 글에서는 MySQL이 서로 다른 콜레이션을 어떻게 중재하는지 그 내부 아키텍처인 Coercibility(콜레이션 강제성) 원리를 파헤치고, 인덱스를 살리는 실무 쿼리 튜닝 및 아키텍처 해법을 정리해 드립니다.

1. 문제 재현: 왜 서로 다른 Collation을 JOIN 하면 오류가 날까?

기존 레거시 테이블(orders_legacy, utf8mb4_unicode_ci)과 신규 이관 테이블(users_new, utf8mb4_0900_ai_ci)을 사용자 코드(user_code) 기준으로 조인하는 상황을 가정해 보겠습니다.

-- 1. 레거시 테이블 (MySQL 5.7 표준이었던 utf8mb4_unicode_ci)
CREATE TABLE orders_legacy (
    order_id BIGINT PRIMARY KEY,
    user_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    INDEX idx_user_code (user_code)
);

-- 2. 신규 테이블 (MySQL 8.0 기본값인 utf8mb4_0900_ai_ci)
CREATE TABLE users_new (
    user_id BIGINT PRIMARY KEY,
    user_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    INDEX idx_user_code (user_code)
);

-- 3. 두 테이블 JOIN 실행
SELECT * 
FROM orders_legacy o
JOIN users_new u ON o.user_code = u.user_code;

위 쿼리를 실행하면 MySQL 엔진은 결과를 반환하는 대신 즉시 아래와 같은 에러를 뱉어냅니다.

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

에러 메시지를 자세히 보면 콜레이션 이름 뒤에 IMPLICIT이라는 키워드가 붙어 있습니다. 이 키워드가 바로 MySQL 내부 아키텍처를 이해하는 핵심 열쇠입니다.

2. 내부 작동 원리: Collation Coercibility (콜레이션 강제성/우선순위)

MySQL 옵티마이저는 = 연산이나 JOIN을 수행할 때, 양변의 문자열 비교 규칙(Collation)이 다르면 어느 한쪽의 규칙으로 통일해야 합니다. 이때 "누구의 콜레이션이 더 힘이 센가(우선순위가 높은가)?"를 판단하기 위해 모든 문자열 표현식에 Coercibility(강제성 수치, 0~6)라는 내부 점수를 부여합니다. 숫자가 낮을수록 우선순위가 높습니다(힘이 셉니다).

MySQL의 콜레이션 충돌 해결 알고리즘 (Resolution Rules)

1. Coercibility 수치가 다를 때: 수치가 더 낮은(우선순위가 높은) 쪽의 콜레이션을 따릅니다.
   • 예: WHERE o.user_code = 'admin' $\rightarrow$ 컬럼(IMPLICIT, 2) vs 상수 리터럴(COERCIBLE, 4)이므로, 상수 'admin'이 컬럼의 콜레이션을 따라가서 에러 없이 비교됩니다.
2. Coercibility 수치가 같을 때 (JOIN 에러의 원인!):
   • o.user_code와 u.user_code는 둘 다 테이블 컬럼이므로 동일하게 Coercibility 2 (IMPLICIT)를 가집니다.
   • 양쪽의 힘(우선순위)이 2 vs 2로 팽팽하게 맞서는데, 둘 다 _bin(Binary) 콜레이션이 아니라면 MySQL 옵티마이저는 어느 쪽 가중치 알고리즘을 써야 할지 결정할 수 없어 Illegal mix of collations 에러를 발생시키고 쿼리를 중단합니다.

💡 예외 규칙 (_bin의 승리): 만약 동일한 문자셋(utf8mb4) 내에서 한쪽 컬럼이 utf8mb4_bin이고 다른 쪽이 utf8mb4_unicode_ci라면, 둘 다 Coercibility가 2라도 MySQL 내부 규칙에 의해 _bin 콜레이션이 승리하여 에러가 나지 않습니다. 하지만 아래에서 설명할 인덱스 무력화 참사가 발생합니다.

3. 실행 계획(Execution Plan) 분석: 잘못된 해결책이 부르는 장애

개발자들이 Illegal mix of collations 에러를 마주했을 때 가장 흔히 하는 실수는 쿼리의 ON 절 아무 곳에나 COLLATE 키워드를 붙여 에러만 안 나게 덮는 것입니다.

특정 컬럼에 COLLATE를 명시하면 해당 컬럼의 Coercibility가 2 (IMPLICIT)에서 0 (EXPLICIT)으로 격상되어 에러는 사라집니다. 하지만 COLLATE를 어느 쪽 테이블에 걸었느냐에 따라 실행 계획(Execution Plan)은 천국과 지옥으로 갈립니다.

시나리오: orders_legacy (1,000만 건, Driven) $\leftrightarrow$ users_new (100건, Driving) 조인

❌ 나쁜 튜닝: 대용량(Driven) 테이블 컬럼이 형변환되도록 유도한 경우

소규모 테이블(users_new)을 먼저 읽고 대용량 테이블(orders_legacy)을 인덱스로 찔러야(Index Lookup) 하는 상황에서, 소규모 테이블 쪽에 COLLATE utf8mb4_0900_ai_ci를 강제해 버리면 대용량 테이블의 컬럼이 묵시적 형변환을 당합니다.

-- [나쁜 예] u.user_code(0900_ai_ci)를 EXPLICIT(0)으로 선언 -> o.user_code가 변환됨!
EXPLAIN SELECT * 
FROM users_new u
JOIN orders_legacy o ON o.user_code = u.user_code COLLATE utf8mb4_0900_ai_ci;

• 원인 분석 (옵티마이저 트레이스): 옵티마이저는 내부적으로 조인 조건을 CONVERT(o.user_code USING utf8mb4) COLLATE utf8mb4_0900_ai_ci = u.user_code로 재작성합니다. o.user_code 컬럼에 변형 함수가 씌워진 것과 같으므로, 기존 utf8mb4_unicode_ci 기준으로 정렬된 B-Tree 인덱스를 전혀 사용할 수 없습니다. 결국 1,000만 건 전체를 스캔(Full Scan)합니다.

올바른 튜닝: Driving 테이블(먼저 읽는 소규모 테이블) 값을 타겟 인덱스 콜레이션에 맞춤

대용량 테이블(orders_legacy)의 인덱스를 온전히 보호하려면, 비교 대상 값을 대용량 테이블의 콜레이션(utf8mb4_unicode_ci)으로 변환시켜 찔러 넣어주어야 합니다.

-- [좋은 예] u.user_code 값을 o.user_code의 콜레이션(unicode_ci)으로 명시적 변환하여 인덱스 보호
EXPLAIN SELECT * 
FROM users_new u
JOIN orders_legacy o ON o.user_code = (u.user_code COLLATE utf8mb4_unicode_ci);

• 튜닝 결과: u.user_code에서 읽은 100개의 문자열만 메모리에서 utf8mb4_unicode_ci로 평가된 뒤, o.user_code의 인덱스(idx_user_code)를 ref 타입으로 정확히 타격하여 0.01초 만에 쿼리가 종료됩니다.

4. 실무 트러블슈팅 및 근본적인 아키텍처 해결 전략

쿼리마다 COLLATE를 붙이는 것은 임시방편일 뿐입니다. 시니어 아키텍트가 권장하는 실무 해결 가이드 3단계를 소개합니다.

전략 1: 이관 및 통합 전 전수 조사 (Collation 파편화 탐지 쿼리)

데이터베이스 내에 조인 시 시한폭탄이 될 수 있는 콜레이션 불일치 컬럼들을 INFORMATION_SCHEMA를 통해 한 번에 색출해야 합니다.

-- 동일 컬럼명을 가지면서 테이블별로 Collation이 다르게 설정된 조인 위험군 검출 쿼리
SELECT 
    COLUMN_NAME,
    GROUP_CONCAT(DISTINCT CONCAT(TABLE_NAME, '(', COLLATION_NAME, ')') SEPARATOR ', ') AS table_collation_list,
    COUNT(DISTINCT COLLATION_NAME) AS collation_cnt
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND DATA_TYPE IN ('varchar', 'char', 'text')
GROUP BY COLUMN_NAME
HAVING COUNT(DISTINCT COLLATION_NAME) > 1;

전략 2: 스토어드 프로시저/함수(Routine) 생성 시의 숨은 함정 제거

테이블들의 콜레이션을 모두 통일했는데도 배치 프로시저를 돌릴 때만 Illegal mix of collations 에러가 나는 경우가 있습니다. 프로시저나 트리거는 생성될 당시(CREATE 시점)의 세션 collation_connection 설정값을 메타데이터에 영구 박제하기 때문입니다. 과거에 만든 프로시저 내부의 변수(DECLARE v_code VARCHAR(50);)는 구형 콜레이션을 가지고 있어 신규 테이블과 조인 시 충돌합니다.

-- 스토어드 프로시저 내부 변수의 구형 Collation 확인
SELECT ROUTINE_NAME, DATABASE_COLLATION, COLLATION_CONNECTION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_SCHEMA = 'your_database_name';

-- 해결책: 프로시저 내 변수 선언 시 명시적으로 COLLATE 지정 또는 올바른 세션 설정 후 재생성
DECLARE v_user_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

전략 3: 스키마 변경이 불가능할 때 — 가상 컬럼 함수 기반 인덱스(Functional Index) 활용

만약 외부 솔루션 테이블이거나 레거시 의존성 때문에 대용량 테이블(orders_legacy, utf8mb4_unicode_ci)의 컬럼 콜레이션을 직접 ALTER 할 수 없는데, 신규 테이블(utf8mb4_0900_as_cs 또는 utf8mb4_bin)이 대용량이고 레거시 테이블도 대용량이라 양쪽 모두 인덱스가 필요한 상황이라면 MySQL 8.0의 함수 기반 인덱스( 또는 Generated Column)로 해결할 수 있습니다.

-- 기존 컬럼은 건드리지 않고, 신규 콜레이션으로 형변환된 인덱스를 추가 생성
ALTER TABLE orders_legacy
ADD COLUMN user_code_bin VARCHAR(50) 
    GENERATED ALWAYS AS (user_code COLLATE utf8mb4_bin) VIRTUAL,
ADD INDEX idx_user_code_bin (user_code_bin);

이렇게 구성하면 기존 레거시 쿼리는 원래의 user_code(unicode_ci) 인덱스를 그대로 사용하고, 신규 테이블과의 조인 쿼리는 user_code_bin 인덱스를 타게 되어 무중단으로 완벽한 호환성과 성능을 확보할 수 있습니다.

5. 요약

1. 서로 다른 콜레이션을 가진 컬럼끼리 JOIN 할 때 에러가 나는 이유는, 두 컬럼 모두 Coercibility(강제성 수치)가 2 (IMPLICIT)로 동일하여 MySQL이 우선순위를 결정하지 못하기 때문입니다.
2. 에러가 나지 않는 _bin과의 조인이나, 쿼리에 무작정 COLLATE를 선언하는 방식은 Driven(후행) 테이블의 인덱스를 깨뜨려 Full Table Scan 장애를 유발합니다.
3. 불가피하게 쿼리에서 COLLATE를 써야 한다면 반드시 먼저 읽히는 소규모(Driving) 테이블의 값에 대용량(Driven) 테이블의 콜레이션을 명시하여 인덱스를 보호해야 합니다.
4. 근본적으로는 PK/FK 및 조인 키로 사용되는 코드성 컬럼들의 콜레이션을 전사 표준(예: utf8mb4_0900_as_cs 또는 utf8mb4_bin)으로 통일하는 스키마 거버넌스가 필수적입니다.

레거시 시스템(MySQL 5.7 이하 또는 MariaDB)에서 가장 표준적으로 권고되던 콜레이션은 단연 utf8mb4_unicode_ci였습니다. 다국어 정렬의 정확성을 보장하기 위해 많은 기업이 이 콜레이션을 표준으로 채택했죠.

하지만 화학식(H₂O), 수학 기호(x²), 서수 표현(1ª), 혹은 상표권 기호(™) 등이 포함된 데이터를 이관하거나 다룰 때, utf8mb4_unicode_ci 환경에서는 위첨자(Superscript)와 아래첨자(Subscript)가 일반 알파벳이나 숫자로 둔갑하여 심각한 데이터 정합성 오류를 일으킵니다.

이번 포스팅에서는 기존에 사용하던 utf8mb4_unicode_ci가 왜 첨자(Superscript/Subscript)의 차이를 무시하는지 그 내부 아키텍처를 파헤치고, 운영 중인 DB에서 안전하게 첨자를 구분하도록 변경하는 실무 마이그레이션 및 쿼리 튜닝 가이드를 공유합니다.

1. 문제 재현: utf8mb4_unicode_ci에서 첨자는 어떻게 취급되는가?

기존 환경(utf8mb4_unicode_ci)에서 위첨자 ª(U+00AA), ²(U+00B2) 및 아래첨자 ₁(U+2081) 데이터가 일반 문자 'a', '2', '1'과 어떻게 비교되는지 SQL로 직접 확인해 보겠습니다.

-- 기존 utf8mb4_unicode_ci 환경에서의 첨자 비교 테스트
SELECT 
    'ª' = 'a' COLLATE utf8mb4_unicode_ci AS super_a_eq_a,
    '²' = '2' COLLATE utf8mb4_unicode_ci AS super_2_eq_2,
    '₁' = '1' COLLATE utf8mb4_unicode_ci AS sub_1_eq_1,
    '²' = '₂' COLLATE utf8mb4_unicode_ci AS super_2_eq_sub_2;

충격적이게도 utf8mb4_unicode_ci는 위첨자 ², 아래첨자 ₂, 그리고 일반 숫자 2를 모두 완벽히 똑같은 값으로 판별합니다. 이로 인해 데이터 이관 시 다음과 같은 장애가 발생합니다.

1. 이관 시 중복 키 에러 (Duplicate Entry): 원본 데이터에서 'TYPE_2', 'TYPE_²', 'TYPE_₂'라는 3개의 서로 다른 상품 코드를 UNIQUE 제약조건이 걸린 타겟 테이블로 이관할 때, 첫 번째 'TYPE_2'만 적재되고 나머지 두 개는 중복 에러를 뱉으며 이관이 중단됩니다.
2. 집계(GROUP BY) 데이터 증발: SELECT code, COUNT(*) FROM items GROUP BY code; 실행 시, 첨자로 구분된 개별 코드들이 하나의 그룹으로 강제 병합되어 통계 및 정산 데이터가 완전히 틀어집니다.

2. 내부 작동 원리: UCA 4.0.0과 유니코드 호환성 분해(NFKD)

왜 utf8mb4_unicode_ci는 멀쩡한 첨자를 일반 문자/숫자와 똑같이 취급할까요? 그 비밀은 이 콜레이션의 기반이 되는 UCA(Unicode Collation Algorithm) 4.0.0과 가중치 레벨(Weight Level) 구조에 있습니다.

유니코드 표준에서 위첨자(ª, ²)와 아래첨자(₁, ₂)는 기본 문자에 서식 태그(<super>, <sub>)가 결합된 호환성 문자(Compatibility Characters)로 분류됩니다.

• ª (U+00AA) $\rightarrow$ 호환성 분해(NFKD) $\rightarrow$ <super> + a (U+0061)
• ² (U+00B2) $\rightarrow$ 호환성 분해(NFKD) $\rightarrow$ <super> + 2 (U+0032)
• ₂ (U+2082) $\rightarrow$ 호환성 분해(NFKD) $\rightarrow$ <sub> + 2 (U+0032)

UCA 알고리즘은 문자를 비교할 때 3단계의 가중치(Level)를 매깁니다.

즉, 첨자의 차이는 Level 3(Tertiary Weight) 영역에 기록됩니다. 하지만 utf8mb4_unicode_ci는 이름 끝의 _ci(Case-Insensitive)가 의미하듯 Level 1 가중치만 비교하고 Level 2와 Level 3 가중치는 완전히 잘라버립니다(Truncate).

실제 MySQL 엔진이 인덱스에 저장하는 내부 가중치 바이트를 WEIGHT_STRING()으로 뜯어보면 이를 명확히 확인할 수 있습니다.

-- utf8mb4_unicode_ci가 생성하는 내부 인덱스 정렬/비교 가중치 확인
SELECT 
    HEX(WEIGHT_STRING('2' COLLATE utf8mb4_unicode_ci)) AS weight_normal_2,
    HEX(WEIGHT_STRING('²' COLLATE utf8mb4_unicode_ci)) AS weight_super_2,
    HEX(WEIGHT_STRING('₂' COLLATE utf8mb4_unicode_ci)) AS weight_sub_2,
    HEX(WEIGHT_STRING('a' COLLATE utf8mb4_unicode_ci)) AS weight_normal_a,
    HEX(WEIGHT_STRING('ª' COLLATE utf8mb4_unicode_ci)) AS weight_super_a;

InnoDB의 B-Tree 인덱스는 원본 문자열이 아닌 이 가중치(0x0E2B, 0x0E33)를 기준으로 정렬하고 중복을 체크합니다. 따라서 DB 엔진 입장에서는 '2', '²', '₂'가 완벽히 동일한 값(0x0E2B)으로 보이는 것입니다.

3. 아키텍처 해결 방안: 어떤 Collation으로 전환해야 하는가?

기존에 utf8mb4_unicode_ci를 사용 중이었다면, MySQL 버전에 따라 첨자 차이를 정확히 인식하도록 다음 두 가지 콜레이션 중 하나로 전환해야 합니다.

• MySQL 5.7 이하 환경: 안타깝게도 utf8mb4_unicode_cs(Case-Sensitive)라는 콜레이션은 MySQL에 존재하지 않습니다. 따라서 5.7 버전에서는 utf8mb4_bin이 유일하고도 가장 확실한 해법입니다.
• MySQL 8.0 이상 환경: 기존 unicode_ci가 주던 사전순 정렬의 이점을 유지하면서 첨자와 대소문자만 엄격히 분리하고 싶다면, Level 3 가중치까지 모두 비교하는 utf8mb4_0900_as_cs로 변경하는 것이 베스트 프랙티스입니다.

4. 실무 트러블슈팅 및 마이그레이션 팁

이미 운영 중인 utf8mb4_unicode_ci 테이블에서 첨자 문제를 해결하기 위한 실무 절차와 튜닝 팁입니다.

① 기존 테이블에서 첨자로 인해 뭉개진(충돌 잠재) 데이터 검출 쿼리

컬럼의 콜레이션을 변경하기 전, 현재 테이블에 일반 문자와 첨자 문자가 혼재되어 있는지 먼저 파악해야 합니다. WEIGHT_STRING은 같지만 실제 BINARY(바이트) 값은 다른 로우를 찾으면 됩니다.

-- utf8mb4_unicode_ci 기준으로는 같지만, 실제 바이트(첨자 여부 등)가 다른 데이터 검출
SELECT 
    a.id AS id_1, a.target_col AS val_1, HEX(a.target_col) AS hex_1,
    b.id AS id_2, b.target_col AS val_2, HEX(b.target_col) AS hex_2
FROM my_table a
JOIN my_table b 
  ON a.target_col = b.target_col          -- 1. 기존 unicode_ci 인덱스를 활용한 동일 가중치 조인
 AND BINARY a.target_col <> BINARY b.target_col -- 2. 실제 바이트(첨자/대소문자)가 다른 건만 필터링
 AND a.id < b.id;

② 컬럼 Collation 변경 시 Online DDL 제약사항 주의 (중요!)

운영 환경에서 ALTER TABLE로 컬럼의 콜레이션을 변경할 때, 해당 컬럼에 인덱스(Index)가 걸려 있다면 INSTANT나 INPLACE 알고리즘이 불가능할 수 있습니다.

콜레이션이 바뀌면 B-Tree 인덱스의 정렬 순서(Weight)가 완전히 달라지므로, MySQL은 인덱스를 처음부터 다시 빌드(Rebuild)해야 합니다. 대용량 테이블에서는 테이블 락(Metadata Lock)이나 디스크 I/O 폭증을 유발할 수 있으므로 반드시 ALGORITHM=COPY 또는 pt-online-schema-change, gh-ost 같은 무중단 스키마 변경 도구를 사용해야 합니다.

-- 대용량 운영 테이블의 안전한 Collation 변경 예시 (MySQL 8.0 기준)
ALTER TABLE my_table 
MODIFY COLUMN item_code VARCHAR(100) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
ALGORITHM=COPY, LOCK=SHARED; -- 읽기 허용, 대규모 환경에서는 gh-ost 권장

③ 실행 계획(Execution Plan)을 망치지 않는 임시 조회 쿼리 튜닝

당장 스키마(utf8mb4_unicode_ci)를 변경할 수 없는 상태에서 이관 프로그램이나 배치 쿼리가 정확히 위첨자 '²'가 포함된 데이터만 조회·업데이트해야 한다면, Index Range Scan + Binary Filter(2단계 필터링) 기법을 사용해야 합니다.

-- [나쁜 예] 컬럼에 직접 COLLATE를 걸면 인덱스 사용 불가 (type: ALL, Full Table Scan)
EXPLAIN SELECT * FROM chemical_formulas 
WHERE formula COLLATE utf8mb4_bin = 'H₂O';

-- [좋은 예] 기존 utf8mb4_unicode_ci 인덱스를 100% 활용한 뒤 첨자만 정밀 타격 (type: ref)
EXPLAIN SELECT * FROM chemical_formulas 
WHERE formula = 'H₂O'               -- Step 1: Index Lookup ('H2O', 'H²O', 'H₂O' 모두 추출)
  AND formula COLLATE utf8mb4_bin = 'H₂O'; -- Step 2: 추출된 소수의 건만 메모리에서 바이트 비교

• 실행 계획 해설: 첫 번째 조건(formula = 'H₂O')이 idx_formula 인덱스를 정상적으로 타서 수백만 건 중 가중치가 같은 3건(H2O, H²O, H₂O)만 디스크에서 읽어옵니다(rows: 3). 이후 두 번째 조건이 CPU 메모리 상에서 아래첨자 ₂가 정확히 일치하는 1건만 걸러내므로 성능 저하 없이 완벽한 정합성을 보장합니다.

5. 요약

1. 기존에 사용하던 utf8mb4_unicode_ci는 UCA 알고리즘의 Level 3(Tertiary) 가중치를 무시하기 때문에, 위첨자(ª, ²)와 아래첨자(₁, ₂)를 일반 문자/숫자와 100% 동일한 값으로 처리합니다.
2. 첨자 차이를 명확히 보존하며 데이터를 이관하려면, MySQL 5.7에서는 utf8mb4_bin으로, MySQL 8.0에서는 utf8mb4_0900_as_cs(또는 utf8mb4_bin)로 타겟 컬럼의 콜레이션을 변경해야 합니다.
3. 콜레이션 변경은 인덱스 재구성을 수반하는 무거운 작업이므로, 변경 전이거나 스키마 수정이 불가능할 때는 WHERE col = '값' AND col COLLATE utf8mb4_bin = '값' 패턴으로 인덱스와 정합성을 동시에 챙기시기 바랍니다.

3. 태그 (Tags)

MySQL, utf8mb4_unicode_ci, Collation, 콜레이션, 위첨자, 아래첨자, 특수문자이관, 데이터이관, utf8mb4_bin, utf8mb4_0900_as_cs, UCA알고리즘, WEIGHT_STRING, 실행계획튜닝, DB마이그레이션, 트러블슈팅