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

飞天小牛肉

发布于 2024-02-01 16:59:42

3660

发布于 2024-02-01 16:59:42

查询分页一般要最少要执行两条 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 页的。