MySQL LIMIT 參數深度優化：避免全表掃描、分頁查詢加速實戰

核心價值

LIMIT 不僅是分頁工具，更是查詢優化利器：能讓優化器提前終止掃描、避免排序全表、減少網路傳輸、控制臨時表大小。

語法詳解

SELECT select_list
FROM table
WHERE condition
ORDER BY sort_col
LIMIT [offset,] row_count
-- 或
LIMIT row_count OFFSET offset

-- 參數說明：
-- offset：起始偏移量（從 0 開始，第 1 行 offset=0）
-- row_count：返回行數上限

優化器行為機制（官方文檔解讀）

分頁查詢效能陷阱與解決

問題：深分頁 OFFSET 大時極慢

-- 第 100 萬頁，每頁 20 行
SELECT * FROM huge_table ORDER BY id LIMIT 20000000, 20;
-- MySQL 必須掃描/排序前 2000 萬行再丟棄，極慢

方案 1：延遲關聯

SELECT t.* FROM huge_table t
JOIN (SELECT id FROM huge_table ORDER BY id LIMIT 20000000, 20) tmp USING (id);

方案 2：游標分頁

-- 首頁
SELECT * FROM huge_table WHERE id > 0 ORDER BY id LIMIT 20;
-- 記錄最後一行 id = 20000020
-- 下一頁
SELECT * FROM huge_table WHERE id > 20000020 ORDER BY id LIMIT 20;

方案 3：覆蓋索引

-- 僅查主鍵 + 覆蓋索引欄位
SELECT id, name, status FROM huge_table
WHERE status = 1 ORDER BY id LIMIT 20000000, 20;
-- 加索引 INDEX(status, id) 實現索引下推

實戰案例：百萬模糊查詢 0.03 秒

-- 原查詢：全表掃描 LIKE '%83%'
SELECT id, name FROM user WHERE name LIKE '%83%' OR `key` LIKE '%83%';

-- 優化：LIMIT 25 讓優化器提前停止
SELECT id, name FROM user WHERE name LIKE '%83%' OR `key` LIKE '%83%' LIMIT 0, 25;
-- 執行時間從 2.5s 降至 0.03s（僅返回前 25 條）

臨時表大小控制

優化器根據 LIMIT n 預估臨時表所需記憶體，避免 tmp_table_size/max_heap_table_size 觸發磁盤臨時表降級。

最佳實踐清單

1. 永遠加 LIMIT：無上限查詢是生產事故高發區
2. 深分頁用游標/延遲關聯：OFFSET > 10000 時必須優化
3. 配合 ORDER BY 索引：排序欄位有索引才能利用「提前終止排序」
4. 避免 LIMIT + 大 OFFSET + 複雜 JOIN：改用子查詢先 LIMIT 再 JOIN
5. 監控慢查詢：long_query_time=1 捕獲無 LIMIT 或深分頁慢查詢

延伸閱讀

• MySQL 條件查詢語句的用法
• MySQL 無重複查詢的實現
• 五種常用的 MySQL 命令列