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的几种分页方式  # 你知道几种方式  # 的是  # 下一页  # 上一页  # 分页  # 跳转  # 当前页  # 每页  # 到第  # 这样的话  # 是在  # 降序  # 我看  # 有很多  # 基础上  # 雅虎  # 希望能  # 带来了  # 几位  # 已在  # 不可避免 

相关文章： 建站之星如何开启自定义404页面避免用户流失？  深圳网站制作费用多少钱,读秀，深圳文献港这样的网站很多只提供网上试读，但有些人只要提供试读的文章就能全篇下载，这个是怎么弄的？  如何注册花生壳免费域名并搭建个人网站？  C#如何序列化对象为XML XmlSerializer用法  jQuery 常见小例汇总  公司网站建设制作费用,想建设一个属于自己的企业网站，该如何去做？  如何通过山东自助建站平台快速注册域名？  外汇网站制作流程,如何在工商银行网站上做外汇买卖？  建站之星客服服务时间及联系方式如何？  如何在企业微信快速生成手机电脑官网？  如何用wdcp快速搭建高效网站？  如何在云指建站中生成FTP站点？  网站制作新手教程,新手建设一个网站需要注意些什么？  C++用Dijkstra(迪杰斯特拉)算法求最短路径  如何使用Golang安装API文档生成工具_快速生成接口文档  建站主机解析：虚拟主机配置与服务器选择指南  电视网站制作tvbox接口,云海电视怎样自定义添加电视源？  上海网站制作开发公司,上海买房比较好的网站有哪些？  小视频制作网站有哪些,有什么看国内小视频的网站，求推荐？  宠物网站制作html代码,有没有专门介绍宠物如何养的网站啊？  Python路径拼接规范_跨平台处理说明【指导】  建站之星如何修改网站生成路径？  建站之星如何一键生成手机站？  北京网站制作费用多少,建立一个公司网站的费用.有哪些部分,分别要多少钱？  如何通过老薛主机一键快速建站？  制作网站建设的公司有哪些,网站建设比较好的公司都有哪些？  香港服务器网站生成指南：免费资源整合与高速稳定配置方案  C#如何使用XPathNavigator高效查询XML  建站之星如何防范黑客攻击与数据泄露？  如何做静态网页,sublimetext3.0制作静态网页？  建站之星logo尺寸如何设置最合适？  网站制作大概要多少钱一个,做一个平台网站大概多少钱？  国美网站制作流程,国美电器蒸汽鍋怎么用官方网站？  招商网站制作流程,网站招商广告语？  建站之星在线客服如何快速接入解答？  网页制作模板网站推荐,网页设计海报之类的素材哪里好？  家族网站制作贴纸教程视频,用豆子做粘帖画怎么制作？  如何在阿里云ECS服务器部署织梦CMS网站？  网站制作专业公司有哪些,如何制作一个企业网站，建设网站的基本步骤有哪些？  网站制作说明怎么写,简述网页设计的流程并说明原因？  微信小程序制作网站有哪些,微信小程序需要做网站吗？  网站制作壁纸教程视频,电脑壁纸网站？  如何在橙子建站中快速调整背景颜色？  设计网站制作公司有哪些,制作网页教程？  如何自己制作一个网站链接,如何制作一个企业网站，建设网站的基本步骤有哪些？  关于BootStrap modal 在IOS9中不能弹出的解决方法(IOS 9 bootstrap modal ios 9 noticework)  模具网站制作流程,如何找模具客户？  如何在IIS中新建站点并配置端口与物理路径？  网站制作公司广州有几家,广州尚艺美发学校网站是多少？  C#如何在一个XML文件中查找并替换文本内容