MySQL 分庫分表與大偏移分頁優化實戰

前言

單表千萬級數據下，傳統 LIMIT offset, length 分頁性能急劇下降。本文梳理單機優化、分庫分表下的分頁策略，並給出工程落地方案。

一、單機分頁優化三板斧

1.1 基礎語法對比

-- 方案 A：大偏移全掃（慢）
SELECT * FROM t LIMIT 100000, 10;

-- 方案 B：主鍵游標（快，需連續遞增 ID）
SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 10;

-- 方案 C：延遲關聯（通用，需覆蓋索引）
SELECT * FROM t
INNER JOIN (SELECT id FROM t WHERE ... ORDER BY id LIMIT 100000, 10) tmp USING (id);

1.2 核心原理

• 方案 B 利用聚簇索引順序讀取，避免掃描前 N 行；
• 方案 C 先在覆蓋索引上定位主鍵，再回表，減少隨機 IO；
• 前提：WHERE 條件能命中索引，ORDER BY 列與索引順序一致。

二、分庫分表場景下的分頁難題

2.1 全局排序分頁

跨分片查詢需各分片取 Top-N 後合併排序：

-- 應用層或中間件（ShardingSphere/MyCat）邏輯
SELECT * FROM t_shard_0 WHERE ... ORDER BY create_time DESC LIMIT 10
UNION ALL
SELECT * FROM t_shard_1 WHERE ... ORDER BY create_time DESC LIMIT 10
...
ORDER BY create_time DESC LIMIT 10;

缺點：深分頁時各分片仍需掃描大量數據，延遲不可控。

2.2 業務妥協方案

1. 禁止跳頁：僅提供「下一頁」，前端記錄上一頁最大排序值（游標分頁）；
2. 搜索代替翻頁：引入 Elasticsearch，按業務字段檢索，天然支持深分頁；
3. 冗余排序字段：在分片鍵中包含排序列（如 shard_key = user_id * 10000 + date），實現分片內有序，減少跨分片合併。

三、工程落地清單

結語

分頁優化無銀彈，核心是減少掃描行數與避免跨分片合併排序。業務若能接受「無跳頁、基於游標」、配合覆蓋索引或 ES，90% 的分頁性能問題迎刃而解。