MySQL 深分頁 LIMIT 優化三大實戰方案：從 10 萬頁到毫秒級響應

MySQL 深分頁 LIMIT 優化三大實戰方案：從 10 萬頁到毫秒級響應

LIMIT offset, rows 在大偏移量時效能崩塌：MySQL 需掃描 offset+rows 行再丟棄 offset 行。本文提供三種優化方案，適用不同業務場景。

問題重現

-- 10 萬頁，每頁 20 條 → 掃描 1,000,020 行
SELECT msg_id, msg_content FROM message 
ORDER BY gmt_create DESC LIMIT 1000000, 20;

EXPLAIN 顯示 type=index 或 ALL，rows 巨大，Extra: Using filesort。

方案一：延遲關聯 / 子查詢優化（通用、改動最小）

核心思想：先在索引上完成分頁（只讀主鍵/排序列），再回表查詳情。

-- 寫法 1：INNER JOIN 子查詢（MySQL 5.6+ 推薦）
SELECT m.msg_id, m.msg_content
FROM message m
INNER JOIN (
    SELECT msg_id 
    FROM message 
    ORDER BY gmt_create DESC 
    LIMIT 1000000, 20
) t USING (msg_id);

-- 寫法 2：IN 子查詢（舊版本或偏好）
SELECT msg_id, msg_content 
FROM message 
WHERE msg_id IN (
    SELECT msg_id 
    FROM message 
    ORDER BY gmt_create DESC 
    LIMIT 1000000, 20
);

• 子查詢只掃描索引（type=index，Extra: Using index），極快。
• 外層按主鍵精確回表，僅 20 次點查。
• 前提：排序列有索引（單列或聯合索引首列）。

方案二：顯式範圍查詢（業務知時間範圍）

若知道查詢頁對應的時間窗口，直接用 BETWEEN 走索引範圍掃描。

SELECT msg_id, msg_content 
FROM message 
WHERE gmt_create BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
ORDER BY gmt_create DESC
LIMIT 20;

• 避免大偏移量掃描，直接定位到相關數據頁。
• 適用：報表、歷史數據翻頁、時間維度業務。

方案三：游標式分頁 / Keyset Pagination（無限滾動、API 分頁）

不使用 OFFSET，改用上一頁最後一條記錄的排序值作為下一頁起點。

-- 第 1 頁
SELECT msg_id, msg_content 
FROM message 
ORDER BY gmt_create DESC 
LIMIT 20;

-- 記錄最後一條 gmt_create = '2023-05-01 12:00:00'
-- 第 2 頁
SELECT msg_id, msg_content 
FROM message 
WHERE gmt_create < '2023-05-01 12:00:00'
ORDER BY gmt_create DESC 
LIMIT 20;

-- 第 N 頁：始終 WHERE gmt_create < 上頁最後值

• 優勢：無論翻多深，索引定位皆為 O(log N)，效能恆定。
• 限制：不支援跳頁（無法直接跳到第 100 頁），適合無限滾動、下一頁 API。
• 去重：排序列有重複值時，需加主鍵作二級排序：WHERE (gmt_create, msg_id) < (last_gmt, last_id)（MySQL 5.7+ 支援元組比較）。

方案對比與選型指南

進階優化技巧

1. 覆蓋索引：INDEX idx_gmt_create_msgid (gmt_create DESC, msg_id)，子查詢直接滿足，無需回表。
2. 強制索引：FORCE INDEX (idx_gmt_create) 防止優化器誤選。
3. 避免 SELECT *：只取需要列，減少回表開銷。
4. 緩存熱門頁：首頁、熱門列表用 Redis 緩存 JSON，降級 DB 壓力。

實戰驗證腳本

-- 建立測試表
CREATE TABLE message (
    msg_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    msg_content VARCHAR(500),
    gmt_create DATETIME NOT NULL,
    INDEX idx_gmt_create (gmt_create DESC)
) ENGINE=InnoDB;

-- 插入 200 萬測試數據（略）

-- 對比執行時間
SET profiling=1;
SELECT * FROM message ORDER BY gmt_create DESC LIMIT 1000000, 20;          -- 原寫法
SELECT * FROM message INNER JOIN (SELECT msg_id FROM message ORDER BY gmt_create DESC LIMIT 1000000, 20) t USING(msg_id); -- 優化
SELECT * FROM message WHERE gmt_create < '2023-05-01 12:00:00' ORDER BY gmt_create DESC LIMIT 20; -- 游標
SHOW PROFILES;

總結：深分頁本質是「掃描大量無用行」。延遲關聯利用索引窄掃描+主鍵點查；範圍查詢利用業務維度縮小掃描；游標分頁從根源消除 OFFSET。根據業務形態選型，配合覆蓋索引，毫秒級響應輕鬆達標。