前提
最近在项目开发过程中,遇到了一需求,大致如下:增加一个系统配置表,表内基础数据在上线后初始化写入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;