MySql order by ... limit 问题

环境说明

  • MySQL 版本
1
2
3
4
5
6
7
select version();

+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
  • 存储引擎:ENGINE=InnoDB

一、问题起因

在给 应急中心 报送数据的时候,采用的是通过使用「业务表」的某个时间字段 作为 游标,通过每次获取的数量作为 偏移量,遍历一个 静态数据的集合,从而达到迭代遍历的目的。

也即,会构造 <last_id|time> 的游标,time 按照月划分(业务需要),从 0 开始计数。

  • time: 表示起始时间,截止时间为 +1 月

  • last_id:表示当前该时间取到第几个

    举例说明:

  • 0|2018-01-01:表示在 [1月, 2月) 从 0 开始获取 n 条数据

  • 128|2018-01-01:表示在 [1月, 2月) 从 128 开始获取 n 条数据

当数据集合为空时,break 至下一个月。

一切看起来正常,但在最终 check 数据 的时候,却发现有 重复的报送

下面,我们通过 懒人投资单 的上报作为实例,尝试解释该问题。

场景还原

  • 注:以下数据均为举例说明,不代表生产环境真实数据情况。

报送投资单(trade_orders),使用 pay_time 作为游标。

  • sql:
1
select * from trade_orders where pay_time >= '2018-01-01 00:00:00' and pay_time <= '2018-01-31 23:59:59' order by pay_time asc limit 100, 500

我们设想这里在相同 pay_time 的上,会根据主键 id 进行 asc

  • 索引:
1
`KEY `pay_time` (`pay_time`),
  • explain:
1
2
3
4
5
+----+-------------+--------------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+----------+---------+------+-------+-------------+
| 1 | SIMPLE | trade_orders | range | pay_time | pay_time | 9 | NULL | 57420 | Using where |
+----+-------------+--------------+-------+---------------+----------+---------+------+-------+-------------+

按照预期,该 sql 理应能够不重复的遍历整个数据集合。但不幸的是,事实并没按预期发生,在报送过程中,出现了重复报送的问题。

场景

  • pay_time2019-01-01 18:09:34

  • 该时间点有 2 条数据,id 为 100,98。

  • 某次 limit 100, 500 恰好取到了这个时间的 1 条数据,取出来的是 id=98,也即发生了 跨页问题

  • 下次偏移依然从这个时间开始,但 MySQL 仍然返回了 id=98,而不是另一条数据:id=100

  • 这里,并没有按照我们预想的一样,有个隐含的排序规则:id asc

二、问题分析

通过上面的描述,我们猜测 MySQL 针对 order by ... limit 的查询,并没有对结果集进行排序。具体到这个实例,并没有按照 id ascid desc 排序。而且很可能是 随机 返回。

去找 mysql 手册,5.1 版本 没找到,直接看 5.7 版本

8.2.1.17 LIMIT Query Optimization,这篇文章很明确给出了答案:

1
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.

通俗翻译过来就是:

1
2
3
如果 ORDER BY 的那个字段(列),在数据库里有多条数据(行),MySQL 会按照随机顺序返回查询结果,具体取决于对应的执行计划。

或者说,如果排序的列是无序的,那么排序的结果行的顺序也是 *非确定性的*。

而我们的查询场景,正好符合这个解释。

通过这篇文章,我们还可以了解到 MySQL 是到底是怎么解析 order by ... limit 语句的,比如如下一段:

1
If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

简而言之,MySQL 在按照 ORDER BY 排序后的匹配结果集,会且只会选取 LIMIT row_count 行返回,而不会再针对该结果集进行排序。

  • 注:这篇文章需要通读并理解。

三、问题解决

既然知道了原因,那么就很好解决了。既然没有按照设想的 id asc,那么就在 sql 指定顺序:

1
select * from trade_orders where pay_time >= '2018-01-01 00:00:00' and pay_time <= '2018-01-31 23:59:59' order by pay_time asc, id asc limit 100, 500

显式指明 id asc,明确告诉 MySQL,我们需要针对主键 id 进行一次排序。

但需要注意的是,这里只能选取具有 unqiue 属性 的字段,才能保证稳定排序。(思考下是为什么)。

四、问题延伸

在实际的业务场景中,即便我们使用了 order by pay_time asc, id asc,但还是可能存在问题。

一个典型的场景如下:

  • 假设脚本执行到了 当前时间(设为 etime)停止,取到了 n 条数据,假设 id=100

  • 恰好这时有 id=98 的订单在变更状态,但并没有被这次 轮询 获取到。

  • 下次脚本启动时,从上次的时间 etime 开始,如果是使用了 id asc,会导致 id=98的数据被忽略,重复取到了 id=100 这条。如果是使用了 id > last_id 的条件,会导致漏掉了 id=98 的数据。

当然,这个问题就和 MySQL 无关了,主要是业务理解不当导致的。

也即,因为 并发 导致遍历的不是一个 静态数据集合。这里,我们需要通过别的手段进行解决。

最简单粗暴的解决方案为,加一个 时间戳限制

1
select * from trade_orders where pay_time >= '{$stime}' and pay_time <= '{$etime} and pay_time <= '{30秒之前}' order by pay_time asc, id asc limit 100, 500

也即,在已经过去的时间里,业务终态的数据集合必然是静态的

五、问题总结

初看似乎是个小问题,想当然 的认为 MySQL 会在 pay_time asc 的基础上进行 id asc。但就因为这么个 想当然的排序,导致应急中心上报需要全部重来,代价极大。

还有一个思考是,所谓web 开发,不仅仅是写代码,更需要结合业务去写代码。

大致总结如下:

  • 技术上,不能 唯经验论,遇到的坑,需要彻底查明原因;

  • 业务上,要学会自我构建 极端场景,尝试从各个角度、各个临界条件,验证代码的健壮性,不要只满足 正常情况大部分情况