返回
基础
分类

当需要先排序后分页时我们往往使用类似的写法select,limit表示列出第二页

日期: 2020-01-02 08:17 浏览次数 : 175

mysql同时使用orderby和limit查询时的一个严重隐患--丢失数据

我经常使用order by和limit来做数据分页显示并排序,一直也没发现过什么问题。但这两天缺遇到一个严重的问题,在按时间戳升序排列并用limit分批读取数据时,却发现在某些记录丢失了,表中明明有的记录确死活读取不到。研究了大半天终于发现了问题所在,记录一下以防忘记,也是给大家提个醒。

在MySQL中我们通常会采用limit来进行翻页查询,比如limit表示列出第一页的10条数据,limit表示列出第二页。但是,当limit遇到order by的时候,可能会出现翻到第二页的时候,竟然又出现了第一页的记录。

在Mysql中我们常常用order by来进行排序,使用limit来进行分页,当需要先排序后分页时我们往往使用类似的写法select * from 表名 order by 排序字段 limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下,会很容易出现排序结果与预期不一致的问题。

问题重现

具体如下:

比如现在有一张user表,表结构及数据如下:

工具和原料

SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc LIMIT 5,5

表结构

数据库:

Ver 14.14 Distrib 5.6.11, for Linux (x86_64) using EditLine wrapper

使用上述SQL查询的时候,很有可能出现和LIMIT 0,5相同的某条记录。而如果使用如下方式,则不会出现重复的情况:

表数据

表结构:

字段 类型 说明
id int(10) 主键
pay_time int(10) 时间戳,有索引
flag tinyint(1) 类型标识,用于分类筛选
SELECT * FROM post WHERE post_status='publish' ORDER BY view_count desc LIMIT 5,5

现在想根据创建时间升序查询user表,并且分页查询,每页2条,那很容易写出sql为:select * from user order by create_time limit pageNo,2;

数据

必赢手机登录网址 ,大概5000条数据, 大部分记录的flag都等于0,pay_time字段时间戳格式都正确

但是,由于post表的字段很多,仅仅希望用这两个字段,不想把post_content也查出来。为了解决这个情况,在ORDER BY后面使用了两个排序条件来解决这个问题,如下:

在执行查询过程中会发现:
1、查询第一页数据时:

需求

筛选出flag=0的记录,按pay_time升序依次读取所有数据。

SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc,ID asc LIMIT 5,5

第一页查询结果

处理方式

使用limit分批读取数据,如:
select id, pay_time from order_customer_new where flag=0 order by pay_time asc, id asc limit 250, 10;

按理来说,MySQL的排序默认情况下是以主键ID作为排序条件的,也就是说,如果在view_count相等的情况下,主键ID作为默认的排序条件,不需要我们多此一举加ID asc。但是事实就是,MySQL再order by和limit混用的时候,出现了排序的混乱情况

2、查询第四页数据时:

发现问题

在读取数据的过程中,发现有时间戳相等的记录,分两次读取出来时,可能会丢失一条记录。见下图,id=465的记录就丢失了。

必赢手机登录网址 1

必赢手机登录网址 2

在MySQL 5.6的版本上,优化器在遇到order by limit语句的时候,做了一个优化,即使用了priority queue

第四页查询结果

问题分析与猜测

当排序值相等,其先后顺序的不确定的。这里我猜想:当465和466处于limit末尾时466排在前面,而当处于limit开头时,466缺排到后面去了。所以465丢失了,466出现了两次。
排序值相等时,其顺序的不确定应该是其结果不可预测。但真正进行排序时应该会采取一定的规则以确定唯一的排序结果,也就是说,即使有相等的排序值,多次排序的结果应该是一样的。从以前的使用经历看,mysql是这么做的。但这次遇到的问题似乎说明mysql并不是这样的。不知道mysql本来就是如此,还是一个bug。

使用 priority queue 的目的,就是在不能使用索引有序性的时候,如果要排序,并且使用了limit n,那么只需要在排序的过程中,保留n条记录即可,这样虽然不能解决所有记录都需要排序的开销,但是只需要 sort buffer 少量的内存就可以完成排序

user表共有8条数据,有4页数据,但是实际查询过程中第一页与第四页竟然出现了相同的数据。

解决办法

既然猜想此问题是因为排序值相等造成顺序不确定引起的,那么就试试增加排序条件让其排序结果是确定的、唯一的。一试果然OK,如下图所示,465出来了。

必赢手机登录网址 3

之所以MySQL 5.6出现了第二页数据重复的问题,是因为 priority queue 使用了堆排序的排序方法,而堆排序是一个不稳定的排序方法,也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

这是什么情况?难道上面的分页SQL不是先将两个表关联查询出来,然后再排好序,再取对应分页的数据吗???

 

我经常使用order by和limit来做数据分页显示并排序,一直也没发现过什么问题。但...

MySQL 5.5 没有这个优化,所以也就不会出现这个问题。

上面的实际执行结果已经证明现实与想像往往是有差距的,实际SQL执行时并不是按照上述方式执行的。这里其实是Mysql会对Limit做优化,具体优化方式见官方文档:https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
这个是5.7版本的说明,提取几个问题直接相关的点做下说明。

也就是说,MySQL 5.5是不存在本文提到的问题的,5.6版本之后才出现了这种情况。

Paste_Image.png

再看下MySQL解释sql语言时的执行顺序:

上面官方文档里面有提到如果你将Limit row_count与order by混用,mysql会找到排序的row_count行后立马返回,而不是排序整个查询结果再返回。如果是通过索引排序,会非常快;如果是文件排序,所有匹配查询的行(不带Limit的)都会被选中,被选中的大多数或者全部会被排序,直到limit要求的row_count被找到了。如果limit要求的row_count行一旦被找到,Mysql就不会排序结果集中剩余的行了。

 SELECT  DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>

这里我们查看下对应SQL的执行计划:

执行顺序依次为 form… where… select… order by… limit…,由于上述priority queue的原因,在完成select之后,所有记录是以堆排序的方法排列的,在进行order by时,仅把view_count值大的往前移动。但由于limit的因素,排序过程中只需要保留到5条记录即可,view_count并不具备索引有序性,所以当第二页数据要展示时,mysql见到哪一条就拿哪一条,因此,当排序值相同的时候,第一次排序是随意排的,第二次再执行该sql的时候,其结果应该和第一次结果一样。

Paste_Image.png

  1. 索引排序字段

    如果在字段添加上索引,就直接按照索引的有序性进行读取并分页,从而可以规避遇到的这个问题。

  2. 正确理解分页

    分页是建立在排序的基础上,进行了数量范围分割。排序是数据库提供的功能,而分页却是衍生的出来的应用需求

    在MySQL和Oracle的官方文档中提供了limit n和rownum < n的方法,但却没有明确的定义分页这个概念。还有重要的一点,虽然上面的解决方法可以缓解用户的这个问题,但按照用户的理解,依然还有问题:比如,这个表插入比较频繁,用户查询的时候,在read-committed的隔离级别下,第一页和第二页仍然会有重合。

    所以,分页一直都有这个问题,不同场景对数据分页都没有非常高的准确性要求

  3. 一些常见的数据库排序问题

    不加order by的时候的排序问题

    用户在使用Oracle或MySQL的时候,发现MySQL总是有序的,Oracle却很混乱,这个主要是因为Oracle是堆表,MySQL是索引聚簇表的原因。所以没有order by的时候,数据库并不保证记录返回的顺序性,并且不保证每次返回都一致的。

    分页问题 分页重复的问题

    如前面所描述的,分页是在数据库提供的排序功能的基础上,衍生出来的应用需求,数据库并不保证分页的重复问题

    NULL值和空串问题

    不同的数据库对于NULL值和空串的理解和处理是不一样的,比如Oracle NULL和NULL值是无法比较的,既不是相等也不是不相等,是未知的。而对于空串,在插入的时候,MySQL是一个字符串长度为0的空串,而Oracle则直接进行NULL值处理。

可以确认是用的文件排序,表确实也没有加额外的索引。所以我们可以确定这个SQL执行时是会找到limit要求的行后立马返回查询结果的。

不过就算它立马返回,为什么分页会不准呢?

官方文档里面做了如下说明:

Paste_Image.png

如果order by的字段有多个行都有相同的值,mysql是会随机的顺序返回查询结果的,具体依赖对应的执行计划。也就是说如果排序的列是无序的,那么排序的结果行的顺序也是不确定的。

基于这个我们就基本知道为什么分页会不准了,因为我们排序的字段是create_time,正好又有几个相同的值的行,在实际执行时返回结果对应的行的顺序是不确定的。对应上面的情况,第一页返回的name为8的数据行,可能正好排在前面,而第四页查询时name为8的数据行正好排在后面,所以第四页又出现了。

那这种情况应该怎么解决呢?

当需要先排序后分页时我们往往使用类似的写法select,limit表示列出第二页。官方给出了解决方案:

Paste_Image.png

如果想在Limit存在或不存在的情况下,都保证排序结果相同,可以额外加一个排序条件。例如id字段是唯一的,可以考虑在排序字段中额外加个id排序去确保顺序稳定。

所以上面的情况下可以在SQL再添加个排序字段,比如fund_flow的id字段,这样分页的问题就解决了。修改后的SQL可以像下面这样:
SELECT * FROM user ORDER BY create_time,id LIMIT 6,2;

再次测试问题解决!!

扩展介绍:
Mysql Order by排序原理