MySQL中order by与limit一起用有坑_编程知识分享|编程教程|资源下载|源码下载

大家平时分页写法差不多是这样的：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 0, 10;

逻辑很直观，按时间倒序，取第一页十条。但坑就坑在——如果 create_time 有相同值，MySQL 会对这些相同值的记录随便排，而这个“随便”，真的是每天都可能不一样。

为什么会乱？

MySQL 排序分两种情况：

稳定排序：排序字段唯一，结果顺序固定。
不稳定排序：排序字段有相同值，剩下的部分按物理存储顺序返回，这个顺序随数据变化而变。

尤其是大表分页，MySQL 可能会走 filesort，这个过程是这样的（示意）：

1. 取出满足条件的记录
2. 按排序字段在内存或磁盘临时区排序
3. 截取 LIMIT 所需数量
4. 返回结果

问题来了，如果你的排序字段值重复，filesort 在“排序后”对这些相同值的记录位置并不固定。今天新插入一条数据，可能就把上一页和下一页的边界打乱了。于是——重复、漏数据，全来了。

真实分页踩坑案例

有一次我们订单表分页接口，第一页最后一条订单 create_time = 2025-08-10 14:00:00，第二页第一条也是这个时间，但两条数据其实是同一笔订单。结果就是，用户翻页看到重复数据，还以为系统坏了。

Java 里很多分页代码都长这样：

String sql = "SELECT * FROM orders ORDER BY create_time DESC LIMIT ?, ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, offset);
ps.setInt(2, pageSize);

跑得挺快，就是结果不稳定。

怎么解决？

1. 加唯一性排序条件

多加一个唯一字段，比如主键 id，让排序变成稳定排序。

SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT 0, 10;

Java 代码同理：

String sql = "SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT ?, ?";

这样即便 create_time 一样，id 还能保证顺序稳定。

2. 游标式分页

不要用页码 + offset，而是用上一页最后一条的“游标”来查下一页。

SELECT * FROM orders 
WHERE create_time < ? 
OR (create_time = ? AND id < ?) 
ORDER BY create_time DESC, id DESC 
LIMIT 10;

Java 写法：

String sql = "SELECT * FROM orders WHERE create_time < ? OR (create_time = ? AND id < ?) ORDER BY create_time DESC, id DESC LIMIT ?";

游标分页的好处是不会漏数据、不会重复，性能也更好，尤其是大表。

3. 用索引排序

如果排序字段有索引，MySQL 可以直接走索引顺序返回，不用 filesort，这样即便配合 limit 也会稳很多。

比如你要按 create_time 排序，就建个 (create_time, id) 的联合索引。

可视化理解执行过程

给你画个简化版流程（假设 order by create_time desc limit 0, 3）：

原始数据：
[ 2025-08-12 | id=5 ]
[ 2025-08-12 | id=8 ]
[ 2025-08-11 | id=3 ]
[ 2025-08-12 | id=1 ]

执行流程：
1. MySQL 找到所有数据
2. 对 create_time 排序，发现有重复值(2025-08-12)
3. 重复值的内部顺序不固定（可能按物理存储）
4. 截取前3条，返回

结果今天可能是 [5, 8, 3]，明天可能变成 [8, 1, 3]。这就是为什么你翻页会“穿越”。