MySQL 深分頁優化實戰：覆蓋索引 + 子查詢 讓 LIMIT 1000000,5 從全表掃描變索引查詢

大偏移量 LIMIT 效能殺手與破解之道

LIMIT 1000000, 20 會讓 MySQL 掃描 1,000,020 行再丟棄前 100 萬行，平均掃描半張表，隨資料增長線性惡化。優化核心思路：先用覆蓋索引快速定位主鍵，再回表取完整行。

優化前後 EXPLAIN 對比

-- 原始：全表掃描 2313 萬行
EXPLAIN SELECT * FROM user_order_info LIMIT 1000000,5;
-- type=ALL, rows=23131886, Extra=NULL

-- 優化：覆蓋索引子查詢 + JOIN 回表
EXPLAIN SELECT * FROM user_order_info
INNER JOIN (SELECT pin FROM user_order_info LIMIT 1000000,5) AS lim USING(pin);
-- 子查詢: type=index, key=PRIMARY, rows=23131886, Extra=Using index
-- 外層:  type=eq_ref, key=PRIMARY, rows=1

為何有效？

1. 子查詢：SELECT pin FROM ... LIMIT ... 僅讀取主鍵索引 (Using index)，不回表，I/O 極小。
2. 外層 JOIN：拿到 5 個主鍵值後，按主鍵 eq_ref 精準回表，僅讀 5 行完整資料。
3. 總掃描行數雖同樣 2313 萬，但索引掃描比全表掃描快數量級 (索引更窄、順序讀取、無隨機 I/O)。

適用條件與替代方案

• 表有主鍵或唯一二級索引 (覆蓋索引需包含所有查詢欄位)。
• 偏移量極大 (＞ 10 萬) 且無法業務限制翻頁深度時使用。
• 游標分頁 (Keyset Pagination)：WHERE id > last_seen_id ORDER BY id LIMIT 20，無偏移量問題，效能恆定，適合無限滾動/APP 列表。
• 延遲關聯：同理可用 EXISTS / IN 子查詢達成同樣效果。

實戰提醒

• MySQL 8.0+ 優化器可能自動轉換，建議仍顯式寫法確保穩定。
• 子查詢欄位必須是索引覆蓋欄位，否則仍會回表導致雙重掃描。
• 監控 Handler_read_key / Handler_read_rnd_next 驗證優化效果。