LIMIT offset, rows 機制:MySQL 必須**掃描並捨棄前 offset 行**,再取 rows 行。當 offset 達百萬時,效能呈線性惡化。
| 方案 | 原理 | 適用場景 | 優點 | 缺點 |
|---|---|---|---|---|
| **延遲關聯** | 先在索引上定位 ID,再回表 | 單表、有覆蓋索引 | 極快、實現簡單 | 需覆蓋索引 |
| **書籤/游標分頁** | 記住上一頁最大 ID,用 WHERE id > last_id | 無限捲動、API 分頁 | 無 offset 問題、效能恆定 | 無法跳頁、需前端配合 |
| **子查詢 + 覆蓋索引** | 子查詢只掃描索引取 ID,外層回表 | 複雜 JOIN 查詢 | 減少回表次數 | SQL 較複雜 |
| **預先計算/物化視圖** | 定期生成分頁快照表 | 極大資料、低即時性需求 | 查詢極快 | 資料非即時、維護成本高 |
-- 原始慢查詢
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 20;
-- 優化:先在索引取 ID,再回表
SELECT lt.*
FROM large_table lt
INNER JOIN (
SELECT id FROM large_table ORDER BY id LIMIT 1000000, 20
) AS derived ON lt.id = derived.id;
內層只掃描主鍵索引(極窄),外層精準回表 20 行。
-- 首頁
SELECT * FROM large_table ORDER BY id LIMIT 20;
-- 記住最後一筆 id = 10020
-- 下一頁
SELECT * FROM large_table WHERE id > 10020 ORDER BY id LIMIT 20;
**效能恆定 O(1)**,無論翻多深皆毫秒級。
SELECT t1.*, t2.name
FROM (
SELECT id FROM large_table
WHERE status = 1
ORDER BY created_at DESC
LIMIT 50000, 20
) AS paged
JOIN large_table t1 ON t1.id = paged.id
JOIN related_table t2 ON t1.rid = t2.id;
after_id/before_id)Handler_read_rnd_next 指標偵測全表掃描分頁效能優化核心在於**避免掃描大量捨棄行**。延遲關聯與書籤分頁是兩大黃金法則,根據業務場景選擇,能將深分頁查詢從秒級降至毫秒級。