MySQL 排序规则变化导致索引比较异常怎么办

我遇到过一种很容易被误判成“索引坏了”的 MySQL 问题：表里明明只有一套数据，换了环境或改过字符集后，同一个条件返回的行数变了，ORDER BY 顺序也不同，EXPLAIN 还可能从走索引变成扫描。多数时候真正变化的是字符串的比较规则，而不是索引文件凭空损坏。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

这篇只处理一个边界：utf8mb4 字符集、排序规则（collation）、字符串比较和索引之间的关系。排查顺序是先看定义，再做最小复现，然后对照执行计划和权重，最后才决定是否统一列的排序规则。

先把列、表和连接的排序规则查清楚

我第一次排这类问题时，最容易漏掉的是“只查数据库默认值”。MySQL 允许服务器、数据库、表、列和连接分别带有字符集与排序规则；列已经显式定义时，库级默认值并不能代表这列的实际比较方式。

-- 先看当前连接的字符集和排序规则，避免只盯着表结构
SELECT
  @@character_set_client AS client_charset,
  @@character_set_connection AS connection_charset,
  @@collation_connection AS connection_collation;

-- 看数据库默认值；它不一定等于具体列的定义
SELECT
  SCHEMA_NAME,
  DEFAULT_CHARACTER_SET_NAME,
  DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

-- 看表和列的最终定义，重点关注 alias 列的 Collation
SHOW CREATE TABLE customer_alias;
SHOW FULL COLUMNS FROM customer_alias;

-- 只筛出目标列，便于和迁移前的记录做对照
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  CHARACTER_SET_NAME,
  COLLATION_NAME,
  COLUMN_TYPE,
  IS_NULLABLE,
  COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'customer_alias'
  AND COLUMN_NAME = 'alias';

这里要记住两个概念：字符集决定字符如何编码，排序规则决定字符如何比较。MySQL 官方文档还明确区分了大小写、重音、特殊字符以及多字符映射等规则，所以“都是 utf8mb4”并不等于“比较结果一定一样”。

用最小表复现比较语义差异

不要一上来就在生产表上改排序规则。我更习惯先用一张只包含目标字段的表，分别放入大小写接近的值，再观察当前规则是否把它们视为相等。下面的例子故意使用短字段，便于把问题和业务数据量分开。

-- 建立一个带普通大小写不敏感排序规则的最小样本
DROP TABLE IF EXISTS customer_alias;
CREATE TABLE customer_alias (
  id BIGINT PRIMARY KEY,
  alias VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  INDEX idx_alias (alias)
);

-- 插入只用于观察比较语义的两组样本
INSERT INTO customer_alias (id, alias) VALUES
  (1, 'GoTeam'),
  (2, 'goteam'),
  (3, 'Go-Team');

-- 等值查询的结果取决于 alias 列的排序规则
SELECT id, alias
FROM customer_alias
WHERE alias = 'goteam';

-- 显式指定二进制排序规则，观察与列默认规则不同的比较结果
SELECT id, alias
FROM customer_alias
WHERE alias COLLATE utf8mb4_bin = 'goteam';

如果第一条查询把 GoTeam 和 goteam 放在同一个等值集合，而第二条查询只保留字节层面相同的值，这不是索引随机失效，而是两条表达式要求的比较语义不同。实际业务中应先确认“昵称是否区分大小写”“搜索是否忽略重音”等规则，再选择 collation。

排序规则名称也要看完整。MySQL 8.4 文档列出了 utf8mb4_0900_ai_ci、utf8mb4_0900_as_cs、utf8mb4_bin 等不同选择，后缀会影响大小写、重音或二进制比较。不要只用“utf8mb4 新旧版本不同”概括所有差异。

对照 EXPLAIN 与 WEIGHT_STRING 判断索引语义

我通常把异常拆成三个问题：结果集是不是变了、索引是不是没有被使用、比较权重是不是变了。EXPLAIN 用来观察访问路径，WEIGHT_STRING() 用来观察字符串在当前排序规则下用于比较和排序的权重；两者回答的不是同一件事。

-- 先看不改写列表达式时，优化器是否能使用 idx_alias
EXPLAIN SELECT id, alias
FROM customer_alias
WHERE alias = 'goteam';

-- 再观察显式 COLLATE 是否改变了表达式语义和访问路径
EXPLAIN SELECT id, alias
FROM customer_alias
WHERE alias COLLATE utf8mb4_bin = 'goteam';

