精华内容
下载资源
问答
  • 最近在项目中遇到一个很神奇的问题,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再排序一次,这样结果就不会重复了

    展开全文
  • ① select * from table limit 2,1; //含义是跳过2条取出1条数据,limit后面是从第2条开始读,读取1条信息,即读取第3条数据 ② select * from table limit 2 offset 1; //含义是从第1条(不包括)数据开始取出2条...

    ① select * from table limit 2,1;
    //含义是跳过2条取出1条数据,limit后面是从第2条开始读,读取1条信息,即读取第3条数据
    ② select * from table limit 2 offset 1;
    //含义是从第1条(不包括)数据开始取出2条数据,limit后面跟的是2条数据,offset后面是从第1条开始读取,即读取第2,3条
    在这里插入图片描述

    select
    IFNULL
    (select distinct salary
    
    	from Employee
    
    	order by salary Desc
    
    	limit 11),
    
    	null
    )as SecondHighestSalary
    
    
    
    例
    
    select  distinct 成绩
    
    from 成绩表
    
    where 课程=‘语文’
    
    order by 课程,成绩 desc
    
    limit 1,1;
    
    ```bash
    在这里插入代码片

    高级版 得到第N高的salary

    CREATE FUNCTION getNthHighestSalary(N INT) 
    RETURNS INT
    BEGIN    
    set N=N-1;   
    if N<0 then    
    RETURN null;     
    else   
    return
      (   # Write your MySQL query statement below.  
          select ifnull      
          (              
          	(select distinct Salary               
          	from Employee                
          	order by Salary desc              
            limit N, 1),null)        
        as getNthHighestSalary    
     );
     END if;
     End
    展开全文
  • 按照以下加了limit 1,2子句后导致排序结果: +----+------+ | id | col1 | +----+------+ | 3 | 5 | | 6 | 4 | | 5 | 3 | | 4 | 3 | ... +----+------+ 从结果集第一行开始,将id放入临时表中,临时表的结构...
  • 3 排序limit组合场景优化 SQL中的排序limit组合是一个很典型的索引优化创景。我们知道btree索引在内存中是有序的,通过遍历btree索引可以直接拿到sort后的结果,这里组合使用limit后,只需要遍历btree的一部分...
  • mysql order by+limit排序无效问题

    千次阅读 2019-04-21 00:41:16
    问题出现场景,在假数据测试的时候,有一条sql,一般正常的分页排序。死活不倒序 后来发现了是在排序的字段有重复,没有唯一,在我接手时...当排序的字段有重复的时候可能会导致排序失败混乱,还是第一次遇到这种...
  • order by --- limit 导致查询速度慢

    千次阅读 2019-05-16 15:57:00
    连用其实是有很多注意点,包括会导致分页查询,不同页查询到同一个数据的情况。 当然都是可以通过一些方法解决,比如在 limit 前, count 下总数据;指定主键id范围进行查询等,但是用的时候需要自己注意。 最后...
  • 在项目中:pgsql自定义函数,遇到 for循环中 limit 出来的数据时重复的问题,经过排查发现是**排序不彻底**导致的。
  • Mysql 进行Order排序limit导致数据重复和消失Bug1.背景2.原因3.处理方案 1.背景 数据库某个定时任务表中有31条数据,对该表进行num字段排序并分页,然后发现 SELECT * FROM A ORDER BY pack_act_num DESC limit 0,...
  • 昨天公司APP项目上线时遇到一个奇葩...使用限制符limit时,mysql使用了idx_order_type索引,扫描了46w行 没有使用限制符limit时,mysql使用了idx_agent_order_type索引(联合索引),扫描了9k行 解决方案: 1...
  • select * from persons limit A offset B; 解释: A就是你需要多少行; B就是查询的起点位置。 示例: select * from persons limit 5 offset 0 ; 意思是,起点0开始查询,返回5条数据。 select * from persons ...
  • mysql对无索引字段进行排序limit ,当被排序字段有相同值时并且在limit范围内,取的值并不是正常排序后的值,有可能第一页查询的记录,重复出现在第二页的查询记录中,导致分页结果查询错乱问题。 也就是说,当排...
  • 其实今天遇到的最大问题还是mysql order by limit 排序分页数据重复的问题,昨天晚上搞了3个小时都没处理,因为不管怎么查数据也总会重复,查询了好多,都反映说在 order by上加一个主键UUID唯一的排序,然后再分页...
  • 在实际开发中遇到一个有趣的问题,有关数据库order by排序不唯一,导致分页出现重复数据问题。这个问题还是有点特殊的。因为数据库研究的少这个问题挺让我困惑。为了解决它找了很久的错误。 首先介绍一下问题的出现...
  • mysql数据库orderBy + limit 导致部分数据重复 一、原因 mysql 的orderBy查询是不稳定的排序,每次的排序可能都是不一样的,那么问题来了,当你的排序列存在重复值较多时,就可能在limit分页时查询到之前重复的数据...
  • 文章目录一、详解排序查询(order by)二、详解分页查询(limit)三、常见的坑 今天和大家聊聊数据查询中常使用到的两个查询,排序和分页查询。 | 声明:本文中被[]包含的表示可选项,|符号分开的表示可选其一。 一...
  • limit介绍 limit用来限制select查询返回的行数,常用于分页等操作。 语法: select 列 from 表 limit [offset,] count; 说明: offset:表示偏移量,通俗点讲就是跳过多少行,offset可以省略,默认为0,表示跳过0行...
  • 在使用 sort+limit 的查询中,由于 minimongo 插入不是成批的,每次插入都会导致创建+销毁一个新模板,即使它永远不会被呈现。 但另一个问题是,列表越大,完成创建/销毁生命周期所需的时间就越长。 样本输出 life...
  • 生产环境一个列表页的分页查询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...
  • 入门学习Linux常用必会60个命令实例详解doc/txt

    千次下载 热门讨论 2011-06-09 00:08:45
    因为Linux与Windows不同,其后台运行着许多进程,所以强制关机可能会导致进程的数据丢失,使系统处于不稳定的状态,甚至在有的系统中会损坏硬件设备(硬盘)。在系统关机前使用 shutdown命令,系统管理员会通知所有...
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则正好降低了排序的成本。 ? 索引有什么坏处? 占用存储空间:索引实际上也是一张表,记录了主键与索引...
  • 1.order by limit 选错索引示例 在日常工作中,经常发现一些简单的查询语句因为加了 order by limit 造成优化器选择索引错误。例如如下sql(此处就不造数据了,只是列出一个sql示例) ...这样就可能导致查询...
  • 最近做项目遇到持久层使用MyBatis查询时,出现查询的结果在log4g日志中显示...值得注意的是Mysql中limit和orderBy一起用由于Mysql的优化的原因会导致查询结果有问题详细见:https://blog.csdn.net/qiubabin/article/d...
  • 查询同样的数据,在使用Order by、limit后可能对查询结果 与耗时产生百倍的影响。优化SQL不光是优化那些1秒以上的慢查询,更重要的是那些超高频率的0.1秒的查询SQL。 在这里我模拟创建了一张表 limit_table 并初始化...
  • 但是当数据量足够大的时候,limit条件中的偏移量offset越大就越会导致性能问题,导致查询耗时增加严重。先看一下测试: -- 本地库中只有二百多万条数据,访者可以在本地将数据写入到千万条做测试。效果会更明显 ...
  • ---因为产生的数据量太大了,占用太大资源,会导致查询效率低下 案例实操 hive (default)> select empno, dname from tb_emp, tb_dept; 六 排序 1 全局排序(order by ,全局排序,一个Reduce) 1.1 使用 order by...
  • 我们经常会使用order by 和limit 在做数据查询时排序,限定条数或者是分页排序,平时运用中也没有发现什么异常。但最近的一个项目使用这种方法查询时发现了一个严重的问题,我在用Job导入数据时,因为存在同时导入了...
  • 【数据库学习】数据库总结

    万次阅读 多人点赞 2018-07-26 13:26:41
    1,数据库 1)概念 数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。 常见数据库管理系统有:Access、mysql、sql server 2)特点 ...逻辑数据独立性(logical data...
  • ORDER BY后面谨慎使用时间排序问题展示修正结果原因详查个人建议官方文档 问题展示   在执行获取评论列表、留言列表等诸多操作时,都需要将从数据库中查出的结果按照时间做倒排处理和翻页处理。同一时间点(例如1s...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 53,426
精华内容 21,370
关键字:

limit导致排序