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

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

查询分页一般要最少要执行两条 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 设置一个最大值,超过这个最大值,分页查询接口就直接返回空数据或者返回错误码

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

责任编辑:武晓燕 来源: 飞天小牛肉
相关推荐

2018-09-06 16:46:33

数据库MySQL分页查询

2021-10-26 14:40:03

MySQL SQL 语句数据库

2023-11-29 17:28:07

2021-11-04 14:32:17

Spring 面试作用域

2021-10-21 14:43:23

Java 语言 Java 基础

2018-05-15 08:44:44

TensorFlowKeras内存

2021-07-26 14:59:23

面试Redis内存数据库

2021-09-07 14:46:42

面试网络HTTP 协议

2022-05-06 22:13:56

JVM垃圾收集算法

2011-08-16 09:21:30

MySQL大数据量快速语句优化

2021-01-07 07:46:34

MyBatis 数据量JDBC

2011-04-18 11:13:41

bcp数据导入导出

2018-07-11 20:07:06

数据库MySQL索引优化

2021-10-26 17:05:55

Redis字符串复杂度

2023-11-28 18:09:49

Java多态

2022-09-03 11:36:11

Python文件网络

2015-03-09 10:40:44

MySQL大量数据插入

2021-08-01 22:59:43

Object八股文quals

2009-12-08 15:19:58

WCF大数据量

2010-07-29 13:30:54

Hibari
点赞
收藏

51CTO技术栈公众号