JOIN 两侧 collation 不一致让索引隐身：utf8mb4_general_ci 与 0900_ai_ci 混用的慢查询复盘

作者陈清和-322

MySQLcollation索引失效JOIN慢查询治理

结论先行：utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 都是「utf8mb4」，但它们是两套不同的比较规则（collation）。跨库 JOIN 时 collation 不一致，要么直接报 Illegal mix of collations，要么为了绕过错误对列做 CONVERT，结果把本该走索引的那一列「函数包裹」掉，退化成 2.3 亿行全表扫描。这个坑最阴的地方在于：两个字符集名字一模一样，肉眼根本看不出区别。

一、现象与背景：两个「一样的 utf8mb4」，JOIN 一下 P99 从 90ms 打到 6s

去年 Q3 给一套用户中心做慢查询巡检，环境是 MySQL 8.0.32、InnoDB、主从单实例。涉及两张表：

t_user（用户中心，5.7 时代建的）：email VARCHAR(190)，字符集 utf8mb4 / utf8mb4_general_ci，规模 4800 万行 / 6.1GB；
t_camp_user（营销库，8.0 新建）：email VARCHAR(190)，字符集 utf8mb4 / utf8mb4_0900_ai_ci，规模 2.3 亿行 / 9.8GB，email 上有二级索引 idx_email。

业务需求很朴素：给定一个 uid，匹配该用户在营销侧登记的 email，SQL 是 ... ON u.email = c.email WHERE u.uid = ?。

事故分三步走：

开发直接写 u.email = c.email，立刻报 ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='——注意，这是报错，不是慢；
为了救火，开发对营销侧套了一层 CONVERT(c.email USING utf8mb4) 绕过报错，结果 c.email 被函数包裹、idx_email 直接失效，EXPLAIN 里 type=ALL、key=NULL，单条从本来的 ~80ms 飙到 4.6s；
高峰时段这个接口每分钟 300+ 次，全表扫 + filesort 把连接池打满，整条链路 P99 从 90ms 涨到 6s，慢日志一晚上刷了几千条。

二、原理：charset 相同 ≠ collation 相同，而「函数包裹列」必废索引

三个机制叠在一起才酿成这事：

charset 是编码，collation 是比较规则。utf8mb4 只说明怎么存字节，真正决定「a 和 á 谁大、大小写敏不敏感」的是 collation。utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 是两套排序，MySQL 认为它们不可直接比较。
collation 优先级（coercibility）：列=2、字符串字面量=4，数值小者胜出。两个「列 vs 列」都是 2，若 collation 又不同，MySQL 不会替你猜，而是直接报 Illegal mix of collations。
CONVERT / COLLATE 会「函数包裹」被转换的那一列。B+ 树里存的是按原 collation 排好序的值，一旦列被套上 CONVERT(...)，优化器就没法用那棵索引树去定位，只能全表扫描。这正是「加了 CONVERT 反而更慢」的根因。
8.0 默认 collation 是 utf8mb4_0900_ai_ci，而大量 5.7 遗留库是 utf8mb4_general_ci——跨库 JOIN 天然踩雷。

三、具体做法（可复现）

1. 先诊断，确认是不是 collation 冲突：

-- 看两侧列的真实字符集与 collation
SELECT table_schema, table_name, column_name,
       character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE column_name = 'email'
  AND table_schema IN ('user_center','marketing');

-- 看连接/服务器的默认 collation
SHOW VARIABLES LIKE 'collation_%';

2. 救火（改 SQL，保住大表索引）： 把比较 collation 固定到需要走索引那侧的原生 collation，让另一侧（已被 uid 点查成 1 行）去被转换：

SELECT c.email
FROM t_user u
JOIN t_camp_user c
  ON u.email COLLATE utf8mb4_0900_ai_ci = c.email   -- 统一到 c 的原生 collation
WHERE u.uid = 12345;

这样 c.email 原生匹配、走 idx_email（type=ref），u 侧被转换但只剩 1 行，代价可忽略。EXPLAIN 验证 c 侧 type=ref、key=idx_email，单条回到 ~80ms。

3. 根治（统一 collation）：

ALTER TABLE t_user
  MODIFY email VARCHAR(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

t_user 4800 万行 / 6.1GB，collation 变更走 ALGORITHM=COPY（整表重建），预计 15–25 分钟。用 gh-ost 在低峰执行，期间给 binlog 回放留余量。

4. 兜底： 应用连接串固定 collation=utf8mb4_0900_ai_ci，my.cnf 里设 collation-server / collation-connection，让字面量与列默认一致，避免「列 vs 常量」场景再踩。

四、踩过的坑 / 反直觉之处

坑 1（最反直觉）：「都是 utf8mb4 就能 JOIN、就能走索引」——错。utf8mb4 只是 charset，collation 才是比较规则，general_ci 和 0900_ai_ci 是两套排序。
坑 2：CONVERT(x USING utf8mb4) 只声明了 charset，没指定 collation，遇到 Illegal mix 仍可能报错，必须显式带 COLLATE。
坑 3（代价最大）：救火时 CONVERT 加错侧——把 2.3 亿行的大表那列包了函数，索引失效全表扫。报错是「快」的，全扫是「慢」的，很多人怕报错反而把自己坑进慢查询。
坑 4：EXPLAIN 默认看不到 collation 转换，得用 EXPLAIN FORMAT=JSON 或 SHOW WARNINGS 才能发现隐式转换。
坑 5：ALTER 改 collation 不是 INPLACE/INSTANT，是 COPY，重建整表、占双倍空间，2.3 亿行表别在高峰裸跑。

五、结论与可复用清单

一句话：collation 不一致，要么报错、要么（为绕过而）废索引；正解是「统一到需要走索引那侧的原生 collation」+「根治 ALTER 统一」。从收益/成本看，救火改 SQL 是 10 分钟见效但治标，根治 ALTER 要 20 分钟+低峰窗口但一劳永逸，两者要一起做。

可复用清单：

建表时显式写 COLLATE，别依赖 server 默认；
跨库/跨表 JOIN 前，用 information_schema.COLUMNS 比对两侧 collation；
应用连接串 / my.cnf 固定 collation；
遇 Illegal mix of collations 或 JOIN 后 key=NULL，先查 collation，别盲目加索引；
救火 CONVERT/COLLATE 要作用在「已被 WHERE 收窄的小侧」，保住大表索引；
根治用 ALTER（COPY，低峰 + gh-ost），别高峰裸跑。

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。