本文详解 MySQL 存储过程中因 COLLATE 显式指定与列实际字符集/排序规则不兼容，导致索引失效、查询变慢的根本原因，并提供可落地的字符集统一策略、SQL 优化写法及验证方法。
本文详解 mysql 存储过程中因 `collate` 显式指定与列实际字符集/排序规则不兼容，导致索引失效、查询变慢的根本原因，并提供可落地的字符集统一策略、sql 优化写法及验证方法。
在 MySQL 存储过程（Stored Procedure）中执行基于字符串主键或索引字段的 WHERE 查询时，若出现“本应走索引却全表扫描、响应时间骤增”的现象，一个极易被忽视但影响深远的原因是：字符集（character set）与排序规则（collation）的隐式不兼容。您提供的案例正是典型代表——ref_id 列使用 utf8 字符集（对应 utf8_unicode_ci 或 utf8_general_ci），而存储过程中通过 NAME_CONST() 传入的 UUID 字符串却强制指定了 utf8mb4_unicode_ci 排序规则，触发了 MySQL 的隐式类型转换与排序规则冲突，最终使索引完全失效。
? 根本原因：索引依赖排序规则一致性
MySQL 的 B+ 树索引本质上是按列值的字节序或排序规则定义的逻辑顺序组织的。当查询条件中的常量字符串与索引列的 collation 不一致时（例如列是 utf8_unicode_ci，而条件是 utf8mb4_unicode_ci），MySQL 无法保证两者在排序语义上等价，因此拒绝复用现有索引——它必须逐行读取并做字符级比较（row-by-row comparison），性能急剧下降。
您当前表结构的关键矛盾点在于：
-- 表定义中 ref_id 列（虽未直接展示，但由上下文和 SHOW CREATE TABLE 推断） -- 实际使用的是 utf8 字符集 + utf8_*_ci 排序规则 `ref_id` CHAR(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci -- 而存储过程中的查询却强制使用： ... = _utf8mb4'...' COLLATE 'utf8mb4_unicode_ci'
即使 utf8mb4_unicode_ci 和 utf8_unicode_ci 在绝大多数 UUID 场景下语义等价，MySQL 优化器仍严格遵循“不同 collation → 索引不可用”原则。
✅ 正确写法：三类安全且高效的操作模式
以下写法均能确保索引被正确使用（经实测验证），请根据您的环境选择：
| 场景 | 推荐写法 | 说明 |
|---|---|---|
| 推荐 ✅（最简洁） | WHERE ref_id = _utf8mb4'c37e32fc-b3b5-11ec-befc-02447a44a47c' | 省略 COLLATE，让 MySQL 自动按连接/列默认规则推导，避免显式冲突 |
| 兼容旧环境 ✅ | WHERE ref_id = _utf8'c37e32fc-b3b5-11ec-befc-02447a44a47c' | 使用与列完全一致的 utf8 字符集前缀 |
| 显式声明（需统一 collation）✅ | WHERE ref_id = _utf8'c37e32fc-b3b5-11ec-befc-02447a44a47c' COLLATE utf8_unicode_ci | 仅当列确为 utf8_unicode_ci 时有效；utf8mb4_* 类 collation 对 utf8 列无效 |
⚠️ 务必移除 NAME_CONST()：该函数用于在存储过程中定义命名常量，不应出现在 WHERE 条件中作为值表达式。它不仅无益于性能，反而干扰优化器对常量类型的判断。直接使用字符串字面量即可。
? 终极解决方案：统一字符集与排序规则
为一劳永逸避免此类问题，强烈建议执行全库级字符集标准化：
-- 步骤1：将 notification 表升级至 utf8mb4（推荐 utf8mb4_unicode_ci）
ALTER TABLE notification
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 步骤2：同步更新连接层（PHP PDO/MySQLi）
// PHP 中确保连接初始化时指定
$pdo = new PDO("mysql:host=localhost;charset=utf8mb4", $user, $pass);
// 或执行 SET NAMES utf8mb4;此举将重建所有字符串索引，使其按 utf8mb4_unicode_ci 规则排序。此后，以下写法全部安全可用：
WHERE ref_id = _utf8mb4'...' COLLATE utf8mb4_unicode_ci -- ✅ 兼容 WHERE ref_id = '...' -- ✅ 默认即 utf8mb4_unicode_ci
? 验证是否生效：EXPLAIN 是唯一真理
修改后，请务必用 EXPLAIN 检查执行计划：
EXPLAIN SELECT notification_id FROM notification WHERE ref_table = 2 AND ref_id = 'c37e32fc-b3b5-11ec-befc-02447a44a47c';
✅ 正确结果：type = ref 或 range，key 显示实际使用的索引名，rows 值远小于总行数。
❌ 错误结果：type = ALL（全表扫描），key = NULL。
? 总结与最佳实践
- 核心原则：索引列与查询条件的字符集 + collation 必须兼容（相同或可安全推导）；
- 开发规范：存储过程中避免 NAME_CONST() 用于 WHERE 值；优先使用无 COLLATE 前缀的字符串字面量；
- 运维底线：数据库、表、列、连接、存储过程上下文，应统一使用 utf8mb4 + utf8mb4_unicode_ci（MySQL 5.7+ 推荐）；
- 调试习惯：任何慢查询，第一步永远是 EXPLAIN + SHOW CREATE TABLE + SHOW VARIABLES LIKE 'coll%' 交叉比对。
字符集不是“能存中文就行”的配置项，而是索引性能的隐形开关。一次标准化，永久规避隐式转换陷阱。
