一。前言
通常,我们分页时怎么实现呢?
SELECT * FROM table ORDER BY id LIMIT 1000, 10;
但是,数据量猛增以后呢?
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越大时,效率越低
原因是mysql并不是跳过offset行,然后单取N行,
而是取offset+N行,返回放弃前offset行,返回N行【同前边说的先查询,后跳过】.
效率较低,当offset越大时,效率越低
三。3条优化建议
1:从业务上去解决
办法:
不允许翻过100
页
以百度为例,
一般翻页到70
页左右.
2:不用offset,用条件查询.
例:
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.找到主键后,根据已知的目标主键在查询,避免跨大数据行去寻找,而是直接定位哪几条数据直接查询。
本方法即延迟索引查询。
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,但缺点是不能有物理删除
如果非有物理删除有空缺不能用方法二,则用延迟索引法,本质是利用索引覆盖先快速取出索引值,根据锁定的目标的索引值。一次性去回行取值,效果很明显。
看到的一片不错的文章,原理相同:
MySQL 百万级分页优化(Mysql千万级快速分页)
http://www.jb51.net/article/31868.htm
本文地址:http://blog.csdn.net/ty_hf/article/details/54894641