MySQL 查詢優化實戰：從 50 秒到 0.06 秒的索引最佳實踐

問題背景

在一張擁有 30 萬條記錄的表 test 中，主鍵 id 已建立聚簇索引。需求是分頁查詢：從第 208,888 條記錄開始取 50 條。直覺寫法如下：

SELECT * FROM `test` ORDER BY id ASC LIMIT 208888, 50;

實測執行時間 **40~50 秒**，嚴重拖慢業務。為什麼有索引卻這麼慢？

根因分析：ORDER BY ... LIMIT offset, n 的深分頁陷阱

• MySQL 必須掃描前 offset 行（此處 208,888 行）並丟棄，才能返回後 50 行。
• 雖然 id 是主鍵（B+ 樹），但「掃描 + 丟棄」仍需遍歷大量葉子節點，產生大量隨機 I/O。
• LIMIT offset, n 隨著 offset 增大性能呈線性下降，百萬級數據常見秒級甚至分鐘級延遲。

優化方案：利用索引範圍查詢替代深分頁

若業務允許「按 ID 範圍查詢」而非「按位置偏移查詢」，可改寫為：

SELECT * FROM `test`
WHERE id BETWEEN 208838 AND 208888;

實測執行時間 **約 0.06 秒**，提升 **近 1000 倍**。

為何 BETWEEN 如此之快？

1. 索引區間掃描：B+ 樹直接定位到 208838 的葉子節點，順序讀取至 208888，僅訪問 51 行數據頁。
2. 無文件排序：主鍵本身有序，省去 ORDER BY 的臨時表與 filesort 開銷。
3. 覆蓋索引潛力：若只查詢主鍵列或二級索引包含所有所需列，可實現「只讀索引不回表」。

適用場景與限制

傳統分頁的優化替代：延遲關聯

若必須保留 LIMIT offset, n 語義，可用「先查主鍵、再回表」兩步走：

-- 步驟 1：只走主鍵索引，極快
SELECT id FROM `test` ORDER BY id LIMIT 208888, 50;

-- 步驟 2：用 IN 或 JOIN 回表取完整列
SELECT t.* FROM `test` t
JOIN (
    SELECT id FROM `test` ORDER BY id LIMIT 208888, 50
) tmp USING (id);

原理：子查詢只掃描主鍵索引（極窄），不回表；外層按主鍵精確點查 50 次，總耗時通常在 **毫秒級**。

更多優化技巧速查

• 避免 SELECT *：只取必要列，提高覆蓋索引命中率。
• 複合索引順序：WHERE a=1 AND b=2 ORDER BY c → 索引 (a,b,c)。
• 使用 EXPLAIN 分析：關注 type（應為 range/ref/eq_ref）、rows、Extra（避免 Using filesort/Using temporary）。
• 大表歸檔：歷史數據移至歸檔表，保持活躍表體積可控。
• 分庫分表：單表超過 2000 萬行考慮水平拆分。

總結

1. 深分頁是性能殺手：LIMIT 200000, 50 強制全掃描前 20 萬行。
2. 業務允許時優先用範圍查詢：WHERE id BETWEEN ... 或 WHERE id > last_id 利用 B+ 樹順序讀取。
3. 必須分頁時用延遲關聯：子查詢只取主鍵 + 外層回表，將「掃描+丟棄」轉為「掃描窄索引+點查」。
4. 持續監控 EXPLAIN：每次 SQL 變更後驗證執行計劃，防止索引失效。

掌握這些模式，能讓 MySQL 在千萬級甚至億級數據下依然保持毫秒級響應。