精华内容
下载资源
问答
  • 作为程序员,经常写 SQL 语句是正常不过了。...具体看下面这三张图其中,第一张图查询所有数据,并按 sort 字段排序,第二张图,查询从第 1 条数据开始,查询 10 条数据,并按 sort 字段排序,第三张图,查询从第 ...

    作为程序员,经常写 SQL 语句是正常不过了。然而,编写一些 SQL 语句,总会出现一些奇怪的问题。

    问题

    最近在项目中遇到一个很神奇的问题,MySQL 使用 order by 进行排序并进行分页的时候,会出现部分数据丢失和重复。具体看下面这三张图

    b93648e306640c620394df1f478f6f4c.png

    5f697e6c8f9bb64c4b381778ef4d77ee.png

    a4092d72ea9fe6ccf3b60c63b22fcfba.png

    其中,

    第一张图查询所有数据,并按 sort 字段排序,

    第二张图,查询从第 1 条数据开始,查询 10 条数据,并按 sort 字段排序,

    第三张图,查询从第 11 条数据开始,查询 10 条数据,并按 sort 字段排序,

    仔细看我用红色标记出来的,可以发现,分类11 的数据在分页后查询不出来,而分类18 则出现了两次。很明显的发现,当进行数据分页时,部分数据出现了丢失和重复。

    分析原因

    在 MySQL 关系型数据库中,往往会存在多种排序算法。通过 MySQL 的源码和官方文档介绍可以得知,它的排序规律可以总结如下:

    当 order by 不使用索引进行排序时,将使用排序算法进行排序;

    若排序内容能全部放入内存,则仅在内存中使用快速排序;

    若排序内容不能全部放入内存,则分批次将排好序的内容放入文件,然后将多个文件进行归并排序;

    若排序中包含 limit 语句,则使用堆排序优化排序过程。

    根据上面的总结,当 order by limit 分页出现数据丢失和重复。而 order by 的 sort 字段没有使用索引(正常情况下,排序的字段也不会使用索引),如果使用了索引,则会进行索引排序。

    因此可以得出,上面的图二和图三的 SQL 语句使用了堆排序。因为 sort 字段没有索引,所以没走索引排序;并且使用了 limit。导致最终使用了堆排序。

    如果了解算法的你,应该知道堆排序是不稳定的。这种不稳定性,指的就是多次排序后,各个数的相对位置发生了变化。

    但是,不是所有的 MySQL 版本都是这样。从 MySQL 5.6 版本开始,优化器在使用 order by limit 时,做了上面的优化,导致排序字段没有使用索引时,使用堆排序。

    问题解决

    通过上面的分析,有两种解决方案可以解决此问题。

    方案一:降低 MySQL 版本为 5.5 或更低版本。此方案不推荐,数据库版本一般是指定的,降低数据库版本工作量较大。

    方案二:在 order by 排序字段里,添加有索引的字段,比如主键ID。这样在排序时可以保证顺序稳定。

    在图二、图三中,增加主键 category_id 字段排序后,就不会出现数据丢失和重复了。

    ecd901745509b5708499b7330c7ed1e3.png

    895c4024e2c8e14be85db90bac6dd1a7.png

    总结

    如果查询数据进行排序和分页时,如果排序字段没有使用索引,一定要添加一个有索引的字段,比如主键 ID,保证顺序稳定。否则,查询的数据会导致数据丢失和重复。

    理解此问题出现的原因后,赶紧去看看你的项目中有没有这种情况吧!要不然出问题就不好办了!

    相关 SQL 语句

    最后,附上新建表和表相关数据的 SQL 语句:

    DROP TABLE IF EXISTS `sys_category`;

    CREATE TABLE `sys_category` (

    `category_id` bigint NOT NULL AUTO_INCREMENT,

    `category_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分类名称',

    `sort` int DEFAULT NULL COMMENT '分类排序',

    PRIMARY KEY (`category_id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

    INSERT INTO `sys_category` VALUES (1,'分类1',1),(2,'分类2',2),(3,'分类3',20),(4,'分类4',21),(5,'分类5',22),(6,'分类6',23),(7,'分类7',0),(8,'分类8',0),(9,'分类9',0),(10,'分类10',0),(11,'分类11',0),(12,'分类12',0),(13,'分类13',0),(14,'分类14',0),(15,'分类15',0),(16,'分类16',0),(17,'分类17',0),(18,'分类18',0);

    参考文章

    展开全文
  • 最近在项目中遇到一个很神奇的问题,MySQL 使用 order by 进行排序并进行分页的时候,会出现部分数据丢失和重复。具体看下面这三张图 其中, 第一张图查询所有数据,并按 sort 字段排序, 第二张图,查询从第 1 ...

    作为程序员,经常写 SQL 语句是正常不过了。然而,编写一些 SQL 语句,总会出现一些奇怪的问题。

    问题

    最近在项目中遇到一个很神奇的问题,MySQL 使用 order by 进行排序并进行分页的时候,会出现部分数据丢失和重复。具体看下面这三张图

    图一

    图二

    图三

    其中,

    • 第一张图查询所有数据,并按 sort 字段排序,
    • 第二张图,查询从第 1 条数据开始,查询 10 条数据,并按 sort 字段排序,
    • 第三张图,查询从第 11 条数据开始,查询 10 条数据,并按 sort 字段排序,

    仔细看我用红色标记出来的,可以发现,分类11 的数据在分页后查询不出来,而分类18 则出现了两次。很明显的发现,当进行数据分页时,部分数据出现了丢失和重复。

    分析原因

    在 MySQL 关系型数据库中,往往会存在多种排序算法。通过 MySQL 的源码和官方文档介绍可以得知,它的排序规律可以总结如下:

    1. 当 order by 不使用索引进行排序时,将使用排序算法进行排序;
    2. 若排序内容能全部放入内存,则仅在内存中使用快速排序;
    3. 若排序内容不能全部放入内存,则分批次将排好序的内容放入文件,然后将多个文件进行归并排序;
    4. 若排序中包含 limit 语句,则使用堆排序优化排序过程。

    根据上面的总结,当 order by limit 分页出现数据丢失和重复。而 order by 的 sort 字段没有使用索引(正常情况下,排序的字段也不会使用索引),如果使用了索引,则会进行索引排序。

    因此可以得出,上面的图二和图三的 SQL 语句使用了堆排序。因为 sort 字段没有索引,所以没走索引排序;并且使用了 limit。导致最终使用了堆排序。

    如果了解算法的你,应该知道堆排序是不稳定的。这种不稳定性,指的就是多次排序后,各个数的相对位置发生了变化。

    但是,不是所有的 MySQL 版本都是这样。从 MySQL 5.6 版本开始,优化器在使用 order by limit 时,做了上面的优化,导致排序字段没有使用索引时,使用堆排序。

    问题解决

    通过上面的分析,有两种解决方案可以解决此问题。

    1. 方案一:降低 MySQL 版本为 5.5 或更低版本。此方案不推荐,数据库版本一般是指定的,降低数据库版本工作量较大。
    2. 方案二:在 order by 排序字段里,添加有索引的字段,比如主键ID。这样在排序时可以保证顺序稳定。

    在图二、图三中,增加主键 category_id 字段排序后,就不会出现数据丢失和重复了。

    image-20210117221819803

    image-20210117221759168

    总结

    如果查询数据进行排序和分页时,如果排序字段没有使用索引,一定要添加一个有索引的字段,比如主键 ID,保证顺序稳定。否则,查询的数据会导致数据丢失和重复。

    理解此问题出现的原因后,赶紧去看看你的项目中有没有这种情况吧!要不然出问题就不好办了!

    相关 SQL 语句

    最后,附上新建表和表相关数据的 SQL 语句:

    DROP TABLE IF EXISTS `sys_category`;
    CREATE TABLE `sys_category` (
      `category_id` bigint NOT NULL AUTO_INCREMENT,
      `category_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分类名称',
      `sort` int DEFAULT NULL COMMENT '分类排序',
      PRIMARY KEY (`category_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
    
    INSERT INTO `sys_category` VALUES (1,'分类1',1),(2,'分类2',2),(3,'分类3',20),(4,'分类4',21),(5,'分类5',22),(6,'分类6',23),(7,'分类7',0),(8,'分类8',0),(9,'分类9',0),(10,'分类10',0),(11,'分类11',0),(12,'分类12',0),(13,'分类13',0),(14,'分类14',0),(15,'分类15',0),(16,'分类16',0),(17,'分类17',0),(18,'分类18',0);
    

    参考文章

    从根上理解order by limit分页数据重复问题

    MySQL 5.6之后 order by limit 排序分页数据重复问题

    展开全文
  • 以下是一个楼主写项目时一个很普通的sql SELECT api.id FROM auction_product_instance api WHERE ...然后再以上的sql上加一个分页limit,再看效果 SELECT api.id FROM auction_product_instan

    以下是一个楼主写项目时一个很普通的sql

    SELECT
    	api.id 
    FROM
    	auction_product_instance api 
    WHERE
    	api.`status` = 1 
    ORDER BY
    	api.`bid_num` DESC
    

    结果集如下:

    3522
    3509
    3516
    3541
    3524
    3523
    3521
    3520
    3519
    3518
    

    然后再以上的sql上加一个分页limit,再看效果

    SELECT
    	api.id 
    FROM
    	auction_product_instance api 
    WHERE
    	api.`status` = 1 
    ORDER BY
    	api.`bid_num` DESC
    limit 10
    

    结果集如下:

    3522
    3509
    3507
    3508
    3510
    3511
    3512
    3513
    3514
    3515
    

    可能细心的童鞋已经发现结果已经不一样了,对比图如下。

    第1个sql结果  第2个sql结果
    3522  			3522
    3509  			3509
    3516  			3507   这里就已经不一样了
    3541  			3508
    3524  			3510
    3523  			3511
    3521  			3512
    3520  			3513
    3519  			3514
    3518  			3515
    

    我给出的结论是问题应该出在排序字段上, 当你要排序的字段可能出现重复时,比如以上例子中的 bid_num 字段可能的值 只有1跟0 且 大部分记录还可能是0,因为Mysql的执行优化器是自动的,当排序字段值一样时,优化器可能按他觉得最优的结果来显示,所以导致结果不一样,甚至分页到第二页的话,可能还有重复的结果。

    解决办法

    在排序字段中加一个能决定顺序的,如id 排序

    sql改变后如下

    SELECT
    	api.id 
    FROM
    	auction_product_instance api 
    WHERE
    	api.`status` = 1 
    ORDER BY
    	api.`bid_num` DESC,
    	api.id DESC
    

    我们来看加分页之后跟没加分页结果如何

    没加分页的结果    加分页limit 10之后的结果
    3522            3522
    3509			3509
    3541			3541
    3524			3524
    3523			3523
    3521			3521
    3520			3520
    3519			3519
    3518			3518
    3517			3517
    3516
    3515
    3514
    

    我们可以从结果中可以得到,加了id排序之后,优化器就知道了如何去确定最终的排序,从而分页也不会出现重复结果。具体产生的原因是什么如有大神解答,请在评论区评论,以上阐述的原因是本人猜测。

    展开全文
  • order by `hot` 可以看到总共数量为30个,然后按照最后一排的hot字段排序,但其实有个误区,编译器这个时候帮我做多了一步就是按照自增id排序导致当我运行 select ... where ... order by `hot` limit 15 select ...
    select * from `t_content_tag_relation_mobile` where ... order by `hot`
                                                                                                    
    
    

    可以看到总共数量为30个,然后按照最后一排的hot字段排序,但其实有个误区,编译器这个时候帮我做多了一步就是按照自增id排序了导致当我运行

    select ... where ... order by `hot` limit 15
    
    

     

    select ... from `t_content_tag_relation_mobile` where ... order by `hot` limit 15 offset 15
                                                                                                         

     

     

     

    相信你们也发现,分页时,居然525这条数据出现了两次。

    这是因为有几条记录的hot字段值都一样,排序时指按照hot排序的时候,当hot字段值是一样时,这几条记录的顺序是随机的,结合limit分页,就会有可能出现这种两次运行结果重复的问题,为了解决这个问题我们可以排序两个字段,在hot排序后按照id再排序一次,这样结果就不会重复了

    展开全文
  • mysql数据库orderBy + limit 导致部分数据重复 一、原因 mysql 的orderBy查询是不稳定的排序,每次的排序可能都是不一样的,那么问题来了,当你的排序列存在重复值较多时,就可能在limit分页时查询到之前重复的数据...
  • mysql order by+limit排序无效问题

    千次阅读 2019-04-21 00:41:16
    问题出现场景,在假数据测试的时候,有一条sql,一般正常的分页排序。死活不倒序 后来发现了是在排序的字段有重复,没有唯一,在我接手时...当排序的字段有重复的时候可能会导致排序失败混乱,还是第一次遇到这种...
  • mysql对无索引字段进行排序limit ,当被排序字段有相同值时并且在limit范围内,取的值并不是正常排序后的值,有可能第一页查询的记录,重复出现在第二页的查询记录中,导致分页结果查询错乱问题。 也就是说,当排...
  • 前言: 上个月,部门同事发现了代码中的一个bug,就是在分页查询的时候使用order by limit,由于order by后面没有加索引,所以查出来的数据会出现重复的问题。... 之所以堆排序的不稳定性会导致“相同的值可能...
  • 生产环境一个列表页的分页查询sql: SELECT * FROM ap_clue a LEFT JOIN ap_dealer d ON...ORDER BY a.date_create DESC limit 90,5; 根据ap_clue表中的date_create字段倒叙排列,但是ap_clue表中很多记录date_cre...
  • 在实际开发中遇到一个有趣的问题,有关数据库order by排序不唯一,导致分页出现重复数据问题。项目中需要对创建时间进行降序查询,每页显示3条初始查询:select * from material WHERE 1=1 and status = 2 order by ...
  • ORDER BY后面谨慎使用按照时间排序问题展示在执行获取评论列表、留言列表等诸多操作时,都需要将从数据库中查出的结果按照时间做倒排处理和翻页处理。同一时间点(例如1s内),若包含的结果数量过多,将不可避免的出现...
  • 在实际开发中遇到一个有趣的问题,有关数据库order by排序不唯一,导致分页出现重复数据问题。 项目中需要对创建时间进行降序查询,每页显示3条 初始查询: select * from material WHERE 1=1 and status = 2 order ...
  • 今天在处理项目数据的时候,发现一个问题,就是当使用模糊查询的时候,会导致排序失效 比如: SELECT * FROM mov_video_info WHERE title LIKE '%魔兽%' ORDER BY title LIMIT 10 模糊搜索结果按匹配度进行...
  • 在实际开发中遇到一个有趣的问题,有关数据库order by排序不唯一,导致分页出现重复数据问题。项目中需要对创建时间进行降序查询,每页显示3条初始查询:select * from material WHERE 1=1 and status = 2 order by ...
  • 常规是这么写的 SELECT id,name FROM `XX_flow` WHERE...如果要对里面的多个条件进行排序,先排序时间再排序热度 会发现翻页以后数据出现重复 SELECT id,name FROM `XX_flow` WHERE `class_id`=1 ORDER BY `date`...
  • 场景:根据时间排序分页查询交易记录表时,查总数没问题,但是从...LIMIT 1,20如果数据库存入时间最低单位到秒,那么在交易多的时候表中会出现多笔在同一时间生成的订单,当以时间排序时,数据库难以判断时间先后,...
  • but I'm trying to limit the amount of results, I almost have it but it gives me an error... can you guys help me? <pre><code><?php foreach (glob("../../imagenes/medianas/*") as $files) { $images...
  • 由于id的字段类型为varchar类型,导致在根据id排序时出现问题 下面是我在MySQL数据库中以uniqueid排序排序后的结果如下: MariaDB [da]> select uniqueid,username from user order by uniqueid limit 15; +...
  • mysql limit 用法错误

    千次阅读 2017-04-13 13:20:58
    在做某个业务的时候,需要先将数据排序,再分页,在给limit上参数的时候沿用了oracle的rownumber方式传参,最终导致了分页查到的数据越来越多,导致响应越来越慢,最终找到了问题所在; 错误代码: 1 SELECT...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 130
精华内容 52
关键字:

limit导致排序