MySQL分页分析原理及提高效率

MySQL分页分析原理及提高效率

PERCONA PERFORMANCE CONFERENCE 2009上，来自雅虎的几位工程师带来了一篇”Efficient Pagination Using MySQL“的报告，有很多亮点，本文是在原文基础上的进一步延伸。

首先看一下分页的基本原理：

MySQL> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\G
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 10020
Extra:
1 row in set (0.00 sec)

limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。文中还提到limit n性能是没问题的，因为只扫描n行。

文中提到一种”clue”的做法，给翻页提供一些”线索”，比如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，如果我们只提供”上一页”、”下一页”这样的跳转（不提供到第N页的跳转），那么在处理”上一页”的时候SQL语句可以是：

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

处理”下一页”的时候SQL语句可以是：

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

不管翻多少页，每次查询只扫描20行。

缺点是只能提供”上一页”、”下一页”的链接形式，但是我们的产品经理非常喜欢”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式，怎么办呢？

如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面的”clue”做法，还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，比如要跳到第8页，我看的SQL语句可以这样写：

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;

跳转到第13页：

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;

原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。

注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。

已在60W数据总量的表中测试，效果非常明显。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

# MySQL分页分析原理及提高效率  # MySQL  # 高效分页  # MySql分页时使用limit+order by会出现数据重复问题解决  # 为什么MySQL分页用limit会越来越慢  # mysql分页的limit参数简单示例  # 浅谈MySQL分页Limit的性能问题  # MySQL分页Limit的优化过程实战  # mysql分页性能探索  # 浅析Oracle和Mysql分页的区别  # SpringMVC+Mybatis实现的Mysql分页数据查询的示例  # 利用Spring MVC+Mybatis实现Mysql分页数据查询的过程详解  # mysql分页时offset过大的Sql优化经验分享  # MySQL优化案例系列-mysql分页优化  # 你应该知道的PHP+MySQL分页那点事  # MYSQL分页limit速度太慢的优化方法  # MySQL分页优化  # MySQL分页技术、6种分页方法总结  # 8种MySQL分页方法总结  # mysql分页原理和高效率的mysql分页查询语句  # MySQL的几种分页方式  # 你知道几种方式  # 的是  # 下一页  # 上一页  # 分页  # 跳转  # 当前页  # 每页  # 到第  # 这样的话  # 是在  # 降序  # 我看  # 有很多  # 基础上  # 雅虎  # 希望能  # 带来了  # 几位  # 已在  # 不可避免 

相关文章： 已有域名和空间如何搭建网站？  如何用手机制作网站和网页,手机移动端的网站能制作成中英双语的吗？  头像制作网站在线制作软件,dw网页背景图像怎么设置？  名字制作网站免费,所有小说网站的名字？  广州营销型建站服务商推荐：技术优势与SEO优化解析  安云自助建站系统如何快速提升SEO排名？  三星网站视频制作教程下载,三星w23网页如何全屏？  油猴 教程,油猴搜脚本为什么会网页无法显示？  如何快速使用云服务器搭建个人网站？  惠州网站建设制作推广,惠州市华视达文化传媒有限公司怎么样？  家具网站制作软件,家具厂怎么跑业务？  制作网站建设的公司有哪些,网站建设比较好的公司都有哪些？  定制建站方案优化指南：企业官网开发与建站费用解析  为什么Go需要go mod文件_Go go mod文件作用说明  如何配置支付宝与微信支付功能？  Android滚轮选择时间控件使用详解  常州自助建站工具推荐：低成本搭建与模板选择技巧  北京网站制作公司哪家好一点,北京租房网站有哪些？  如何选择长沙网站建站模板？H5响应式与品牌定制哪个更优？  定制建站是什么？如何实现个性化需求？  建站之星安装步骤有哪些常见问题？  家庭建站与云服务器建站，如何选择更优？  javascript中对象的定义、使用以及对象和原型链操作小结  上海制作企业网站有哪些,上海有哪些网站可以让企业免费发布招聘信息？  Thinkphp 中 distinct 的用法解析  香港服务器建站指南：外贸独立站搭建与跨境电商配置流程  建站之星代理如何获取技术支持？  百度网页制作网站有哪些,谁能告诉我百度网站是怎么联系？  MySQL查询结果复制到新表的方法(更新、插入)  如何通过FTP空间快速搭建安全高效网站？  已有域名如何免费搭建网站？  宝塔建站教程：一键部署配置流程与SEO优化实战指南  *服务器网站为何频现安全漏洞？  天津个人网站制作公司,天津网约车驾驶员从业资格证官网？  网站好制作吗知乎,网站开发好学吗？有什么技巧？  c++如何打印函数堆栈信息_c++ backtrace函数与符号名解析【方法】  矢量图网站制作软件,用千图网的一张矢量图做公司app首页，该网站并未说明版权等问题，这样做算不算侵权？应该如何解决？  如何构建满足综合性能需求的优质建站方案？  html制作网站的步骤有哪些,iapp如何添加网页？  php json中文编码为null的解决办法  网站制作服务平台,有什么网站可以发布本地服务信息？  Python如何创建带属性的XML节点  如何用狗爹虚拟主机快速搭建网站？  建站之星ASP如何实现CMS高效搭建与安全管理？  如何在Golang中指定模块版本_使用go.mod控制版本号  齐河建站公司：营销型网站建设与SEO优化双核驱动策略  Python路径拼接规范_跨平台处理说明【指导】  建站之星备案流程有哪些注意事项？  网站制作培训多少钱一个月,网站优化seo培训课程有哪些？  智能起名网站制作软件有哪些,制作logo的软件？