MySQL 的深度分页优化之滚动分页

业余草

共 3045字,需浏览 7分钟

 ·

2022-11-17 15:37

你知道的越多,不知道的就越多,业余的像一棵小草!

你来,我们一起精进!你不来,我和你的竞争对手一起精进!

编辑:业余草

来源:juejin.cn/post/6917073818717126669

推荐:https://www.xttblog.com/?p=5357

自律才能自由

MySQL 的深度分页优化之滚动分页

概述

分页方法有:

  1. 传统方法:偏移位(offset),例如:LIMIT 1, 10
  2. 传统方法上优化
  3. 滚动分页(seek 方法)
MySQL 的深度分页

传统方法

分页最常使用页标形式:pagepageSize

SELECT * FROM test ORDER BY id DESC LIMIT 10000020;

LIMIT 100000, 20 的意思扫描满足条件的 100020 行,扔掉前面的 100000 行,返回最后的 20 行。

所以深度分页一定炸。例如,谷歌和百度搜索均没有深度分页,ElasticSearch 也只默认返回前 1W 条分页。ElasticSearch分页查询的3个坑面试官:分页场景(limit, offset)为什么会慢?面试官:MySQL 跨库分页、分表分页之后,面临的一些新问题

工作中开发:

  1. 先查询总数
  2. 再查询对应页数的信息
/**
 * 查询用户参与活动列表
 *
 * @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);
}

传统方法存在两个问题:

  1. 需要求总数
  2. offset 偏移量问题

传统方法上优化

缩小范围:字段(主键)

深度分页的处理技巧,如下:

  1. 限制获取的字段,深度分页获取主键 Id

即先查询到 Id

  1. 通过主键 Id 定向查询

再查询对应 Id 的数据

-- 传统方式(耗时129.570s)
select * from test LIMIT 2000000010;

-- (耗时5.114s)
SELECT a.* FROM test a, (select id from test LIMIT 2000000010) b where a.id = b.id;

# 说明
# task_result表为生产环境的一个表,总数据量为3400万,id为主键,偏移量达到2000万

此方法的核心在于 Innodb 的聚簇索引,即先查询主键(不会回表),然后再根据 Id列表查询对应的数据。

但分页,仍是要返回总数。

因为: 分页的 count,这个也是导致慢的罪魁祸首。

滚动分页(seek 方法)

滚动分页:

  1. 不要返回数据总数,从业务上解决 count 问题。
  2. seek 方法,避免使用 offset

在深度分页场景下,好的替代方法是 seek 方法。

简单的说,seek 方法就是寻找一个唯一的列或一组列来标识每一行。不使用 offset 子句,我们只需要将该唯一值用作一个书签,表示获取的最后一行的位置,并从 WHERE 子句中的这个位置开始查询下一组。

-- 优化后:
SELECT *
FROM test
WHERE (date,id) > ('2010-07-12T10:29:47-07:00'111866)
ORDER BY dateid
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 dateid
LIMIT 10

「举个实际例子:」

参见飞书

// 老版本中:
// 在线文档中,列表中有这两个字段

has_more: true;
last_label: "1589516276 | 1935976"
  1. has_more

无非后端再查询下,是否有最后一条,最后一条

  1. last_label: 查询到最后一个下标

看看得:时间戳 和 Id

参考资料

  • https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow

浏览 95
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报