MySQL LIMIT 子句深度解析：語法詳解、效能陷阱、三大優化策略與 MS-SQL TOP 對比

LIMIT 語法詳解

-- 兩參數：offset, row_count
SELECT * FROM table LIMIT 6, 10;  -- 第 7-16 行（offset 從 0 開始）

-- 單參數：row_count（等同 LIMIT 0, n）
SELECT * FROM table LIMIT 5;      -- 前 5 行
SELECT * FROM table LIMIT 0, 5;   -- 等價寫法

效能陷阱：大 Offset 掃描捨棄機制

MySQL 執行 LIMIT 10000, 10 時：

1. 全表掃描/索引掃描前 10010 行。
2. 捨棄前 10000 行。
3. 返回後 10 行。

掃描成本與 Offset 成正比，深分頁效能線性惡化。

優化策略一：子查詢定位主鍵 ID（延遲關聯）

SELECT * FROM cyclopedia 
WHERE ID >= (
    SELECT ID FROM cyclopedia ORDER BY ID LIMIT 90000, 1
) LIMIT 100;

實測：0.19s vs 原始 8.88s（快 46 倍）。

進階簡化（去除多餘 MAX）：

SELECT * FROM cyclopedia 
WHERE ID >= (
    SELECT ID FROM cyclopedia LIMIT 90000, 1
) LIMIT 100;

優化策略二：雙層子查詢取 Max ID（舊文獻寫法）

SELECT * FROM cyclopedia 
WHERE ID > (
    SELECT MAX(ID) FROM (
        SELECT ID FROM cyclopedia ORDER BY ID LIMIT 90001
    ) AS tmp
) LIMIT 100;

實測 0.23s，略慢於策略一。策略一更簡潔高效。

優化策略三：游標分頁（記住上頁最大 ID）

-- 第一頁
SELECT * FROM cyclopedia ORDER BY ID LIMIT 100;

-- 記住最後 ID = 90000
-- 下一頁
SELECT * FROM cyclopedia WHERE ID > 90000 ORDER BY ID LIMIT 100;

優勢：純索引範圍掃描，無子查詢、無排序、效能恆定極快。

為什麼不直接用 LIMIT 90000, 100？

實測：1 row in set (8.88) sec —— 觸發全表掃描捨棄機制，極慢。

核心教訓：SELECT * 強制回表，配合大 Offset 掃描，雙重效能殺手。

MS-SQL TOP vs MySQL LIMIT 對比

MySQL LIMIT 在大 Offset 掃描上略優於早期 SQL Server TOP，但本質相同，深分頁必須優化。

最佳實踐總結

1. 淺分頁（< 1000）：直接 LIMIT。
2. 深分頁、需跳頁：子查詢定位 ID WHERE id >= (SELECT id ... LIMIT offset, 1) LIMIT pageSize。
3. 僅順序瀏覽：游標分頁 WHERE id > last_id LIMIT pageSize（最快、最穩定）。
4. 避免 SELECT *，只查必要欄位，配合覆蓋索引避免回表。
5. 排序欄位必須有索引，否則 filesort 會抵消所有優化。