迁到 8.0 后翻页重复、join 变慢：排序规则不一致

上周把一套业务库从 5.7 迁到了 8.0。迁移本身很顺，数据校验也过了，但从第二天开始，三个看起来毫不相干的问题同时冒出来。

第一个：商品列表接口翻页时会重复。第 2 页的最后一条，在第 3 页的第一条又出现一次，同时有另一条商品从来没出现过。前端加了去重，用户看到的是「总数 120 件，翻到底只有 117 件」。

第二个：一个 join 查询从 30ms 变成了 1.2 秒。SQL 没改，数据量只涨了 3%，执行计划里那张小表从 ref 变成了 ALL。

第三个：某天下午一条补数据的 INSERT 报 Duplicate entry 'ABC-001' for key 'uk_code'，但我在表里 SELECT 了半天，只有 abc-001，没有 ABC-001。

三个问题查了两天才串到一起，根因只有一个：新旧表的排序规则（collation）不一致。迁移时新库的默认 collation 是 utf8mb4_0900_ai_ci，而老表建表时写的是 utf8mb4_general_ci，ALTER TABLE ... CONVERT 的时候漏掉了几张历史表。

一、三个症状为什么会同源

排序规则决定的不只是「怎么排序」，它同时决定三件事：两个字符串相不相等、谁大谁小、以及索引里的键怎么排。这三件事分别对应上面三个症状。

utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 都是「大小写不敏感、重音不敏感」，所以在 = 比较上表现接近，这也是迁移时数据校验没报错的原因——校验脚本比的是主键和业务字段，用的是 =。

但它们的权重表不是一套。general_ci 是 MySQL 早期自己实现的简化规则，0900_ai_ci 基于 Unicode 9.0 的 UCA 算法，字符的相对大小、多字符展开（比如德语 ß 等价于 ss）都不一样。同一批数据在两种规则下排出来的顺序可以完全不同。

而索引的物理顺序，是按列的 collation 排的。列改了 collation，索引就得重建，顺序也就变了。分页、join、唯一约束全都踩在这上面。

二、先确认表里到底有几种排序规则

不要凭印象。8.0 里 information_schema 能直接查出每一列的实际 collation，一条 SQL 就能看清整个库的分裂程度：

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> 'utf8mb4_0900_ai_ci'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

查出来 11 张表、37 个列还是 utf8mb4_general_ci，集中在订单、商品、字典这三块，正好是最早建的那批表。新加的营销、结算相关表都是 0900_ai_ci。一个库里两套规则共存，出问题是时间问题。

顺带补一句，表级默认值（information_schema.TABLES.TABLE_COLLATION）和列的显式 collation 是两码事，CONVERT TO 改的是表默认值，列的显式设置会保留，所以改完还得再查一遍列。

三、排序变了，分页就会漏数据

商品列表的 SQL 当时是这样的：

SELECT id, title, price FROM product
WHERE status = 1
ORDER BY sort_weight DESC
LIMIT 20 OFFSET 40;

sort_weight 是运营配置的权重，取值只有 0 到 9 十档。也就是说，几十条记录的权重完全一样，ORDER BY sort_weight DESC 对它们来说是「无序」的。数据库返回的顺序取决于执行计划：走索引就按索引里行的物理顺序，走文件排序就看排序算法是否稳定。

老表上一直走同一个索引，行顺序碰巧稳定，所以翻页看起来是对的。迁到新表之后，索引因为 collation 变化被重建，行在索引页里的分布变了，同一档权重内部的顺序也跟着变了。LIMIT 20 OFFSET 40 两次查询拿到的是两个不同的快照，页边界自然对不上。

这类问题的正解不是去死磕 collation，而是排序必须有唯一键兜底：

ORDER BY sort_weight DESC, id DESC

加上 id 之后，任何一档权重内部的顺序都是确定的，翻页稳定。这一条改完，重复和漏数据立刻消失——它其实和 collation 没有直接关系，只是被 collation 变化「引爆」了。很多线上翻页问题都是这么来的：不稳定排序 + 深度分页，平时看不出来，一旦执行计划变了就暴露。

顺便记一个判断方法：把同一条分页 SQL 连跑两次，如果两次结果的 id 顺序不同，就是不稳定的排序，和索引、collation、统计信息都没关系，缺的是 tie-breaker。

四、join 两侧规则不同，索引直接失效

变慢的那条 SQL 大意是：订单表关联商品表，取商品名。

