Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

采坑笔记——mysql的order by和limit排序问题

春卷要炸着吃 2019-01-29 16:16:00 阅读数:162 评论数:0 点赞数:0 收藏数:0

背景说明

今天写出一个十分弱智的bug,记录一下,提醒自己以后别这种犯错,不怕丢人哈~
在写一个分页查询记录的sql时,要根据添加的时间逆序分页输出,之前的写法是酱紫

select
record.a,
y.c
from
(
select
a,b
from
x
order by timestamp desc
limit 0,10
) record
left join y
on record.b = y.d;

因为一些新的需求,要在后面加一些where条件,limit操作不能在嵌套查询里面加了,于是乎把limit 0,10提出来放到最外面,结果order by还留在里面,我当时想嵌套查询出来的record表已经按timestamp字段逆序排列了,再left另一张表,最终再limit出来的结果应该也是逆序的,但结果却很打脸,是正序的。

分析原因

  1. 首先控制变量,代码回滚到之前,把后来加的各种逻辑都去掉,还原到上述sql,只把limit 0,10移到最后,发现timestamp是正序的,那么问题应该就出在这里了,与后来加的其他逻辑没有关系。
  2. 那么再试一下删掉limit操作,结果timestamp是无序的!这不可能啊,于是认真看了下数据,发现一些规律,可能是按y表的自增id或created_at时间字段排序的(因为这两个字段是索引字段),那么到这里,我们至少可以得到一个简单的结论,就是联表查询结果,不是按照嵌套查询中的order by排序的,现在正向一看,确实不可能按这个排序,因为括号里面的逻辑对括号外是不可见的。
  3. 还有个问题,上述去掉limit后,最终不是按left join主表的顺序输出,按照我们常理想象,mysql是循环主表的记录去关联另一张表,那么输出的顺序应该还是主表的顺序啊,但结果却是按另一张表的字段排序的,这又是为什么呢?
    去官方手册中找找线索,发现order by模块中有这么一句话。
    order by模块
    再去limit模块中看一下
    limit模块
    从以上两个截图中,我们可以发现一些端倪,limit操作会对查询有一些优化,查询到指定条数的数据,就可以提前结束了,比如我们本文中的left操作,拿到10条结果就结束查询线程,返回客户端。我猜测,如果没有limit操作,反正全部都要join,可能mysql会对循环逻辑做一些优化,不一定要按主表来循环,思想类似于java编译中的重排序,也对应了上面截图中的那句话。

解决方案

采用最简单、最粗暴的方式,直接把order by 和 limit操作放到最外面就ok啦,其实效率上并没有什么降低,只要索引建的合理即可。

版权声明
本文为[春卷要炸着吃]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/supercj/p/10333918.html

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;

支付宝红包,每日可领