MySQL LIMIT 深度解析：偏移量性能陷阱與三種優化模式實測

LIMIT 語法回顧

LIMIT [offset,] row_count
LIMIT row_count OFFSET offset

-- 等價關係
LIMIT 5          ≡ LIMIT 0, 5
LIMIT 10, 20     ≡ LIMIT 20 OFFSET 10
LIMIT 95, -1     -- 從第 96 行到末尾（-1 表示無上限）

性能陷阱：OFFSET 掃描丟棄機制

MySQL 執行 LIMIT M, N 時：

1. 按排序順序掃描前 M + N 行。
2. 丟棄前 M 行。
3. 返回後 N 行。

耗時與 M 近似線性增長，與 N 無關。

實測對比（表 yanxue8_visit，主鍵 vid）

淺分頁（offset=10）

-- 直接 LIMIT
SELECT * FROM yanxue8_visit LIMIT 10, 10;  -- 0.0004~0.0005s

-- 子查詢定位
SELECT * FROM yanxue8_visit 
WHERE vid >= (SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10, 1)
LIMIT 10;  -- 0.0005~0.0006s
結論：offset 極小時，直接 LIMIT 更優（省去子查詢開銷）。

深分頁（offset=10000）

-- 直接 LIMIT
SELECT * FROM yanxue8_visit LIMIT 10000, 10;  -- 0.0187s

-- 子查詢定位（延遲關聯）
SELECT * FROM yanxue8_visit 
WHERE vid >= (SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1)
LIMIT 10;  -- 0.0061s（快 3 倍）

-- 進一步簡化（利用 LIMIT 直接返回單值）
SELECT * FROM yanxue8_visit 
WHERE vid >= (SELECT vid FROM yanxue8_visit LIMIT 10000, 1)
LIMIT 10;  -- 理論更快，實測差異微小

結論：offset 越大，延遲關聯優勢越明顯。

三大優化模式總結

MS-SQL 對比啟示

MS-SQL TOP N 同樣需掃描前 M 行，百萬級 TOP 90000 耗時 390ms，而 MySQL LIMIT 90000,1 僅 60ms。**MySQL LIMIT 實現機制更高效**，得益於索引遍歷優化。

生產環境決策樹

IF 分頁類型 == '游標/無限滾動' AND 排序 == '主鍵':
    USE 主鍵範圍查詢 (id > last_id)
ELSE IF offset < 1000:
    USE 直接 LIMIT
ELSE:
    USE 延遲關聯 (子查詢定位主鍵 + 回表)
    IF 有複雜 WHERE:
        確保 WHERE 條件下推到子查詢
    IF 排序非主鍵:
        考慮覆蓋索引 (排序列 + 主鍵)

總結

MySQL LIMIT offset, N 本質是「掃描 offset+N 行 → 丟棄 offset 行」。**淺分頁直用 LIMIT，深分頁必上延遲關聯或游標範圍查詢**。將此規範寫入團隊 SQL 審查清單，杜絕 LIMIT 1000000, 10 進入生產。