记录一次 order by 查询的数据丢失和乱序问题

2,405 阅读2分钟

1. 问题场景

系统中有一个列表页,列表页的数据来自下面的表 t,表 t 中有30多条数据。列表页是分页显示的,30条数据显示了 3 页。现在出现的问题是点击第 2 页和第 3 页时,会显示第1页的部分数据,还有一部分数据查询不出来。经过调试发现不是业务代码的问题,而是分页 sql 出的问题

2. 问题定位

SELECT 
    * 
FROM Table t 
ORDER BY t.is_fixation DESC limit ?,?

这条语句是一个分页查询页面用的 sql。需要根据表 t 的 is_fixation 字段降序排序。其中 is_fixation 字段没有索引,而且表 t 的 30 条数据中,is_fixation 一部分是 0 ,一部分是 1,如下:


3. 问题 sql 分析

SELECT 
    * 
FROM Table t 
ORDER BY t.is_fixation DESC limit ?,?

该语句根据字段 is_fixation 降序排序,因为只有 30 条数据,默认的 mysql 引擎会将查询出来的数据放在内存中排序,排序算法用的是快速排序,而快速排序是不稳定的排序算法, 所以 is_fixation 为 0 的记录每次查询时都会被乱序排列,所以本来在第一页的数据显示到了第二页或者第三页,导致出现乱序和数据查询不出来的问题

4. 解决方案

(1)可以给  is_fixation 字段添加索引,这样在数据插入表时就是有序的,查询出来时也不用采用快速排序等排序算法再排序了

(2)可以多加一列 id 用于排序,这样避免不稳定的排序算法带来的问题:

SELECT 
    * 
FROM Table t 
ORDER BY t.is_fixation DESC, id limit ?,?

(3)不是方案的方案:如果表 t 的数据量很大,导致 MySQL 存储引擎选择归并排序算法时,就不会出现这个问题,因为归并排序是稳定的排序算法(走不走归并排序根据数据量来的,如果你explain中出现Using filesort不一定是指用外部排序,只是标识需要排序而已)