一、前言：深分頁(yè)是數(shù)據(jù)庫(kù)最常見的性能陷阱

大家好，我是程序員卷卷狗。

在實(shí)際項(xiàng)目中，分頁(yè)查詢是最常見的 SQL 場(chǎng)景之一。但隨著業(yè)務(wù)數(shù)據(jù)量不斷增長(zhǎng)，我們經(jīng)常會(huì)遇到類似的請(qǐng)求：

SELECT * FROM order LIMIT 100000, 20;

看似普通的分頁(yè)，卻是 MySQL 性能下降最典型的原因之一。深分頁(yè)會(huì)導(dǎo)致大量無(wú)效掃描，使數(shù)據(jù)庫(kù)壓力急劇上升，嚴(yán)重時(shí)甚至拖垮整個(gè)系統(tǒng)。

理解 MySQL 深分頁(yè)的本質(zhì)，以及掌握高性能替代方案，是后端開發(fā)必須具備的能力。

二、深分頁(yè)為什么慢：MySQL 的掃描機(jī)制決定了性能上限

MySQL 在執(zhí)行 LIMIT offset, size 時(shí)，后臺(tái)需要先掃描 offset+size 行，再丟棄前 offset 行，最后只返回 size 行。

例如：

LIMIT 100000, 20;

實(shí)際上 MySQL 做了：

• 掃描 100020 行
• 丟棄前 100000 行
• 返回最后 20 行

這意味著：OFFSET 越大，MySQL 的掃描成本越高。

深分頁(yè)本質(zhì)上是：大量無(wú)意義的掃描與丟棄操作導(dǎo)致性能變差。

三、傳統(tǒng)的 LIMIT 深分頁(yè)問題實(shí)例

假設(shè) order 表有 500 萬(wàn)行。

執(zhí)行：

SELECT * FROM order ORDER BY id LIMIT 3000000, 20;

執(zhí)行過程如下：

• 掃描 3000020 行
• 丟掉 3000000 行
• 返回 20 行

在 InnoDB 中，行是按主鍵組織的，因此需要大量磁盤隨機(jī)讀，性能極其低下。

四、深分頁(yè)優(yōu)化方案一：利用索引覆蓋 + 子查詢替代 OFFSET

最廣泛使用的方法是：先查主鍵，再回查數(shù)據(jù)

示例：

SELECT * 
FROM order o 
JOIN (
    SELECT id 
    FROM order 
    ORDER BY id 
    LIMIT 100000, 20
) tmp ON o.id = tmp.id;

優(yōu)勢(shì)：

• 子查詢只掃描主鍵索引，成本遠(yuǎn)低于掃描整行
• 回表只發(fā)生 20 次
• 適用于大部分分頁(yè)場(chǎng)景

這是后端分頁(yè)中最通用的優(yōu)化方式。

五、深分頁(yè)優(yōu)化方案二：基于主鍵條件的“游標(biāo)式分頁(yè)”

核心思想：只查詢比上一頁(yè)最后一條記錄大的數(shù)據(jù)

示例：

SELECT * 
FROM order 
WHERE id > #{lastId} 
ORDER BY id 
LIMIT 20;

效果：

• 不存在 OFFSET
• 不需要無(wú)效掃描
• 執(zhí)行速度穩(wěn)定
• 延遲極低

適用于：

• 按主鍵（或有序字段）分頁(yè)
• 下拉加載、滾動(dòng)加載
• 長(zhǎng)列表查詢

這是現(xiàn)代后端系統(tǒng)最推薦的分頁(yè)方式。

六、深分頁(yè)優(yōu)化方案三：使用延遲關(guān)聯(lián)減少掃描

對(duì)于關(guān)聯(lián)查詢，可以先通過索引獲取主鍵，再做回表關(guān)聯(lián)。

示例：

SELECT o.* 
FROM order o 
JOIN (
    SELECT id 
    FROM order 
    WHERE status = 1
    ORDER BY id
    LIMIT 100000, 20
) t ON o.id = t.id;

優(yōu)點(diǎn)：

• 外層只回表 20 行
• 內(nèi)層查詢只掃描索引
• 大幅降低磁盤 I/O

適用于：

• 過濾條件多
• 需要使用復(fù)合索引
• 單表數(shù)據(jù)量大

七、深分頁(yè)優(yōu)化方案四：反向分頁(yè)

如果用戶想看最后幾頁(yè)：

SELECT * FROM order ORDER BY id DESC LIMIT 20 OFFSET 100000;

可以轉(zhuǎn)換為：

SELECT * 
FROM order 
ORDER BY id ASC 
LIMIT total - 100000 - 20, 20;

減少掃描量，提升性能。

適用于：

• 翻頁(yè)至尾部頁(yè)面的場(chǎng)景
• 數(shù)據(jù)傾斜導(dǎo)致深分頁(yè)的場(chǎng)景

八、深分頁(yè)優(yōu)化方案五：通過業(yè)務(wù)改造避免深分頁(yè)

包括：

• 限制最大頁(yè)數(shù)
• 滾動(dòng)分頁(yè)替代頁(yè)碼分頁(yè)
• 使用搜索條件縮減數(shù)據(jù)量
• 使用緩存 + 分段加載
• 使用 ES、ClickHouse 等搜索引擎替代 MySQL 深分頁(yè)查詢

深分頁(yè)本質(zhì)上是業(yè)務(wù)問題，避免深分頁(yè)比優(yōu)化深分頁(yè)更高效。

九、面試高頻問題與標(biāo)準(zhǔn)回答

問：MySQL 為什么深分頁(yè)會(huì)慢？

答：LIMIT offset,size 會(huì)導(dǎo)致 MySQL 實(shí)際掃描 offset+size 行，再丟棄前 offset 行，隨著 offset 增大，會(huì)出現(xiàn)大量無(wú)效掃描，磁盤 I/O 和 CPU 消耗急劇增加。

問：如何優(yōu)化 LIMIT 100000,20？

答：可以通過索引覆蓋查詢、延遲關(guān)聯(lián)、主鍵游標(biāo)分頁(yè)等方式，將 OFFSET 分離為基于主鍵或索引的范圍過濾，從而避免大量無(wú)效掃描。

問：游標(biāo)分頁(yè)的原理是什么？

答：通過記錄上一頁(yè)的最大主鍵，將下一頁(yè)限制為 WHERE id > lastId 的形式，使分頁(yè)不再依賴 OFFSET，提高查詢效率。

問：什么時(shí)候必須放棄 MySQL 分頁(yè)？

答：當(dāng)查詢數(shù)據(jù)量非常巨大且業(yè)務(wù)允許時(shí)，可以將搜索功能遷移到 Elasticsearch 或 ClickHouse，提高深分頁(yè)性能。

十、總結(jié)

深分頁(yè)的性能問題來(lái)自 MySQL 掃描機(jī)制本身，而不是 SQL 寫得好壞。

真正的解決方案在于：

• 用主鍵分頁(yè)替代 OFFSET
• 用索引覆蓋替代全表掃描
• 用延遲關(guān)聯(lián)減少回表次數(shù)
• 用業(yè)務(wù)手段避免深分頁(yè)
• 在極端場(chǎng)景下采用專業(yè)搜索引擎

一句話總結(jié)：

深分頁(yè)的關(guān)鍵不是查詢更多數(shù)據(jù)，而是避免不必要的掃描。

到此這篇關(guān)于MySQL中深分頁(yè)LIMIT 100000的優(yōu)化方案的文章就介紹到這了,更多相關(guān)MySQL深分頁(yè)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論