萬盛學電腦網

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

MySQL 高效分頁查詢:避免深分頁效能崩潰

問題根源

LIMIT offset, rows 機制:MySQL 必須**掃描並捨棄前 offset 行**,再取 rows 行。當 offset 達百萬時,效能呈線性惡化。

優化方案對比

方案原理適用場景優點缺點
**延遲關聯** 先在索引上定位 ID,再回表 單表、有覆蓋索引 極快、實現簡單 需覆蓋索引
**書籤/游標分頁** 記住上一頁最大 ID,用 WHERE id > last_id 無限捲動、API 分頁 無 offset 問題、效能恆定 無法跳頁、需前端配合
**子查詢 + 覆蓋索引** 子查詢只掃描索引取 ID,外層回表 複雜 JOIN 查詢 減少回表次數 SQL 較複雜
**預先計算/物化視圖** 定期生成分頁快照表 極大資料、低即時性需求 查詢極快 資料非即時、維護成本高

方案 1:延遲關聯(最推薦)

-- 原始慢查詢
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 20;

-- 優化:先在索引取 ID,再回表
SELECT lt.* 
FROM large_table lt
INNER JOIN (
  SELECT id FROM large_table ORDER BY id LIMIT 1000000, 20
) AS derived ON lt.id = derived.id;

內層只掃描主鍵索引(極窄),外層精準回表 20 行。

方案 2:書籤分頁(API/無限捲動首選)

-- 首頁
SELECT * FROM large_table ORDER BY id LIMIT 20;

-- 記住最後一筆 id = 10020
-- 下一頁
SELECT * FROM large_table WHERE id > 10020 ORDER BY id LIMIT 20;

**效能恆定 O(1)**,無論翻多深皆毫秒級。

方案 3:子查詢覆蓋索引(複雜查詢適用)

SELECT t1.*, t2.name
FROM (
  SELECT id FROM large_table 
  WHERE status = 1 
  ORDER BY created_at DESC 
  LIMIT 50000, 20
) AS paged
JOIN large_table t1 ON t1.id = paged.id
JOIN related_table t2 ON t1.rid = t2.id;

實務建議

  • **嚴禁** 在大表直接使用大 offset
  • API 設計採用 **cursor-based pagination**(after_id/before_id)
  • 若必須支援跳頁,限制最大頁數(如前 1000 頁),超過引導用搜尋/篩選
  • 建立**覆蓋索引**包含 WHERE/ORDER BY/SELECT 欄位
  • 監控 Handler_read_rnd_next 指標偵測全表掃描

總結

分頁效能優化核心在於**避免掃描大量捨棄行**。延遲關聯與書籤分頁是兩大黃金法則,根據業務場景選擇,能將深分頁查詢從秒級降至毫秒級。

copyright © 萬盛學電腦網 all rights reserved