查询分页一般要最少要执行两条 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 设置一个最大值,超过这个最大值,分页查询接口就直接返回空数据或者返回错误码。
从业务角度来说,可以认为超过这个最大值用户已经不是在分页了,而是在刷数据,如果确实是要找某条数据,那么正常理解应该是输入合适的条件来适当缩小范围,而不是一页一页地分页。