MySQL 大數據量分頁讀取性能陷阱：LIMIT 偏移量大時的優化替代方案

MySQL 大數據量分頁讀取性能陷阱：LIMIT 偏移量大時的優化替代方案

啟動加載 100 萬行數據構建內存索引，初始方案使用 ORDER BY modify_time LIMIT offset, 10000 分頁讀取，隨著 offset 增大（990000），單次查詢耗時達秒級，總耗時超 5 分鐘。根因：InnoDB 二級索引掃描需回表，大偏移量導致大量無效行讀取。

問題現場

-- 表結構
CREATE TABLE t (
  uid INT, product INT, state INT, modify_time DATETIME,
  PRIMARY KEY (uid, product),
  KEY idx_modify_time (modify_time)
) ENGINE=InnoDB;

-- 分頁查詢
SELECT * FROM t ORDER BY modify_time LIMIT 0, 10000;      -- 快
SELECT * FROM t ORDER BY modify_time LIMIT 990000, 10000; -- 慢（掃描 100 萬行只取 1 萬）

原理深度解析

LIMIT M, N 語義：跳過前 M 行，返回後 N 行。InnoDB 執行流程：

1. 按 idx_modify_time 掃描葉子節點
2. 對每條索引記錄，回表查詢聚簇索引獲取完整行
3. 計數器累加，達到 M 後開始返回結果
4. 返回 N 行後停止

代價 = M + N 次回表。M=990000 時，實際掃描 ~100 萬行索引 + 100 萬次回表，極其低效。

優化方案對比

❌ 方案一：延遲關聯（Delayed Join）

SELECT t.* FROM t
INNER JOIN (
  SELECT id FROM t ORDER BY modify_time LIMIT 990000, 10000
) tmp USING (id);

優勢：子查詢只掃描索引（覆蓋），無回表；外層按主鍵批量查找。
劣勢：仍需掃描 M 行索引，大偏移量下改善有限。

✅ 方案二：游標/ID 基準分頁（推薦）

前提：表有單調遞增主鍵/唯一鍵（如自增 ID、時間戳+ID 組合）。

-- 首頁
SELECT * FROM t WHERE id > 0 ORDER BY id LIMIT 10000;

-- 後續頁（記住上一頁最大 id）
SELECT * FROM t WHERE id > 9987654 ORDER BY id LIMIT 10000;

優勢：type=range 索引範圍掃描，直接定位起始點，**無需掃描前 M 行**，性能恆定 O(N)。

✅ 方案三：增加自增 ID 列專用於分頁（本文採用）

1. ALTER TABLE t ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;（原主鍵改為唯一索引）
2. 查詢改為：SELECT * FROM t WHERE id >= 10000 AND id < 20000（或 id > last_id LIMIT 10000）
3. 每次查詢恆定毫秒級返回，總耗時從 5 分鐘降至 < 10 秒

適用場景對比

避坑指南

• ⚠️ 業務需「跳到第 100 頁」：Keyset 分頁不支持隨機跳轉，需前端改為「加載更多」或提供近似跳轉（預估 ID 範圍）
• ⚠️ 主鍵非單調（UUID、雪花算法無序段）：需增加 created_at + id 組合索引作為游標
• ⚠️ 並發寫入導致 ID 空洞：不影響正確性，僅導致每頁實際行數略有波動
• ⚠️ 數據刪除導致 ID 不連續：同樣不影響，WHERE id > last_id 邏輯自動跳過

總結

大偏移量 LIMIT 是性能殺手。生產環境讀取大量數據構建緩存/同步/導出時，**務必使用基於單調鍵的範圍查詢（ID 游標/Keyset 分頁）**，將 O(M+N) 降為 O(N)，性能提升數量級。