萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 分頁查詢優化實戰:從 LIMIT 偏移到延遲關聯/游標分頁的演進指南

分頁查詢性能陷阱


-- 典型深翻頁災難
SELECT * FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20;
-- MySQL 必須掃描 1,000,020 行,丟棄前 1,000,000 行,極慢

四大分頁優化方案對比

方案一:延遲關聯 / 子查詢回表 (最通用、無業務侵入)


-- 1. 只在索引上定位主鍵 (覆蓋索引掃描,極快)
SELECT id FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20;

-- 2. 主鍵回表查詳細列
SELECT t.* 
FROM huge_table t
JOIN (
    SELECT id FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20
) tmp USING (id);

原理: 子查詢僅掃描索引葉子節點 (窄行),回表僅 20 行。
前提: WHERE/Order By 列有覆蓋索引。

方案二:游標/Keyset 分頁 (極高性能、需業務配合)


-- 首頁
SELECT * FROM huge_table WHERE status=1 ORDER BY id LIMIT 20;

-- 下一頁: 記住上一頁最大 id
SELECT * FROM huge_table WHERE status=1 AND id > 1000020 ORDER BY id LIMIT 20;

-- 上一頁: 記住上一頁最小 id
SELECT * FROM huge_table WHERE status=1 AND id < 1000001 ORDER BY id DESC LIMIT 20;

優勢: 無 OFFSET 掃描,恆定毫秒級。
限制: 僅支持「下一頁/上一頁」,不支持跳頁;排序列必須唯一 (或 (sort_col, pk) 組合唯一)。

方案三:覆蓋索引 + LIMIT (中小偏移量)


-- 建立覆蓋索引
CREATE INDEX idx_status_id_name ON huge_table (status, id, name, ...);

-- 查詢僅需索引列
SELECT id, name FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20;
-- 無需回表,但仍需掃描 100 萬索引條目

方案四:應用層緩存頁碼 (極少變更的數據)

  • 首頁/熱門頁緩存完整 HTML/JSON
  • 翻頁請求命中緩存直接返回
  • 數據變更時主動失效相關頁緩存

MySQL 8.0 標準語法:OFFSET ... FETCH


-- 標準 SQL:2008 語法 (MySQL 8.0.2+ 支持)
SELECT * FROM huge_table WHERE status=1 ORDER BY id
OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY;

-- 等價於 LIMIT 1000000, 20
-- 可讀性更好,遷移其他數據庫無需改動

實戰選型決策樹

  1. **偏移量 < 1000** → 直接 LIMIT offset, size (簡單優先)
  2. **偏移量大、需跳頁** → **延遲關聯** (通用、無業務侵入)
  3. **僅需連續翻頁 (列表/Feed 流)** → **游標分頁** (極致性能)
  4. **極大表 + 高併發** → **緩存首頁/熱頁 + 游標分頁 + 異步緩存預熱**

避坑指南

  • ❌ ORDER BY RAND() LIMIT 10 → 全表掃描+文件排序,禁用
  • ❌ LIMIT 1000000, 20 無覆蓋索引 → 全表掃描+回表,災難
  • ❌ 游標分頁用非唯一排序列 → 數據重複/丟失,**必須 ORDER BY sort_col, pk**
  • ✅ 監控 Handler_read_next、Handler_read_rnd_next 觀察掃描行數
  • ✅ EXPLAIN 確認 type=ref/range、Extra=Using index

框架集成範例

MyBatis 游標分頁



Spring Data JPA


Pageable firstPage = PageRequest.of(0, 20, Sort.by("id"));
Slice slice = userRepository.findByStatus(1, firstPage);
// 下一頁
Pageable nextPage = PageRequest.of(slice.getNumber()+1, 20, Sort.by("id"));
// 或手動構建 Keyset: userRepository.findByStatusAndIdGreaterThan(1, lastId, PageRequest.of(0,20,Sort.by("id")));
copyright © 萬盛學電腦網 all rights reserved