mysql在使用order by +limit分页时遇到的数据重复及丢失问题

最新推荐文章于 2025-03-03 16:03:38 发布

Dawnㅤ

最新推荐文章于 2025-03-03 16:03:38 发布

阅读量1.4k

点赞数 31

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/syg1207/article/details/144179985

版权

1、问题复现

执行以下sql，可以看到第一页和第二页出现了重复数据

2、问题分析

通过EXPLAIN查看执行计划，发现排序方式是文件排序

2.1 EXPLAIN 输出中常见字段及其含义

id: 每个 SELECT 语句的标识符。如果 SQL 包含多个子查询或联合查询，每个子查询或联合部分都有一个唯一的 id。
select_type: 查询类型，表示 SELECT 的性质。
SIMPLE: 简单查询，不包含子查询或 UNION。
- PRIMARY: 最外层的查询。
- SUBQUERY: 子查询中的第一个 SELECT。
- DERIVED: 导出表的 SELECT (FROM 子句中的子查询)。
- UNION: UNION 中的第二个或后面的 SELECT 语句。
- UNION RESULT: UNION 的结果。
table: 显示这一行的数据是关于哪张表的。
partitions: 匹配的分区。如果表没有进行分区，这个字段为空。
type: 连接类型，表示 MySQL 如何找到表中的行。
- system: 表只有一行数据，这是 const 类型的特例。
- const: 表最多有一个匹配行，读取常量一次。
- eq_ref: 对于前一个表的每一行组合，从该表中读取一行。
- ref: 使用索引选择行。
- range: 只检索给定范围内的行，使用索引来选择行。
- index: 扫描全索引。
- ALL: 全表扫描。
possible_keys: 表示在查询中可能使用的索引。如果为 NULL，则没有相关的索引。
key: 实际使用的索引。如果为 NULL，则没有使用索引。
key_len: 使用的索引长度。越短越好，因为这通常意味着更少的数据需要被读取。
ref: 显示了哪些列或常量与 key 一起被使用来选择行。
rows: 根据表统计信息和索引选择，MySQL 认为必须检查的行数。
filtered: 表示这个表中通过所有条件过滤的行的比例。
Extra: 包含不适合其他列的额外信息。
- Using filesort: 当查询中包含 ORDER BY 时，MySQL 需要进行额外的排序操作。
- Using temporary: 当查询中包含 GROUP BY 或 DISTINCT 时，MySQL 需要创建临时表来处理结果。
- Using index: 表示 MySQL 能够使用覆盖索引（即索引包含了查询所需的所有列），无需回表查询。
- Using where: 表示 MySQL 服务器将在存储引擎检索行后再进行过滤。
- Using join buffer: 使用了连接缓冲区来处理连接操作。

2.2 问题原因

查询官方文档，我们可以看出：

如果order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。我们上述sql，根据排序字段进行排序时，会有重复数据，因此会导致分页出现重复数据

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

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

很显然，这是由于order by 排序导致的，那么mysql order by排序底层是采用什么机制呢？

2.2.1 分析原因：

2.2.1.1 order by排序

MySQL支持二种方式的排序，Index和FileSort。

Index排序：索引排序，即我们通常为了查询建立的索引，也就是说，Mysql能为排序和查询使用相同的索引；
FileSort：文件排序，当不能使用Index排序时，使用文件排序。FileSort排序有两种方法，堆排序和快速排序。
Index排序使用场景

2.2.1.2 Index排序使用场景

where + order by 场景

        如果order by的字段加了索引，则使用索引排序；

只有order by场景

        order by中字段满足组合索引字段排序，则使用组合索引；

order by id（主键）

        不管有没有where条件，或者where 字段有没有索引，只要order by id，都会使用Index 排序，而不会使用FileSort

其余情况下，使用FileSort排序，可以通过EXPLAIN中的Extra来判断使用哪种排序方式。

从上图也可以看出使用Order by limit，若order by的字段没有加索引，确实会使用FileSort。

