MySQL字符集不同导致关联索引失效的解决方法

字符集或排序规则不一致会导致JOIN索引失效

你有没有遇到过这种情况：一个看似简单的JOIN查询，EXPLAIN一看，明明有索引，优化器却偏偏不用，硬是走了全表扫描？性能瞬间跌入谷底。很多时候，幕后黑手就藏在细节里——关联字段的字符集（CHARACTER SET）或排序规则（COLLATION）不一致。

这背后的原理其实很直接：B+树索引的运作，依赖于对键值的严格有序比较。当两边的排序规则不同时，数据库引擎就“懵”了——它无法确定在一种规则下相等的值，在另一种规则下是否还相等。为了保证结果百分之百正确，优化器只能放弃走索引这条“捷径”，退而求其次进行全表扫描，或者在内部进行隐式的字段值转换。无论哪种，性能开销都相当可观。

为什么字符集不一致会让 JOIN 索引失效

简单来说，这不是优化器“不想用”，而是“不敢用”。举个例子，utf8mb4_unicode_ci和utf8mb4_general_ci这两种常见的排序规则，对某些特殊字符（比如一些emoji或德语的变音符号）的排序和等价判断逻辑就不完全一样。如果关联字段采用了不同的规则，数据库根本无法保证基于索引扫描出来的结果集是正确的。因此，在EXPLAIN的执行计划中，你通常会看到possible_keys列有值（说明索引存在），但key列却是NULL（说明最终没使用）。

如何快速定位是字符集/排序规则惹的祸

当发现JOIN查询性能异常，且EXPLAIN显示possible_keys有值而key为NULL时，如果关联字段恰好是VARCHAR或TEXT这类文本类型，就该高度怀疑字符集问题了。排查起来也不复杂：

• 方法一：查看表结构。使用SHOW CREATE TABLE table_name命令，仔细对比参与JOIN的两个字段定义，看它们的CHARACTER SET和COLLATION是否完全一致。
• 方法二：查询系统表。直接查询information_schema.COLUMNS视图，信息更集中：

  SELECT column_name, character_set_name, collation_name 
  FROM information_schema.COLUMNS 
  WHERE table_name IN ('tb_mdm_verification_rules_result', 'tb_unqualify_sc_pool') 
  AND column_name IN ('project_code', 'customer_id');

• 一个关键提醒：别只盯着字符集。即使两个字段都是utf8mb4，只要排序规则不同（例如utf8mb4_0900_as_cs对比utf8mb4_unicode_ci），索引失效的问题同样会出现。

统一 collation 的实操步骤（不改数据）

解决问题的核心目标很明确：让参与关联的字段拥有完全一致的字符集和排序规则。目前，一个广泛兼容且推荐的选择是utf8mb4字符集搭配utf8mb4_unicode_ci排序规则（MySQL 5.7及以上版本默认支持良好）。如果业务要求区分大小写和重音，则可以考虑utf8mb4_0900_as_cs，但务必先评估业务逻辑是否允许。

具体操作可以按以下步骤进行：

• 第一步：确认现状。先用SHOW FULL COLUMNS FROM sunacwy_mdm.tb_mdm_verification_rules_result LIKE 'project_code';这样的命令，看清字段当前的定义。
• 第二步：修改单个字段。使用ALTER TABLE ... MODIFY语句修改字段属性，这个过程通常不会影响已有数据：

  ALTER TABLE sunacwy_mdm.tb_mdm_verification_rules_result 
  MODIFY project_code VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• 第三步：批量修改（谨慎操作）。如果需要修改多个字段，可以写在一个语句里，但要注意这可能引起较长时间的表锁，在业务低峰期操作或分步执行是更稳妥的选择：

  ALTER TABLE tb_unqualify_sc_pool 
  MODIFY project_code VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, 
  MODIFY mdm_data_id VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• 重要注意事项：MODIFY操作会自动重建字段上的索引。如果该字段是主键或外键约束的一部分，则需要先删除约束，修改字段后再重新添加约束。

容易被忽略的坑：连接字符串字面量和隐式转换

你以为统一了表结构就万事大吉了？还有一个隐蔽的角落可能让你前功尽弃：SQL语句中硬编码的字符串字面量。

• 典型错误示例：ON t1.project_code = t2.project_code AND t1.customer_id = 'abc123'。如果t1.customer_id的排序规则是utf8mb4_bin，而字符串字面量'abc123'默认采用连接会话的字符集和排序规则（通常是utf8mb4_general_ci），那么比较时仍可能触发隐式转换，导致索引无法被高效利用。
• 安全写法：为字面量显式指定排序规则，使其与字段定义匹配：t1.customer_id = _utf8mb4'abc123' COLLATE utf8mb4_unicode_ci。
• 更根本的解决之道：在应用层，确保传入数据库的参数其编码与目标字段完全一致。检查一下JDBC连接串，类似useUnicode=true&characterEncoding=utf8mb4这样的参数是否配置正确。
• 额外检查点：视图、存储过程或函数中如果引用了表字段并进行字符串操作或与常量比较，同样需要检查其内部的排序规则上下文，避免留下性能隐患。