java百万级数据深度分页如何优化?

一、场景举例
1、查询文章列表，一直滑动翻页，不用跳转到指定页数。
2、从数据库查询百万客户数据写入到redis。
3、访问某小程序的积分商城查看商品，一直滑动翻页，不用跳转到指定页数。

二、问题分析
.设计客户表结构，插入100万数据：

`CREATE TABLE `t_customer` (
`id` bigint NOT NULL COMMENT '主键',
`no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
`create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_date` datetime NULL DEFAULT NULL COMMENT '更新时间',
`create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
`update_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
`delete_flag` tinyint(1) NULL DEFAULT 0 COMMENT '删除标识',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_no`(`no` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '客户表' ROW_FORMAT = Dynamic;`

. 深度分页问题SQL，如下所示：

SELECT
c.id,c.no,c.name
FROM
t_customer c
WHERE
c.delete_flag = 0
order by c.id limit #{offset}, #{pageSize}

. 深度分页造成的结果
当以上的场景翻页很深，offset越来越大，回表的记录越来越多，sql查询性能会急剧下降，会出现大量的慢SQL(超过1s的)

三、解决方案

1、子查询，利用聚集索引减少回表，从而提升查询效率，类似以下的SQL：
SELECT
c.id, c.`name`,c.`no`
FROM
t_customer c
where
c.id >= (select id from t_customer WHERE delete_flag = 0 ORDER BY id limit #{offset},1) limit #{pageSize};

2、内连接(inner join)查询, 我们可以把子查询(利用聚集索引)的结果当成一张临时表，然后和原表进行关联查询，类似以下的SQL：
SELECT
c.id, c.`name`,c.`no`
FROM
t_customer c,
(SELECT id  FROM t_customer  WHERE delete_flag = 0 ORDER BY id LIMIT #{offset}, #{pageSize}) as d
where
c.id = d.id;

3、使用游标分页查询，当我们查询第二页的时候，把第一页的查询结果(返回最后一条记录的Id)放到第二页的查询条件中，类似以下的SQL：
SELECT
c.id,c.no,c.name
FROM
t_customer c
WHERE
c.delete_flag = 0 and id > #{lastId}
order by c.id limit #{pageSize};

常见的例子：
-- 一般的解法(深度分页)
SELECT
   c.id,c.no,c.name
FROM
   t_customer c
WHERE
   c.delete_flag = 0 
order by c.id limit 999998,500;

-- 优化的解法1(inner join优化）
SELECT
	c.id, c.`name`,c.`no` 
FROM
	t_customer c,
  (SELECT id  FROM t_customer  WHERE delete_flag = 0 ORDER BY id LIMIT 999998,500) as d
where 
  c.id = d.id;
	
-- 优化的解法2(子查询优化）	
SELECT
	c.id, c.`name`,c.`no` 
FROM
	t_customer c
where 
  c.id >= (select id from t_customer WHERE delete_flag = 0 ORDER BY id limit 999998,1) limit 500;

-- 优化的解法3(最优解-游标查询)			
SELECT
   c.id,c.no,c.name
FROM
   t_customer c
WHERE
   c.delete_flag = 0 and id > 1629258659047518218
order by c.id limit 500;

四、结果演示

一般的解法(深度分页)

优化的解法1(inner join优化）

优化的解法2(子查询优化） 

 优化的解法3(最优解-游标查询)