数据库单表大量数据的分页优化方案

最新推荐文章于 2024-04-08 10:22:43 发布

vinter-he

最新推荐文章于 2024-04-08 10:22:43 发布

阅读量4.1k

点赞数 1

分类专栏：数据库文章标签：大表分页优化大表分页速度降低大数据量分页优化大数据量limit速度变慢

本文链接：https://blog.csdn.net/winterfeng123/article/details/115006703

版权

当单表数据量巨大，特别是在访问后期页面时，普通的分页查询效率显著降低。问题在于MySQL的limit机制，大偏移量导致扫描大量无用行。解决方案包括：1) 使用索引覆盖子查询获取扫描起点；2) 记住上次查询结束位置，但不适合跳页查询；3) 设定降级策略限制异常访问；4) 若必要，采取分表优化。

摘要由CSDN通过智能技术生成

1 何时需要优化

1.1 单表数据量大，访问最后几页的时候特别慢

比如我们单表的数据有1000w以上，此时当偏移量offset比较大的时候，普通查询会导致查询效率从前几页的几十毫秒，增加到十几秒甚至更高

1.2 非正常访问

其实绝大部分情况下没有多少用户真的翻页到最后几页，大致的情况为前端分页组件有跳转到最后几页的链接，或者是被别人恶意请求数据，比如爬虫爬取数据。

2 问题出现在mysql的limit机制

分页操作通常会使用 limit 加上偏移量的办法实现，同时再加上合适的 order by 子句。但这会出现一个常见问题：当偏移量非常大的时候，它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。

也就是说，在使用limit的时候，当过滤条件不足以过滤掉大部分行的时候，offset过大会导致mysql扫描出大量的数据，再丢弃其中很大一部分，留下想要的部分。

3 解决方案

假设我们的sql如下：

/*偏移量为100，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
/*偏移量为4800000，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno &#