MySQL 分頁查詢優化：從 LIMIT 深分頁痛點到 ID 緩存機制的進化之路

前言：深分頁的效能陷阱

SELECT * FROM t WHERE ... ORDER BY x LIMIT 100000, 20 —— 當 OFFSET 達到十萬、百萬級，MySQL 必須掃描並捨棄前 10 萬行，I/O 與 CPU 雙重暴擊。隨著資料增長、用戶翻閱末頁，系統將陷入「磁碟狂轉、回應超時」的惡性循環。

核心問題剖析

• 全量掃描後截取： MySQL 先按條件檢索所有符合行、排序、再捨棄 OFFSET 行，最後取 LIMIT 行。
• 索引失效： 複雜 WHERE + ORDER BY 往往導致無法用索引排序，退化為 filesort + 臨時表。
• 記憶體壓力： 大結果集排序需大量 sort_buffer，不足則落磁碟臨時表。

優化演進路線圖

階段 1：延遲關聯—— 經典折衷

-- 先只取主鍵 ID，再回表
SELECT a.* FROM t a
JOIN (SELECT id FROM t WHERE ... ORDER BY x LIMIT 100000, 20) b
USING (id);

• 優點：子查詢只掃描索引（Covering Index），不回表，大幅減少 I/O。
• 缺點：OFFSET 仍需掃描前 N 行索引條目，深分頁仍慢。

階段 2：游標/Keyset 分頁—— 無 OFFSET 設計

-- 首頁
SELECT * FROM t WHERE ... ORDER BY x LIMIT 20;

-- 記住最後一行的排序鍵值
-- 次頁
SELECT * FROM t WHERE ... AND x > 'last_x_value' ORDER BY x LIMIT 20;

• 優點：無 OFFSET，索引範圍掃描極快，適合「無限滾動」、「下一頁」場景。
• 缺點：不支援隨機跳頁、排序鍵需唯一或含主鍵防重複。

階段 3：ID 緩存機制—— 兼容跳頁的進階方案（本文核心）

針對「複雜條件 + 需支援任意跳頁 + 深分頁」場景，提出兩階段查詢 + 會話級 ID 緩存：

步驟 1：首次查詢提取所有符合條件的 ID（或前 N 頁上限）

SELECT id FROM t WHERE $condition ORDER BY $field LIMIT $max_pages * $page_size;

• 只掃描索引，速度極快。
• 結果集僅含整數 ID，記憶體佔用極小（百萬 ID 約 8 MB）。

步驟 2：將 ID 陣列按頁切片，存入會話/快取

# PHP 陣列範例
$ids = [];  // 從步驟1取得的所有 ID
$page_ids = array_chunk($ids, $page_size);  // 切成 $page_ids[0]..[$max_pages-1]

# 存入 Session（檔案/Redis/Memcached）
$_SESSION['search_ids'][$search_token] = [
    'ids'   => $page_ids,
    'time'  => time(),
    'cond'  => $condition_hash   // 防止不同查詢互串
];

• search_token： 獨特標識（隨機串/雜湊），支援多視窗並發查詢不互覆蓋。
• TTL 清理： 設定過期時間（如 30 分鐘），或定時掃描清理過期 token。

步驟 3：後續分頁請求直接 IN 查詢

$ids_str = implode(',', $_SESSION['search_ids'][$token]['ids'][$page_no]);
$sql = "SELECT * FROM t WHERE id IN ($ids_str) ORDER BY FIELD(id, $ids_str);";
# ORDER BY FIELD 保持原序，避免 IN 亂序

• 單次查詢僅 20 行主鍵點查，毫秒級回應，與 OFFSET 無關。
• 總查詢時間 = 首次全量 ID 查詢 + 後續 N 次 IN 查詢，平均成本極低。

步驟 4：自動清理策略

• Session 層：gc_maxlifetime 或自訂掃描器清理過期 token。
• 資料庫層（若用臨時表）：建立 search_cache(token, page_no, ids_csv, created_at)，定期 DELETE WHERE created_at < NOW() - INTERVAL 1 HOUR。

進階優化：合併步驟 1+2

CREATE TEMPORARY TABLE tmp_search_ids (
    token VARCHAR(64),
    page_no INT,
    ids_csv TEXT,
    PRIMARY KEY(token, page_no)
) ENGINE=MEMORY;

INSERT INTO tmp_search_ids
SELECT $token, FLOOR((@rownum:=@rownum+1-1)/$page_size), GROUP_CONCAT(id)
FROM (SELECT id FROM t WHERE $condition ORDER BY $field LIMIT $max_pages*$page_size) t1
JOIN (SELECT @rownum:=0) t2
GROUP BY FLOOR((@rownum-1)/$page_size);

• 單條 SQL 完成「抽 ID → 分頁 → 存暫存表」，效能最強。
• MEMORY 引擎全記憶體操作，微秒級讀寫。

適用性邊界與替代方案

結語

MySQL 原生 LIMIT OFFSET 在深分頁面前不堪一擊。透過「先取 ID、後取詳情」的兩階段設計，配合會話級緩存，能將深分頁查詢從秒級降至毫秒級，且完美支援任意跳頁。這是應用層協作資料庫的經典範例——有時最好的索引，是不查索引。

1. 上一篇文章： MySQL.sock 連線失敗徹底排查：多種修正路徑與 PHP/phpMyAdmin 設定指南
2. 下一篇文章： Slackware 10.1 下 MySQL 徹底支援中文：從編譯安裝到 utf8mb4 現代化配置指南

MySQL 資料庫相關知識

MySQL 聯合索引實戰：員工-部門多對多關係防重複插入

業務場景：員工與部門多對多，中間表 employee_dep

MySQL 字符串截取函數大全：LEFT、RIGHT、SUBSTRING、SUBSTRING_INDEX 實戰指南

核心函數速查表 函數語法說明示例結果 LEFTLEFT(s

MySQL INNER JOIN 核心語法與實戰範例：多表關聯查詢完全指南

INNER JOIN 是關聯式資料庫最核心的多表查詢操作，僅

MySQL-Proxy入門（一）：架構定位、核心功能與讀寫分離原理圖解

MySQL-Proxy位於MySQL客戶端與伺服器之間的中介