《Dubbo 实现原理与源码解析 —— 精品合集》 《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》 《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》 《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》 《Java 面试题 + Java 学习指南》

摘要: 原创出处 占小狼 「占小狼」欢迎转载,保留摘要,谢谢!


🙂🙂🙂关注微信公众号:【芋道源码】有福利:

  1. RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表
  2. RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址
  3. 您对于源码的疑问每条留言将得到认真回复。甚至不知道如何读源码也可以请教噢
  4. 新的源码解析文章实时收到通知。每周更新一篇左右
  5. 认真的源码交流微信群。

1. 现象与问题

ORDER BY排序后,用LIMIT取前几条,发现返回的结果集的顺序与预期的不一样。

下面是我遇到的问题:

图片

图片

图片

图片

可以看到,带LIMIT与不带LIMIT的结果与我预期的不一样,而且“很不可思议”,真是百思不得其解。

后来百度了一下,如果order by的列有相同的值时,mysql会随机选取这些行,为了保证每次都返回的顺序一致可以额外增加一个排序字段(比如:id),用两个字段来尽可能减少重复的概率。

于是,改成 order by status, id;

图片

问题虽然是解决了,但还是看看官方文档上怎么说的吧!

2. LIMIT查询优化

摘自“LIMIT查询优化”

图片

如果你只需要结果集中的指定数量的行,那么请在查询中使用LIMIT子句,而不是抓取整个结果集并丢弃剩下那些你不要的数据。

MySQL有时会优化一个包含LIMIT子句并且没有HAVING子句的查询:

  • MySQL通常更愿意执行全表扫描,但是如果你用LIMIT只查询几行记录的话,MySQL在某些情况下可能会使用索引。
  • 如果你将LIMIT row_count子句与ORDER BY子句组合在一起使用的话,MySQL会在找到排序结果的第一个row_count行后立即停止排序,而不是对整个结果进行排序。如果使用索引来完成排序,这将非常快。如果必须执行文件排序,则在找到第一个row_count行之前,选择所有与查询匹配但不包括LIMIT子句的行,并对其中大部分或所有行进行排序。一旦找到第一个row_count之后,MySQL不会对结果集的任何剩余部分进行排序。这种行为的一种表现形式是,一个ORDER BY查询带或者不带LIMIT可能返回行的顺序是不一样的。
  • 如果LIMIT row_count与DISTINCT一起使用,一旦找到row_count惟一的行,MySQL就会停止。
  • LIMIT 0 可以快速返回一个空的结果集,这是用来检测一个查询是否有效的一种很有用的方法。
  • 如果服务器使用临时表来解析查询,它将使用LIMIT row_count子句来计算需要多少空间。
  • 如果ORDER BY不走索引,而且后面还带了LIMIT的话,那么优化器可能可以避免用一个合并文件,并使用内存中的filesort操作对内存中的行进行排序。

如果ORDER BY列有多行具有相同的值,服务器可以自由地以任何顺序返回这些行,并且根据总体执行计划可能以不同的方式返回。换句话说,这些行的排序顺序对于无序列是不确定的。

影响执行计划的一个因素是LIMIT,因此对于一个ORDER BY查询而言,带与不带LIMIT返回的行的顺序可能是不一样的。

看下面的例子:

图片

包含LIMIT可能会影响每一个category行的顺序。例如:

图片

如果你需要确保无论带不带LIMIT都要以相同的顺序返回,那么你可以在ORDER BY中包含附加列,以使顺序具有确定性。例如:

图片

3. 小结

1、如果你只需要结果集中的某几行,那么建议使用limit。这样这样的话可以避免抓取全部结果集,然后再丢弃那些你不要的行。

2、对于order by查询,带或者不带limit可能返回行的顺序是不一样的。

3、如果limit row_count 与 order by 一起使用,那么在找到第一个row_count就停止排序,直接返回。

4、如果order by列有相同的值,那么MySQL可以自由地以任何顺序返回这些行。换言之,只要order by列的值不重复,就可以保证返回的顺序。

5、可以在order by子句中包含附加列,以使顺序具有确定性。

文章目录
  1. 1. 1. 现象与问题
  2. 2. 2. LIMIT查询优化
  3. 3. 3. 小结