MySQL 深分頁優化方案：臨時表緩存 ID 列表 + IN 查詢替代 OFFSET 掃描

痛點：大偏移量 LIMIT 災難

SELECT * FROM huge_table WHERE condition ORDER BY id LIMIT 1000000, 20;

MySQL 需掃描並丟棄前 1,000,000 行，**IO 與 CPU 雙重浪費**，深分頁極慢。

優化思路：空間換時間，預計算 ID 集合

1. 首次請求：SELECT id FROM huge_table WHERE condition ORDER BY id LIMIT 1000000（只取 ID，覆蓋索引極快）。
2. 緩存 ID 列表：
   • 方案 A：MySQL 臨時表 CREATE TEMPORARY TABLE page_cache (page_no INT, ids TEXT)，分頁號存儲對應 ID 列表（逗號分隔）。
   • 方案 B：應用層 Session/Redis 存儲 List allIds，按頁切片。
3. 後續翻頁：直接 SELECT * FROM huge_table WHERE id IN (ids_of_page_N)，**主鍵精確點查，毫秒級返回**。
4. 過期清理：臨時表加 created_at 定時清理；Session 靠超時自動失效；Redis 設 TTL。

關鍵細節

• 臨時表僅會話可見，連接斷開自動銷毀，無需手動清理。
• ID 列表長度受 max_allowed_packet 限制，超大分頁可分批 IN 或用 JOIN 臨時表。
• 數據變更導致 ID 列表失效：適用於「寫入頻率低、讀取極高、允許略微不一致」的報表/列表場景**。強一致性場景勿用。