精华内容
下载资源
问答
  • Mysql查询使用limit

    千次阅读 2017-11-28 16:06:22
    Mysql查询使用limit关联查询是日常开发中常见的场景,但两张大表join操作消耗的资源操作通常是很大的。

    Mysql子查询使用limit

    关联查询是日常开发中常见的场景。但两张大表join操作消耗的资源是很大的,而且在部分场景下,可能仅仅只需要这张表的关联字段。因此在很多情况下,为了减小join带来的开销,一般会采用先查出符合要求的关联字段(通常是另一张表的id),再用查出的数据去查询另一张表的数据。
    当然这个方法不一定速度更快,毕竟撇开具体场景谈优化都是扯淡。

    SELECT id,name FROM pic WHERE id IN (
        SELECT pic_id FROM download GROUP BY pic_id ORDER BY create_time DESC LIMIT 10
    )

    报错如下:
    [Err] 1235 - This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
    在子查询语句外层再加一层select就可以了

    SELECT id,name FROM pic WHERE id IN (
        select pic_id from (
            SELECT pic_id FROM download GROUP BY pic_id ORDER BY create_time DESC LIMIT 10
        ) a
    )

    其实常用的关联查询的优化方式还是关联字段加索引 :)


    要改变写博客的习惯才行,把博客当成笔记好了

    展开全文
  • mysql limit分页查询优化写法

    千次阅读 2013-09-05 10:29:42
    mysql中进行分页查询时,一般使用limit查询,而且通常查询中都使用orderby排 序。但是在表数据量比较大的时候,例如查询语句片段limit 10000, 20,数据库读取10020条数据,然后把前10000条丢弃,把最后的20...
    在mysql中进行分页查询时,一般会使用limit查询,而且通常查询中都会使用orderby排 序。但是在表数据量比较大的时候,例如查询语句片段limit 10000, 20,数据库会读取10020条数据,然后把前10000条丢弃,把最后的20条返回给你,这种消耗是可以避免的,也是没必要的。下边介绍几种优化方法:
      优化方法1(让分页操作在索引中进行):
      一般表中经常作为条件查询的列都会建立索引,例如如下查询
      Sql代码
      SELECT msg_id, msg_content FROM message ORDER BY gmt_create desc LIMIT 100, 20;
      可以写成如下方式
      Sql代码
      SELECT msg_id, msg_content FROM message
      INNER JOIN (
      SELECT msg_id FROM message
      ORDER BY gmt_create LIMIT 100, 20
      ) AS page USING(msg_id);
      这样当前查询页的内容就只会在索引中进行,当得到当前页的msg_id再统一通过一个INNER JOIN得到最终要得到的数据详情,避免了对大量数据详情进行操作的消耗。当然JOIN操作也可以通过子查询实现,不过书中介绍5.6之前版本的 mysql相比子查询还是优先使用JOIN。
      优化方法2(显式指定要查询的索引列范围)
      例如方法一中的gmt_create是建立索引的列,而且你也知道要查询的时间范围,这样你就可以通过如下查询语句:
      Sql代码
      SELECT msg_id, msg_content FROM message
      WHERE gmt_create BETWEEN #startTime# AND #endTime#
      ORDER BY gmt_create desc
      这样数据库通过一个范围查询就可以得到想要的数据。
      优化方法3(OFFSET作为查询条件显式指定)
      例子还是如上,我们可以在查询参数中显式指定一个查询时间,叫做lastVisitTime吧。我们查询第一页可以用如下语句:
      Sql代码
      SELECT msg_id, msg_content FROM message
      ORDER BY gmt_create desc
      LIMIT 20
      我们把读出来的数据的最后一条数据的gmt_create字段记录在lastVisitTime字段中,那么后边页的查询就可以用如下语句实现:
      Sql代码
      SELECT msg_id, msg_content FROM message
      WHERE gmt_create < #lastVisitTime#
      ORDER BY gmt_create desc
      LIMIT 20;
      这种查询方式,无论你查询多少页,分页都不会是影响效率的因素。
    展开全文
  • 效率分析关键词:explain + SQL语句 一,最常见MYSQL最基本的分页方式limit: select * from `table` order by id desc ...随着数据量的增加,页数越来越多,在数据慢慢增长的过程中,可能就出现limit 10000,2

    效率分析关键词:explain + SQL语句


    一,最常见MYSQL最基本的分页方式limit:

    select * from `table` order by id desc limit 0, 20
    在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引。随着数据量的增加,页数会越来越多,在数据慢慢增长的过程中,可能就会出现limit 10000,20这样的情况,limit 10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。

    这种方式有几个不足:较大的偏移(OFFSET)会增加结果集,小比例的低效分页足够产生磁盘I/O瓶颈,需要扫描的行多。

    简单的解决方法:不显示记录总数,没用户在乎这个数字;不让用户访问页数比较大的记录,重定向他们;避免count(*) ,不显示总数,让用户通过“下一页”来翻页 ,缓存总数;单独统计总数,在插入和删除时递增/递减

    二,第二种就是分表,计算HASH值。

    Mysql分表准则
    在大量使用mysql时,数据量大、高访问时,为了提高性能需要分表处理,简介下mysql分表的标准,后续会继续补充

    环境:
    业务类型:OLTP
    硬件:
    cpu:8cpu 2.4GHZ
    mem:48G
    磁盘:raid5 6×sas

    什么样的表需要拆分:根据表的体积、表的行数、访问特点来衡量表是否需要拆分

    一.拆分标准是:
      1.表的体积大于2G或行数大于1000w,以单表主键等简单形式访问数据,这个时候需要分表
      2.表的体积大于2G或行数大于500W,以两表jion,小范围查询(结果集小100行)等形式访问数据,这个时候需要分表
      3.表的体积大于2G或行数大于200w,以多表join,范围查询,order by,group by,高频率等复杂形式访问数据,尤其DML,这个时候需要分表
      4.表的字段中含有text等大字段的、varchar(500)以上的、很少使用的字符型字段拆分成父子表,这种分表可以和以上联合使用
      5.数据有时间过期特性的,需要做数据分表归档处理

    只要达到上面任何一个标准,都需要做分表处理

    二.分表方法:
      1.冷热数据分表:适用小访问量,冷数据很少使用
         1.1 单表字段很多,把频繁使用整型字段的和非频繁使用的字符型字段或大字段拆到两个表中
         1.2 表数据具有时间过期性,把过期数据拆分到历史表里或者按时间梯度分表
      2.横向分表:适用大访问量
         2.1 如哈希等分切表或其他基于对某数字取余的切表,优点是方便数据分布,缺点是无法再扩展
         2.2 按主键id递增分表,比如每100w个id一个分表,优点是方便扩展,缺点是压力不均
         2.3 按日期分表,比如每天、每月、每年一个分表,优点是方便扩展,缺点是压力不均

    说明

    1.表的体积如何预估
    CREATE TABLE `td_skate` (
          `valid` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '值id',
          `propertyid` BIGINT(20) NULL DEFAULT NULL COMMENT '属性id',
          `text` VARCHAR(400) NULL DEFAULT NULL,
          `entext` VARCHAR(400) NULL DEFAULT NULL,
          `picurl` VARCHAR(200) NULL DEFAULT NULL COMMENT '属性值说明图片,保存图片相对地址',
          `isother` BIGINT(20) NULL DEFAULT NULL COMMENT '是否是other值, 0  否  1  是',
          `createtime` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
          `createuser` BIGINT(20) NULL DEFAULT NULL COMMENT '创建用户',
          `lastmodify` DATETIME NULL DEFAULT NULL COMMENT '最后修改时间',
          `updatetimeuser` BIGINT(20) NULL DEFAULT NULL COMMENT '最后修改人',
          `deletetime` DATETIME NULL DEFAULT NULL COMMENT '删除时间',
          `deleteuser` BIGINT(20) NULL DEFAULT NULL COMMENT '删除人',
          `description` VARCHAR(4000) NULL DEFAULT NULL COMMENT '产品描述',
          `isdelete` INT(11) NULL DEFAULT '0',
          PRIMARY KEY (`valid`),
          INDEX `fk_td_prodline_attrval_td_prodline_attr` (`propertyid`),
          CONSTRAINT `fk_td_prodline_attrval_td_prodline_attr` FOREIGN KEY (`propertyid`) REFERENCES `td_prodline_attr` (`propertyid`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=2491650;

    把表的所有字段占用字节数相加,再乘以预估行数就是表的体积,比如上面的表,预估有1000W,那他的体积是
    (8+8+400+400+200+8+8+8+8+8+8+8+4000+8)×10000000=50.8G,可以看到这个表设计非常不合理,可以修改如下:
    int替代bigint
    timestamp替代datetime
    状态位isdelete用tinyint替代
    根据业务特点看能否把varchar(4000)放到一个字表中
    优化后表大小:(4+4+400+400+200+4+4+4+4+4+4+4+1)×10000000=10.37G,如果要进一步提升性能,需要删除外键,分表,保证单表在2G以下。
    如果需要查看description信息,通过主键关联查看子表,只会扫描有效的子表信息, 性能将会提升非常大。  


    2.表的行数预估就很简单,根据业务特点,访问量等预估.


    三,第三种是偏移:


    SELECT * FROM `table` WHERE id <= (SELECT id FROM `table` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize
    或者
    select * FROM `table` AS t1 JOIN (SELECT id FROM `table` ORDER BY id desc LIMIT 900,1) AS t2 WHERE t1.id<=t2.id order by t1.id desc limit 5
    原理就是记录住当前页id的最大值和最小值,计算跳转页面和当前页相对偏移,由于页面相近,这个偏移量不会很大,这样的话m值相对较小,大大减少扫 描的行数。其实传统的limit m,n,相对的偏移一直是第一页,这样的话越翻到后面,效率越差,而上面给出的方法就没有这样的问题。
    比如还是SELECT * FROM `table` ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是9527,最小的是9500,如果我们只提供”上一页”、”下一页”这样 的跳转(不提供到第N页的跳转),那么在处理”上一页”的时候SQL语句可以是:
    SELECT * FROM `table` WHERE id > 9527 ORDER BY id  ASC LIMIT 20;
    处理”下一页”的时候SQL语句可以是:
    SELECT * FROM `table` WHERE id < 9500 ORDER BY id  DESC LIMIT 20;
    不管翻多少页,每次查询只扫描20行。
    缺点是只能提供”上一页”、”下一页”的链接形式,但是我一般来说非常喜欢”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式,怎么办呢?
    如果LIMIT m,n不可避免的话,要优化效率,只有尽可能的让m小一下,我们扩展前面做法,还是SELECT * FROM `table` ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是9527,最小的是9500,比如要跳到第8页,我看的SQL语句可以这 样写:
    SELECT * FROM `table` WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;
    跳转到第13页:
    SELECT * FROM `table` WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;
    注意SQL语句里面的ASC和DESC,如果是ASC取出来的结果,显示的时候记得倒置一下。
    整体来说在面对百万级数据的时候如果使用上面第三种方法来优化,系统性能上是能够得到很好的提升,在遇到复杂的查询时也尽量简化,减少运算量。 同时也尽量多的使用内存缓存,有条件的可以考虑分表、分库、阵列之类的大型解决方案了。

    展开全文
  • 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 #{limit},#{offset}来进行分页查询。
    • 当表中记录较多且页数(#{limit})较大时,分页查询效率变慢。
    • 变慢的原因时,分页查询时,会先查询出limit + offset条记录,然后截取后面的offset记录。
    • 本文以实际1.4亿的表big_table为例,记录如何优化分页查询。

    测试说明

    • 测试表:big_table,数据量:1.4亿。

    原始SQL(4500ms)

    -- 原始分页查询,耗时:4500ms
    select * 
    from big_table
    limit 2000000,10;
    

    慢的原因:

    • 1.查询条件为*
    • 2.limit = 2000000太大

    优化一(推荐):用具体字段代替*(1600ms)

    -- 用明确字段代替*,耗时:1600ms
    select id,uid,media_id,media_name,news_id,comment 
    from big_table
    limit 2000000,10;
    

    优化二:先查寻索引(450ms)

    -- 方法1:先对索引进行分页查询,耗时:450ms
    select * from big_table AS h inner join
     (select id from big_table
      limit 2000000,10) AS ss 
     on h.id = ss.id;
    
    -- 方法2:先查询出起始位置的索引,耗时:450ms
    select * from big_table
    where id > (
    	select id from big_table limit 2000000,1
    )
    limit 10;
    

    优化三:between … and(5ms)

    限制:id必须是连续递增的。

    -- 上一页保留最后一条记录所在id,耗时:5ms
    select * from big_table
    where id between 4882489 and 4882489 + 10 ;
    

    优化四(推荐):保留上一页记录所在id(5ms)

    限制:需要保留上一页的最后一条记录所在id

    -- 上一页需要保留最后一条记录所在id,耗时:5ms
    select * from big_table
    where id > 4882488
    limit 10;
    
    展开全文
  • MySQL limit 分页查询优化数据表及数据量使用普通limit查询使用优化的limit查询不使用order by desc排序 数据表及数据量 数据表为普通的数据表字段不多两个,数据量为千万级数据量,可以自行百度大量生产sql数据进行...
  • 查询数据时,可能会查询出很多的记录。而用户需要的记录可能只是很少的一部分。这样就需要来限制查询结果的数量。LIMITMySQL中的一个特殊关键字。LIMIT子句可以对查询结果的记录条数进行限定,控制它输出的行数。 ...
  • mysql的5中查询子句之五limit限制查询

    千次阅读 2017-06-20 10:50:40
    limit 在语句最后,起到限制条目的作用。...N 取出的条目1、取出触发器id最高的前三个项目mysql> select functionid,itemid,triggerid,function from functions order by triggerid desc limit 3;2、取出触发
  • MySQL使用Limit分页查询的性能优化

    千次阅读 2019-01-07 12:11:04
    LimitMySQL中的一个特殊关键字。Limit子句可以对查询结果的记录条数进行限定,控制它输出的行数。 在MySQL数据库中创建用户信息表(tb_user),并添加数据用于测试使用。 -- 判断数据表是否存在,存在则删除 ...
  • MySQL使用LIMIT限制查询结果的数量

    万次阅读 2017-07-11 21:36:41
    MySQL中,提供了LIMIT关键字,可以指定查询结果,从哪一条记录开始,以及一共查询多少条记录SELECT 字段名1,字段名2,…… FROM 表名 LIMIT [OFFSET,] 记录数参数说明LIMIT后面,可以跟两个参数第一个参数,OFFS
  • mySQLmysql 分页查询

    万次阅读 多人点赞 2019-05-05 14:36:37
    MySQLlimit分页公式、总页数公式 1) limit分页公式 (1)limit分页公式:curPage是当前第几页;pageSize是一页多少条记录 limit (curPage-1)*pageSize,pageSize (2)用的地方:sql语句中 select * from student...
  • 假设,一个表中有一百条数据,我要查询第5页,每页10条数据,SQL语句怎么写?是用limit么? 不用涉及到Java语言,就SQL语句
  • MySQL SELECT LIMIT指定行数查询

    千次阅读 2014-02-22 11:01:11
    MySQL SELECT LIMIT指定行数查询   MySQL是我接触得最多的数据库,它以轻量性、开源性以及高效性而著称。曾是SUN公司旗下的产品,随着SUN公司被甲骨文公司所收购,MySQL也转嫁到甲骨文公司旗下了。相比微软公司的...
  • MySQL_数据分页查询limit用法)

    万次阅读 多人点赞 2013-09-17 18:39:35
    select * from table_name limit 0,5 或 select * from table_name limit 5 取第11条到第15条数据,共5条 select * from table_name limit 10,5 limit关键字的用法: LIMIT[offset,]rows offset指定要返回的第...
  • select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行 我们来写分页 物理分页 select * from table LIMIT...
  • mysql查询中不能使用LIMIT

    千次阅读 2019-01-11 05:21:29
    mysql查询中不能使用LIMIT /* 今天遇到个问题,如下: This version of MySQL doesn't yet support 'LIMIT &amp; IN/ALL/ANY/SOME subquery' */ 直接po截图和代码 我们可以通过LIMIT来删除表中的数据 第一...
  • mysql查询语句limit限制行数

    千次阅读 2014-02-06 18:34:18
    mysql查询语句,通过limit来限制查询的行数。 例如: select name from usertb where age > 20 limit 0, 1; //限制从第一条开始,显示1条 select name from usertb where age > 20 limit 1; //同上面的一个效果 ...
  • MySQLLIMIT查询和BETWEEN查询对比

    千次阅读 2017-10-19 13:46:34
    当数据库某表中数据量较大时,查询会变得比较慢。在此情况下可以考虑BETWEEN查询替换LIMIT查询 实测:app_log大约2000000条数据,从第1500000开始取出10条数据 每次执行SQL语句之前进行数据库查询缓存操作reset ...
  • mysqllimit来让一个模糊查询只在前端页面上分页中显示出3列,sql语句怎么写?
  • 使用hibernate实现mysqllimit查询

    千次阅读 2016-05-30 11:25:13
    给大家分享个知识点,hibernate 的hql不支持limit的使用。以后大家如果有限制查询从第几条至第几条时。就用  List investments = new ArrayList();  Session session = this.getSession();  String hql = ...
  • Mysql 分页查询limit 不能使用运算符

    千次阅读 2019-03-19 19:41:52
    进行分页查询的时候,如果写成以下sql,语句执行报错: select * from user where id = 123456 and code = 111 and create_date >= 20190101 and create_date <= 20190202 limit (1 - 1) * 1, 20 因为...
  • mysql order by limit slow 慢查询

    千次阅读 2017-12-13 09:46:45
    最近项目中遇到很奇怪的事情 言简意赅: 背景:数据量大约在300万左右, id为主键,有一个有好几个字段的索引, INDEX_1(f1,f2,f3,f4) , sql 查询条件为 f1=1, w5=xx,w6=zz.....,order by id desc limit 10;...
  • mysql 大数据量时 limit查询优化

    千次阅读 2018-03-13 18:45:39
    一般,我们在做分页时,用的是语句如下:select * ...1000万,则利用以上的查询会非常慢,可以利用以下语句进行优化:Select * From table Where ID&gt;=( Select ID From table order by ID limit 90000,1 )li...
  • Mysql查询limit

    千次阅读 2016-12-08 18:27:07
    执行带有Limit查询是有问题的: SELECT * FROM task_and_device WHERE job_id IN (SELECT id FROM wx_device_task_job WHERE user_id=79269 AND STATUS=1 ORDER BY ...
  • 更优美的limit使用方法(limitmysql 数据查询的性能影响) 本文在zhangyachen的基础上重新排版 来源:zhangyachen https://github.com/zhangyachen/zhangyachen.github.io/issues/117 一,前言 首先说明...
  • http://www.phpfensi.com/mysql/20141008/6772.html本文章来给大家介绍一篇关于mysql中使用order by 和limit查询变慢解决办法,因为在mysql分页利用limit来操作,同时排序也需要使用了order by ,所以经常两者同是...
  • MySQL联合查询,order by与LIMIT

    千次阅读 2015-03-23 09:25:59
    MySQL联合查询,order by与LIMIT
  • 关于mysql查询中 使用 limit

    千次阅读 2017-07-18 16:09:44
    MySQL 8.0中 子查询是不能使用LIMIT的 但是 只要 变通一下 sql语句, 就可以做到 执行
  • MySqllimit查询的解决办法

    千次阅读 2011-02-15 11:30:00
    MySQL4.1中子查询是不能使用LIMIT的,手册中也明确指明 “This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ ” 这样的语句是不能正确执行的。 select * from ...
  • mysql中分页查询(LIMIT和OFFSET关键字) 大家看图和看案例,图文并茂,比较好理解! /* 注意:mysql数据库中LIMIT分页查询时,索引是从0开始的(即表中第一条数 据的索引是0,第二条数据的索引是1,以此类推) */...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 225,172
精华内容 90,068
关键字:

limit会查询所有mysql

mysql 订阅