MySQL 的深度分页优化之滚动分页
你知道的越多，不知道的就越多，业余的像一棵小草！
你来，我们一起精进！你不来，我和你的竞争对手一起精进！
编辑：业余草
来源：juejin.cn/post/6917073818717126669
推荐：https://www.xttblog.com/?p=5357
自律才能自由
MySQL 的深度分页优化之滚动分页
概述
分页方法有：
传统方法：偏移位( offset)，例如：LIMIT 1, 10传统方法上优化 滚动分页（ seek方法）
传统方法
分页最常使用页标形式：page，pageSize
SELECT * FROM test ORDER BY id DESC LIMIT 100000, 20;
LIMIT 100000, 20 的意思扫描满足条件的 100020 行，扔掉前面的 100000 行，返回最后的 20 行。
所以深度分页一定炸。例如，谷歌和百度搜索均没有深度分页，ElasticSearch 也只默认返回前 1W 条分页。ElasticSearch分页查询的3个坑、面试官：分页场景（limit, offset）为什么会慢？、面试官：MySQL 跨库分页、分表分页之后，面临的一些新问题
工作中开发：
先查询总数 再查询对应页数的信息
/**
* 查询用户参与活动列表
*
* @param userId 用户Id
* @param page 当前页
* @param limit 页大小
* @return 返回
*/
public Page getActivityList(String userId, Integer page, Integer pageSize) {
Integer total = this.activityMapper.selectUserJoinedActivityCount(userId);
if (Objects.isNull(total) || total <= 0) {
return new Page<>(0, Collections.emptyList());
}
PageInfo pageInfo = new PageInfo(page, pageSize);
List<ActivityDTO> activityList =
activityMapper.selectUserJoinedActivityList(userId, pageInfo);
return new Page<>(activityList.size(), activityList);
}
传统方法存在两个问题：
需要求总数 offset偏移量问题
传统方法上优化
❝缩小范围：字段（主键）
❞
深度分页的处理技巧，如下：
限制获取的字段，深度分页获取主键 Id
❝即先查询到
❞Id
通过主键 Id定向查询
❝再查询对应
❞Id的数据
-- 传统方式（耗时129.570s）
select * from test LIMIT 20000000, 10;
-- （耗时5.114s）
SELECT a.* FROM test a, (select id from test LIMIT 20000000, 10) b where a.id = b.id;
# 说明
# task_result表为生产环境的一个表，总数据量为3400万，id为主键，偏移量达到2000万
此方法的核心在于 Innodb 的聚簇索引，即先查询主键（不会回表），然后再根据 Id列表查询对应的数据。
但分页，仍是要返回总数。
❝因为: 分页的
❞count，这个也是导致慢的罪魁祸首。
滚动分页（seek 方法）
滚动分页：
不要返回数据总数，从业务上解决 count问题。seek方法，避免使用offset
在深度分页场景下，好的替代方法是 seek 方法。
简单的说，seek 方法就是寻找一个唯一的列或一组列来标识每一行。不使用 offset 子句，我们只需要将该唯一值用作一个书签，表示获取的最后一行的位置，并从 WHERE 子句中的这个位置开始查询下一组。
-- 优化后：
SELECT *
FROM test
WHERE (date,id) > ('2010-07-12T10:29:47-07:00', 111866)
ORDER BY date, id
LIMIT 10
-- 或者，换个写法：Id
SELECT *
FROM test
WHERE date >= '2010-07-12T10:29:47-07:00' AND NOT (date='2010-07-12T10:29:47-07:00' AND id < 111866)
ORDER BY date, id
LIMIT 10
「举个实际例子：」
❝参见飞书
❞
// 老版本中：
// 在线文档中，列表中有这两个字段
has_more: true;
last_label: "1589516276 | 1935976"
has_more
❝无非后端再查询下，是否有最后一条，最后一条
❞
last_label: 查询到最后一个下标
❝看看得：时间戳 和
❞Id
参考资料
https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow
评论