这真的不是八股!经典 MySQL 大数据量查询分页问题

2024年 1月 29日 95.2k 0

查询分页一般要最少要执行两条 SQL 语句:

SELECT COUNT(*) FROM tablename WHERE columnName = 'xx'
SELECT * FROM tablename WHERE columnName = 'xx' limit 0,100

正常情况下没有问题,但是当数据量非常大的时候,首先 count(*) 会非常慢这是肯定的,其次分页越多,limit 的效率就会越低。

比如 limit 200000, 10,这个等同于数据库要扫描出 200010 条数据,然后再丢弃前面的 200000 条数据,返回剩下 10 条数据给用户,这种取法很明显越往后速度越慢,妥妥的慢 SQL。

《高性能 MySQL》中对这个问题有过说明:

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

数据模拟

我们创建两张表(部门表和员工表),并模拟插入 500w 条员工数据:

图片

测试下分页查询员工的 SQL 执行速度,先来看偏移量比较小的情况:

SELECT a.empno,a.empname,a.job,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

再来看下偏移量非常大的情况:

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

可以很明显的看出,偏移量很小的时候,查询速度还是非常快的,当偏移量上到百万量级,这个执行时间已经无法忍受了,一条查询语句跑十几秒这不直接给数据库干阻塞了?

优化方案

使用覆盖索引 + 子查询

偏移量之前的数据是没有价值的,所以我们可以先在聚集索引中根据偏移量找到开始位置的 id 值,再根据这个 id 值去非聚集索引上查询所需要的行数据,这样就避免了大量的无用的回表查询。

总结来说就是:利用子查询获取偏移 n 条的位置 id,基于这个位置再往后取

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

可以看见,执行效率有显著提升

记录上次查找位置

这个应该是比较常见的解决手段了,就是记住上次查找结果的主键位置,从而避免使用偏移量。

比如存储了上次分页的最后一条数据 id 是 4800000,SQL 就可以直接跳过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;

受影响的行: 0
时间: 0.000s

这个效率是最好的,无论怎么分页,耗时基本都是一致的,因为他执行完条件之后,都只扫描了 25 条数据。

但这种方案只适合顺序分页(比如 Feeds 流场景),这样才能记住前一个分页的最后 id。如果用户跳着分页,比如刚刚刷完第 25 页,马上跳到 35 页,使用这种方案的话,数据显示的其实是 26 页的数据,而不是 35 页的。

降级

这种方案属于兜底策略:为 limit 和 offset 设置一个最大值,超过这个最大值,分页查询接口就直接返回空数据或者返回错误码。

从业务角度来说,可以认为超过这个最大值用户已经不是在分页了,而是在刷数据,如果确实是要找某条数据,那么正常理解应该是输入合适的条件来适当缩小范围,而不是一页一页地分页。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论