MySQL8 分页数据重复或丢失问题说明(order by limit)

这个问题，在生产环境出现了；当时测试那边也没测试到，我也是无语，还是客户发现的；

可能是因为现场数据较少，只有两页，客户一眼就能看到少数据了，也就是录入成功后的数据查不出来，还是在第二页，，如果pageSIze选择大一些，用一页展示是可以正常展示所有的数据的；关键关键这个问题是查询出来的数量是对的，经过排查，窝草sql没问题，就是mysql数据库执行sql时不稳定导致的

SELECT
	*
FROM
	xxxxx
WHERE
	(flag= 0)
ORDER BY
	business_type ASC,
	created_time DESC
LIMIT 15,15

解决方案：

类似上面这样的排序就会出问题，因为这两个都没有索引造成的，最后mysql采用了堆排序，堆排序是不稳定的，因为要加一个索引列,id或者其他的索引列都可以

SELECT
	*
FROM
	xxxxx
WHERE
	(flag= 0)
ORDER BY
	business_type ASC,
	created_time DESC,
    id asc
LIMIT 15,15

问题原因

在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。

如果order by的列有相同的值时， mysql会随机选取这些行，具体根据执行计划有所不同。

分析原因

在 MySQL 关系型数据库中，往往有多种排序算法。通过 MySQL 源码和官方文档介绍就可以知道，它的排名规则可以总结如下：

当 order by 没有索引排序时，会使用排序算法进行排序；

如果所有排序后的内容都可以放入内存，则只在内存中使用快速排序；

如果排序后的内容无法放入内存，则将排序后的内容批量放入文件中，然后对多个文件进行合并排序；

如果排序包含限制语句，则使用堆排序来优化排序过程。

根据上面的总结，当order by limit Paging 数据丢失和重复时。 order by 的 sort 字段不使用索引（一般情况下，Sorted 字段也不使用索引），如果使用了索引，则会进行索引排序。

因此可以得出结论，在上面的SQL语句使用了堆排序。因为 sort 字段没有索引，所以我没有使用索引排序；并使用限制。导致最终使用堆排序。

如果你知道算法，你应该知道堆排序是不稳定的。这种不稳定性，意味着经过多次排序后，数字的相对位置发生了变化。

然而，并不是所有的 MySQL 所有版本都是这样。从 MySQL 5.6 版本开始，优化器在使用 order by limit 的时候，做了上面的优化，导致排序字段没有使用索引，使用堆排序。

堆排序的不稳定性导致了查询数据的重复或丢失

解决方法

在 order by 中的排序字段中，添加索引字段，例如主键 ID。这样排序才能稳定。

分页需要确定性的排序顺序：如果查询数据是排序分页的，如果排序字段没有使用索引，一定要加索引字段，比如主键ID，确保序列稳定。否则，查询数据会导致数据丢失和重复。

到 order by 子句是一个好的开始，这样我们就可以继续将此索引用于流水线 order by。如果这仍然不能产生确定性的排序顺序，只需添加任何唯一的列并相应地扩展索引。

若想 使用 order by 加 limit的 形式

1、 使用 索引列 进行排序
 
2、若不能使用索引字段的话，只能进行 双排序，也就是`ORDER BY sort DESC,id asc` 把索引字段id作为 附加条件 进行排序 就可以了

SELECT
	*
FROM
	xxxxx
WHERE
	(flag= 0)
ORDER BY
	business_type ASC,
	created_time DESC,
    id asc
LIMIT 15,15