Andy's Blog » » 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 table LIMIT 1000000, 1) LIMIT 10;

是的,速度提升到0.x秒了,看样子还行了
可是,还不是完美的!

以下这句才是完美的!

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

比上面那句,还要再快5至10倍

转载请注明作者及出处:http://www.21andy.com/

另外,如果需要查询 id 不是连续的一段,最佳的方法就是先找出 id ,然后用 in 查询

SELECT * FROM table WHERE id IN(10000, 100000, 1000000...);

再分享一点
查询字段一较长字符串的时候,表设计时要为该字段多加一个字段,如,存储网址的字段
查询的时候,不要直接查询字符串,效率低下,应该查诡该字串的crc32或md5

Incoming search terms:

Tags: MySQL优化, 分页, 海量

本文地址: http://www.21andy.com/new/20120812/2084.html

28 评论 to “PHP开源CMS之MODx”

  1. deeka 于 2012-08-12 01:05:04 发表:

    SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;
    只有二货才会想到用这个方法,你怎么就能保证1000000与1000010之间的总数就是一页呢?不要以为有点小聪明就乱弹琵琶。

    andy 于 2012-08-12 05:18:28 回复:

    首先我想告诉你,我可以保证.
    你是不是想说,你也许什么时候删除了1000002,1000005,1000007这几条记录,这样取出来这页就只有7条了?
    如果只有这一页7条又如何?几百万数据在乎这一页只有7条吗?
    好,要完美
    难道你不知道数据库的字段是随便你建的吗?难道你就只能有一个id字段吗?你不知道建个id2专门用来保证连续吗?
    要么id2要么你2

    deeka 于 2012-08-12 10:14:22 回复:

    百万级的数据体积本来就大了,你还加一个id2,果然不是一般的二呀,简直就是千年老二了。
    不过话说回来,百万级,这只是小学生级的,对行千万级,亿级的,如果你能随便加个字段,还能自己用算法保证是连续的,算你牛!

    deeka 于 2012-08-12 10:15:42 回复:

    对了,听几个朋友提到,你的博客还是有点小知名度的,有些东西最好实验一下再帖出来,不要乱转载,以免误人子弟。

    andy 于 2012-08-12 11:08:25 回复:

    这不是转载,这是我的经验之谈
    既然你不服,我就给你点数据
    我有个运行一年多的站,一个表就700多万数据,大小8G,分页用的就是BETWEEN AND,生网站地图的时候,速度提升近10倍
    是你没有实验而来喷,麻烦喷之前先实验
    你要是有比这更快的,麻烦写出来,别只会喷

    deeka 于 2012-08-12 14:37:16 回复:

    你那数据才700多万,我一直运营的是3000万的数据,大小40多G,你这点简直就是小儿科啦。

    Henry 于 2012-08-13 08:45:43 回复:

    deeka,
    所以你到底有沒有更好的辦法麻?

    ddd 于 2012-08-24 17:21:23 回复:

    人家的标题都写明了:百万级的分页,你硬要拉出一个千万级别的。这不是唱对台戏吗?有好的分页方案就发出来呀。

  2. 王乙乙 于 2012-08-12 13:37:02 发表:

    其实我相信,比andy写的技术更好更牛逼的肯定还有。但是我很感谢andy能写出来分享给我们。
    deeka我不赞同你用这种语气来指责andy,特别你还用了“二货”这个词,你要有好的技术,可以发出来,我们都会佩服仰慕你。
    支持andy!

    deeka 于 2012-08-12 14:34:23 回复:

    二货这词的意思不是说技术不够牛,而是对技术的认识,只知其一不知其二,而且还自我感觉良好的人,称之为二货。
    PS:其实我也是个二货,跟你唯一的区别在于,我是一个谦虚的二货,而你们是一群没有接受意见的二货。

    无语森 于 2012-08-13 11:40:11 回复:

    真心无语森 andy的是经验之谈 不一定完全正确 但确实是存在的
    从你说话看 你人品有问题

  3. thomasdu 于 2012-08-13 09:42:12 发表:

    deeka是来干什么的?

  4. 于 2012-08-13 20:25:49 发表:

    大家都抱着解决的问题来了, 讨论只是为了互相学习, 提高了. 没必要开骂吧. 每一种技术都不可能是最完美和最优化的方案, 但是只要比我们现在掌握更具有某些优点, 那么他便有价值了. 真的有火气的也麻烦你能够提供你的解决方案来对比下.

  5. caodahua 于 2012-08-14 08:35:46 发表:

    我支持deeka,BS误人子弟及死要面子的人。

    andy 于 2012-08-14 09:47:43 回复:

    SB一个,来这发评论还用马甲
    见你本帖发言,已知技术水平和人品,我估计你是个做技术时间不长的装逼小青年,除了喷就是吹,故没有再回复
    以后见你发言,直接删除,本帖你已发的,权当给你留个纪念

    马甲鉴定:
    1.两次发言IP
    219.134.213.29 广东省深圳市 电信ADSL
    183.37.58.48 广东省深圳市
    2.注意你的标点符号,不用打这么完整

  6. caodahua 于 2012-08-14 13:06:05 发表:

    又是想当然,同是深圳的IP就是马甲了?另外,完整的标点是一个人的习惯,那我是不是可以说你们几个打的不完整的就是马甲呢?
    deeka是我同事,他把链接发我看了,我看不过你们围攻他,所以出口支持一下,有什么不妥?
    只有一些恼羞成怒的人,才会做删帖的事情。
    从你们的反应看来,你是相当不蛋定,由此看来,技术很一般,人品嘛,不评价了。

  7. 18K 于 2012-08-14 16:42:54 发表:

    deeka,这就是你的不对了,别人有好的解决方案,共享出来给大家,及时有点不合理,你可以做修改或者意见,何必说话这么伤人呢。二逼货、、、

  8. 小灰灰 于 2012-08-14 17:01:12 发表:

    技术跟人品成正比的

  9. Seven Heart 于 2012-08-14 18:22:48 发表:

    最后一个IN最近用上了,以前还以为用IN索引是无效的……
    deeka知错了吧,叫你整天把“二货”挂在嘴边

  10. deeka 于 2012-08-14 21:25:02 发表:

    好吧,我退出这场骂战,你们好好跟andy大神学分页吧。走之前,说两句:
    1、mysql优化了解相关原理,比如说为什么索引能提高查询速度?一个sql语句最多能使用到多少个索引?
    2、别钻牛角钻,分页功能如果不能带条件,那还有什么意义,技术是要解决问题,不是带来问题;
    3、放下架子,不耻下问。如果换了我,有人对我的方案有质疑,骂我是二货,我会很高兴的追问对方是不是有更好的方案。也许你们都觉得我根本没有更好的方案,好吧,随便你们怎么猜,我才不会傻逼到跟你们讨论下去,也不差多一句难听的,我凭什么跟你们分享呀!
    走了,各位。

    于 2012-08-16 19:25:58 回复:

    早点分享,岂不是万事大吉.
    何必凡事惹争端.
    走了也看不见,也好,眼不见,心不烦.

    知识在分享的时候才最有分量.
    只有经过检验过后的才算的上"真理",打引号是因为所有真理都只是相对真理而已!!!

  11. 企聚网 于 2012-08-17 01:26:35 发表:

    支持Andy , 最小成本能做到的优化 我觉得是很不错的方法,相比这上面某些人 钻牛角的要好。
    支持 支持

  12. 夏天 于 2012-08-17 23:33:59 发表:

    @deeka 嘴巴好臭呀, 鸡巴人.
    出门在外的, 别给你老爸丢人呀.

    话说 " BETWEEN 1000000 AND 1000010" 用在分页上, 是在没有很多查询逻辑出现(WHERE .... AND ... AND ... ), 并且是信息完整度很高的数据(这个表没有删除过信息).

    感觉遇见的比较少, 这种情况. 当然, 特殊情况特殊讨论, 想必 andy 这里也是一个方法. ;-) , Anyway, Thanks for sharing.

  13. Sid 于 2012-09-19 02:16:35 发表:

    个人认为使用分表方式解决更好些吧。

  14. heek 于 2012-09-20 09:58:49 发表:

    一个都不愿意分享的人,有什么资格去指责别人 此人人品大大的有问题 caodahua也类似,不愧是同事,物以类聚 除了指责还会干什么啊

  15. zhangwei93 于 2012-10-03 00:52:42 发表:

    学习了。,其实有些东西可以在应用层实现,这样可以大大减小数据库的压力

    比如这一句
    SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;

    如果已经知道id的值了,那么就没有必要多一条SELECT的子查询了,相信在速度上也会提高很多

  16. seven 于 2012-10-07 15:54:26 发表:

    有分享精神就是值得稱讚的,公然開罵是不對的,我很喜歡andy的blog,從中學到了不少東西,也認識到了自己的不足.
    如果您對事物有不同的見解可以提出來一起討論,公然開罵抨擊他人只會讓大家對你不爽.

  17. kiss 于 2013-05-16 19:18:55 发表:

    其实问题的关键在于,根本是功能不一样的SQL,完成没有可比性。
    比较速度的前提是在完成相同事情的前提下。

    那假设你把10条数据放一行数据的10个字段中。
    主键查询那不是查询速度更快?

    有可比性吗?作者连比较速度的最基本条件都不懂还发文误人子弟。