经过实践,总结以下比较好的limit分页优化方案

1. 模仿百度、谷歌方案(前端业务控制)

类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了

2. 记录每次取出的最大id, 然后where id > 最大id

select * from table_name Where id > 最大id limit 10000, 10;
这种方法适用于:除了主键ID等离散型字段外,也适用连续型字段datetime等
最大id由前端分页pageNum和pageIndex计算出来。

3. IN获取id

select * from table_name where id in (select id from table_name where ( user = xxx )) limit 10000, 10;

4. join方式 + 覆盖索引(推荐)

select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)

如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!

select id from test where pid = 1 limit 100000,10;
创建索引:alter table test add index idx_pid_id(pid, id)

网上也很多关于limit的五条优化准则,都是翻译自mysql手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。原文地址:http://www.zhenhua.org/article.asp?id=200

文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存 mysql 5.0.19)

1、offset比较小的时候。

select * from yanxue8_visit limit 10,10


多次运行,时间保持在0.0004-0.0005之间

Select * From yanxue8_visit Where vid >=(

Select vid From yanxue8_visit Order By vid limit 10,
1

) limit 10


多次运行,时间保持在0.0005-0.0006之间,主要是0.0006
结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。


2、offset大的时候。

select * from yanxue8_visit limit 10000,10


多次运行,时间保持在0.0187左右

Select * From yanxue8_visit Where vid >=(

Select vid From yanxue8_visit Order By vid limit 10000,
1

) limit 10


多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。

以后要注意改正自己的limit语句,优化一下mysql了

转载自:

https://blog.csdn.net/sunct/article/details/90441965

https://blog.csdn.net/weixin_30501857/article/details/95531135?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.edu_weight&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.edu_weight

 posted on 2020-10-10 09:39  xibuhaohao  阅读(217)  评论(0编辑  收藏  举报