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不一定是指用外部排序,只是标识需要排序而已)