SELECT o.id, p.title
FROM orders o
JOIN product p ON p.code = o.product_code
WHERE o.created_at >= '2026-11-01';

orders.product_code 是 general_ci，product.code 是 0900_ai_ci。EXPLAIN 里 product 那张表的 type 从 ref 变成了 ALL，rows 从预估 1 行变成 42 万行，Extra 里带着 Using where; Using join buffer (hash join)。

原因很直接：两个不同 collation 的字符串列做等值比较时，MySQL 必须先把它们归一到同一个规则才能比，这个过程发生在行级，无法用索引下推。optimizer 一旦判断「用索引也要全扫一遍再比」，就会干脆放弃索引。

SHOW WARNINGS 在这种场景下通常会给出提示：

Cannot use ref access on index 'idx_code' due to type or collation conversion on field 'code'

有人会想在 SQL 上打补丁，写成 ON p.code = o.product_code COLLATE utf8mb4_general_ci。这个改法有两个问题：一是把转换挪到了 p.code 上，那是索引列，索引直接废掉；二是每行都要做一次转换，CPU 消耗比全表扫还难看。我试过一次，1.2 秒变成 3.4 秒。

唯一的正解是把两列的 collation 改成一致，让比较在「同规则」下进行，索引才能用上。

五、唯一索引在 ci 下把大小写当成同一个值

第三个问题最有迷惑性。uk_code 建在 product.code 上，ci 表示 case insensitive，所以 'ABC-001' 和 'abc-001' 在索引眼里是同一个键。表里存的是小写，插入大写自然冲突。报错信息里显示的是「你要插的值」，不是「已存在的值」，所以 SELECT * FROM product WHERE code = 'ABC-001' 反而能查出来（同样是 ci 比较），肉眼一看更懵。

这次的真实原因是数据清洗脚本：上游系统导出的编码有大写也有小写，脚本没有统一 UPPER()，直接灌了进来。在老表上也该冲突，只是老表当时没有这条唯一索引，是迁移时新建的。

这里要做一个业务决策，而不是技术决策：编码到底区不区分大小写？

我们最后把 code 单独改成 utf8mb4_0900_as_cs，同时在写入侧加了 UPPER() 归一。改的时候要注意：as_cs 是大小写敏感，历史数据里如果本来就存在 Abc 和 abc 两条，改完唯一索引会建不上，得先清理。这一点必须在变更前跑一次重复检查。

六、统一规则的正确做法

统一 collation 看着就是一条 ALTER，但有几个容易翻车的地方。

第一，CONVERT TO 和 MODIFY COLUMN 不是一回事。ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 会重建整张表，把所有字符列一起转换；MODIFY COLUMN c VARCHAR(64) COLLATE ... 只改一列，但同样会重建表（8.0 里改 collation 不支持 INSTANT）。大表必须走 gh-ost 或 pt-online-schema-change。

第二，改 collation 会重建所有相关索引。一张 2000 万行的表，索引重建的时间往往比数据转换本身长得多。我们在预发环境实测：数据 8 GB、5 个索引，gh-ost 跑了 47 分钟，期间主库 CPU 从 18% 涨到 55%。

第三，改完必须 ANALYZE TABLE。统计信息还是按旧的顺序采的，optimizer 可能继续选错执行计划。

gh-ost --host=10.0.3.11 --database=shop --table=orders \
  --alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci" \
  --allow-on-master --max-load='Threads_running=40' \
  --critical-load='Threads_running=80' --chunk-size=1000 --execute

第四，改之前先确认业务侧没有依赖旧顺序的地方。比如「按名称排序」的列表，改完 collation 顺序会变，如果有分页缓存或者导出文件对顺序敏感，用户会觉得「数据乱了」。我们在变更公告里专门写了这一条。

小结

• 排序规则同时决定比较、排序和索引顺序。迁库时漏改 collation，症状会分散在分页、join、唯一约束三个地方，很难第一时间串起来。
• 用 information_schema.COLUMNS 查实际 collation，不要看建表语句——表的默认值和列的显式值经常不一致。
• 分页必须有唯一键做 tie-breaker。不稳定排序平时不报错，执行计划一变就漏数据。
• join 两侧 collation 不同会导致索引失效，EXPLAIN 里表现为 type=ALL 加 Using join buffer，SHOW WARNINGS 会点明是 collation 转换。
• ci 的唯一索引把大小写视为同一个键，编码类字段应该显式用 _as_cs，并在写入侧归一。
• 改 collation 等于重建表和全部索引，大表走在线变更工具，改完补一次 ANALYZE TABLE。