MySQL 千萬級數據深分頁優化：從 9 秒到 0.04 秒的實戰之路

問題現場

表 collect (100 萬行，1.6GB)，查詢：

SELECT id, title FROM collect LIMIT 900000, 10;  -- 8-9 秒

同表查詢 LIMIT 1000, 10 僅 0.01 秒。為什麼深分頁性能斷崖式下跌？

根因分析：OFFSET 掃描機制

MySQL 執行 LIMIT offset, n 時：

1. 全表掃描/索引掃描定位到 offset 位置
2. 丟棄前 offset 行
3. 返回後續 n 行

Offset 越大，丟棄的行越多，IO 與 CPU 線性增長。LIMIT 900000, 10 實際掃描 900,010 行。

優化方案演進

方案一：主鍵索引覆蓋 + 子查詢 (MySQL 5.6+)

-- 利用主鍵聚簇索引順序，子查詢只掃描主鍵值
SELECT id, title FROM collect 
WHERE id >= (SELECT id FROM collect ORDER BY id LIMIT 900000, 1)
LIMIT 10;

• 子查詢 SELECT id ... LIMIT 900000, 1 走主鍵索引，只讀 8B × 90萬 = 7.2MB，極快
• 外層 WHERE id >= ... LIMIT 10 範圍查詢，命中主鍵索引
• 缺陷：需主鍵連續遞增，有刪除間隙時結果偏移

方案二：聯覆蓋索引 (終極方案，推薦)

建立聯合索引 (where_column, pk_column)：

ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);

查詢：

-- 只查主鍵，走覆蓋索引，不回表
SELECT id FROM collect WHERE vtype = 1 ORDER BY id LIMIT 900000, 10;
-- 0.04 秒！

核心規律：
WHERE 條件列在前 + ORDER BY 主鍵在後 的聯合索引，
只 SELECT 主鍵 時走覆蓋索引，深分頁極快。
一旦 SELECT * 或查詢非索引列，優化器放棄覆蓋索引轉全表掃描。

方案三：延遲關聯 / IN 子查詢 (通用業務查詢)

需返回完整列時：

-- 1. 先用覆蓋索引取主鍵
SELECT id FROM collect WHERE vtype = 1 ORDER BY id LIMIT 900000, 10;

-- 2. 再用主鍵 IN 查詢完整數據 (主鍵點查極快)
SELECT * FROM collect WHERE id IN (900001, 900002, ..., 900010);

或單條 SQL：

SELECT * FROM collect 
WHERE id IN (
    SELECT id FROM collect WHERE vtype = 1 ORDER BY id LIMIT 900000, 10
);

MySQL 5.6+ 優化器將 IN 子查詢轉為 EXISTS 半連接，主鍵查詢極快。

方案四：游標分頁 / Keyset Pagination (API 設計層面)

不使用 OFFSET，改用「上一頁最大 ID」：

-- 第一頁
SELECT * FROM collect WHERE vtype = 1 ORDER BY id LIMIT 20;

-- 後續頁 (假設上一頁最大 id = 900000)
SELECT * FROM collect WHERE vtype = 1 AND id > 900000 ORDER BY id LIMIT 20;

• 性能恆定 O(1)，無深分頁問題
• 不支持跳頁、總頁數、倒序翻頁
• 適合無限滾動、Feed 流、API 分頁

實測數據對比 (100 萬行，vtype=1 約 30 萬行)

索引設計黃金法則

-- 深分頁查詢模式：
SELECT [columns] FROM table 
WHERE [where_conditions] 
ORDER BY [pk_column] 
LIMIT [offset], [size];

-- 最優索引：
CREATE INDEX idx_where_pk ON table (where_column, pk_column);
-- 覆蓋索引條件：SELECT 列 ⊆ 索引列 (where_column, pk_column)
-- 僅 SELECT pk_column 時 100% 走覆蓋索引

避坑指南

1. 不要盲目加索引：聯合索引列順序決定生死，(id, vtype) 完全無效。
2. SELECT * 會殺死覆蓋索引：業務若需完整列，必須用延遲關聯兩步走。
3. WHERE 條件列區分度低 (如性別、狀態) 時，優化器可能仍選全表掃描。
   → 強制索引 FORCE INDEX (idx_vtype_id) 或重寫查詢。
4. 主鍵非遞增 (UUID/雪花算法亂序) 時，方案一失效，方案二/三依然有效。
5. 分表是下策：同樣邏輯在分表上仍面臨深分頁，先優化索引與查詢模式。

框架層集成建議

• MyBatis: ` SELECT * FROM t WHERE id IN (SELECT id FROM t WHERE ... LIMIT #{offset}, #{size}) `
• JPA/Hibernate: 避免 setFirstResult(900000)，改用 WHERE id > :lastId 或 Native Query
• 分頁組件: 封裝 PageHelper.optimizeOffset() 自動改寫為延遲關聯

深分頁優化本質是將隨機 IO 轉為順序索引掃描 + 主鍵點查。掌握聯合索引覆蓋與延遲關聯模式，千萬級數據也能毫秒級翻頁。