分頁是業務最常見、也是最易寫出慢查詢的場景。LIMIT 100000, 10 看似無辜，實則強制 MySQL 掃描 10 萬行索引、回表 10 萬行、丟棄 10 萬行，最終只取 10 行。本文給出三種經典優化模式，並建立「影響結果集」核心思維模型。

核心概念：影響結果集

影響結果集 ≠ 返回行數，而是 存儲引擎層面實際掃描/訪問的行數。
優化本質 = 讓影響結果集無限趨近於最終返回行數。

模式一：覆蓋索引 + 延遲關聯（最通用）

-- 原始慢查詢（掃描 100010 行，回表 100010 行）
SELECT * FROM order_manage
WHERE create_time > '2024-01-01'
ORDER BY create_time DESC LIMIT 100000, 10;

-- 優化：子查詢只取主鍵（覆蓋索引），主查詢按主鍵回表 10 行
SELECT o.*
FROM order_manage o
INNER JOIN (
    SELECT id FROM order_manage
    WHERE create_time > '2024-01-01'
    ORDER BY create_time DESC LIMIT 100000, 10
) tmp ON o.id = tmp.id;

原理： 子查詢走 INDEX(create_time, id) 覆蓋索引，僅掃描索引葉子節點（極窄、無回表），影響結果集 = 100010 索引條目（順序讀，極快）。主查詢 IN (10 ids) 精準回表 10 行。

模式二：游標分頁 / 基鍵集分頁（極致效能，需業務配合）

適用於「上一頁/下一頁」或「無限滾動」，不支持跳頁。

-- 首頁
SELECT * FROM posts WHERE tag_id=123 ORDER BY last_post_time DESC, id DESC LIMIT 20;

-- 記錄本頁最後一行的排序鍵值
-- last_post_time = '2024-05-20 10:00:00', id = 98765

-- 下一頁：直接定位，無 OFFSET
SELECT * FROM posts
WHERE tag_id=123
  AND (last_post_time < '2024-05-20 10:00:00'
       OR (last_post_time = '2024-05-20 10:00:00' AND id < 98765))
ORDER BY last_post_time DESC, id DESC LIMIT 20;

-- 上一頁：反向條件
SELECT * FROM posts
WHERE tag_id=123
  AND (last_post_time > '2024-05-20 10:00:00'
       OR (last_post_time = '2024-05-20 10:00:00' AND id > 98765))
ORDER BY last_post_time ASC, id ASC LIMIT 20;
-- 取回後應用層再反轉順序

優勢： 影響結果集恆定 = LIMIT N，無論翻多深，效能恆定。
索引要求： INDEX(tag_id, last_post_time, id) —— WHERE 等值列在前，ORDER BY 列依序在後。

模式三：ORDER BY 下推 + 復合索引（避免 filesort）

-- 慢查詢：WHERE 走索引，但 ORDER BY 觸發 filesort
SELECT * FROM users WHERE area='Xiamen' AND sex=1 ORDER BY last_login DESC LIMIT 30;

-- 最優索引：等值列在前，排序列在後
CREATE INDEX idx_area_sex_lastlogin ON users(area, sex, last_login DESC);
-- 8.0 支持降序索引，5.7 需 ORDER BY last_login ASC 配合索引 ASC，或接受 filesort（小結果集可忽略）

原則： 復合索引列順序 = WHERE 等值列（高選擇性優先） → ORDER BY 列 → 覆蓋列。範圍列會切斷後續列排序能力。

索引使用鐵律速查表

場景	影響結果集	優化動作
全表 LIMIT 10	10	無需索引，直接返回
WHERE 走索引，無排序	匹配行數	可接受
WHERE 走索引，ORDER BY 未命中索引	匹配行數 + 全排序	擴展索引含 ORDER BY 列
WHERE 未命中，ORDER BY 命中	全表掃描 + 排序	強制索引或改寫
深分頁 LIMIT 100000,10	100010	延遲關聯 / 游標分頁

架構層面建議

• 業務分頁深度封頂： 前端只提供「上一頁/下一頁/首頁/末頁」，禁止任意跳頁。
• 熱門列表 Redis 預緩存： 首頁 100 條熱數據直接走緩存，繞過 MySQL。
• 搜索/列表分離： 複雜條件檢索走 Elasticsearch，MySQL 僅按 ID 回表詳情。
• 外鍵與 JOIN 能不用就不用： 應用層組裝或異步物化寬表。

分頁優化的核心不是語法技巧，而是 「小表驅動大表、索引覆蓋業務列、影響結果集最小化」 的系統性思維。掌握三板斧，配合業務妥協（放棄跳頁），千萬級分頁也能穩穩毫秒級。