在Mysql中我们常常用order by来进行排序,使用limit来进行分页,当需要先排序后分页时我们往往使用类似的写法select * from 表名 order by 排序字段 limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下,会很容易出现排序结果与预期不一致的问题。
比如现在有一张user表,表结构及数据如下:
现在想根据创建时间升序查询user表,并且分页查询,每页2条,那很容易写出sql为:select * from user order by create_time limit pageNo,2;
在执行查询过程中会发现:
1、查询第一页数据时:
2、查询第四页数据时:
user表共有8条数据,有4页数据,但是实际查询过程中第一页与第四页竟然出现了相同的数据。
这是什么情况?难道上面的分页SQL不是先将两个表关联查询出来,然后再排好序,再取对应分页的数据吗???
上面的实际执行结果已经证明现实与想像往往是有差距的,实际SQL执行时并不是按照上述方式执行的。这里其实是Mysql会对Limit做优化,具体优化方式见官方文档:https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
这个是5.7版本的说明,提取几个问题直接相关的点做下说明。
上面官方文档里面有提到如果你将Limit row_count与order by混用,mysql会找到排序的row_count行后立马返回,而不是排序整个查询结果再返回。如果是通过索引排序,会非常快;如果是文件排序,所有匹配查询的行(不带Limit的)都会被选中,被选中的大多数或者全部会被排序,直到limit要求的row_count被找到了。如果limit要求的row_count行一旦被找到,Mysql就不会排序结果集中剩余的行了。
这里我们查看下对应SQL的执行计划:
可以确认是用的文件排序,表确实也没有加额外的索引。所以我们可以确定这个SQL执行时是会找到limit要求的行后立马返回查询结果的。
不过就算它立马返回,为什么分页会不准呢?
官方文档里面做了如下说明:
如果order by的字段有多个行都有相同的值,mysql是会随机的顺序返回查询结果的,具体依赖对应的执行计划。也就是说如果排序的列是无序的,那么排序的结果行的顺序也是不确定的。
基于这个我们就基本知道为什么分页会不准了,因为我们排序的字段是create_time,正好又有几个相同的值的行,在实际执行时返回结果对应的行的顺序是不确定的。对应上面的情况,第一页返回的name为8的数据行,可能正好排在前面,而第四页查询时name为8的数据行正好排在后面,所以第四页又出现了。
那这种情况应该怎么解决呢?
官方给出了解决方案:
如果想在Limit存在或不存在的情况下,都保证排序结果相同,可以额外加一个排序条件。例如id字段是唯一的,可以考虑在排序字段中额外加个id排序去确保顺序稳定。
所以上面的情况下可以在SQL再添加个排序字段,比如fund_flow的id字段,这样分页的问题就解决了。修改后的SQL可以像下面这样:
SELECT * FROM user
ORDER BY create_time,id LIMIT 6,2;
再次测试问题解决!!
扩展介绍:
Mysql Order by排序原理
相关推荐
mysql order by limit 的一个坑.docx
mysql 中order by 与group by的顺序是:selectfromwheregroup byorder by注意:group by 比order by先执行,order by不会对group by 内部进行排序,如果group by后只有一条记录,那么order by 将无效。要查出group ...
排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成。 排序语法: select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc...
本PDF文档是作为萌狼蓝天CSDN萌狼蓝天-MySQL专栏-的补充 因为文档的图片上传失败了没有图片,所以需要看图的可以下载本附件
MySQL中union和order by是可以一起使用的,但是在使用中需要注意一些小问题,下面通过例子来说明。首先看下面的t1表。 1、如果直接用如下sql语句是会报错:Incorrect usage of UNION and ORDER BY。 SELECT * FROM ...
activiti5.9修复mysql order by 排序bug 详情见博客地址:http://blog.csdn.net/qq413041153/article/details/7740773#comments
–按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下: nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b ...
本文就和大家一起深入研究下mysql中group by与order by.下面是我模拟我的内容表 我现在需要取出每个分类中最新的内容 select * from test group by category_id order by `date` 结果如下 明显。这不是我想...
order by 从英文里理解就是行的排序方式,默认的为升序。 order by 后面必须列出排序的字段名,可以是... 您可能感兴趣的文章:sql中 order by 和 group by的区别深度分析mysql GROUP BY 与 ORDER BYgroup by,having,o
两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低,接下来为大家介绍下连接查询排序limit
因此,常常出现这样的错误 代码如下:select * from [IND] where INDID>10unionselect * from [IND] where INDID<9>10 order by INDID descunionselect * from [IND] where INDID<9 order by INDID desc此时就出现...
在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序
mysql中order by 排序查询、asc升序、desc降序,group by 分组查询、having 只能用于group by子句、作用于组内,having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。
Mysql的分页的两个参数 select * from user limit 1,2 ... 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
如果DELETE语句包括一个ORDER BY子句,则各行按照子句中指定的顺序进行删除。此子句只在与LIMIT联用是才起作用。例如,以下子句用于查找与WHERE子句对应的行,使用timestamp_column进行分类,并删除第一(最旧的)行...
今天在使用ORDER BY的过程中出现了一点问题,发现之前对ORDER BY理解是错误的。 之前在w3s网站上看到ORDER BY的用法,以为是对选出来的数据按关键字升序或者降序排列,结果今天尝试select数据集数据的时候,发现使用...
假设有一个表:reward(奖励表),表结构如下: CREATE TABLE test.reward ( id int(11) NOT NULL AUTO_INCREMENT, uid int(11) NOT NULL COMMENT '用户uid', money decimal(10, 2) NOT NULL COMMENT '奖励金额'...
MYSQL随机抽取查询:MySQL Order By Rand()效率问题一直是开发人员的常见问题,俺们不是DBA,没有那么牛B,所只能慢慢研究咯,最近由于项目问题,需要大概研究了一下MYSQL的随机抽取实现方法
order by 从英文里理解就是行的排序方式,默认的为升序。 order by 后面必须列出排序的字段名,可以是多个字段名。 group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用,使用时至少需要一个分组标志...