如何解决MySQL中由Order By与Limit结合产生的选择错误

MySQL在ORDER BY字段存在重复值时，LIMIT返回结果不稳定；根本原因是官方允许相同排序值的行顺序不确定，尤其5.6+使用堆排序（priority queue）优化导致分页重复或丢失；唯一可靠解法是ORDER BY后追加唯一非空字段（如id）确保排序确定性。

ORDER BY 字段存在重复值时，LIMIT 返回结果不稳定

这是最常见也最容易被忽略的问题：当 ORDER BY 的列（比如 create_time、score）有多个相同值时，MySQL 不保证这些行的相对顺序。加上 LIMIT 后，优化器可能用堆排序（priority queue）提前截断，导致第 1 页和第 2 页出现重复或跳行。

典型现象包括：

• SELECT * FROM t ORDER BY create_time DESC LIMIT 0,10 和 LIMIT 10,10 返回部分重叠记录
• 分页翻页时某条数据“消失”或“重复出现”
• 不加 LIMIT 时排序稳定，一加就乱

根本原因不是 bug，而是 MySQL 明确允许的未定义行为——官方文档写得很清楚：“If multiple rows have identical values in the ORDER BY columns, the server is free to return them in any order.”

必须添加确定性二级排序字段

唯一可靠、零成本、兼容所有 MySQL 版本（5.6+）的解法，就是在 ORDER BY 后追加一个**唯一且非空**的字段，通常是主键 id 或带唯一约束的字段。

正确写法示例：

SELECT * FROM partner_organization WHERE partner_type = 1 AND city_id IN (1, 2) ORDER BY create_time DESC, id DESC LIMIT 140,10

关键点：

• 二级字段必须能打破所有并列情况，id 最稳妥；uuid 或带唯一索引的业务字段也可
• 方向要一致：如果主排序是 DESC，二级也建议 DESC（避免跨页时因方向不同导致边界错位）
• 不要用 ORDER BY create_time DESC, RAND() —— 这会让结果更不可控

子查询写法看似绕路，实则风险更高

有人尝试用子查询“先 limit 再 order by”，例如：

SELECT * FROM (  SELECT * FROM t WHERE ... ORDER BY create_time DESC) AS tmp ORDER BY create_time DESC LIMIT 10

这种写法不仅语法冗余，还容易触发以下问题：

• MySQL 可能忽略内层 ORDER BY（除非配合 LIMIT 或 GROUP BY）
• 外层再排序时，已丢失原始全集顺序，二级字段没机会参与
• 执行计划更难预测，尤其在大表上易退化为全表扫描 + filesort

真正有效的子查询模式只有一种：

SELECT * FROM (  SELECT * FROM t WHERE ... ORDER BY create_time DESC, id DESC) AS tmp LIMIT 140,10

但这就等价于直接在外层写，纯属多套一层括号，无实际收益。

索引与版本的影响不能替代排序逻辑

有人以为加索引或升级 MySQL 就能一劳永逸，其实不然：

• 即使 create_time 有索引，只要该列存在重复值，LIMIT 仍可能返回非确定结果
• MySQL 8.0+ 改进了 priority queue 实现，但**未改变语义承诺**：官方依然不保证重复值的顺序
• 所谓“8.0 没这个问题”是误传——只是某些场景下巧合稳定，不代表可依赖

所以，无论你用的是 5.7、8.0 还是 Percona Server，只要业务要求分页结果严格可重现，就必须显式指定二级排序字段。这不是 workaround，是标准实践。

最容易被跳过的细节：那个二级字段，得真唯一、真非空、真索引友好。别用 updated_at 这种可能为空或重复的字段凑数。