问题
今天在做数据查询的时候遇到了一个非常有趣的问题:在使用limit分页查询的时候,发现不使用分页的时候就可以查询到相关的数据,一旦使用了limit分页查询,部分数据就查询不到了,如下SQL:
SELECT
b.`id`,
b.`status`,
a.`car_no`,
b.`code`,
a.`create_time`,
FROM
a
JOIN b ON a.id = b.case_id
WHERE
a.valid = 1
ORDER BY
a.create_time DESC
LIMIT 10, 20
解决思路
因为A表对B表是一对多,而排序又采用的是A表的create_time,所以在每次的排序结果都是不同的,也就是我们常说的“排序结果不确定”。
造成这样问题的主要原因是:MySQL内部主要有常规排序,优化排序和优先队列排序三种排序方式,排序算法主要包括:快速排序、归并排序和堆排序;MySQL5.6后针对limit M,N的语句采用了优先队列,同时优先队列采用堆实现,使用的是堆排序算法,而堆排序主要又是非稳定的排序,所以就出现了上述问题。
解决方案
解决方案就是在排序中使用唯一值,例如使用子表的Id或者create_time字段,如果没有唯一值的话,可以尝试使用多字段排序的方式来构建唯一值。
参考链接:
MySQL 使用 limit 分页会导致数据丢失、重复和索引失效
缺陷的背后(一)—MYQL之LIMIT M,N 分页查找