背景

今天在线上遇到一个 BUG，具体表现可以概括为：在一个分页查询中，某一个数据无论在第几页都无法查询到，但借助关键词搜索却可以查询出来。

分析

看到这个 BUG 第一时间没有任何想法，只觉得十分奇怪，提取查询的关键 SQL：

SELECT
	* 
FROM
	campaign_agency 
WHERE
	itnt_code = 'EGIT' 
ORDER BY
	created_at DESC 
LIMIT ${pageIndex}, ${pageSize};

表结构其实也维护了 created_at 的排序

CREATE TABLE `campaign_agency`  (
--
  INDEX `idx_created_at`(`created_at` ASC) USING BTREE,
--
);

这个 SQL 一眼看上去也没什么奇怪的地方，逻辑也十分简单，先根据创建时间排序然后分页。

是不是分页参数出了问题？代码里面其实是借助 PageHelper 来实现的分页，基本可以肯定参数是没问题。

既然参数没问题，那么尝试一下看全量是否能查到：

SELECT
	* 
FROM
	campaign_agency 
WHERE
	itnt_code = 'EGIT' 
ORDER BY
	created_at DESC

全量确实查到了该条数据，可是一旦将分页参数加上这条数据就神奇得消失了：

SELECT
	* 
FROM
	campaign_agency 
WHERE
	itnt_code = 'EGIT' 
ORDER BY
	created_at DESC 
LIMIT 0, 10;

-- 数据应该出现在 0 ~ 20 之间

SELECT
	* 
FROM
	campaign_agency 
WHERE
	itnt_code = 'EGIT' 
ORDER BY
	created_at DESC 
LIMIT 10, 10;

经过一番检索之后了解到 MySQL 自身的特性：MySQL 在 5.6 之后就尝试用 优先队列 来优化偏移量不大的排序+分页，因为排序+分页可以看作是求 TopN ，熟悉算法的话自然而然就能想到 堆排序，而 优先队列 又可以用来实现 堆排序，但需要注意的是 堆排序 是 不稳定 的排序算法，即排序之后不能保证数据的位置相对于原来是一致的。如果字段有重复值，order by ${fieldName} 和 limit 组合使用的结果会和预期不符。

这里引用一个大佬的文章：深入浅出 MySQL 优先队列(你一定会踩到的order by limit 问题)

原理懂了，那么数据到底是怎么消失的呢？

第一页原理分析：

SELECT
	* 
FROM
	campaign_agency 
WHERE
	itnt_code = 'EGIT' 
ORDER BY
	created_at DESC 
LIMIT 0, 10;

该 SQL 对应的 EXPLAIN 如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	campaign_agenc		index		idx_created_at	5		20	10.00	Using where; Backward index scan

可以看出该 SQL 还是用到了 created_at 索引，但消失的数据位于第 11 行，故没有展示出来。

第二页原理分析：

SELECT
	* 
FROM
	campaign_agency 
WHERE
	itnt_code = 'EGIT' 
ORDER BY
	created_at DESC 
LIMIT 10, 10;

该 SQL 对应的 EXPLAIN 如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	campaign_agenc		ALL					23	10.00	Using where; Using filesort

可以看到两条 SQL 的执行计划有较大的差距，第一页用到了索引，第二页 MySQL 认为全表扫描更好，去除了排序发现消失的数据其实是位于第 2 行，由于有重复数据，所以它在第二页刚好被淘汰了。

总的来说是第一页和第二页的执行计划不一致导致了这条数据的离奇消失。

总结

既然知道了根本原因，那么解决的方式就很简单了：

排序里面多加上一个唯一标识字段，比如唯一主键。
强制使用 created_at 索引。

笔者选用的是方案一，相比起方案二来说更容易让人理解。

总而言之，分页排序充满了太多的随机性，为了避免这种随机性带来的系统不稳定请牢记 永远不要单独使用非唯一字段排序。