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个字段中。
主键查询那不是查询速度更快?
有可比性吗?作者连比较速度的最基本条件都不懂还发文误人子弟。