MySQL LIMIT 大偏移量優化實戰：從 3.84s 到 0.00s 的性能躍升

痛點現場：百萬數據分頁翻到第 10 萬頁

SELECT * FROM cp_mo100227 WHERE cpid=769 LIMIT 888888, 10;
-- 執行時間：3.84 秒

原因：MySQL 需先掃描 offset + limit 行（888,898 行），再丟棄前 888,888 行，全是無效 IO。

優化原理：延遲關聯 / 覆蓋索引定位主鍵

1. 利用覆蓋索引（或主鍵索引）僅掃描索引葉子節點，定位到目標 offset 的主鍵值。
2. 再用主鍵回表查詢完整列，僅讀取 limit 行數據。

方案對比實測（表 240 萬行，cpid=769 約 90 萬行，id 主鍵、time 有索引）

1. 原始寫法

SELECT id, substr(mobile,1,7), time, cpid, linkid
FROM cp_mo100227 WHERE cpid=769 LIMIT 888888, 10;
-- 3.84 秒

2. 子查詢定位主鍵（推薦）

SELECT id, substr(mobile,1,7), time, cpid, linkid
FROM cp_mo100227
WHERE cpid=769
  AND id >= (SELECT id FROM cp_mo100227 WHERE cpid=769 ORDER BY id LIMIT 888888, 1)
LIMIT 10;
-- 0.00 秒

3. 時間範圍定位（業務允許時）

SELECT id, substr(mobile,1,7), time, cpid, linkid
FROM cp_mo100227
WHERE cpid=769
  AND time >= '2010-02-24 21:07:48'
LIMIT 10;
-- 0.01 秒

關鍵前提條件

• 表有**單調遞增主鍵**（自增 ID 或雪花 ID），或**單調遞增時間列**並建有索引。
• 查詢條件 cpid=769 有索引支援（聯合索引 idx_cpid_id 或 idx_cpid_time 最佳）。
• 應用層改用「上一頁最大 ID/時間」作為游標，而非頁碼跳轉。

系統化優化策略

JOIN 查詢順帶優化提示

MySQL JOIN 採用 Nested Loop Join（嵌套循環）。
原則：用小結果集驅動大結果集，而非單純「小表驅動大表」。
LEFT JOIN 時，從表條件寫 ON 後，主表條件寫 WHERE 後，否則退化為內連接。

總結

LIMIT 大偏移量 = 全表掃描的偽裝。
核心解法：**覆蓋索引定位主鍵 + 游標分頁**。
實測從 3.84s 降至 0.00s，**提升 3800 倍**。
請立即檢查代碼庫中所有 LIMIT offset, size 且 offset > 10000 的查詢，逐個優化。