MySQL 分頁查詢效能優化：從基礎 LIMIT 到子查詢深度分頁策略

MySQL 分頁查詢效能優化：從基礎 LIMIT 到子查詢深度分頁策略

在 Web 應用開發中，分頁查詢是最常見的資料庫操作之一。隨著資料量增長，分頁查詢的效能問題逐漸凸顯，尤其是深度分頁（大偏移量）時的效能急劇下降。本文將系統介紹 MySQL 分頁查詢的基礎用法、效能瓶頸成因，以及子查詢優化策略與混合分頁方案。

基礎分頁查詢與索引優化

最基本的分頁 SQL 寫法如下：

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT offset, rows

在中小型資料量（如百萬筆以內）場景下，這類寫法通常足夠應付。關鍵在於確保 WHERE、ORDER BY 條件欄位已建立合適索引。例如：

SELECT * FROM articles 
WHERE category_id = 123 
ORDER BY id 
LIMIT 50, 10

此查詢應在 category_id 與 id 上建立複合索引（INDEX idx_cat_id (category_id, id)），使 MySQL 能直接利用索引完成篩選與排序，避免 filesort 與全表掃描。

深度分頁的效能瓶頸

當使用者翻閱至後段頁面（如第 1000 頁，每頁 10 筆），SQL 變為：

SELECT * FROM articles 
WHERE category_id = 123 
ORDER BY id 
LIMIT 10000, 10

MySQL 必須先掃描並捨棄前 10,000 筆符合條件的記錄，再取得後 10 筆。隨著偏移量增大，掃描成本線性增長，查詢延遲顯著上升，甚至導致服務超時。

子查詢優化策略：延遲關聯

解決深度分頁的核心思路是「先定位主鍵，再回表查試完整欄位」。利用子查詢僅掃描索引取得目標頁首筆記錄的主鍵值，再以此為起點進行範圍查詢：

SELECT * FROM articles 
WHERE category_id = 123 
  AND id >= (
    SELECT id FROM articles 
    ORDER BY id 
    LIMIT 10000, 1
  )
ORDER BY id 
LIMIT 10

優勢在於：

• 子查詢僅掃描索引（Covering Index），不回表，極快
• 外層查詢利用主鍵範圍查詢，直接定位目標資料
• 避免了大偏移量的掃描與捨棄操作

實戰建議：混合分頁策略

子查詢雖優化深度分頁，但在淺分頁（小偏移量）時，額外的子查詢開銷反而可能比直接 LIMIT 更慢。建議採用策略模式動態切換：

• 淺分頁（如前 100 頁）：使用標準 LIMIT offset, rows
• 深度分頁（超過 100 頁）：啟用子查詢優化方案

判斷邏輯可在應用層實現，或封裝於資料存取層（DAO）中，對上層業務透明。

總結

MySQL 分頁效能優化的核心原則：

1. 永遠為查詢條件與排序欄位建立合適的複合索引
2. 理解 LIMIT offset, rows 的掃描機制與偏移量成本
3. 深度分頁採用「子查詢定位主鍵 + 範圍查詢」延遲關聯模式
4. 根據實際業務分頁深度分佈，實施混合策略自動切換

掌握這些技巧，能讓你的分頁查詢在百萬、千萬級資料量下依然保持毫秒級響應。