MySQL 深分頁(LIMIT 200000, 50)是經典效能殺手。本文透過實測對比,揭示「延遲關聯」與「覆蓋索引」優化原理,助你輕鬆將查詢時間從 40 秒降至 0.06 秒。
表 test 有 30 萬筆資料,主鍵 id 為自增索引。需查詢第 208888 筆之後的 50 筆。
SELECT * FROM `test` ORDER BY id ASC LIMIT 208888, 50;
-- 執行時間:40~50 秒
原因:MySQL 需掃描前 208888 行並排序(雖有索引,但 ORDER BY ... LIMIT offset, n 仍需遍歷 offset 行),產生臨時表與 filesort。
SELECT * FROM `test` WHERE id BETWEEN 208838 AND 208888;
-- 執行時間:~0.06 秒(快 700 倍!)
原理:直接利用 B+ 樹主鍵索引做範圍查找,避免掃描大量無關行,屬於「Index Range Scan」。
當無法直接用 ID 範圍(如按非主鍵排序)時:
-- 步驟 1:只查主鍵(覆蓋索引,極快)
SELECT id FROM `test` ORDER BY create_time DESC LIMIT 200000, 50;
-- 步驟 2:用 IN 關聯回主表查完整欄位
SELECT * FROM `test` WHERE id IN (上一步查出的 50 個 id) ORDER BY create_time DESC;
| 場景 | 優化手段 | 關鍵點 |
|---|---|---|
| 主鍵遞增分頁 | WHERE id > last_id LIMIT n | 游標式分頁,無 offset 痛點 |
| 非主鍵排序分頁 | 延遲關聯 / 覆蓋索引 | 只讓索引覆蓋 ORDER BY + LIMIT 欄位 |
| 搜尋 + 分頁 | Elasticsearch / 全文索引 | MySQL 全文搜尋效能有限 |
| 統計總數 | COUNT(*) 優化 / 緩存總數 | InnoDB COUNT(*) 需全掃描 |
SELECT *,只查必要欄位(利用覆蓋索引)。ORDER BY 欄位務必有索引,否則 filesort 災難性。EXPLAIN 分析執行計劃,關注 type、rows、Extra。WHERE id > ? LIMIT n),前端改為「載入更多」而非頁碼跳轉。