sql深度分页优化方案

假设我们有一个user表，数量级千万，有id，name，create_time等字段，要根据create_time深度分页

先创建create_time的索引，然后，普通的查询方式是：

select * from user where 1=1 limit 100000,20;

但这种方式查询效率慢， 原因有二：

1. limit语句会先扫描offset+pageSize行，然后再丢弃掉前offset行，返回后pageSize行数据，offset越大，查询越耗时 。
2. create_time是非聚簇索引，需要先查询出主键ID，再回表查询，通过主键ID查询出所有字段。
   关于聚簇索引的概念看聚簇索引和非聚簇索引
   

因此需进一步优化。

一、子查询

我们可以先用子查询查出符合条件的主键，再用主键ID做条件查出所有字段。示例代码如下：

SELECT * FROM user WHERE id IN (
    SELECT id FROM user WHERE create_time>'2022-07-03' LIMIT 100000,10
);

不过，这种查询会报错，说是子查询中不支持使用limit。没关系，我们可以加一层子查询嵌套，就可以解决这个问题。示例代码如下：

SELECT * FROM user WHERE id IN (
  SELECT id FROM (
    SELECT id FROM user WHERE create_time>'2022-07-03' LIMIT 100000,10
  ) AS t
);

为什么先用子查询查出符合条件的主键ID，就能缩短查询时间呢？这是因为子查询用到了覆盖索引，无需回表查询，从而加快了查询效率。

二、inner join关联查询

我们可以把子查询的结果当成一张临时表，然后和原表进行关联查询。示例代码如下：

SELECT * FROM user INNER JOIN (
  SELECT id FROM user WHERE create_time>'2022-07-03' LIMIT 100000,10
) AS t ON user.id=t.id;

三、使用分页游标

具体的实现方式是：当我们查询第二页的时候，把第一页的查询结果放到第二页的查询条件中。例如，我们首先查询第一页：

SELECT * FROM user WHERE create_time>'2022-07-03' LIMIT 10;

然后，查询第二页，把第一页的查询结果的最大id放到第二页查询条件中：

SELECT * FROM user WHERE create_time>'2022-07-03' AND id>10 LIMIT 10;

这样，相当于每次都是查询第一页，也就不存在深分页的问题了。不过，这种查询方式无法跳转到指定页数，只能一页页向下翻。所以，这种查询只适合特定场景，比如新闻APP的首页。