지난 포스팅에서 데이터 이관 시 첨자(ª, ²)나 특수문자 변질을 막기 위해 특정 테이블이나 컬럼의 콜레이션을 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)라는 내부 점수를 부여합니다. 숫자가 낮을수록 우선순위가 높습니다(힘이 셉니다).
| Coercibility 수치 | 등급 명칭 (에러 표기) | 대상 표현식 (Expressions) | 우선순위 |
| 0 | EXPLICIT | 쿼리에 명시적으로 COLLATE 절을 사용한 경우 (예: col COLLATE utf8mb4_bin) | 최상 (1순위) |
| 1 | NONE | 서로 다른 콜레이션을 가진 문자열을 CONCAT() 등으로 결합한 결과 | 2순위 |
| 2 | IMPLICIT | 테이블의 일반 컬럼(Column), 스토어드 루틴 파라미터, 지역 변수 | 3순위 (컬럼 기본값) |
| 3 | SYSCONST | USER(), VERSION(), DATABASE() 등 시스템 함수 반환값 | 4순위 |
| 4 | COERCIBLE | 쿼리에 직접 입력한 문자열 리터럴 상수 (예: 'admin', 'ª') | 5순위 |
| 5 | NUMERIC | 숫자나 날짜 타입이 문자열 컨텍스트에서 사용될 때 (예: 1234, NOW()) | 6순위 |
| 6 | IGNORABLE | NULL 또는 NULL로부터 파생된 표현식 | 최하 (7순위) |
MySQL의 콜레이션 충돌 해결 알고리즘 (Resolution Rules)
- Coercibility 수치가 다를 때: 수치가 더 낮은(우선순위가 높은) 쪽의 콜레이션을 따릅니다.
- 예: WHERE o.user_code = 'admin' $\rightarrow$ 컬럼(IMPLICIT, 2) vs 상수 리터럴(COERCIBLE, 4)이므로, 상수 'admin'이 컬럼의 콜레이션을 따라가서 에러 없이 비교됩니다.
- 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;
| id | select_type | table | type | possible_keys | key | rows | Extra |
| 1 | SIMPLE | u | ALL | NULL | NULL | 100 | NULL |
| 1 | SIMPLE | o | ALL | NULL | NULL | 10000000 | Using where; Using join buffer (hash join) |
- 원인 분석 (옵티마이저 트레이스): 옵티마이저는 내부적으로 조인 조건을 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);
| id | select_type | table | type | possible_keys | key | rows | Extra |
| 1 | SIMPLE | u | ALL | NULL | NULL | 100 | NULL |
| 1 | SIMPLE | o | ref | idx_user_code | idx_user_code | 1 | Using index condition |
- 튜닝 결과: 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. 요약
- 서로 다른 콜레이션을 가진 컬럼끼리 JOIN 할 때 에러가 나는 이유는, 두 컬럼 모두 Coercibility(강제성 수치)가 2 (IMPLICIT)로 동일하여 MySQL이 우선순위를 결정하지 못하기 때문입니다.
- 에러가 나지 않는 _bin과의 조인이나, 쿼리에 무작정 COLLATE를 선언하는 방식은 Driven(후행) 테이블의 인덱스를 깨뜨려 Full Table Scan 장애를 유발합니다.
- 불가피하게 쿼리에서 COLLATE를 써야 한다면 반드시 먼저 읽히는 소규모(Driving) 테이블의 값에 대용량(Driven) 테이블의 콜레이션을 명시하여 인덱스를 보호해야 합니다.
- 근본적으로는 PK/FK 및 조인 키로 사용되는 코드성 컬럼들의 콜레이션을 전사 표준(예: utf8mb4_0900_as_cs 또는 utf8mb4_bin)으로 통일하는 스키마 거버넌스가 필수적입니다.