如何解决MySQL迁移中由于字符序Collation不匹配导致的Join连接变慢？-mysql教程-PHP中文网

explain中key为null但possible_keys有值，是collation不一致的铁证；需用information_schema.columns比对关联字段的collation_name，即使字符集相同但排序规则不同（如utf8mb4_0900_as_cs与utf8mb4_unicode_ci）也会导致索引失效，alter table修改时必须同时指定character set和collate。

如何解决mysql迁移中由于字符序collation不匹配导致的join连接变慢？

EXPLAIN 显示 key 为 NULL 但 possible_keys 有值，先查 COLLATION 是否一致

这几乎是字符序不匹配的铁证。别急着改表，先确认问题根源：执行 EXPLAIN 后如果 key 列为空、type 是 ALL 或出现 Using join buffer，而单表查询极快，那大概率是 JOIN 字段的 COLLATION 对不上。

用下面这条语句直接比对两个表中关联字段的定义：

SELECT table_name, column_name, character_set_name, collation_name 
FROM information_schema.COLUMNS 
WHERE table_schema = 'your_db' 
  AND table_name IN ('t1', 't2') 
  AND column_name IN ('join_col');

注意重点看 collation_name —— 即使都是 utf8mb4 字符集，只要一个是 utf8mb4_0900_as_cs、另一个是 utf8mb4_unicode_ci，就足以让索引失效。

ALTER TABLE 修改字段时必须同时指定 CHARACTER SET 和 COLLATE

只改 CHARACTER SET 不改 COLLATE，等于白干。MySQL 实际做等值比较时依赖的是排序规则（collation），不是字符集本身。比如你把一个字段从 utf8 改成 utf8mb4，但没显式声明 COLLATE utf8_unicode_ci，MySQL 可能按库默认值补上 utf8mb4_0900_as_cs，反而更不兼容。

正确写法：ALTER TABLE t2 MODIFY join_col VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
别硬套“最新” collation：如果老表用的是 utf8_general_ci，新表又用 utf8mb4_0900_as_cs，照样不匹配
大表修改前务必测试锁表现：ALTER TABLE ... MODIFY 默认会重建索引并锁表；若支持 ALGORITHM=INPLACE（如 MySQL 8.0+ 的某些场景），可加该选项降低影响

存储过程或应用层传参时避免显式 COLLATE 强制转换

迁移后容易忽略的坑：应用代码或存储过程中，对字符串字面量用了类似 _utf8mb4'value' COLLATE utf8mb4_unicode_ci 这种写法，而目标字段实际是 utf8 + utf8_general_ci。MySQL 会因字符集和 collation 双重不兼容，拒绝走索引。

安全写法只有三类：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

省略 COLLATE：如 WHERE ref_id = _utf8mb4'xxx'，让 MySQL 按列定义自动推导
匹配字段原字符集：字段是 utf8，就用 _utf8'xxx'
显式声明且完全一致：仅当确认字段 collation 是 utf8_unicode_ci 时，才写 _utf8'xxx' COLLATE utf8_unicode_ci

特别注意 NAME_CONST() 在存储过程里生成的参数——它常带 _utf8mb4 ... COLLATE 前缀，极易触发隐式转换。