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

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

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

SELECT * FROM order LIMIT 100000, 20;

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

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

二、深分頁為什么慢：MySQL 的掃描機制決定了性能上限

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

例如：

LIMIT 100000, 20;

實際上 MySQL 做了：

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

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

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

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

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

執(zhí)行：

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

執(zhí)行過程如下：

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

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

四、深分頁優(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)勢：

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

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

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

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

示例：

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

效果：

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

適用于：

• 按主鍵（或有序字段）分頁
• 下拉加載、滾動加載
• 長列表查詢

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

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

對于關(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)點：

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

適用于：

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

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

如果用戶想看最后幾頁：

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;

減少掃描量，提升性能。

適用于：

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

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

包括：

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

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

九、面試高頻問題與標準回答

問：MySQL 為什么深分頁會慢？

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

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

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

問：游標分頁的原理是什么？

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

問：什么時候必須放棄 MySQL 分頁？

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

十、總結(jié)

深分頁的性能問題來自 MySQL 掃描機制本身，而不是 SQL 寫得好壞。

真正的解決方案在于：

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

一句話總結(jié)：

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

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

最新評論