善用 LIMIT 參數優化 MySQL 查詢效能：從分頁到避免全表掃描

前言：為什麼要關注 LIMIT？

在 MySQL 開發中，LIMIT 子句最常見的用途是實現分頁查詢。但許多開發者未意識到，LIMIT 在特定場景下還能幫助優化器選擇更高效的執行計畫，甚至避免全表掃描。本文將系統梳理 LIMIT 的語法、優化機制及實戰技巧。

基本語法回顧

SELECT ... FROM table_name
[WHERE condition]
[ORDER BY col1, col2 ...]
LIMIT [offset,] row_count
-- 或
LIMIT row_count OFFSET offset

• offset：偏移量，起始值為 0（非 1），表示跳過前幾行。
• row_count：要返回的最大行數。

範例：SELECT * FROM testtable LIMIT 20, 980 返回第 21 至 1000 筆記錄（跳過前 20 筆，取 980 筆）。

與 Oracle 分頁寫法對比

Oracle 無 LIMIT，需用巢狀子查詢配合 ROWNUM 實現分頁，SQL 複雜且效率較低：

SELECT * FROM (
  SELECT a1.*, ROWNUM rn FROM testtable a1 WHERE ROWNUM <= 1000
) WHERE rn > 20;

MySQL 的 LIMIT 語法簡潔直觀，維護成本低，但語法簡單不代表執行效率一定高，關鍵在於優化器如何處理。

MySQL 官方文檔揭示的 LIMIT 優化行為

根據 MySQL 手冊，當使用 LIMIT 而非 HAVING 時，優化器可能採取不同策略：

1. 部分行選擇時改用索引：若查詢原本會全表掃描，加上 LIMIT 後，在某些情況下會改用索引掃描（與 INDEX MERGE 機制有關）。
2. 配合 ORDER BY 提前終止排序：LIMIT n 與 ORDER BY 同用時，找到前 n 筆符合條件的記錄即停止排序，避免對整張表排序。
3. 配合 DISTINCT 提前結束：找到第一筆符合條件的記錄即停止查詢。
4. GROUP BY 搭配 LIMIT 減少分組計算：若 GROUP BY 能透過索引順序讀取，LIMIT n 會跳過不必要的分組聚合。
5. 傳輸第 n 行後放棄剩餘查詢：網路層面也能節省資源。
6. LIMIT 0 快速返回空結果集：適用於檢查查詢合法性、獲取欄位型別等元資料場景。
7. 臨時表大小估算：優化器根據 LIMIT # 估算臨時表所需空間。

實戰優化技巧

1. 大偏移量分頁優化（避免掃描大量無用行）

問題：LIMIT 1000000, 20 會先掃描 1,000,020 行再丟棄前 100 萬行，極其低效。

解法：利用覆蓋索引 + 子查詢定位主鍵，再回表：

SELECT * FROM posts
WHERE id >= (SELECT id FROM posts ORDER BY id LIMIT 1000000, 1)
LIMIT 20;

或應用層記錄上一頁最大 ID，改用 WHERE id > last_id ORDER BY id LIMIT 20（游標式分頁）。

2. 配合 ORDER BY 利用索引避免 filesort

確保 ORDER BY 欄位與 WHERE 條件欄位組成聯合索引，且排序方向一致，優化器可直接按索引順序讀取前 N 行，跳過排序步驟。

3. 替代 HAVING 進行過濾

聚合後過濾時，若只需前幾組結果，改用 LIMIT 可減少臨時表大小與掃描量。

總結

LIMIT 不只是分頁工具，更是查詢優化的關鍵槓桿。理解其與 ORDER BY、DISTINCT、GROUP BY、索引的交互機制，能在大數據量場景下顯著降低響應時間與資源消耗。下次寫查詢時，不妨問自己：「這裡加個 LIMIT 能不能讓優化器偷懶？」