关闭

转 高效mysql分页

350人阅读 评论(0) 收藏 举报

转自http://www.fuchaoqun.com/2009/04/efficient-pagination-using-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数据总量的表中测试,效果非常明显。

Tags: ,

  1. 23 Responses to “高效的MySQL分页”

  2. By 小宝 on Apr 29, 2009 | Reply

    好方法,在limit值比较大的时候,效果明显的;跟优化limit方法有异曲同工之妙

  3. By 风行L on Apr 30, 2009 | Reply

    have a try

  4. By map on Apr 30, 2009 | Reply

    你这个方法不错。

  5. By fcicq on May 6, 2009 | Reply

    根本原因是 btree 没有 child 数量的记录. 如果有的话, limit 怎么写事实上都没关系.

  6. By solomon on May 27, 2009 | Reply

    这个id不一定就是现在的条目吧,如果我中间删除了一部分数据,这个分页就不准了啊

  7. By 超群.com on May 28, 2009 | Reply

    @ solomon

    除非你在翻页的时候删除的,否则不会。

    一般的应用也不回这么苛刻要求准确。

  8. By rainbow on Jun 20, 2009 | Reply

    我想问一下,id是专为分布建的一个字段吧,还是什么?它的值是怎么赋进去的!

  9. By 超群.com on Jun 21, 2009 | Reply

    @ rainbow

    很多时候我们会为内容建立一个主键,int autoincrement,这个就可以是ID。

    另外,最好是掌握原理,高效的Mysql分页想表达的是如何通过定位范围来减少扫面行数,这个是关键。

  10. By 印第安小狮 on Nov 9, 2009 | Reply

    嗯,这个不错. 刚刚在找分页的具体语句, 没想到得到个意外收获. 呵呵,谢谢啦,我继续处理我的分页.

  11. By 电脑民工 on Apr 11, 2010 | Reply

    如果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;
    —————————————————-
    为什么不
    SELECT * FROM message WHERE id > (max(id1,id2)+(10-8-1)*20) ORDER BY id ASC LIMIT 20;
    还是非要offset 20,20呢….

    其实有页码的,就适合id连续分布的而已.如果是这种情况,知道当前页面号, 目标页面号, 知道asc or desc, SQL是可以计算得到参数并且不用offset m,n的

  12. By memo on Jun 10, 2010 | Reply

    测试一下 貌似效果不错啊~

  13. By loverc on Jul 16, 2010 | Reply

    但是有时候排序 是不是也是一种开销呢?

  14. By 超群.com on Jul 17, 2010 | Reply

    那也比遍历来得快阿

  15. By codeif.com on Nov 15, 2010 | Reply

    这个效果有这么明显嘛? 前面的order by 和 扔掉前面1w条时间效率差别大吗?上面的方法有具体的数据测试吗?

  16. By codeif.com on Nov 15, 2010 | Reply

    文章的最后一句没看到,不好意思,思路很好,谢谢分享

  17. By tony.lee on Nov 24, 2010 | Reply

    explain :
    SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;
    扫描rows不是20行,而是count(*)-9527行吧

  18. By 11 on Dec 10, 2010 | Reply

    如果id不是连续的呢?我如何得到上次结果的最后一个id

  19. By lucky on Dec 14, 2010 | Reply

    如果用户自定义order by字段的情况怎么分页呢?譬如,我要order by某个名称。

  20. By 超群.com on Dec 14, 2010 | Reply

    @lucky

    order by也是一样阿,比如order by age desc, 在条件里面加age < xxx即可

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:375322次
    • 积分:2479
    • 等级:
    • 排名:第14844名
    • 原创:70篇
    • 转载:25篇
    • 译文:0篇
    • 评论:23条
    最新评论