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

一。前言

通常，我们分页时怎么实现呢？

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越大时,效率越低

 

三。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