精华内容
下载资源
问答
  • MySQL分页优化

    2020-09-10 03:53:59
    主要为大家详细介绍了MySQL分页优化,内容思路很详细,有意对MySQL分页优化的朋友可以参考一下
  • mysql 分页优化

    2017-06-13 00:07:54
    mysql 分页优化
    以下分享一点我的经验 

    一般刚开始学SQL的时候,会这样写 
    复制代码代码如下:

    SELECT * FROM table ORDER BY id LIMIT 1000, 10; 

    但在数据达到百万级的时候,这样写会慢死 
    复制代码代码如下:

    SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 

    也许耗费几十秒 

    网上很多优化的方法是这样的 
    复制代码代码如下:

    SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10; 

    是的,速度提升到0.x秒了,看样子还行了 
    可是,还不是完美的! 

    以下这句才是完美的! 
    复制代码代码如下:

    SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010; 

    比上面那句,还要再快5至10倍 

    另外,如果需要查询 id 不是连续的一段,最佳的方法就是先找出 id ,然后用 in 查询 
    复制代码代码如下:

    SELECT * FROM table WHERE id IN(10000, 100000, 1000000...); 


    再分享一点 
    查询字段一较长字符串的时候,表设计时要为该字段多加一个字段,如,存储网址的字段 
    查询的时候,不要直接查询字符串,效率低下,应该查诡该字串的crc32或md5 

    如何优化Mysql千万级快速分页 

    Limit 1,111 数据大了确实有些性能上的问题,而通过各种方法给用上where id >= XX,这样用上索引的id号可能速度上快点儿。By:jack 
    Mysql limit分页慢的解决办法(Mysql limit 优化,百万至千万条记录实现快速分页) 

    MySql 性能到底能有多高?用了php半年多,真正如此深入的去思考这个问题还是从前天开始。有过痛苦有过绝望,到现在充满信心!MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发。可是数据量到了10万,百万至千 万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了。用事实说话,看例子: 
    数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据,填充10万篇新闻。 
    最后collect 为 10万条记录,数据库表占用硬盘1.6G。OK ,看下面这条sql语句: 

    select id,title from collect limit 1000,10; 很快;基本上0.01秒就OK,再看下面的 
    select id,title from collect limit 90000,10; 从9万条开始分页,结果? 
    8-9秒完成,my god 哪出问题了????其实要优化这条数据,网上找得到答案。看下面一条语句: 
    select id from collect order by id limit 90000,10; 很快,0.04秒就OK。 为什么?因为用了id主键做索引当然快。网上的改法是: 
    select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10; 
    这就是用了id做索引的结果。可是问题复杂那么一点点,就完了。看下面的语句 
    select id from collect where vtype=1 order by id limit 90000,10; 很慢,用了8-9秒! 
    到 了这里我相信很多人会和我一样,有崩溃感觉!vtype 做了索引了啊?怎么会慢呢?vtype做了索引是不错,你直接 select id from collect where vtype=1 limit 1000,10; 是很快的,基本上0.05秒,可是提高90倍,从9万开始,那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。从这里开始有人 提出了分表的思路,这个和dis #cuz 论坛是一样的思路。思路如下: 
    建一个索引表: t (id,title,vtype) 并设置成定长,然后做分页,分页出结果再到 collect 里面去找info 。 是否可行呢?实验下就知道了。 
    10万条记录到 t(id,title,vtype) 里,数据表大小20M左右。用 
    select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢?我猜想是因为collect 数据太多,所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描,只是因为数据量小,只有10万才快。OK, 来个疯狂的实验,加到100万条,测试性能。 
    加了10倍的数据,马上t表就到了200多M,而且是定长。还是刚才的查询语句,时间是0.1-0.2秒完成!分表性能没问题?错!因为我们的limit还是9万,所以快。给个大的,90万开始 
    select id from t where vtype=1 order by id limit 900000,10; 看看结果,时间是1-2秒! 
    why ?? 分表了时间还是这么长,非常之郁闷!有人说定长会提高limit的性能,开始我也以为,因为一条记录的长度是固定的,mysql 应该可以算出90万的位置才对啊? 可是我们高估了mysql 的智能,他不是商务数据库,事实证明定长和非定长对limit影响不大? 怪不得有人说 discuz到了100万条记录就会很慢,我相信这是真的,这个和数据库设计有关! 
    难道MySQL 无法突破100万的限制吗???到了100万的分页就真的到了极限??? 
    答案是: NO !!!! 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法,来个疯狂的测试!一张表搞定100万记录,并且10G 数据库,如何快速分页! 
    好了,我们的测试又回到 collect表,开始测试结论是: 30万数据,用分表法可行,超过30万他的速度会慢道你无法忍受!当然如果用分表+我这种方法,那是绝对完美的。但是用了我这种方法后,不用分表也可以完美解决! 
    答 案就是:复合索引! 有一次设计mysql索引的时候,无意中发现索引名字可以任取,可以选择几个字段进来,这有什么用呢?开始的select id from collect order by id limit 90000,10; 这么快就是因为走了索引,可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试 
    select id from collect where vtype=1 limit 90000,10; 非常快!0.04秒完成! 
    再测试: select id ,title from collect where vtype=1 limit 90000,10; 非常遗憾,8-9秒,没走search索引! 
    再测试:search(id,vtype),还是select id 这个语句,也非常遗憾,0.5秒。 
    综上:如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键! 
    完美解决了分页问题了。可以快速返回id就有希望优化limit , 按这样的逻辑,百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引时非常重要的! 
    好了,回到原题,如何将上面的研究成功快速应用于开发呢?如果用复合查询,我的轻量级框架就没的用了。分页字符串还得自己写,那多麻烦?这里再看一个例子,思路就出来了: 
    select * from collect where id in (9000,12,50,7000); 竟然 0秒就可以查完! 
    mygod ,mysql 的索引竟然对于in语句同样有效!看来网上说in无法用索引是错误的! 
    有了这个结论,就可以很简单的应用于轻量级框架了: 

    代码如下: 
    复制代码代码如下:

    $db=dblink(); 
    $db->pagesize=20; 
    $sql=”select id from collect where vtype=$vtype”; 
    $db->execute($sql); 
    $strpage=$db->strpage(); //将分页字符串保存在临时变量,方便输出 
    while($rs=$db->fetch_array()){ 
    $strid.=$rs['id'].','; 

    $strid=substr($strid,0,strlen($strid)-1); //构造出id字符串 
    $db->pagesize=0; //很关键,在不注销类的情况下,将分页清空,这样只需要用一次数据库连接,不需要再开; 
    $db->execute(“select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)”); 
    <?php while($rs=$db->fetch_array()): ?> 
    <tr> 
    <td> <?php echo $rs['id'];?></td> 
    <td> <?php echo $rs['url'];?></td> 
    <td> <?php echo $rs['sTime'];?></td> 
    <td> <?php echo $rs['gTime'];?></td> 
    <td> <?php echo $rs['vtype'];?></td> 
    <td> <a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td> 
    <td> <?php echo $rs['tag'];?></td> 
    </tr> 
    <?php endwhile; ?> 
    </table> 
    <?php 
    echo $strpage; 

    通过简单的变换,其实思路很简单:1)通过优化索引,找出id,并拼成 “123,90000,12000″ 这样的字符串。2)第2次查询找出结果。 
    小小的索引+一点点的改动就使mysql 可以支持百万甚至千万级的高效分页! 
    通 过这里的例子,我反思了一点:对于大型系统,PHP千万不能用框架,尤其是那种连sql语句都看不到的框架!因为开始对于我的轻量级框架都差点崩溃!只适 合小型应用的快速开发,对于ERP,OA,大型网站,数据层包括逻辑层的东西都不能用框架。如果程序员失去了对sql语句的把控,那项目的风险将会成几何 级数增加!尤其是用mysql 的时候,mysql 一定需要专业的dba 才可以发挥他的最佳性能。一个索引所造成的性能差别可能是上千倍! 
    PS: 经过实际测试,到了100万的数据,160万数据,15G表,190M索引,就算走索引,limit都得0.49秒。所以分页最好别让别人
    展开全文
  • mysql分页优化

    2020-05-12 17:22:36
    mysql分页优化简单分页子查询优化方案jion查询优化方案结论 简单分页 最开始,我们使用的分页查询SQL看起来像是这样的 select * from table order by id desc limit (pageNum -1) * pageSize , pageSize 在数据行...

    简单分页

    最开始,我们使用的分页查询SQL看起来像是这样的

    select * from table order by id desc limit (pageNum -1) * pageSize ,  pageSize
    

    在数据行不是很大的情况下 分页的查询时间还是在可以接受的。但是当我们的数据量越来越多,达到百万级的时候查询到中后一些的数据就比较慢了。于是就开始进行分页的查询优化

    这里我准备了一个数据行2864338的表,表结构(activity的表,数据生成比较麻烦就直接使用了,把ID_字段的类型改成了bigint 避免比较的时候出现类型转换)

    CREATE TABLE `act_hi_detail` (
      `ID_` bigint(22) NOT NULL,
      `TYPE_` varchar(255) COLLATE utf8_bin NOT NULL,
      `PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `EXECUTION_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `ACT_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `NAME_` varchar(255) COLLATE utf8_bin NOT NULL,
      `VAR_TYPE_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
      `REV_` int(11) DEFAULT NULL,
      `TIME_` datetime(3) NOT NULL,
      `BYTEARRAY_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `DOUBLE_` double DEFAULT NULL,
      `LONG_` bigint(20) DEFAULT NULL,
      `TEXT_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
      `TEXT2_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`ID_`),
      KEY `ACT_IDX_HI_DETAIL_PROC_INST` (`PROC_INST_ID_`),
      KEY `ACT_IDX_HI_DETAIL_ACT_INST` (`ACT_INST_ID_`),
      KEY `ACT_IDX_HI_DETAIL_TIME` (`TIME_`),
      KEY `ACT_IDX_HI_DETAIL_NAME` (`NAME_`),
      KEY `ACT_IDX_HI_DETAIL_TASK_ID` (`TASK_ID_`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    

    执行一下count(*)
    在这里插入图片描述

    子查询优化方案

    这时候SQL看起来应该是这样的

    select * from table where id >= (select id from table order by id limit (pageNum -1) * pageSize , 1) order by id limit pageSize
    

    然后我们按照最开始的分页查询一下200W出的数据
    在这里插入图片描述
    可以看见 在数据量200W出的分页查询时间为2.62s。接下来使用子查询方式优化分页
    在这里插入图片描述
    结果真是非常amazing啊,只用了0.49秒。
    这个效率提升还是非常明显的,这里可能有人说了 select * 可能有一定的影响,那在来一次select 一些字段作为比较
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    可以发现,使用子查询优化的分页时间都是比较短的 在接近300W的数量时都是秒级查询

    jion查询优化方案

    此方案来源https://www.cnblogs.com/scotth/p/7995856.html

    select l.* from table as l inner join (select id from table order by id limit (pageNum-1)*pageSize , pageSize)
    on l.id= r.id order by id
    

    在这里插入图片描述
    可以看到 查询速度也很快。

    结论

    在能预估数据量快速上升的表做分页的时候,最好使用优化后的分页查询。文中的两种方案在此次分析中均有不错的表现。

    展开全文
  • MySql分页优化

    2013-05-28 18:31:34
    优化mysql分页查询速度。
  • Mysql分页优化

    2016-11-25 16:58:32
    Mysql分页优化[http://www.xarg.org/2011/10/optimized-pagination-using-mysql/]
    展开全文
  • 主要介绍了MySQL优化案例系列-mysql分页优化,需要的朋友可以参考下
  • MySQL 分页优化

    2018-09-30 09:44:29
    MySQL 可以通过以下方式优化分页效率 更多精彩 更多技术博客,请移步 asing1elife’s blog 问题 MySQL 分页查询多行会导致查询出很多无用数据后再进行筛选,效率低下 例如 limit 1000, 10 会排序出前 1010 行...

    MySQL 可以通过以下方式优化分页效率

    更多精彩

    问题

    1. MySQL 分页查询多行会导致查询出很多无用数据后再进行筛选,效率低下
    2. 例如 limit 1000, 10 会排序出前 1010 行数据,然后取最后 10 行

    解决

    1. 子查询先利用覆盖索引查询到满足条件的主键数据,再利用主键回表查找对应记录
    SELECT
    	co.name,
    	co.point
    FROM
    	pt_course co
    INNER JOIN (
    	SELECT
    		id
    	FROM
    		pt_course 
    	LIMIT 1000, 10
    ) coo
    ON co.id = coo.id;
    
    展开全文
  • Mysql 分页优化

    2016-07-17 14:40:05
    所有一个高效的分页变得尤为重要。 优化原则尽量减少扫描数据库中记录的条数。 常用SQL select * from `表名` order by id desc limit 0,20; 当数据量变大时 select * from `表名` order by id desc limit 100000,20...
  • mysql 分页优化解析

    2021-01-19 22:12:51
    所以,可以试着让mysql也存储分页,当然要程序配合。(这里只是提出一个设想,欢迎大家一起讨论) ASP的分页:在ASP系统中有Recordset对象来实现分页,但是大量数据放在内存中,而且不知道什么时候才失效(请ASP高手...
  • mysql分页优化方法

    2017-05-26 18:25:00
    mysql分页优化方法 今天遇到开发反应分页语句很慢,马上看一下到底是啥的分页语句 原分页语句 SELECT * FROM `tt` LIMIT 4863000, 1000 执行这个语句需要6秒+时间 执行计划为全表扫描 在网上看到...
  • SELECT * FROM test_table WHERE a=? AND b=? AND c=? AND id > 4353045 ORDER BY id asc LIMIT 1000 SELECT * FROM metric_dimension WHERE a = ? AND b=? AND c=? LIMIT 1000 OFFSET 4863;
  • 要用最新的分页优化加速器解决方案: 1.Mysql上亿万级分页慢加速器方法1 SELECT `id` FROM `product` WHERE 1 order by `id` limit 10000,100; SELECT `id`,`url`,`content` FROM `product` ...
  • 这里分享下最近学习到的mysql分页策略的一种方式,之后有新的优化方案会继续更新,欢迎讨论。 环境 系统 内存 MySQL版本 引擎 CentOS release 6.3 8G 5.6.34 innodb 测试表 sql CREATE TABLE `data_test` ...
  • 优化优化后 感谢以下分享自己的心得, ...MySQL数据库优化处理实现千万级快速分页分析,来看下吧。 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vt...
  • 千万级MySQL分页优化

    千次阅读 热门讨论 2018-06-06 12:03:27
    对于只有几万条数据的表这样做当然没问题,也不会在用户体验上有何不妥,但是要是面对成百万上千万的数据表时,这样就不足以满足我们的业务需求了,如何做到对千万级数据表进行高效分页?首先要学会使用 explain 如果你还...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,958
精华内容 17,183
关键字:

mysql分页优化

mysql 订阅