MySQL 的深度分頁優(yōu)化之滾動分頁
你知道的越多，不知道的就越多，業(yè)余的像一棵小草！
你來，我們一起精進(jìn)！你不來，我和你的競爭對手一起精進(jìn)！
編輯：業(yè)余草
來源：juejin.cn/post/6917073818717126669
推薦：https://www.xttblog.com/?p=5357
自律才能自由
MySQL 的深度分頁優(yōu)化之滾動分頁
概述
分頁方法有：
傳統(tǒng)方法：偏移位( offset)，例如：LIMIT 1, 10傳統(tǒng)方法上優(yōu)化 滾動分頁（ seek方法）
傳統(tǒng)方法
分頁最常使用頁標(biāo)形式：page，pageSize
SELECT * FROM test ORDER BY id DESC LIMIT 100000, 20;
LIMIT 100000, 20 的意思掃描滿足條件的 100020 行，扔掉前面的 100000 行，返回最后的 20 行。
所以深度分頁一定炸。例如，谷歌和百度搜索均沒有深度分頁，ElasticSearch 也只默認(rèn)返回前 1W 條分頁。ElasticSearch分頁查詢的3個(gè)坑、面試官：分頁場景（limit, offset）為什么會慢？、面試官：MySQL 跨庫分頁、分表分頁之后，面臨的一些新問題
工作中開發(fā)：
先查詢總數(shù) 再查詢對應(yīng)頁數(shù)的信息
/**
* 查詢用戶參與活動列表
*
* @param userId 用戶Id
* @param page 當(dāng)前頁
* @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);
}
傳統(tǒng)方法存在兩個(gè)問題：
需要求總數(shù) offset偏移量問題
傳統(tǒng)方法上優(yōu)化
?縮小范圍：字段（主鍵）
?
深度分頁的處理技巧，如下：
限制獲取的字段，深度分頁獲取主鍵 Id
?即先查詢到
?Id
通過主鍵 Id定向查詢
?再查詢對應(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;
# 說明
# task_result表為生產(chǎn)環(huán)境的一個(gè)表，總數(shù)據(jù)量為3400萬，id為主鍵，偏移量達(dá)到2000萬
此方法的核心在于 Innodb 的聚簇索引，即先查詢主鍵（不會回表），然后再根據(jù) Id列表查詢對應(yīng)的數(shù)據(jù)。
但分頁，仍是要返回總數(shù)。
?因?yàn)? 分頁的
count，這個(gè)也是導(dǎo)致慢的罪魁禍?zhǔn)住?/p>?
滾動分頁（seek 方法）
滾動分頁：
不要返回?cái)?shù)據(jù)總數(shù)，從業(yè)務(wù)上解決 count問題。seek方法，避免使用offset
在深度分頁場景下，好的替代方法是 seek 方法。
簡單的說，seek 方法就是尋找一個(gè)唯一的列或一組列來標(biāo)識每一行。不使用 offset 子句，我們只需要將該唯一值用作一個(gè)書簽，表示獲取的最后一行的位置，并從 WHERE 子句中的這個(gè)位置開始查詢下一組。
-- 優(yōu)化后：
SELECT *
FROM test
WHERE (date,id) > ('2010-07-12T10:29:47-07:00', 111866)
ORDER BY date, id
LIMIT 10
-- 或者，換個(gè)寫法：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í)際例子：」
?參見飛書
?
// 老版本中：
// 在線文檔中，列表中有這兩個(gè)字段
has_more: true;
last_label: "1589516276 | 1935976"
has_more
?無非后端再查詢下，是否有最后一條，最后一條
?
last_label: 查詢到最后一個(gè)下標(biāo)
?看看得：時(shí)間戳 和
?Id
參考資料
https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow