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

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 = b.depno order by a.id desc limit 4800000,25;

执行结果:

[SQL]
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;
受影响的行: 0
时间: 0.001s
[SQL]
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 4800000,25;
受影响的行: 0
时间: 12.275s

3.1 显式告诉mysql过滤掉足够多的行

上面的sql中,并没有写过滤条件,假设我们不仅仅知道过上面的滤条件,还知道要从多少行开始扫描(假设这个点叫扫描开始标志位),那我们就可以显式告诉mysql应该从哪些数据行开始扫描。

3.1.1 使用索引覆盖子查询先获取扫描开始标志位
/*子查询获取偏移100条的位置的id,在这个位置上往后取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
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;

/*子查询获取偏移4800000条的位置的id,在这个位置上往后取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
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;

执行结果,执行效率相比之前有大幅的提升:

[SQL]
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
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影响的行: 0
时间: 0.106s

[SQL]
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
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;
受影响的行: 0
时间: 1.541s

请注意:这是必须要获取到正确的标志位,且子查询必须使用索引覆盖才可以达到这个效果。假设我们的索引不能覆盖子查询,这样写只会让sql更慢。获取正确的标志位的意思是,里面使用的过滤条件和排序方式必须和外面一致。

其原理就是仅仅扫描了索引过滤掉了足够的数据再去连表查询,如果索引不能覆盖过滤条件和排序方式,这样写就不起作用了

3.1.2 记住上次查询的结束位置
/*记住了上次的分页的最后一条数据的id是100,这边就直接跳过100,从101开始扫描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;

/*记住了上次的分页的最后一条数据的id是4800000,这边就直接跳过4800000,从4800001开始扫描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;

执行结果:

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.001s

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影响的行: 0
时间: 0.000s

注意:此种情况因为是要记录上一次查询到了多少条,然后增加了一个过滤条件。所以不适合跳页查询的情况,尤其是倒着跳页的情况。比如先查询了第10000页,然后再去查询第1页,导致没有数据,这种情况虽然也有优化的可能,但是优化起来复杂度较高。

3.2 降级策略

一般情况下来说,如果没有跳页一个正常用户是没有可能翻页到1000页以后的,这种情况大多出现于脚本刷数据等情况,所以可以针对这种恶意情况,可以使用相应的策略限制此种访问或者返回欺骗性数据。

比如:

  • 1.页码和自己前端规定的不符的时候
  • 2.页面超出界定值的时候
  • 3.访问速度过快的时候

3.3 分表

本文讨论大表优化,但是有时候大表的查询速度的确是有上限的,必要的时候还是要进行分表优化,分表不论是使用路由的方式还是其他的方式都可以,本文就不赘述了。

本文sql数据来自于如下文章:mp.weixin.qq.com-s-8X3D06hSYT-1Fwk1r24I_w

  • 0
    点赞
  • 1
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页
评论

打赏作者

vinter-he

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值