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
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 回复:
人家的标题都写明了：百万级的分页，你硬要拉出一个千万级别的。这不是唱对台戏吗？有好的分页方案就发出来呀。
其实我相信，比andy写的技术更好更牛逼的肯定还有。但是我很感谢andy能写出来分享给我们。
deeka我不赞同你用这种语气来指责andy，特别你还用了“二货”这个词，你要有好的技术，可以发出来，我们都会佩服仰慕你。
支持andy！
deeka 于 2012-08-12 14:34:23 回复:
二货这词的意思不是说技术不够牛，而是对技术的认识，只知其一不知其二，而且还自我感觉良好的人，称之为二货。
PS：其实我也是个二货，跟你唯一的区别在于，我是一个谦虚的二货，而你们是一群没有接受意见的二货。
无语森 于 2012-08-13 11:40:11 回复:
真心无语森 andy的是经验之谈 不一定完全正确 但确实是存在的
从你说话看 你人品有问题
deeka是来干什么的？
大家都抱着解决的问题来了, 讨论只是为了互相学习, 提高了. 没必要开骂吧. 每一种技术都不可能是最完美和最优化的方案, 但是只要比我们现在掌握更具有某些优点, 那么他便有价值了. 真的有火气的也麻烦你能够提供你的解决方案来对比下.
我支持deeka，BS误人子弟及死要面子的人。
andy 于 2012-08-14 09:47:43 回复:
SB一个，来这发评论还用马甲
见你本帖发言，已知技术水平和人品，我估计你是个做技术时间不长的装逼小青年，除了喷就是吹，故没有再回复
以后见你发言，直接删除，本帖你已发的，权当给你留个纪念
马甲鉴定：
1.两次发言IP
219.134.213.29 广东省深圳市 电信ADSL
183.37.58.48 广东省深圳市
2.注意你的标点符号，不用打这么完整
又是想当然，同是深圳的IP就是马甲了？另外，完整的标点是一个人的习惯，那我是不是可以说你们几个打的不完整的就是马甲呢？
deeka是我同事，他把链接发我看了，我看不过你们围攻他，所以出口支持一下，有什么不妥？
只有一些恼羞成怒的人，才会做删帖的事情。
从你们的反应看来，你是相当不蛋定，由此看来，技术很一般，人品嘛，不评价了。
deeka,这就是你的不对了，别人有好的解决方案，共享出来给大家，及时有点不合理，你可以做修改或者意见，何必说话这么伤人呢。二逼货、、、
技术跟人品成正比的
最后一个IN最近用上了，以前还以为用IN索引是无效的……
deeka知错了吧，叫你整天把“二货”挂在嘴边
好吧，我退出这场骂战，你们好好跟andy大神学分页吧。走之前，说两句：
1、mysql优化了解相关原理，比如说为什么索引能提高查询速度？一个sql语句最多能使用到多少个索引？
2、别钻牛角钻，分页功能如果不能带条件，那还有什么意义，技术是要解决问题，不是带来问题；
3、放下架子，不耻下问。如果换了我，有人对我的方案有质疑，骂我是二货，我会很高兴的追问对方是不是有更好的方案。也许你们都觉得我根本没有更好的方案，好吧，随便你们怎么猜，我才不会傻逼到跟你们讨论下去，也不差多一句难听的，我凭什么跟你们分享呀！
走了，各位。
超 于 2012-08-16 19:25:58 回复:
早点分享,岂不是万事大吉.
何必凡事惹争端.
走了也看不见,也好,眼不见,心不烦.
知识在分享的时候才最有分量.
只有经过检验过后的才算的上"真理",打引号是因为所有真理都只是相对真理而已!!!
支持Andy ， 最小成本能做到的优化 我觉得是很不错的方法，相比这上面某些人 钻牛角的要好。
支持 支持
@deeka 嘴巴好臭呀, 鸡巴人.
出门在外的, 别给你老爸丢人呀.
话说 " BETWEEN 1000000 AND 1000010" 用在分页上, 是在没有很多查询逻辑出现(WHERE .... AND ... AND ... ), 并且是信息完整度很高的数据(这个表没有删除过信息).
感觉遇见的比较少, 这种情况. 当然, 特殊情况特殊讨论, 想必 andy 这里也是一个方法. ;-) , Anyway, Thanks for sharing.
个人认为使用分表方式解决更好些吧。
一个都不愿意分享的人，有什么资格去指责别人 此人人品大大的有问题 caodahua也类似，不愧是同事，物以类聚 除了指责还会干什么啊
学习了。，其实有些东西可以在应用层实现，这样可以大大减小数据库的压力
比如这一句
SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;
如果已经知道id的值了，那么就没有必要多一条SELECT的子查询了,相信在速度上也会提高很多
有分享精神就是值得稱讚的,公然開罵是不對的,我很喜歡andy的blog,從中學到了不少東西,也認識到了自己的不足.
如果您對事物有不同的見解可以提出來一起討論,公然開罵抨擊他人只會讓大家對你不爽.
其实问题的关键在于，根本是功能不一样的SQL，完成没有可比性。
比较速度的前提是在完成相同事情的前提下。
那假设你把10条数据放一行数据的10个字段中。
主键查询那不是查询速度更快？
有可比性吗？作者连比较速度的最基本条件都不懂还发文误人子弟。