大偏移量 LIMIT 100000, 10 是 MySQL 分頁效能殺手：伺服器需掃描並丟棄前 10 萬行，再返回 10 行。本文深度剖析「子查詢延遲關聯」優化原理、實測數據對比、適用邊界與進階替代方案。
-- 典型慢查詢
SELECT * FROM `orders` WHERE `user_id` = 123 ORDER BY `created_at` DESC LIMIT 100000, 10;
-- 執行計劃：index range scan (user_id, created_at) → 回表讀取 100,010 行 → 丟棄 100,000 行 → 返回 10 行
-- 代價：大量隨機 I/O、CPU 處理無用行、鎖持有時間長思想： 先在索引上定位目標主鍵 ID（極窄、無回表），再用主鍵回表取完整列。
-- 優化後
SELECT o.*
FROM `orders` o
JOIN (
SELECT `id`
FROM `orders`
WHERE `user_id` = 123
ORDER BY `created_at` DESC
LIMIT 100000, 10
) AS derived USING (`id`);| 偏移量 | 原生 LIMIT | 延遲關聯 | 提升倍數 |
|---|---|---|---|
| 10 (小偏移) | 0.0004-0.0005s | 0.0005-0.0006s | 0.8x (略慢，子查詢開銷) |
| 10,000 (大偏移) | 0.0187s | 0.0061s | 3.1x |
| 100,000 | ~0.18s | ~0.04s | 4.5x |
結論： 偏移量越大，優化效果越明顯；小偏移反而因子查詢開銷略慢。
若查詢欄位均包含在索引中，可直接在子查詢投影所需列，省去外層 JOIN：
-- 假設索引 idx_uid_created (user_id, created_at, id, amount, status)
SELECT `id`, `amount`, `status`, `created_at`
FROM `orders`
WHERE `user_id` = 123
ORDER BY `created_at` DESC
LIMIT 100000, 10;
-- 此時 EXPLAIN 顯示 Using index，零回表，極致快| 方案 | 原理 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|---|
| 延遲關聯 | 子查詢取 ID + JOIN 回表 | 通用、無業務改造 | 仍需回表、大偏移仍有掃描開銷 | 中等偏移、既有系統改造 |
| 游標/Keyset 分頁 | WHERE id > last_id LIMIT 10 | 無偏移掃描、極快、穩定 | 不支援任意頁跳轉、需前端配合 | 無限滾動、API 分頁、高併發 |
| 覆蓋索引 | 索引包含所有查詢列 | 零回表、最快 | 索引膨脹、寫入放大、不適用 SELECT * | 固定查詢模式、窄表 |
| 緩存/搜尋引擎 | Redis/Elasticsearch 預計算 | DB 完全卸載、功能豐富 | 架構複雜、資料一致性挑戰 | 高併發、複雜篩選、全文搜尋 |
-- 首頁
SELECT * FROM orders WHERE user_id=123 ORDER BY created_at DESC, id DESC LIMIT 10;
-- 翻頁：記住上一頁最後一行的 (created_at, id)
SELECT * FROM orders
WHERE user_id=123
AND (created_at < '2023-01-15 10:30:00' OR (created_at = '2023-01-15 10:30:00' AND id < 98765))
ORDER BY created_at DESC, id DESC LIMIT 10;SELECT *，只選主鍵 id（或覆蓋索引列）。WITH target_ids AS (
SELECT id FROM orders WHERE user_id=123 ORDER BY created_at DESC LIMIT 100000, 10
)
SELECT o.* FROM orders o JOIN target_ids USING (id);架構建議： 新系統直接採用 Keyset 分頁（游標式），從根源消除大偏移問題；存量系統優先套用延遲關聯快速見效，再規劃遷移至 Keyset 或搜尋引擎架構。
