MySQL 的深度分頁(yè)優(yōu)化之滾動(dòng)分頁(yè)

MySQL 的深度分頁(yè)優(yōu)化之滾動(dòng)分頁(yè)

概述

分頁(yè)方法有：

1. 傳統(tǒng)方法：偏移位(offset)，例如：LIMIT 1, 10
2. 傳統(tǒng)方法上優(yōu)化
3. 滾動(dòng)分頁(yè)（seek 方法）

傳統(tǒng)方法

分頁(yè)最常使用頁(yè)標(biāo)形式：page，pageSize

SELECT * FROM test ORDER BY id DESC LIMIT 100000, 20;

LIMIT 100000, 20 的意思掃描滿(mǎn)足條件的 100020 行，扔掉前面的 100000 行，返回最后的 20 行。

所以深度分頁(yè)一定炸。例如，谷歌和百度搜索均沒(méi)有深度分頁(yè)，ElasticSearch 也只默認(rèn)返回前 1W 條分頁(yè)。ElasticSearch分頁(yè)查詢(xún)的3個(gè)坑、面試官：分頁(yè)場(chǎng)景（limit, offset）為什么會(huì)慢？、面試官：MySQL 跨庫(kù)分頁(yè)、分表分頁(yè)之后，面臨的一些新問(wèn)題

工作中開(kāi)發(fā)：

1. 先查詢(xún)總數(shù)
2. 再查詢(xún)對(duì)應(yīng)頁(yè)數(shù)的信息

/**
 * 查詢(xún)用戶(hù)參與活動(dòng)列表
 *
 * @param userId 用戶(hù)Id
 * @param page 當(dāng)前頁(yè)
 * @param limit 頁(yè)大小
 * @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);
}

傳統(tǒng)方法存在兩個(gè)問(wèn)題：

1. 需要求總數(shù)
2. offset 偏移量問(wèn)題

傳統(tǒng)方法上優(yōu)化

?

縮小范圍：字段（主鍵）

?

深度分頁(yè)的處理技巧，如下：

1. 限制獲取的字段，深度分頁(yè)獲取主鍵 Id

?

即先查詢(xún)到 Id

?

2. 通過(guò)主鍵 Id 定向查詢(xún)

?

再查詢(xún)對(duì)應(yīng) Id 的數(shù)據(jù)

?

-- 傳統(tǒng)方式（耗時(shí)129.570s）
select * from test LIMIT 20000000, 10;

-- （耗時(shí)5.114s）
SELECT a.* FROM test a, (select id from test LIMIT 20000000, 10) b where a.id = b.id;

# 說(shuō)明
# task_result表為生產(chǎn)環(huán)境的一個(gè)表，總數(shù)據(jù)量為3400萬(wàn)，id為主鍵，偏移量達(dá)到2000萬(wàn)

此方法的核心在于 Innodb 的聚簇索引，即先查詢(xún)主鍵（不會(huì)回表），然后再根據(jù) Id列表查詢(xún)對(duì)應(yīng)的數(shù)據(jù)。

但分頁(yè)，仍是要返回總數(shù)。

?

因?yàn)? 分頁(yè)的 count，這個(gè)也是導(dǎo)致慢的罪魁禍?zhǔn)住?/p>?

滾動(dòng)分頁(yè)（seek 方法）

滾動(dòng)分頁(yè)：

1. 不要返回?cái)?shù)據(jù)總數(shù)，從業(yè)務(wù)上解決 count 問(wèn)題。
2. seek 方法，避免使用 offset

在深度分頁(yè)場(chǎng)景下，好的替代方法是 seek 方法。

簡(jiǎn)單的說(shuō)，seek 方法就是尋找一個(gè)唯一的列或一組列來(lái)標(biāo)識(shí)每一行。不使用 offset 子句，我們只需要將該唯一值用作一個(gè)書(shū)簽，表示獲取的最后一行的位置，并從 WHERE 子句中的這個(gè)位置開(kāi)始查詢(xún)下一組。

-- 優(yōu)化后：
SELECT *
FROM test
WHERE (date,id) > ('2010-07-12T10:29:47-07:00', 111866)
ORDER BY date, id
LIMIT 10

-- 或者，換個(gè)寫(xiě)法：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

「舉個(gè)實(shí)際例子：」

?

參見(jiàn)飛書(shū)

?

// 老版本中：
// 在線文檔中，列表中有這兩個(gè)字段

has_more: true;
last_label: "1589516276 | 1935976"

1. has_more

?

無(wú)非后端再查詢(xún)下，是否有最后一條，最后一條

?

2. last_label: 查詢(xún)到最后一個(gè)下標(biāo)

?

看看得：時(shí)間戳 和 Id

?

參考資料

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