MySQL 單表百萬數據分頁性能優化實戰：從 37 秒到 0.2 秒

測試環境基線

現狀痛點：原生 LIMIT 深分頁性能崩塌

-- 每頁 20 條，觀察不同上限耗時
SELECT * FROM product LIMIT 10, 20;      -- 0.016s
SELECT * FROM product LIMIT 100, 20;     -- 0.016s
SELECT * FROM product LIMIT 1000, 20;    -- 0.047s
SELECT * FROM product LIMIT 10000, 20;   -- 0.094s
SELECT * FROM product LIMIT 400000, 20;  -- 3.229s
SELECT * FROM product LIMIT 866613, 20;  -- 37.44s  ← 最後一頁不可接受

結論： MySQL LIMIT offset, count 需掃描並丟棄前 offset 行，耗時與 offset 成正比。

優化方案一：覆蓋索引 + 子查詢（延遲關聯）

-- 1. 僅掃描主鍵索引獲取目標 ID（覆蓋索引，極快）
SELECT id FROM product LIMIT 866613, 20;  -- 0.2s

-- 2. 回表查詢完整列：利用 ID 範圍或 JOIN
-- 寫法 A：子查詢 + ID >=
SELECT * FROM product 
WHERE id >= (SELECT id FROM product LIMIT 866613, 1) 
LIMIT 20;  -- 0.2s

-- 寫法 B：INNER JOIN（推薦，優化器更穩定）
SELECT p.* 
FROM product p
INNER JOIN (SELECT id FROM product LIMIT 866613, 20) tmp USING (id);  -- 0.2s

原理解析

1. 覆蓋索引掃描：SELECT id ... 僅讀取主鍵索引葉子節點，無需回表，I/O 極小。
2. 延遲關聯：先在索引上定位目標主鍵集合，再憑主鍵精準回表，將「掃描 86 萬行 → 丟棄 86 萬行」轉為「掃描索引 86 萬行 → 回表 20 行」。
3. JOIN vs 子查詢：5.6+ 優化器對 DERIVED 表物化優化較好，但 INNER JOIN ... USING (id) 邏輯更清晰，執行計劃穩定為 eq_ref 類型。

進階場景：非唯一索引分頁去重

若按 column1 分頁且存在重複值，需引入唯一鍵作為 tie-breaker：

-- 第 1 頁
SELECT * FROM t ORDER BY column1 DESC, id DESC LIMIT 10;

-- 第 2 頁（假設上頁最後一行 column1=100, id=500）
SELECT * FROM t 
WHERE column1 < 100 OR (column1 = 100 AND id < 500)
ORDER BY column1 DESC, id DESC LIMIT 10;

此即 Yahoo 提出的「額外唯一列邊界值」分頁法，保證無重無漏。

生產環境落地清單

• ✅ 所有深分頁接口改造為 INNER JOIN (SELECT pk ...) 模式。
• ✅ 列表頁默認按主鍵倒序，避免業務字段非唯一導致翻頁抖動。
• ✅ 超大數據量（千萬級+）考慮引入 Elasticsearch / ClickHouse 做游標分頁或搜索分頁。
• ✅ 監控慢查詢日志，設置 long_query_time = 1 捕獲退化 SQL。

總結

百萬級單表深分頁優化核心：**覆蓋索引定位主鍵 + 延遲關聯回表**。實測將尾頁查詢從 37 秒降至 0.2 秒，**提升 180 倍**。此模式通用於所有有主鍵/唯一索引的 InnoDB 表，建議作為分頁查詢的標準寫法納入團隊規範。