欢迎光临BDM
一枚菜鸟码农的成仙之路

MySQL 里 ORDER BY 与 LIMIT 结合的坑

起因

今天测试时出了一个 Bug:首先手动在一个记录表里导入了一百条数据,在测试应用里其中一条数据使用分页查询无论如何都查询不出来

只有 SELECT ALL() 或者把 LIMIT 的范围扩大到数据条目以外才能查出这条数据

背景

DDL:

CREATE TABLE `table_a`  (
  `id` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `field_a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '字段A',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP
  PRIMARY KEY (`id`) USING BTREE
  INDEX `create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

分页查询的 SQL 很简单:

SELECT id, field_a
FROM table_a
ORDER BY create_time
LIMIT num * size, size

这个记录表自运行以来,一直没什么改动,也没遇到过问题,怎么今天测试时发生如此诡异的事情?

解决

于是我采用常规二分法试图碰运气找出数据丢失的原因,总数据 100 条,先分成两页。

第一页:
SELECT id, field_a
FROM table_a
ORDER BY create_time
LIMIT 0, 50
第二页:
SELECT id, field_a
FROM table_a
ORDER BY create_time
LIMIT 50, 50

结果里面都没有那条数据!

仔细观察两份结果,发现里面有不少重复的,于是继续缩小范围:

第一页:
SELECT id, field_a
FROM table_a
ORDER BY create_time
LIMIT 0, 1
第二页:
SELECT id, field_a
FROM table_a
ORDER BY create_time
LIMIT 0, 2

上面两个查询获得了完全不同的结果!

看来我对 SQL 依旧一无所知,于是查阅官方文档,发现有这么一段话:

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns

如果一个字段有多个相同的值,那么对其使用 ORDER BY 排序时可能会返回不同顺序的结果,并且是否使用 LIMIT 也会决定返回的结果集。

也就是说, 对相同的数据进行ORDER BY 排序,再结合使用 LIMIT 会使返回结果不可预估。

此次问题的场景,是我手工插入了 100 条数据,这些数据的 create_time 都是同一秒,所以出现了这个问题。

对于这块业务的正常流程,一般慢则十秒钟左右才会插入一条数据,快也要一秒,是不会发生 create_time 大幅度相同的问题的,所以问题迟迟没有暴露出来。

官方在该页下给出了解决方案:

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting

ORDER BY 的字段添加一个类似 id 的唯一值的列,可以明确排序的结果。

结论

如上述文档所述,为了解决这个问题,可以改写 SQL,在 ORDER BY 的字段里也加上 id

改之前:

SELECT id, field_a
FROM table_a
ORDER BY create_time
LIMIT 0, 50

改之后:

SELECT id, field_a
FROM table_a
ORDER BY create_time, id
LIMIT 0, 50

还未能测试索引的影响,测试后会更新到此篇文章中。

本文遵守知识共享署名-相同方式共享 4.0 国际许可协议,未经允许不得转载BigDickMan » MySQL 里 ORDER BY 与 LIMIT 结合的坑

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们GitHub