Mysql Limit分页并对数据使用Order By排序踩坑

前提

最近在项目开发过程中，遇到了一需求，大致如下：增加一个系统配置表，表内基础数据在上线后初始化写入Mysql中，列表查询时，按照数据的更新时间进行倒叙展示，保证最近更新数据展示在最前面。一个简单的需求，却发生了一个意外，在部署完成，数据初始化以后，却发现分页存在问题，在不同的数据页中出现了相同的数据。遂查看了mysql的官方文档，得出了原因，并对SQL进行了优化调整，接下来让我们揭开这神秘的失效面纱。

表数据

第一版SQL

获取第一页数据

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

获取第二页数据

SELECT * FROM `user` ORDER BY create_time DESC LIMIT 10, 10;

我们可以看到出现大量的重复数据，那么是limit本身存在问题吗？于是查看了官方的文档，描述如下：MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.17 LIMIT Query Optimization 我们截取主要的描述

大致含义：

如果多行在列中具有相同的值ORDER BY，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。换句话说，这些行的排序顺序对于无序的列是不确定的。

影响执行计划的一个因素是 LIMIT，因此ORDER BY 带有和不带有的查询LIMIT可能会以不同的顺序返回行。考虑这个查询，它按category列排序，但对于id和 rating列是不确定的

所以在对字段进行排序时，尽量使用不会出现大量数值重复的字段，否则排序的结果将会出现不确定性。

解决方案

增加辅助排序，增加对主键字段进行倒叙，SQL如下，即可解决排序出现不确定性的问题。

SELECT * FROM `user` ORDER BY create_time DESC, user_id DESC LIMIT 0, 10;