精华内容
下载资源
问答
  • Mysql 分页查询limit 不能使用运算符

    千次阅读 2020-05-17 08:40:20
    进行分页查询的时候,如果写成以下sql,语句执行会报错: select * from user where id = 123456 and code = 111 and create_date >= 20190101 and create_date <= 20190202 limit (1 - 1) * 1, 20 因为...

    mysql中limit后面不能使用运算符:

    进行分页查询的时候,如果写成以下sql,语句执行会报错:

    select * from user where id = 123456 and code = 111 
    and create_date >= 20190101 and create_date <= 20190202 
    limit (1 - 1) * 1, 20
    

    因为mysql中limit后面不能带运算符,只能是常量。
    解决方法:
    使用concat,动态sql。

    set @sql = concat('select* from user where id= 123456 andcode= 111 
    and create_date >= 20190101 and create_date <= 20190202 limit', (1-1)*1,',20');
    prepare texts from @sql;
    execute texts;
    
    

    文章转载自:https://blog.csdn.net/changhea/article/details/88670881

    展开全文
  • 1. 问题背景 线上有一个批处理任务,会批量读取昨日的数据,经过一系列加工后,插入到今日的表中... End MySQL使用limit进行分页查询时,可能会出现重复数据,可以通过加上order by子句并保证排序字段的唯一性来解决。

    1. 问题背景

    线上有一个批处理任务,会批量读取昨日的数据,经过一系列加工后,插入到今日的表中。表结构如下:

    CREATE TABLE `detail_yyyyMMdd` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `batch_no` varchar(64) NOT NULL COMMENT '批次号',
      `order_id` varchar(64) NOT NULL COMMENT '订单ID',
      `user_id` varchar(64) NOT NULL COMMENT '用户ID',
      `status` varchar(4) NOT NULL COMMENT '状态',
      `product_id` varchar(32) NOT NULL COMMENT '产品ID',
      PRIMARY KEY (`id`),
      KEY `idx_batchno_userid` (`batch_no`,`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='明细表';
    

    因数据量较大,批量读取昨日数据时,使用了分页查询limit语句,查询sql如下:

    SELECT id,batch_no,order_id,user_id,status,product_id FROM detail_yyyyMMdd WHERE batch_no=‘batch_type_yyyyMMdd’ LIMIT ?,?;
    

    从某一天开始,客服频繁收到客诉,反馈数据未更新。

    2. 问题排查

    初步排查,客诉反馈的数据确实没有插入。线上增加了一些业务日志后,发现分页查询昨日数据时,第二次查询的结果集与第一次查询的结果集有重复数据。

    与DBA沟通,确认了客诉爆发前一天做了一次数据库变更,兄弟团队为了解决一个慢查询问题,增加了一个索引,变更sql如下:

    ALTER TABLE `detail_yyyyMMdd` ADD INDEX `idx_batchno_status_productid` (`batch_no`, `status`, `product_id`);
    

    DBA分析,该变更sql上线前,分页查询会走idx_batchno_userid索引,上线后则走idx_batchno_status_productid,而该索引存在大量重复记录,导致每次分页查询的数据都可能和之前的重复。

    为什么索引有重复记录时,分页查询的数据就可能与之前的重复呢?在网上搜了下,这里贴一篇官方的文档:https://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

    If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

    One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.

    If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic.

    在MySQL 5.6的版本上,优化器在遇到order by limit语句时,做了个查询优化,即使用了priority queue(优先级队列)。采用priority queue能够根据limit N维护一个大小为N的堆,在排序的过程中,只用保留N条记录即可。但堆排序是一个不稳定的排序算法,所以当排序字段值存在重复时,返回的数据顺序可能会不一样,其中一个影响因素就是limit。

    解决方案:查询语句加上order by id(保证排序字段的唯一性),上线后,问题得到解决。

    但仍然存在两个疑点:

    • 原来的索引idx_batchno_userid也会有重复记录,为什么一直没有爆出问题?
    • 线上的查询语句只有limit,没有order by,可以直接按照索引的有序性(索引聚簇表)进行读取并分页,不需要触发priority queue优化。

    3. 问题定位

    仔细观察了两次分页查询的结果集后发现,两次结果集的数据顺序分别对应两个索引,即第一次分页查询走了索引idx_batchno_userid,第二次分页查询走了索引idx_batchno_status_productid,两个索引的数据顺序是不一样的,从而导致两次分页查询的数据存在重复。与DBA交流,MySQL的优化器会基于成本选择最优的索引,而这两个索引的成本相差不大。

    这个结论在线下得到复现,但并不能稳定复现。在总结果集没有变化的情况下,两次分页查询分别走了不同索引的根因,还有待继续深挖。

    End

    MySQL使用limit进行分页查询时,可能会出现重复数据,可以通过加上order by子句并保证排序字段的唯一性来解决。
    在这里插入图片描述

    展开全文
  • 在业务中经常会遇到关于分页的需求,这就会经常会用到MySQL中的limit offset,rows来分段取出每页中需要的数据。但是当数据量足够大的时候,limit条件中的偏移量offset越大就越会导致性能问题,导致查询耗时增加严重...

          在业务中经常会遇到关于分页的需求,这就会经常会用到MySQL中的limit offset,rows来分段取出每页中需要的数据。但是当数据量足够大的时候,limit条件中的偏移量offset越大就越会导致性能问题,导致查询耗时增加严重。先看一下测试:

    -- 本地库中只有二百多万条数据,访者可以在本地将数据写入到千万条做测试。效果会更明显
    select count(*) from notes;

                            

    -- 发送不同级别偏移量的查询
    set profiling=1;
    select * from notes limit 100,3;
    select * from notes limit 10000,3;
    select * from notes limit 1000000,3;
    select * from notes limit 10000000,3;

                          

    -- 查看几条查询的耗时情况
    show profiles

                         

    -- 单独查看偏移量为一百万和一千万的查询
    show profile for query 3;

       

            从人正常的逻辑去考虑limit 偏移量去取数据的话大家可能会考虑到从100开始取,取三条数据,那我就直接跳过前一百条数据,然后取出后面三条数据。但实际上MySQL并不是这么做的,查看上面两图中可以看到当偏移量增大时,Sending data是耗时的主要原因,这是因为MySQL并没有那么智能,并非是跳过偏移量取后面的数据而是先把偏移量+要取得条数,然后再把偏移量这一段的数据抛弃掉再返回。关于优化的解决方案的话大致可以分为几条:

           1> 在业务允许的情况下限制页数:

            可以看看百度和谷歌的做法,百度搜索出来的结果集的话最大的分页数是76页,可以想到76页已经是一个足够大的页数了,哪里会有人在那里一直翻页翻到76页,而谷歌更狠,我所查到的谷歌搜索结果集最大页数是28页,而且谷歌搜索出来的页数和百度不一样的是谷歌并非将页数定死的。

       

          2>  在大的数据量面前使用where id>offset 来代替使用limit offset:

    -- 同样的效果
    select * from notes limit 1000000,3;
    
    select * from notes where id>1000000 limit 3;

            就像上图两条语句同样的效果但是第二条能提升不小的效率,但是这却会导致一个问题就是,如果选择这样去优化limit offset,其前提必须是主键id必须是连续的,可以在表内写入标记为做逻辑删除,不做物理删除。

        3>  假如说如果真的要物理删除,那解决方法的话就只能先取出前offset条数据的ID,再去做偏移取数据。

     

    展开全文
  • 本文档针对mysql分页limit慢的问题,使用联合索引在大数据量的情况下优化limit分页的性能
  • MySQL使用 limit 分页limit m,n 其中,m是偏移量,n是要查询的数量。 当偏移量m过大的时候,查询效率会很低。因为MySQL是先查出m+n个数据,然后抛弃掉前m个数据。 优化方法:可以通过子查询查出查出目标数据...

    MySQL使用 limit 分页:

    limit m,n
    

    其中,m是偏移量,n是要查询的数量。

    当偏移量m过大的时候,查询效率会很低。因为MySQL是先查出m+n个数据,然后抛弃掉前m个数据。

    优化方法:可以通过子查询查出查出目标数据集合的第一个数据所在的行,然后用 >= 操作。

    如下:

    SELECT * FROM articles WHERE id >= (SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10 
    

    子查询在主键id上先查到第一个目标数据的id,然后外层查询根据这个id,使用 >= 方式获取10条数据。

    因为主键id有索引,所以子查询速度很快。


    对于有大数据量的mysql表来说,使用LIMIT分页存在很严重的性能问题。

    优化思想避免数据量大时扫描过多的记录

    在这里插入图片描述

    为了保证index索引列连续,可以为每个表加一个自增字段,并且加上索引。

    展开全文
  • MySql:分页查询limit的正确用法

    千次阅读 2019-10-28 22:44:55
    MySql:分页查询limit的正确用法 -- 原始分页查询 -- 4500 ms -- 慢的原因:1.* 2.limit太大 select * from post_history limit 2000000,10; -- 用明确字段代替* -- 1600ms select id,uid,media_id,...
  • 在业务中经常会遇到关于分页的需求,这就会经常会用到MySQL中的limit offset,rows来分段取出每页中需要的数据。但是当数据量足够大的时候,limit条件中的偏移量offset越大就越会导致性能问题,导致查询耗时增加严重...
  • jdbc分页查询,利用mysqllimit实现分页查询。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。...
  • MySQL-分页查询Limit用法)

    千次阅读 2020-06-27 15:45:19
    如果能进行分页显示,将更加快捷、清新的浏览! 这样,我们也容易查看前几条或者中间某几行数据。 语法(格式):SELECT*FROMtableLIMIT[offset,]rows|rowsOFFSEToffset; 说明:LIMIT 子句可以被用于强制 SELECT ...
  • Mysql的分页的两个参数 select * from user limit 1,2 ... 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
  • mysql分页查询(LIMIT和OFFSET关键字) 大家看图和看案例,图文并茂,比较好理解! /* 注意:mysql数据库中LIMIT分页查询时,索引是从0开始的(即表中第一条数 据的索引是0,第二条数据的索引是1,以此类推) */...
  • mysql分页limit参数

    千次阅读 2020-05-24 12:01:57
    Mysql分页的两个参数 select * from user limit 1,2 1表示从第几条数据开始查(默认索引是0,如果写1,从第二条开始查) 2,表示这页显示几条数据
  • 主要介绍了Mysql Limit 分页查询优化的相关资料,非常不错,介绍的非常详细,具有参考借鉴价值,需要的朋友可以参考下
  • 假设,一个表中有一百条数据,我要查询第5页,每页10条数据,SQL语句怎么写?是用limit么? 不用涉及到Java语言,就SQL语句
  • MySQL使用Limit分页查询的性能优化

    千次阅读 2019-01-07 12:11:04
    当数据表中的记录越来越多的时候,可以通过分页查询的方法,对数据进行筛选,仅返回当前页面所需的数据内容。LimitMySQL中的一个特殊关键字。Limit子句可以对查询结果的记录条数进行限定,控制它输出的行数。 在...
  • } } /** * 返回查询结果,包含分页信息 * @param total * @param params * @param data * @return */ public static Object getPageResult(int total, Map, Object> params, Object data) { if (params.containsKey...
  • 主要介绍了MYSQL分页limit速度太慢的优化方法,需要的朋友可以参考下
  • MySQLLimit 分页查询用法 每次做分页查询容易搞混 Limit 后面两个参数是干啥的,所以记录下! 1、select * from book limit 0,2; 查询结果: 2、select * from book limit 2,2; 运行结果: 从上面两个例子可以...
  • MySQL 中通常我们使用 limit 来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢。 本文主要讨论 limit 分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先...
  • select * from 表名 limit start,count 从start开始,获取count条数据 start 索引从0开始 例子 设定每页显示m条数据,当前显示第n页(n从1开始) n start 1 0~m-1 2 m*1~2*m-1 3 m*2~3*m-1 ...
  • 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。SELECT * FROM table LIMIT [offset,] rows | `rows OFFSET offset ` (LIMIT ...
  • - 在查询数据库的时候,由于要查询很大的数据,上百万的级别,往往让出现内存溢出的问题,所以这时候需要分批去取数据库表中的全部数据来进行处理,最简单的方法就是使用分页查询语句:MySQLLIMIT语句是满足这个...
  • MySQL中使用LIMIT进行分页的方法

    万次阅读 多人点赞 2018-02-02 16:15:45
    客户端通过传递start(页码),pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据,那我们知道MySql数据库提供了分页的函数limit m,n,但是该函数的用法和我们的需求不一样,所以就需要我们根据实际情况去...
  • 分页查询,如何分页,总页数显示
  • MySQL limit 分页查询优化数据表及数据量使用普通limit查询使用优化的limit查询不使用order by desc排序 数据表及数据量 数据表为普通的数据表字段不多两个,数据量为千万级数据量,可以自行百度大量生产sql数据进行...
  • 我想你很难找出一个不使用 OFFSET 和 LIMIT 进行数据库分页的人。对于简单的小型应用程序和数据量不是很大的场景,这种方式还是能够“应付”的。 如果你想从头开始构建一个可靠且高效的系统,在一开始就要把它做好...
  • 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句...
  • 数据库使用的MySQL,有一个日志表,需要进行分页查询,于是很容易就想到了limit [offset偏移量] [count数量]这个查询方式,当我们偏移量比较小时,似乎是没什么问题 SELECT * FROM t_log WHERE type = 1 LIMIT 5, 50 ...
  • 排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成。 排序语法: select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 63,483
精华内容 25,393
关键字:

mysql分页查询limit

mysql 订阅