讲师中心 微信公众号

工具推荐

视频效率加速
AniShort
AniShort

AI短剧协同制作平台

Seko
Seko

AI短剧一键生成成片

Laper
Laper

剧本策划到成片协作

立刻MV
立刻MV

低成本歌曲 MV、视频生成

mysql limit大数据量分页优化方法_MySQL

胖涛酱_9357

胖涛酱_9357

发布时间:2016-06-01 13:32:28

|

1109人浏览过

|

来源于php中文网

原创

bitsCN.com

mysql limit大数据量分页优化方法

 

Mysql的优化是非常重要的。其他最常用也最需要优化的就是limit。Mysql的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降。 

  同样是取10条数据 

 

      select * from yanxue8_visit limit 10000,10 和 

      select * from yanxue8_visit limit 0,10 

 

  就不是一个数量级别的。 

  网上也很多关于limit的五条优化准则,都是翻译自Mysql手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。 

  文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存 Mysql 5.0.19) 

  1、offset比较小的时候。 

 

      select * from yanxue8_visit limit 10,10 

      多次运行,时间保持在0.0004-0.0005之间http://www.zhutiai.com 

      Select * From yanxue8_visit Where vid >=( 

      Select vid From yanxue8_visit Order By vid limit 10,1 

      ) limit 10 

 

  多次运行,时间保持在0.0005-0.0006之间,主要是0.0006 

  结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。 

  2、offset大的时候。 

  

 

     select * from yanxue8_visit limit 10000,10 

      多次运行,时间保持在0.0187左右 

      Select * From yanxue8_visit Where vid >=( 

      Select vid From yanxue8_visit Order By vid limit 10000,1 

      ) limit 

 

10 

  多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。 

  以后要注意改正自己的limit语句,优化一下Mysql了 

 

据表 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秒到了一个数量级。从这里开始有人提出了分表的思路,这个和discuz 

论坛是一样的思路。思路如下: 

建一个索引表: 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)"); 

    fetch_array()): ?> 

   

 

   

 

   

 

   

 

   

 

   

 

   

    target="_blank">

 

   

    $rs['tag'];?>

 

   

 

   

    ?> 

     

   

    echo $strpage; 

    ?> 

 

通过简单的变换,其实思路很简单:1)通过优化索引,找出id,并拼成 "123,90000,12000" 这样的字符串。2)第2次查询找出结果。 

小小的索引+一点点的改动就使mysql 可以支持百万甚至千万级的高效分页! 

通过这里的例子,我反思了一点:对于大型系统,PHP千万不能用框架,尤其是那种连sql语句都看不到的框架!因为开始对于我的轻量级框架都差点崩溃!只适合小型应用的快速开发,对于ERP,OA,大型网站,数据层包括逻辑层的东西都不能用框架。如果程序员失去了对sql语句的把控,那项目的风险将会成几何级数增加!尤其是用mysql 

的时候,mysql 一定需要专业的dba 才可以发挥他的最佳性能。一个索引所造成的性能差别可能是上千倍! 

 

性能优化: 

基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识. 

 

    1. 

    Select * From cyclopedia Where ID>=( 

    Select Max(ID) From ( 

     Select ID From cyclopedia Order By ID limit 90001 

    ) As tmp 

    ) limit 100; 

    2. 

    Select * From cyclopedia Where ID>=( 

    Select Max(ID) From ( 

     Select ID From cyclopedia Order By ID limit 90000,1 

    ) As tmp 

    ) limit 100; 

 

同样是取90000条后100条记录,第1句快还是第2句快? 

第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录 

第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录 

第1句执行结果.100 rows in set (0.23) sec 

第2句执行结果.100 rows in set (0.19) sec 

很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的. 

其实第2句完全可以简化成 

 

    Select * From cyclopedia Where ID>=( 

    Select ID From cyclopedia limit 90000,1 

    )limit 100; 

 

直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足. 

可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁? 

这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的"高分".Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的. 

第1种方案同样可用于MS-SQL,而且可能是最好的.因为靠主键ID来定位起始段总是最快的. 

 

    Select Top 100 * From cyclopedia Where ID>=( 

    Select Top 90001 Max(ID) From ( 

     Select ID From cyclopedia Order By ID 

    ) As tmp 

    ) 

 

但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的limit就有优势的多,执行: 

 

    Select ID From cyclopedia limit 90000 

    Select ID From cyclopedia limit 90000,1 

 

的结果分别是: 

90000 rows in set (0.36) sec 

1 row in set (0.06) sec 

而MS-SQL只能用Select Top 90000 ID From cyclopedia 执行时间是390ms,执行同样的操作时间也不及MySQL的360ms. 

bitsCN.com

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热门AI工具

更多
咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

Flowith
Flowith Hot

Flowith是一款基于画布和智能体工作流的 AI 协作工作空间。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

立刻MV
立刻MV Hot

立刻MV是一款AI文本写作工具,AI 音乐视频(MV)创作工具。

UpDream
UpDream Hot

一款AI视频创作工具,主要用于哔哩哔哩推出的自研AI视频创作工具,适合需要提升相关任务效率的用户。

花生AI
花生AI Hot

一款AI视频创作工具,主要用于B站推出的AI视频创作工具,适合需要提升相关任务效率的用户。

火山引擎

火山引擎是一款面向企业的云计算与AI服务平台。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

0

2026.11.17

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

20

2026.11.17

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

0

2026.11.17

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

0

2026.11.17

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

0

2026.11.17

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

0

2026.11.17

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

180

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

120

2026.11.12

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

120

2026.11.12

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn