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数据总量的表中测试，效果非常明显。

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

赞 (0)

相关推荐

  • 在MySQL中使用LIMIT进行分页的方法

    今天看一个水友说他的MySQL现在变的很慢.问什么情况时.说单表超过2个G的一个MyISAM.真垃圾的回答方式. 简单答复:换一个强劲的服务器.换服务器很管用的:) ---        最终让取到慢查询: SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; 如: S

  • mysql 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.这

  • php+mysql分页代码详解

    复制代码 代码如下: <?php    $perpagenum = 10;//定义每页显示几条    $total = mysql_fetch_array(mysql_query("select count(*) from a"));//查询数据库中一共有多少条数据    $Total = $total[0];                          //    $Totalpage = ceil($Total/$perpagenum);//上舍,取整    if(!i

  • mysql分页原理和高效率的mysql分页查询语句

    以前我在mysql中分页都是用的 limit 100000,20这样的方式,我相信你也是吧,但是要提高效率,让分页的代码效率更高一些,更快一些,那我们又该怎么做呢? 第一部分:看一下分页的基本原理: 复制代码 代码如下: mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20***************** 1. row **************id: 1select_type: SIMPLEtable: m

  • mysql 分页优化解析

    如果你的数据量有几十万条,用户又搜索一些很通俗的词,然后要依次读最后几页重温旧梦.mysql该很悲壮的不停操作硬盘. 所以,可以试着让mysql也存储分页,当然要程序配合.(这里只是提出一个设想,欢迎大家一起讨论) ASP的分页:在ASP系统中有Recordset对象来实现分页,但是大量数据放在内存中,而且不知道什么时候才失效(请ASP高手指点). SQL数据库分页:用存储过程+游标方式分页,具体实现原理不是很清楚,设想如果用一次查询就得到需要的结果,或者是id集,需要后续页时只要按照结果中的I

  • oracle,mysql,SqlServer三种数据库的分页查询的实例

    MySql: MySQL数据库实现分页比较简单,提供了 LIMIT函数.一般只需要直接写到sql语句后面就行了.LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数.例如:select * from table WHERE - LIMIT 10; #返回前10行select * from table WHERE - LIMIT 0,10; #返回前

  • MySQL 百万级分页优化(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

  • mysql+php分页类（已测）

    复制代码 代码如下: <?php       /*      mysql_pager.class.php 三个参数. mysql_query()的结果, url变量page, 您要的每页记录数      例子在这个文件底部      淡水河边整理测试      */ class mysql_pager {         // define properties         var $page;         var $result;         var $results_per_pa

  • 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 *

  • MySQL中Stmt 预处理提高效率问题的小研究

    MySQL中Stmt 预处理提高效率问题的小研究

    复制代码 代码如下: DELIMITER $$ set @stmt = 'select userid,username from myuser where userid between ? and ?'; prepare s1 from @stmt; set @s1 = 2; set @s2 = 100; execute s1 using @s1,@s2; deallocate prepare s1; $$ DELIMITER ; 用这种形式写的查询,可以随意替换参数,给出代码的人称之为预处理,

  • Python性能分析工具pyinstrument提高代码效率

    Python性能分析工具pyinstrument提高代码效率

    目录 安装 简单的使用 分析 Flask 代码 分析 Django 代码 分析异步代码 工作原理 最后的话 天下武功,唯快不破. 编程也不例外,你的代码跑的快,你能快速找出代码慢的原因,你的码功就高. 安装 pip install pyinstrument 简单的使用 在程序的开始,启动 pyinstrument 的 Profiler,结束时关闭 Profiler 并打印分析结果如下: from pyinstrument import Profiler profiler = Profiler()

  • MySQL的主从复制原理详细分析

    MySQL的主从复制原理详细分析

    目录 前言 一.主从复制概念 二.读写分离的概念 三.主库和从库 1. 主库 2. 从库 四.主从复制的流程 五.主从复制效果展示 前言 在实际生产环境中,如果对mysql数据库的读和写都在一台数据库服务器中操作,无论是在安全性.高可用性,还是高并发等各个方面都是不能满足实际需求的,一般要通过主从复制的方式来同步数据,再通过读写分离来提升数据库的并发负载能力. 一.主从复制概念 主从复制是MySQL提供的基本的技术,主从复制的流程:binlog二进制日志(除了查询其他的更改相关的操作都会记录在b

  • MySQL数据库主从复制原理及作用分析

    目录 1.数据库主从分类: 2.mysql主从介绍由来 3.主从作用 4.主从复制原理 5.主从复制配置(数据一致时) 5.1主从服务器分别安装mysql5.7 5.2主数据库与从数据库数据一致 5.3在主数据库里创建一个同步账号授权给从数据库使用 5.4在从库上测试连接 5.5配置主数据库 5.6配置从数据库 5.7配置并启动主从复制的功能(mysql02从数据库上) 5.8测试: 主库: 从库: 主库创建数据库clq并且加入数据: 从库中查看: 6.主从配置(数据不一致时) 6.1一般全备主

  • mysql累积聚合原理与用法实例分析

    mysql累积聚合原理与用法实例分析

    本文实例讲述了mysql累积聚合原理与用法.分享给大家供大家参考,具体如下: 累积聚合为聚合从序列内第一个元素到当前元素的数据,如为每个员工返回每月开始到现在累积的订单数量和平均订单数量 行号问题有两个解决方案,分别是为使用子查询和使用连接.子查询的方法通常比较直观,可读性强.但是在要求进行聚合时,子查询需要为每个聚合扫描一次数据,而连接方法通常只需要扫描一次就可以得到结果.下面的查询使用连接来得到结果 SELECT a.empid, a.ordermonth,a.qty AS thismont

  • mysql连续聚合原理与用法实例分析

    mysql连续聚合原理与用法实例分析

    本文实例讲述了mysql连续聚合原理与用法.分享给大家供大家参考,具体如下: 连续聚合是按时间顺序对有序数据进行聚合的操作. 在下面的救示例中将使用EmpOrders表,该表用于存放每位员工每月发生的订购数量. 运行如下 代码创建EmpOrders表并填充示例数据. CREATE TABLE EmpOrders ( empid INT NOT NULL, ordermonth DATE NOT NULL, qty INT NOT NULL,test PRIMARY KEY (empid,orde

  • mysql游标的原理与用法实例分析

    本文实例讲述了mysql游标的原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是游标 创建游标 使用游标 首发日期:2018-04-18 什么是游标: 如果你前面看过mysql函数,会发现无法使用返回多行结果的语句.但如果你又确实想要使用时,就需要使用到游标,游标可以帮你选择出某个结果(这样就可以做到返回单个结果). 另外,使用游标也可以轻易的取出在检索出来的行中前进或后退一行或多行的结果. 游标可以遍历返回的多行结果. 补充: Mysql中游标只适用于存储过程以及函数. 创建游标

  • mysql自定义函数原理与用法实例分析

    本文实例讲述了mysql自定义函数原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是函数 函数的创建 函数的调用 函数的查看 函数的修改 函数的删除 首发日期:2018-04-18 什么是函数: 函数存储着一系列sql语句,调用函数就是一次性执行这些语句.所以函数可以降低语句重复.[但注意的是函数注重返回值,不注重执行过程,所以一些语句无法执行.所以函数并不是单纯的sql语句集合.] mysql函数有自己的自定义函数(已经定义好了的函数),想了解更多的可以参考我的另一篇博文:mys

随机推荐