-- 用权重函数观察两个值在指定排序规则下的比较依据
SELECT
  'GoTeam' AS sample_a,
  'goteam' AS sample_b,
  HEX(WEIGHT_STRING('GoTeam' COLLATE utf8mb4_0900_ai_ci)) AS ai_ci_a,
  HEX(WEIGHT_STRING('goteam' COLLATE utf8mb4_0900_ai_ci)) AS ai_ci_b,
  HEX(WEIGHT_STRING('GoTeam' COLLATE utf8mb4_bin)) AS bin_a,
  HEX(WEIGHT_STRING('goteam' COLLATE utf8mb4_bin)) AS bin_b;

如果查询结果变化但执行计划没有变化，优先检查排序规则和连接配置；如果只有加了 COLLATE 的写法不再走原索引，先把它当成“表达式语义可能不匹配索引定义”的信号，再结合实际计划判断，不要直接把问题归结为索引损坏。MySQL 文档把 WEIGHT_STRING() 定位为比较和排序权重的调试工具，适合做这种小范围对照。

统一列排序规则并保留完整列属性

确认业务确实需要统一后，再改列，而不是为了让某一条查询“看起来正常”就强行套一个 COLLATE。修改列字符集或排序规则会影响现有数据的比较方式，也会让相关索引按新的列定义重新组织。

-- 迁移前先记录原属性，下面的示例保留 NOT NULL 和默认值
SHOW FULL COLUMNS FROM customer_alias LIKE 'alias';

-- 将列统一到目标排序规则；生产环境应安排 COPY 算法带来的锁表和耗时窗口
ALTER TABLE customer_alias
  MODIFY alias VARCHAR(80)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_as_cs
    NOT NULL,
  ALGORITHM = COPY;

-- 修改后重新查看列定义和索引定义
SHOW FULL COLUMNS FROM customer_alias LIKE 'alias';
SHOW CREATE TABLE customer_alias;

列转换不是无条件安全的：官方文档提示，字符集之间的转换可能因目标字符集无法表示某些字符而有损；改变字符集或排序规则的 ALTER TABLE 还需要使用 ALGORITHM=COPY。另外，修改列时如果漏掉原来的 NOT NULL、默认值等属性，结果列定义会悄悄发生额外变化，所以应把完整列属性放在迁移脚本里。

如果业务同时存在“登录精确匹配”和“搜索友好匹配”，不要让一列承担两套语义。可以保留原始标识列，再为搜索场景设计单独的规范化字段和索引；这属于数据模型决策，不是给原索引随便加一个后缀就能解决。

把应用连接设置固定下来

列定义统一了，连接仍可能带着不同的 collation_connection。这在多语言服务、连接池复用或升级驱动后尤其容易出现：同样的 SQL 文本从不同连接发出，字面量的字符集和排序规则上下文可能不一致。

-- 在连接建立后显式设置客户端、连接和结果集的字符集
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 把实际会话值记录到诊断日志，而不是依赖服务器默认值
SELECT
  @@character_set_client,
  @@character_set_connection,
  @@character_set_results,
  @@collation_connection;

应用层要把这段配置固化在连接初始化路径里，并在连接池复用时确认不会被后续代码改写。对于需要区分大小写的查询，还应把规则写进列设计或专用字段，而不是由某个接口临时添加 COLLATE。

用迁移前后清单收尾

我会把下面这张清单作为发布前的最后一步。它不是“跑一个校验脚本就算完成”，而是确保业务意图、列定义、查询表达式和执行计划仍然互相匹配。

这次排查给我的经验是：先确认“比较相等”究竟是什么意思，再谈索引是否异常。只要列排序规则、查询表达式和连接会话对同一个业务字段表达同一套规则，索引比较变化通常都能被解释清楚。

相关问题

utf8mb4 和 utf8mb4_0900_ai_ci 是一回事吗？

不是。前者是字符集，后者是该字符集下的一种排序规则；字符集解决编码，排序规则解决比较和排序。

给 WHERE 条件加 COLLATE 一定会导致索引失效吗？

不一定。它会改变表达式的比较语义，是否还能使用原索引要以实际的 EXPLAIN 为准；如果语义与索引列定义不同，应优先调整模型或索引设计。

改排序规则后为什么查询行数变化了？

因为不同排序规则可能把大小写、重音或其他字符视为相等或不等。先用最小数据和 WEIGHT_STRING() 对照，再确认这是否符合业务要求。