limit 及翻页优化
limit offset,N, 当offset非常大时, 效率极低,
原因是mysql并不是跳过offset行,然后单取N行,
而是取offset+N行,返回放弃前offset行,返回N行.
效率较低,当offset越大时,效率越低
优化办法:
1: 从业务上去解决
办法: 不允许翻过100页
以百度为例,一般翻页到70页左右.
1:不用offset,用条件查询.
例:
mysql> select id,name 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)
问题: 2次的结果不一致
原因: 数据被物理删除过,有空洞.
解决: 数据不进行物理删除(可以逻辑删除).
最终在页面上显示数据时,逻辑删除的条目不显示即可.
(一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1)
3: 非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办?
分析: 优化思路是 不查,少查,查索引,少取.
我们现在必须要查,则只查索引,不查数据,得到id.
再用id去查具体条目. 这种技巧就是延迟索引.
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)