什么是深分页问题

select * from table where uid = {uid} limit 300 30;300 是 offset，30是要返回多少条数据。

也就是查询第十页的内容。每页 30 条。也就是得到第 300 条到第 330 条的数据

innodb 会做一件很消耗性能的事情：把第 1 条数据到第 330 条数据全部返回到 server 层，然后根据 offset = 300将前面的 300
条数据抛弃。

还没完，如果涉及到回表操作，330 条数据全部回表查询后才会把前 300 条数据抛弃。这就更加损耗性能了。

如果你在尝试在一张巨型表中explain如上语句，数据库甚至会在type那一栏中显示“ALL”，也就是* *全表扫描**。这是因为* *优化器** ，会在执行器执行sql语句前，判断下哪种执行计划的代价更小。但优化器在看到非主键索引的600w次回表之后，直接摇了摇头，说“还是全表一条条记录去判断吧”，于是选择了全表扫描。

所以，出现深分页时，大概率会导致全表扫描。完完全全的性能杀手。

这是https:
//ramzialqrainy.medium.com/faster-pagination-in-mysql-you-are-probably-doing-it-wrong-d9c9202bbfd8中的一些数据，可以看到随着分页的深入（offset递增），耗时呈指数型上升。

如何解决深分页问题

减少回表损耗

延迟 join

1
2
3

select * from table inner join (
   select id from table where uid = '{uid}' limit 300，30; 
) as t2 using (id)

原理在于join的驱动表中只需要返回id，是不需要进行回表的，然后原表中字段的时候只需要查询30行数据（也仅需要回表这30行数据）。

Seek Method

深分页的本质原因是，offset越大，需要扫描的行数越多，然后再丢掉，导致查询性能下降。如果我们可以精确定位到上次查询到哪里，然后直接从那里开始查询，就能省去“回表、丢弃”这两个步骤了。

我们可以每次记录上一次查询的进度，从该进度接着往查询。例如：

select id from table where uid = '{uid}' and id> {last_id} limit 30;

这也是我们平时最常用的分页方法。但是这个方法有几个弊端，需要我们做一定的取舍：

Seek Method 局限一：无法支持跳页。

传统分页设计下，需要支持直接跳到第X页，SeekMethod 无法实现。但是目前大部分软件设计都是瀑布流设计，除了一些后台管理、通知公告等业务外，几乎不会支持分页查询。

Seek Method 局限二：排序场景下有限制

但大部分的业务的SQL并没有本文中的例子这么简单，至少会多一个条件：按照创建时间/更新时间等排序（大部分情况倒序）：

select
  * 
from 
  table
where 
  uid = '{uid}' 
limit 
    X, 30 
order by 
  create_time desc;
注意大数据量下需要先分页再排序。

如果需要改成瀑布流的话，需要先排序再分页，性能不如上一种。这种情况可以给 time
加索引

select 
  * 
from 
  table 
where 
  uid = '{uid}'  and create_time > {last_select_time }
order by 
  create_time desc 
limit 
  30;

这样一眼看去没有什么问题，但是问题是create_time 和
id有一个最大的区别在于ID能保证全局唯一，但是create_time 不能。万一全局范围内create_time
出现重复，那么这个方法是有可能出现丢数据的。如下图所示，当翻页的时候直接用select_time>200的话，可能会丢失数据。