【MySQL】官网学习 order by limit 中的乱序问题

前言

对于同一句sql语句:order by … limit …,可能会出现不同的返回结果。通过官网能了解到,至少在5.7.32版本中这是存在的,需要我们编写正确的sql语句避免返回结果的乱序问题。前置知识:

  • 索引的顺序特性
  • 文件排序 (filesort)
  • 排序的稳定性
    简单来说,排序的稳定可以这么表达:
    1. cheng[age: 18, class: A],han[age: 18, class: A] 固定这个先后组合且视为已按age排序
    2. 在不同的数据集中都保持这个先后组合,即
      liiu[age: 1class: A]....... cheng[age: 18, class: A],han[age: 18, class: A] ........yang[age: 99, class: B]
    3. 不论数据集大小如何,对age排序后的返回值中 cheng[age: 18, class: A],han[age: 18, class: A] 不会产生颠倒的情况

官网指明的学习路线

  1. limit 优化
  2. order by 优化

点明问题

官网原文:

One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

limit 与其他语句结合使用的情况

使用 limit 的优化思想是,希望查询优化器不要扫描自己认为没必要返回的值,如果能够借助索引能确认row_count 的结果集,则可以直接返回。反之可能要用到文件排序甚至是临时表。官网举得例子要满足的条件:没有having参与的sql语句。
针对不同的情况,查询优化器做出的反应如下:

  • 只有少量数据
    查询优化器认为全表扫描更快

  • limit 与 order by 结合使用
    不论是利用索引完成了 order by 任务,还是使用了文件排序,当 row_count 能够被确认时,就不再遍历其他数据了。简单来说:

    1. 使用了索引,如果可以利用索引顺序的特性,那么row_count 个数据被找到后就会立即返回结果,速度很快。
    2. 如果使用了文件排序,可能会比使用索引对更多的数据进行排序,但是收集完 row_count 个数据后,也会立即返回
  • limit 与 distinct 结合使用
    distinct 只是让limit 变得更加严格了,但是现象是一致的,但是收集完 row_count 个数据后,也会立即返回。

  • limit 与 group by 结合使用

    • group by xxx,yyy 的遍历过程:
      1. 对数据先排序
      2. 遍历数据行 hash(xxx,yyy)[注:这里使用伪代码]
      3. 感知到 hash (xxx,yyy) 变化就能产生多个分组
    • limit 语句选举出 row_count 个结果集,同样也只关注 xxx,yyy 列

乱序问题根源

官网原文:

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.

MySQL 出于效率考虑,order by col1, col2 语句用于排序时,并不负责关心 col3, col4 … 的值。也就是同样的 col1, col2 的行数据,在limit row_count 的结果集中不负责 col3, col4 … 的排序(且返回值不确定),这是一个很自然的事情。侧面也反映了MySQL 会使用不稳定的排序算法。(网上查的用的是快排,目前没在官方文档上明确看到这个说法)
官网的例子加以说明:

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+
mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

解决问题

  • 思路:让返回值稳定,前提是让Mysql能找到一个多次查询依旧稳定的序列。
    1. 选一个业务需求的排序列 如:last_update_time
    2. 如果last_update_time由于批量更新出现重复值,则使用 id 加以固定排序
  • 结论:通过在排序字段后面追加id字段,既可以保证返回值稳定
mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL,GROUP BY、ORDER BY和LIMIT这三个关键字的顺序是有一定规定的。一般来说,它们的顺序应该是先使用GROUP BY进行分组,然后使用HAVING进行筛选,接着使用ORDER BY进行排序,最后使用LIMIT进行结果限制。 具体来说,在查询,应该按照以下顺序使用这三个关键字: 1. 使用GROUP BY对结果进行分组,以便按照某个字段或表达式进行聚合操作。 2. 使用HAVING对分组后的结果进行筛选,只保留满足条件的分组。 3. 使用ORDER BY对筛选后的结果进行排序,可以按照一个或多个字段进行升序或降序排序。 4. 最后使用LIMIT对排序后的结果进行限制,只返回指定数量的结果。 所以,在你提供的引用,需求四的查询语句的顺序是正确的,先使用GROUP BY进行分组,然后使用HAVING进行筛选,接着使用ORDER BY进行排序,最后使用LIMIT进行结果限制。而需求三的查询语句则没有使用HAVING进行筛选,所以它的顺序是不正确的。 总结起来,正确的顺序是:GROUP BY -> HAVING -> ORDER BY -> LIMIT。 #### 引用[.reference_title] - *1* *2* *3* [mysql join 、group by、having、order by、limit 执行顺序](https://blog.csdn.net/weixin_43689776/article/details/101107378)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值