MyException - 我的异常网
当前位置:我的异常网» MySQL » Mysql优化-大数据量下的分页计策

Mysql优化-大数据量下的分页计策

www.MyException.Cn  网友分享于:2013-08-06  浏览:0次
Mysql优化-大数据量下的分页策略

一。前言

通常,我们分页时怎么实现呢?

1
SELECT FROM table ORDER BY id LIMIT 1000, 10;

但是,数据量猛增以后呢?

1
SELECT * FROM table ORDER BY id LIMIT 1000000, 10;

如上第二条查询时很慢的,直接拖死。

最关键的原因mysql查询机制的问题:

不是先跳过,后查询;

而是先查询,后跳过。(解释如下)

什么意思?比如limit 100000,10,在找到需要的那10条时,先会轮询经过前10W条数据,先回行查询出前100000条的字段数据,然后发现没用舍弃掉,直到最后找到需要的10条。

二。分析

limit offset,N, 当offset非常大时,效率极低,
原因是mysql并不是跳过offset行,然后单取N行,
而是取offset+N行,返回放弃前offset行,返回N行【同前边说的先查询,后跳过】.
效率较低,当offset越大时,效率越低

三。3条优化建议

1:从业务上去解决

办法:不允许翻过100页

以百度为例,一般翻页到70页左右.

 

2:不用offset,用条件查询.

例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select id, from lx_com limit 5000000,10;
+---------+--------------------------------------------+
| id      | name                                       |
+---------+--------------------------------------------+
| 5554609 |温泉县人民政府供暖中心          |
..................
| 5554618 |温泉县邮政鸿盛公司                |
+---------+--------------------------------------------+
10 rows in set (5.33 sec)
  
mysql> select id,name from lx_com where id>5000000 limit 10;
+---------+--------------------------------------------------------+
| id      | name                                                   |
+---------+--------------------------------------------------------+
| 5000001 |南宁市嘉氏百货有限责任公司                |
.................
| 5000002 |南宁市友达电线电缆有限公司                |
+---------+--------------------------------------------------------+
10 rows in set (0.00 sec)

现象:从5.3秒到不到100毫秒,查询速度大大加快;但是数据结果却不一样

优点:利用where条件来避免掉先查询后跳过的问题,而是条件缩小范围,从而直接跳过。

存在问题: 有时有会发现用此方法与limitM,N,两次的结果不一致[如上边实例所展示]

原因:数据被物理删除过,有空洞.

解决:数据不进行物理删除(可以逻辑删除).

最终在页面上显示数据时,逻辑删除的条目不显示即可.

(一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1)

3:延迟索引.

非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办?

优化思路:

利用索引覆盖,快速查询出满足条件的主键id;然后凭借主键id作为where条件,达到快速查询。

(速度快在哪里?利用索引覆盖不需要回行就可以快速查询出满足条件的id,时间节约在这里了)

我们现在必须要查,则只查索引,不查数据,得到id.再用id去查具体条目. 这种技巧就是延迟索引.

慢原因:

查询100W条数据的id,name,m每次查询回行抛弃,跨过100W后取到真正要的数据。【就是我们刚刚说的,先查询,后跳过】

优化后快原理:

a.利用索引覆盖先查询出主键id,在索引上就拿到信息了,避免回行

b.找到主键后,根据已知的目标主键在查询,避免跨大数据行去寻找,而是直接定位哪几条数据直接查询。

本方法即延迟索引查询。

1
2
3
4
5
6
7
8
9
mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);
+---------+-----------------------------------------------+
| id      | name                                          |
+---------+-----------------------------------------------+
| 5050425 | 陇县河北乡大谈湾小学                |
........
| 5050434 | 陇县堎底下镇水管站                   |
+---------+-----------------------------------------------+
10 rows in set (1.35 sec)

 

四。总结:

从方案上来说,肯定是方法一优先,从业务上去满足是否要翻那么多页。

如果业务要求,则用id>n limit m的方式来代替limit n,m,但缺点是不能有物理删除

如果非有物理删除有空缺不能用方法二,则用延迟索引法,本质是利用索引覆盖先快速取出索引值,根据锁定的目标的索引值。一次性去回行取值,效果很明显。

文章评论

那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
我是如何打败拖延症的
我是如何打败拖延症的
程序员必看的十大电影
程序员必看的十大电影
编程语言是女人
编程语言是女人
一个程序员的时间管理
一个程序员的时间管理
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
老程序员的下场
老程序员的下场
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
程序员都该阅读的书
程序员都该阅读的书
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
总结2014中国互联网十大段子
总结2014中国互联网十大段子
为什么程序员都是夜猫子
为什么程序员都是夜猫子
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
每天工作4小时的程序员
每天工作4小时的程序员
如何成为一名黑客
如何成为一名黑客
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
漫画:程序员的工作
漫画:程序员的工作
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
旅行,写作,编程
旅行,写作,编程
程序员应该关注的一些事儿
程序员应该关注的一些事儿
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
我的丈夫是个程序员
我的丈夫是个程序员
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
鲜为人知的编程真相
鲜为人知的编程真相
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
10个调试和排错的小建议
10个调试和排错的小建议
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
程序员和编码员之间的区别
程序员和编码员之间的区别
那些争议最大的编程观点
那些争议最大的编程观点
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
Java程序员必看电影
Java程序员必看电影
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
中美印日四国程序员比较
中美印日四国程序员比较
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
代码女神横空出世
代码女神横空出世
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有