業務 SQL:
SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,
start_time, end_time, market_type, back_leaf_category,
item_status, picture_url
FROM relation
WHERE biz_type='0' AND end_time>='2014-05-29'
ORDER BY id ASC LIMIT 149420, 20;
表量 36 萬,LIMIT 149420, 20 導致 MySQL 掃描 14.9 萬行再丟棄,CPU 長期 30-40%,執行時間 1.2 秒+。
覆蓋索引先取主鍵 → 再回表取完整列。
子查詢僅掃描索引(極窄),獲取目標 20 個 ID,外層按主鍵精準回表,避免大量無效回表與排序開銷。
EXPLAIN: type=range, key=ind_endtime, rows=349622, Extra=Using where; Using filesort
執行時間:~1.2 秒

SELECT a.*
FROM relation a
JOIN (
SELECT id
FROM relation
WHERE biz_type='0' AND end_time>='2014-05-29'
ORDER BY id ASC LIMIT 149420, 20
) b ON a.id = b.id;
EXPLAIN:
id=1, table=, type=ALL, rows=20
id=1, table=a, type=eq_ref, key=PRIMARY, rows=1
id=2, table=relation, type=index, key=PRIMARY, rows=733552
執行時間:~0.4 秒(提速 3 倍)

WITH cte AS (...) SELECT a.* FROM relation a JOIN cte b ON a.id=b.id;| 方案 | 原理 | 優點 | 缺點 |
|---|---|---|---|
| 前端快取 | 減少落庫查詢 | 最省資源 | 資料實時性妥協 |
| 書籤/游標分頁 | 記錄上次最大 ID,向後取 | 無 OFFSET 掃描 | 不支援隨機跳頁 |
| Sphinx/ES 搜尋 | 倒排索引天然支援分頁 | 功能豐富 | 架構複雜度高 |
| 延遲關聯 | 覆蓋索引取 ID + 主鍵回表 | 改動最小、相容性強 | 極深分頁仍有物化開銷 |