1. 问题现象与背景分析
最近在开发一个电商后台管理系统时，遇到了一个奇怪的现象：商品列表分页查询时，第二页出现了第一页已经展示过的商品。我们的SQL语句看起来很正常：
SELECT id, name, price FROM products WHERE status = 1 ORDER BY sales_volume DESC LIMIT 0, 20; -- 第一页当查询第二页时（LIMIT 20, 20），部分商品竟然和第一页重复了。这个问题在促销活动期间尤为明显，因为很多商品的销量（sales_volume）相同。
经过排查，发现这是MySQL 5.6+版本特有的问题。在MySQL 5.5及以下版本中，相同的SQL不会出现这种情况。根本原因在于MySQL 5.6对ORDER BY + LIMIT查询做了优化，使用了优先队列（priority queue）算法。
2. 问题根因：优先队列与不稳定排序
2.1 MySQL的排序机制演变
在MySQL 5.6之前，执行ORDER BY + LIMIT查询时，会先对所有符合条件的记录进行完整排序，然后再应用LIMIT截取。这种方式虽然逻辑简单，但当数据量大时性能较差。
MySQL 5.6引入了一项优化：当检测到ORDER BY + LIMIT组合时，会使用优先队列（堆排序算法）来优化。这种算法只需要维护一个大小为LIMIT的堆，不需要对所有数据进行完整排序，大大提高了性能。
2.2 为什么会导致数据重复
堆排序是一种不稳定的排序算法。所谓不稳定，是指当排序键相同时，记录的相对顺序可能会发生变化。在我们的例子中，当多个商品的sales_volume相同时：
- 第一页查询时，MySQL从所有记录中选出sales_volume最大的20个商品，但sales_volume相同的商品顺序是不确定的
- 第二页查询时，MySQL再次执行同样的过程，可能选出与第一页部分sales_volume相同的商品，但顺序不同
- 最终导致两页出现重复商品
3. 解决方案与实战验证
3.1 方案一：添加唯一键排序
最可靠的解决方案是在ORDER BY子句中添加一个唯一列（通常是主键）作为次要排序条件：
SELECT id, name, price FROM products WHERE status = 1 ORDER BY sales_volume DESC, id ASC -- 添加id作为次要排序条件 LIMIT 0, 20;这样即使sales_volume相同，记录也会按照id排序，保证顺序的确定性。我们在生产环境验证了这个方案，彻底解决了分页重复问题。
注意：如果表没有主键，可以使用其他唯一索引列。实在没有唯一列时，可以考虑添加自增主键。
3.2 方案二：使用子查询优化
对于特别大的表，还可以使用子查询先确定边界值，再获取详细数据：
SELECT t1.id, t1.name, t1.price FROM products t1 JOIN ( SELECT id FROM products WHERE status = 1 ORDER BY sales_volume DESC, id ASC LIMIT 20, 20 ) t2 ON t1.id = t2.id ORDER BY t1.sales_volume DESC, t1.id ASC;这种写法利用了覆盖索引的优势，性能更好，但SQL更复杂。
3.3 方案三：业务层缓存
对于实时性要求不高的场景，可以在第一次查询时获取全量数据ID，然后在业务层做分页：
// 第一次查询获取所有符合条件的ID List<Long> allIds = productDao.getAllIds("status = 1 ORDER BY sales_volume DESC"); // 业务层分页 List<Long> pageIds = allIds.subList(start, start + size); List<Product> products = productDao.getByIds(pageIds);这种方案避免了SQL分页问题，但只适合数据量不大且实时性要求不高的场景。
4. 深入原理：MySQL执行过程分析
4.1 SQL执行顺序的误解
很多开发者认为SQL的执行顺序就是书写顺序，实际上MySQL的执行顺序是：
- FROM + JOIN 确定数据源
- WHERE 条件过滤
- GROUP BY 分组
- HAVING 分组后过滤
- SELECT 选择列
- DISTINCT 去重
- ORDER BY 排序
- LIMIT 分页
4.2 优先队列的工作机制
当MySQL检测到ORDER BY + LIMIT时，优化器会考虑使用优先队列：
- 初始化一个大小为LIMIT的堆
- 逐行扫描表数据，将每行与堆顶元素比较
- 如果新行应该排在堆内，则替换堆顶并重新堆化
- 最终堆内元素就是排序后的结果
这个过程只需要维护LIMIT大小的内存，避免了全表排序。
4.3 不同MySQL版本的差异
我们在测试环境验证了不同版本的行为：
| MySQL版本 | 行为 |
|---|---|
| 5.5及以下 | 全表排序，不会出现分页重复 |
| 5.6-5.7 | 默认使用优先队列，可能出现重复 |
| 8.0+ | 优先队列优化更智能，但相同问题仍存在 |
5. 生产环境最佳实践
5.1 索引设计建议
为了优化分页查询性能，建议创建合适的索引：
ALTER TABLE products ADD INDEX idx_status_sales_id (status, sales_volume DESC, id);这个复合索引可以完全覆盖我们的查询条件，避免filesort。
5.2 事务隔离级别的影响
即使解决了排序问题，在READ-COMMITTED隔离级别下，如果两次分页查询之间有新数据插入，仍可能导致数据重复或丢失。对于严格要求分页一致性的场景：
- 使用SERIALIZABLE隔离级别
- 或者在业务低峰期执行分页查询
- 或者使用快照读（如MySQL的START TRANSACTION WITH CONSISTENT SNAPSHOT）
5.3 深分页优化技巧
当分页很深时（如LIMIT 10000, 20），传统分页方式性能很差。可以采用"记住上次最大ID"的方法：
SELECT id, name, price FROM products WHERE status = 1 AND id > ? -- 上次看到的最后一条ID ORDER BY id ASC LIMIT 20;这种分页方式性能几乎恒定，但要求必须按主键排序。
6. 同类问题扩展与排查
6.1 其他数据库的表现
我们测试了其他常见数据库的分页行为：
- PostgreSQL: 与MySQL类似，需要明确排序条件
- Oracle: 必须使用ROWNUM或12c+的OFFSET-FETCH语法
- SQL Server: 使用OFFSET-FETCH语法，行为与MySQL类似
6.2 常见错误排查方法
当遇到分页问题时，可以按以下步骤排查：
- 检查ORDER BY是否包含足够唯一的排序条件
- 使用EXPLAIN分析是否使用了正确的索引
- 检查隔离级别是否导致数据变化
- 确认表中是否有大量排序键相同的记录
- 测试去掉LIMIT是否返回预期的排序结果
6.3 性能与一致性的权衡
在实际项目中，需要根据业务特点权衡：
- 后台管理系统：通常更注重一致性，可以使用严格排序
- 用户端列表：可以适当放宽一致性要求，提升性能
- 排行榜类：必须保证严格排序，可能需要定期预计算
7. 实战案例：电商系统改造过程
在我们的电商系统中，最终采用了以下解决方案：
- 所有分页查询必须包含主键作为最终排序条件
- 为常用分页查询创建专用复合索引
- 对商品列表等高频查询引入缓存
- 对深分页实现"无限滚动"式加载
改造后，分页查询性能提升30%，彻底解决了数据重复问题。特别是在大促期间，系统稳定性显著提高。
