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

相关栏目： 【 网站优化151355 】 【 网络推广146373 】 【 网络技术251813 】 【 AI营销90571 】

相关推荐： HTML透明颜色代码怎么让下拉菜单透明_下拉菜单透明背景指南【技巧】  怎么用AI帮你为初创公司进行市场定位分析？  原生JS获取元素集合的子元素宽度实例  原生JS实现图片轮播切换效果  Laravel如何创建自定义Facades？（详细步骤）  Laravel如何实现用户注册和登录？（Auth脚手架指南）  Laravel API资源(Resource)怎么用_格式化Laravel API响应的最佳实践  Laravel如何发送系统通知_Laravel Notifications实现多渠道消息通知  如何在香港免费服务器上快速搭建网站？  Laravel Blade模板引擎语法_Laravel Blade布局继承用法  如何在云主机快速搭建网站站点？  Laravel怎么处理异常_Laravel自定义异常处理与错误页面教程  Laravel如何发送邮件_Laravel Mailables构建与发送邮件的简明教程  Laravel如何实现本地化和多语言支持_Laravel多语言配置与翻译文件管理  Laravel Blade组件怎么用_Laravel可复用视图组件的创建与使用  弹幕视频网站制作教程下载,弹幕视频网站是什么意思？  Laravel如何使用API Resources格式化JSON响应_Laravel数据资源封装与格式化输出  Laravel怎么在Blade中安全地输出原始HTML内容  Laravel怎么在Controller之外的地方验证数据  使用Dockerfile构建java web环境  如何用5美元大硬盘VPS安全高效搭建个人网站？  Laravel用户密码怎么加密_Laravel Hash门面使用教程  Python正则表达式进阶教程_复杂匹配与分组替换解析  Python企业级消息系统教程_KafkaRabbitMQ高并发应用  详解jQuery中基本的动画方法  油猴 教程,油猴搜脚本为什么会网页无法显示？  如何在搬瓦工VPS快速搭建网站？  Android okhttputils现在进度显示实例代码  香港服务器租用每月最低只需15元？  Python文件流缓冲机制_IO性能解析【教程】  微信小程序 闭包写法详细介绍  装修招标网站设计制作流程,装修招标流程？  Python并发异常传播_错误处理解析【教程】  HTML透明颜色代码在Angular里怎么设置_Angular透明颜色使用指南【详解】  齐河建站公司：营销型网站建设与SEO优化双核驱动策略  如何在香港服务器上快速搭建免备案网站？  Laravel怎么使用Markdown渲染文档_Laravel将Markdown内容转HTML页面展示【实战】  HTML5建模怎么导出为FBX格式_FBX格式兼容性及导出步骤【指南】  个人网站制作流程图片大全,个人网站如何注销？  如何在腾讯云免费申请建站？  html5如何设置样式_HTML5样式设置方法与CSS应用技巧【教程】  Laravel怎么写单元测试_PHPUnit在Laravel项目中的基础测试入门  如何用JavaScript实现文本编辑器_光标和选区怎么处理  Laravel的辅助函数有哪些_Laravel常用Helpers函数提高开发效率  海南网站制作公司有哪些,海口网是哪家的？  Laravel如何处理CORS跨域问题_Laravel项目CORS配置与解决方案  百度浏览器如何管理插件 百度浏览器插件管理方法  高端网站建设与定制开发一站式解决方案 中企动力  佐糖AI抠图怎样调整抠图精度_佐糖AI精度调整与放大细化操作【攻略】  高端企业智能建站程序：SEO优化与响应式模板定制开发