`SELECT * FROM big_table LIMIT 800000, 20` 需掃描前 800,020 行,**丟棄 80 萬行**,隨偏移量線性增長:
| 方案 | SQL 示例 | 耗時 | 原理 |
|---|---|---|---|
| 原生深分頁 | `SELECT * FROM article LIMIT 800000, 20` | 38.69 s | 掃描 80 萬行丟棄 |
| 只查主鍵列 | `SELECT id, category_id FROM article LIMIT 800000, 20` | 0.94 s | 覆蓋索引,避免回表 |
| 子查詢延遲關聯 | `SELECT id, category_id FROM article WHERE id >= (SELECT id FROM article LIMIT 800000, 1) LIMIT 20` | 0.36 s | 子查詢走索引定位起始 ID,主查詢範圍掃描 |
| BETWEEN 範圍查詢 | `SELECT id, category_id FROM article WHERE id BETWEEN 800000 AND 800020` | 0.0001 s | 直接索引範圍掃描,需 ID 連續無洞 |
-- 只查索引覆蓋的列
SELECT id, category_id FROM article
WHERE category_id = 5
ORDER BY id
LIMIT 800000, 20;
INDEX idx_cat_id (category_id, id)Using index,無回表SELECT a.*
FROM article a
INNER JOIN (
SELECT id FROM article
WHERE category_id = 5
ORDER BY id
LIMIT 800000, 1
) b ON a.id = b.id
LIMIT 20;
-- 首頁
SELECT * FROM article WHERE category_id = 5 ORDER BY id LIMIT 20;
-- 翻頁:記住上頁最大 ID
SELECT * FROM article
WHERE category_id = 5 AND id > 800020 -- last_seen_id
ORDER BY id LIMIT 20;
SELECT * FROM article
WHERE id BETWEEN 800000 AND 800020
AND category_id = 5;
| 方案 | 適用場景 | 精度 | 成本 |
|---|---|---|---|
| 精確 COUNT(*) | 小表/實時需求 | 精確 | 全表掃描/索引掃描 |
| EXPLAIN 估算行數 | 大表/近似值可接受 | 估算 | 極低 |
| Redis 緩存總數 | 高併發/準實時 | 近似 | 需維護緩存一致性 |
| 單獨統計表 | 極大表/精確需求 | 精確 | 寫入額外開銷 |
| 分表分庫 | 單表超 2000 萬 | 精確 | 架構複雜度高 |
// 伪代碼:游標分頁服務
function fetchPage(categoryId, lastId, pageSize) {
// 1. 參數校驗
// 2. 緩存熱點首頁
// 3. 執行 Keyset 分頁 SQL
// 4. 返回 {data, nextCursor, hasMore}
// 5. 異步更新總數緩存
}
Hibernate/MyBatis Plus `PageHelper` 默認生成 `LIMIT offset, size`。**大表分頁務必手寫 SQL 或自定義攔截器改寫為 Keyset/延遲關聯模式**,否則生產事故必發。
MySQL 分頁優化核心:**拒絕大 OFFSET**。優先 **Keyset 分頁(游標法)**,次選 **延遲關聯/子查詢定位**,輔以 **覆蓋索引**。`COUNT(*)` 改用緩存/估算/統計表。ORM 框架深分頁必須介入改寫。**架構層面:單表控制 2000 萬以內,超限分庫分表**。