order by + limit 会触发优化器短视决策，优先获取前n行而非全量结果；分页深度大时prefer_ordering_index自动失效；order by字段重复值导致执行计划不可控；mysql 8.0新optimizer_switch开关可能间接干扰排序索引选择。
ORDER BY + LIMIT 触发优化器“短视决策”
优化器看到 LIMIT 后，会把成本估算重心从“全量结果集”转向“拿到前 N 行的最快路径”。它假设只要扫到足够匹配的行就能停，于是可能放弃高选择性但需回表的复合索引，转而选主键索引做全表扫描+内存排序——尤其当 WHERE 条件过滤性差、或统计信息不准时。这种切换不是 bug，是优化器在有限信息下做的“合理误判”。
分页深度大时 prefer_ordering_index 自动失效
MySQL 默认开启 prefer_ordering_index=on，但它有隐含阈值：当 LIMIT offset, row_count 中的 offset + row_count 超过一定规模（如 5000），优化器会认为“索引+回表”的总开销高于全表扫描+filesort，直接弃用排序索引。这个临界点不固定，受 innodb_buffer_pool_size、实际数据分布、临时表大小限制共同影响。
- 实测中，
LIMIT 10000, 20很可能走全表扫描，而LIMIT 100, 20稳定走索引 - 用
EXPLAIN FORMAT=JSON查看"using_filesort": true和"using_index_for_order_by": false可确认是否被绕过 - 临时关闭该行为：
SET SESSION optimizer_switch='prefer_ordering_index=off';，但仅限调试，生产慎用
ORDER BY 字段重复值导致执行计划不可控
当 ORDER BY create_time DESC 遇到大量相同时间戳时，MySQL 允许这些行任意排序。优化器可能因此在不同采样下选择不同执行路径：一次走 idx_create_time，另一次因“堆排序提前截断”收益预估变化，改走 PRIMARY。这不是统计信息漂移，而是语义层面的不确定性放大了计划波动。
- 必须补二级排序字段：
ORDER BY create_time DESC, id DESC，且id必须非空、唯一 - 避免用
RAND()或函数表达式作为二级字段，否则索引失效 - 直方图对重复值字段无效，别指望
ANALYZE TABLE ... UPDATE HISTOGRAM能解决这个问题
升级 MySQL 8.0 后 optimizer_switch 新开关干扰
8.0 默认启用 hash_join=on、derived_merge=on 等新规则，它们会改变 JOIN 顺序或子查询展开方式，间接影响外层 ORDER BY + LIMIT 的驱动表选择和索引可用性。比如子查询被合并后，原本能走 idx_status_created 的排序，变成先聚合再排序，只能走主键。
- 对比新旧库的
SELECT @@optimizer_switch;，重点关注新增的=on项 - 对关键 SQL，用
/*+ NO_HASH_JOIN(t1) */或/*+ NO_MERGE(t1) */临时禁用干扰项 -
ANALYZE TABLE必须带WITH SYNC，否则后台异步更新可能让新开关和旧统计信息错配
ANALYZE TABLE 之后，或升级后首次大批量查询触发，而你很难复现——因为那条 SQL 单独跑可能又恢复正常。真正要盯住的不是某次执行慢，而是 INFORMATION_SCHEMA.INNODB_TABLESTATS.last_update 时间戳与 EXPLAIN 中 rows 预估偏差是否同步突变。
