MySQL 分頁查詢優化策略：從 OFFSET 陷阱到 ID 緩存方案

問題：深分頁效能崩潰

SELECT * FROM table WHERE condition ORDER BY col LIMIT 100000, 20;

MySQL 必須掃描前 100,000 筆符合條件的記錄再丟棄，隨著偏移量增大，I/O 與 CPU 暴增，深分頁（如第 5000 頁）幾乎不可用。

優化思路：記住「符合條件的 ID 清單」

將「查詢條件」與「分頁邏輯」分離：

1. 先取得所有符合條件的 主鍵 ID 列表（輕量、可索引覆蓋）。
2. 將 ID 列表暫存（Session、暫存表、Redis）。
3. 分頁請求時，直接按 ID 列表切片，再 WHERE id IN (...) 精準回補完整欄位。

方案 A：MySQL 暫存表

-- 1. 建立暫存表，記錄查詢批次 token
CREATE TEMPORARY TABLE tmp_page_ids (
    token CHAR(32),
    page_no INT,
    ids TEXT,  -- 逗號分隔的 ID 字串
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (token, page_no)
);

-- 2. 首次查詢：取得所有符合條件的 ID，按每頁 N 筆分組寫入暫存表
INSERT INTO tmp_page_ids (token, page_no, ids)
SELECT 'abc123', FLOOR((@rownum:=@rownum+1)/20), GROUP_CONCAT(id)
FROM (SELECT @rownum:=0) r, (SELECT id FROM big_table WHERE condition ORDER BY col) t
GROUP BY FLOOR((@rownum)/20);

-- 3. 後續分頁：直接讀取對應 page_no 的 ids
SELECT * FROM big_table WHERE id IN (SELECT ids FROM tmp_page_ids WHERE token='abc123' AND page_no=5);

優點：資料在 MySQL 內部，無網路傳輸；缺點：暫存表隨連線消失，長連線或連線池需自行清理。

方案 B：應用層 Session/Redis 快取

# PHP 伪代碼
$token = md5(serialize($condition) . $user_id);
$ids = $redis->get("page_ids:$token");
if (!$ids) {
    $ids = $db->query("SELECT id FROM big_table WHERE $condition ORDER BY col")->fetchAll(PDO::FETCH_COLUMN);
    $redis->setex("page_ids:$token", 1800, implode(',', $ids)); // 30 分鐘過期
}
$page_ids = array_slice(explode(',', $ids), $offset, $limit);
$rows = $db->query("SELECT * FROM big_table WHERE id IN (?)", [implode(',', $page_ids)]);

優點：跨請求、跨伺服器共享；缺點：需序列化/反序列化、記憶體佔用大（百萬 ID 約 10-20MB）。

進階優化：游標式分頁

若不需「跳轉任意頁」，改用 Keyset Pagination：

-- 第一頁
SELECT * FROM table WHERE condition ORDER BY id LIMIT 20;

-- 下一頁（用上一頁最大 id）
SELECT * FROM table WHERE condition AND id > 12345 ORDER BY id LIMIT 20;

• 無 OFFSET 掃描，效能恆定。
• 適合無限捲動、API 列表、資料同步。
• 不支援隨機跳頁。

清理策略

• 暫存表：啟動事件 DELETE FROM tmp_page_ids WHERE created_at < NOW() - INTERVAL 1 HOUR。
• Redis：設定 TTL 自動過期。
• Session：PHP session.gc_maxlifetime 配合垃圾回收。

總結建議