索引失效主因是字段、子查询结果、连接层三者COLLATION不一致，需用SHOW FULL COLUMNS和SELECT @@collation_connection逐层验证，统一为相同COLLATE（如utf8mb4_0900_as_cs）并配置连接层字符集。
嵌套查询里索引失效，大概率不是SQL写得差，而是字段、子查询结果、连接层三者COLLATION没对齐——MySQL宁可全表扫描也不愿用错规则比对。
怎么确认是 COLLATION 不一致导致的索引失效
别靠EXPLAIN猜key是不是NULL，直接查三层真实值：
-
SHOW FULL COLUMNS FROM t1 LIKE 'name'→ 看Collation列，这是字段级实际排序规则 -
SHOW FULL COLUMNS FROM (SELECT name FROM t2) AS tmp→ 子查询返回列的Collation可能和原表不同（尤其跨库或视图） -
SELECT @@collation_connection→ 连接层决定'abc'这类字面量按什么规则解释，它会影响IN/JOIN中右侧表达式的默认collation
只要任意一对不完全相同（比如utf8mb4_unicode_ci vs utf8mb4_0900_as_cs），就触发隐式转换，t2上的索引立刻失效。
为什么在子查询里加 CONVERT 或 COLLATE 会让索引彻底失效
这类写法看似能跑通，实则把索引推下悬崖：
-
WHERE u.name IN (SELECT CONVERT(name USING utf8mb4) FROM t2)→CONVERT强制转码，t2.name原有索引无法命中 -
ON u.code = t2.code COLLATE utf8mb4_unicode_ci→ MySQL必须对每行t2.code做实时collation转换，索引失效且执行计划变成Using join buffer - ORM生成的SQL不会自动补这些，后续所有人写JOIN都得手动抄，极易漏掉或写错
这不是临时修复，是把结构缺陷藏进SQL层，下次出问题更难定位。
ALTER TABLE 修改字段 COLLATION 的实操要点
必须同时指定CHARACTER SET和COLLATE，只改一个等于白干：
- 错误写法：
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4→ 保留原COLLATE，可能还是utf8mb4_general_ci - 正确写法：
ALTER TABLE t2 MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs→ 和t1.name的collation完全对齐 - 批量改多个字段：
MODIFY a ..., MODIFY b ...分步写，避免锁表太久 - 改完立刻验证：
SHOW CREATE TABLE t2确认字段定义，再跑EXPLAIN看key是否出现索引名
大表操作会重建索引并锁表，MySQL 8.0+且无全文索引时可加ALGORITHM=INPLACE减少影响，但依然要选低峰期。
连接层不统一，改表等于白改
字段和子查询都改对了，但应用连进来时@@collation_connection仍是latin1_swedish_ci或utf8mb4_unicode_ci，WHERE条件里的字符串字面量仍被强行转码：
- Java JDBC必须带：
?useUnicode=true&characterEncoding=utf8mb4&collationConnection=utf8mb4_0900_as_cs - Python pymysql必须传：
charset='utf8mb4'，写'utf8'会掉进utf8mb3陷阱 - 验证是否生效：
SELECT CHARSET('测试'), COLLATION('测试')→ 返回值必须和t1.name的Collation完全一致
最容易被忽略的是：即使两个字段CHARACTER SET都是utf8mb4，只要COLLATE不同，索引照样失效——这不是配置疏漏，是MySQL优化器的硬性限制。