2.2.1.3 FileSort排序使用场景

在不能使用Index索引排序的时候，使用FileSort排序，FileSort排序有两种方式：

堆排序：在排序量不大的情况下，使用堆排序
快速排序：在需要大量排序的情况下，使用快排

从执行计划中只能看出来是使用Index排序，还是FileSort排序。而使用堆排序还是快排，是Mysql根据待排序数据量的大小进行切换具体根据函数check_if_pq_applicable进行判定的。

有一个简单的判断，如果使用的是order by limit n ，且在数据量不大的情况下（数据可以在内存中加载），使用的是堆排序；
而当n到了一个数量级的时候会切换成快排，具体使用那种算法是优化器通过函数check_if_pq_applicable进行判定的。
同时，如果没有limit时，就算数据量小，使用的也是快排。

在大量排序的情况下快速排序是有优势的，而堆排序使用优先队列只完成少量的排序是有优势的，因为它根本不需要排序完成只排序你需要的数据量就可以了，MySQL认为快速排序的速度是堆排序的3倍。

2.2.1.4 问题释疑

从导致问题的sql，我们可以判断使用的是FileSort排序中的堆排序。那么为啥堆排序会出问题呢？因为快速排序和堆排序是不稳定的排序算法，也就是对于重复值是不能保证顺序的。而直接利用索引的话其返回数据是稳定的，因为索引的B+树叶子结点的顺序（就是聚簇索引的顺序）是唯一且一定的。
因此在这种不稳定的算法情况下上面的查询出现了不一样的结果，归根结底就是使用索引避免排序和堆排序对于重复值的处理上是不同的。

order by limit排序时，根据取值大小创建相应容量的堆，即
order by limit 0,10；——创建一个10容量的堆，然后取前10
order by limit 10,10 ——创建一个20容量的堆，然后取10到20

那现在开始给APPROVE_STATUS加索引，使用where条件，执行以下sql

ALTER TABLE xz.t_pe_employee ADD INDEX IDX_APPROVE_STATUS(APPROVE_STATUS);

加完索引我们再来查询，可以看到，查询结果依旧有重复数据

我们通过explan分析可以看到，查询虽然走了IDX_APPROVE_STATUS的索引，但排序仍然是文件排序；

此处是因为我们order by有两个字段，当前只有一个字段走了索引，我们接下来尝试一下给另外一个字段加索引

ALTER TABLE xz.t_pe_employee ADD INDEX IDX_ORG_CODE(ORG_CODE);

再来查询结果我们可以看到，不再有重复数据

我们通过explan可以看到，此时走的是索引排序

此处查询走的是IDX_ORG_CODE而不是IDX_APPROVE_STATUS索引，是因为以下原因

索引选择性：

索引选择性是指索引字段值的唯一性。选择性越高，索引的效果越好。如果 ORG_CODE 字段的选择性比 APPROVE_STATUS 字段高，MySQL 可能会选择 IDX_ORG_CODE 索引，因为它能够更有效地减少需要扫描的行数。

例如，ORG_CODE 可能有更多不同的值，而 APPROVE_STATUS 可能只有少数几个值（如 '0', '1', '2' 等）。

查询条件的复杂性：

在当前查询中，ORG_CODE 是一个 IN 条件，涉及多个值。如果这些值在表中分布较广，使用 IDX_ORG_CODE 索引可以更快地定位到这些行。
相比之下，APPROVE_STATUS 只有两个条件 (= '2' 和 <> '0')，可能无法显著减少需要扫描的行数。

索引覆盖：

如果 IDX_ORG_CODE 索引能够覆盖更多的查询条件，MySQL 会选择它。即使 IDX_ORG_CODE 不能完全覆盖所有条件，但它可能比 IDX_APPROVE_STATUS 更接近覆盖所有条件。

除了加索引外，还有一个更简单的方法，排序中使用唯一值（例如主键id），保证每条数据不重复，我们可以在排序后再加一个根据主键id排序，这样就肯定会走索引排序