MySQL LIMIT offset, size 分頁在資料量超過百萬時效能會急劇下降(LIMIT 1000000, 10 需掃描 100 萬行再丟棄)。本文深度解析 LIMIT 效能瓶頸、延遲關聯、覆蓋索引、複合索引、IN 子查詢、遞歸 CTE 等多種優化方案,並提供百萬/千萬級實測數據對比,助你實現秒級分頁。
-- 慢查詢:掃描前 1000010 行,丟棄前 1000000 行
SELECT * FROM big_table LIMIT 1000000, 10;
-- 快查詢:僅掃描 10 行
SELECT * FROM big_table LIMIT 0, 10;
根因: MySQL 無法直接「跳躍」到 offset 位置,必須順序掃描前 offset+size 行,再丟棄前 offset 行。offset 越大,掃描行數越多,I/O 與 CPU 開銷線性增長。
| 方案 | SQL 範例 | 10萬行耗時 | 90萬行耗時 | 原理 |
|---|---|---|---|---|
| 原生 LIMIT | SELECT * FROM t LIMIT 900000, 10 | ~0.05s | ~8-9s | 全掃描+丟棄 |
| 子查詢延遲關聯 | SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 900000, 1) LIMIT 10 | ~0.006s | ~0.006s | 子查詢只取 id(覆蓋索引),主查詢主鍵範圍查詢 |
| 複合索引覆蓋 | INDEX idx_vtype_id (vtype, id)SELECT id FROM t WHERE vtype=1 ORDER BY id LIMIT 900000, 10 | ~0.04s | ~0.04s | 索引覆蓋 WHERE+ORDER BY,僅掃描索引樹 |
| IN 子查詢兩階段 | SELECT * FROM t WHERE id IN (SELECT id FROM t WHERE vtype=1 ORDER BY id LIMIT 900000, 10) | ~0.05s | ~0.05s | 第一階段只取 id(覆蓋索引),第二階段主鍵 IN 查詢 |
| 遞歸 CTE (MySQL 8.0+) | WITH RECURSIVE ... | ~0.01s | ~0.01s | 遞歸定位游標,避免掃描 |
-- 步驟 1:子查詢只掃描主鍵索引,定位目標 offset 的 id
SELECT id FROM big_table ORDER BY id LIMIT 900000, 1;
-- 假設返回 id = 900000
-- 步驟 2:主查詢用主鍵範圍查詢,極快
SELECT * FROM big_table
WHERE id >= 900000
ORDER BY id LIMIT 10;
合併為單條 SQL:
SELECT * FROM big_table
WHERE id >= (SELECT id FROM big_table ORDER BY id LIMIT 900000, 1)
ORDER BY id LIMIT 10;
適用: 有自增/單調主鍵、無複雜 WHERE 條件。
場景:SELECT * FROM articles WHERE category_id = 5 ORDER BY id LIMIT 900000, 10
-- 1. 建立複合索引:WHERE 欄位在前,ORDER BY/LIMIT 欄位在後
ALTER TABLE articles ADD INDEX idx_cat_id (category_id, id);
-- 2. 第一階段:只查 id(覆蓋索引,極快)
SELECT id FROM articles WHERE category_id = 5 ORDER BY id LIMIT 900000, 10;
-- 0.04s 返回 10 個 id
-- 3. 第二階段:用 IN 查詢完整欄位(主鍵查詢,極快)
SELECT * FROM articles WHERE id IN (900001, 900002, ..., 900010);
關鍵原則:
(WHERE 欄位, ORDER BY 欄位, LIMIT 欄位)IN 列表,MySQL 會優化為主鍵批次查找。-- PHP/應用層邏輯
$ids = $db->query("SELECT id FROM articles WHERE category_id = 5 ORDER BY id LIMIT 900000, 10")
->fetchAll(PDO::FETCH_COLUMN); // 只取 id 陣列
$idList = implode(',', $ids); // "900001,900002,...,900010"
$rows = $db->query("SELECT id, title, content FROM articles WHERE id IN ($idList)")
->fetchAll();
優勢: 完全不依賴特定 SQL 技巧,任何 ORM/框架均可實現,效能接近原生 JOIN。
WITH RECURSIVE paginate AS (
-- 錨點:第一頁
SELECT id FROM big_table ORDER BY id LIMIT 10
UNION ALL
-- 遞歸:從上一頁最大 id 繼續
SELECT bt.id
FROM big_table bt
JOIN paginate p ON bt.id > (SELECT MAX(id) FROM paginate)
ORDER BY bt.id LIMIT 10
)
SELECT * FROM big_table WHERE id IN (SELECT id FROM paginate);
適用: 無連續主鍵、需精確游標分頁、避免 OFFSET 計算。
WHERE id > last_id LIMIT 10),體驗更好、效能恆定。MySQL LIMIT 深分頁優化核心三招:
結合游標分頁業務設計、適當分表架構,MySQL 輕鬆支撐千萬級秒級分頁。**索引設計是核心,SQL 改寫是手段,業務妥協是終極解。**