一次线上事故的反思-MySQL中order by与limit一起使用的坑

2023/03/29

问题场景

一个新项目提供了一个查询所有Bucket的接口，同时使用了Order By 和 limit 同时进行排序和分页查询

1
2
3
4
5
6
7
8
9

SELECT
	* 
FROM
	tb_bucket 
WHERE
	uid = ${userId} 
ORDER BY
	create_time DESC 
    limit ${(page - 1)*size}, #{size}

上线后前端反馈数据出现来回跳动，数据一会出现在第一页一会出现在第二页，导致数据一部分缺失一部分重复

问题分析

发生问题后首先检查了一遍代码逻辑，并未发现其他问题，当把SQL拿出来单独执行的时候出现了数据错误的问题。发现出现数据错误的数据都有相同的create_time，而SQL又是基于create_time排序的，故怀疑order by 和 limit 同时使用的问题。以create_time进行降序排序，当create_time存在多条重复时基于limit分页出现数据错误问题。

查阅了MySQL的官方文档发现的MySQL limit的查询优化所导致

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns

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

MySQL 官方文档原文

问题解决

解决思路是：避免ORDER BY列的值出现重复。因此，可以加入排序列，比如id等等。

1
2
3
4
5
6
7
8
9

SELECT
	* 
FROM
	tb_bucket 
WHERE
	uid = ${userId} 
ORDER BY
	create_time DESC id ASC
    limit ${(page - 1)*size}, #{size}

加入id作为排序条件后，数据混乱的问题解决