MySQL 分頁查詢優化：從全表掃描到 ID 緩存分頁

MySQL 分頁查詢優化：從全表掃描到 ID 緩存分頁

復雜條件分頁 WHERE ... ORDER BY ... LIMIT m,n 在大數據量下極其低效：MySQL 必須全表掃描找出所有符合條件的行，排序後再截取第 m 到 n 行。若用戶翻到末頁，IO 與 CPU 消耗巨大。

優化思路：應用層緩存符合條件的主鍵 ID 列表

將「查找符合條件的 ID」與「按 ID 取詳情」分離，利用 ID 列表實現 O(1) 翻頁。

實施方案

1. 首次搜索：
   SELECT id FROM table WHERE condition ORDER BY field LIMIT max_pages * page_size;
   只取主鍵 ID，限制最大緩存行數（如前 100 頁）。
2. 緩存 ID 列表：
   方案 A（MySQL 臨時表）：創建臨時表 search_cache_，字段 page_1, page_2, ... 存各頁 ID 列表（逗號分隔），帶 created_at 字段定期清理過期緩存。
   方案 B（Session/文件）：PHP $_SESSION['search_ids'][token][page] = $ids，或序列化到文件。需為並發搜索生成唯一 token（時間戳+隨機數）避免覆蓋。
3. 後續翻頁：
   直接讀取對應頁的 ID 列表：
   SELECT * FROM table WHERE id IN (id1,id2,...);
   主鍵查詢極快，避免全表掃描與排序。
4. 緩存清理策略：
   定時任務刪除 created_at > 30min 的臨時表/Session 文件；或按比例隨機清理（如 1% 請求觸發掃描）。

進階優化

• 合併步驟 1 和 2A：SELECT ... INTO OUTFILE ... 或 INSERT INTO cache_table SELECT ... 直寫緩存。
• 其他數據庫（SQL Server、Oracle）提供遊標/結果集緩存機制，原理同理。
• 若業務極度依賴深度翻頁與復雜查詢，考慮引入 Elasticsearch / Solr 等搜索引擎替代 MySQL 分頁。

注意事項

• ID 列表緩存適用於「讀多寫少、數據相對靜態」場景；高頻寫入業務需評估緩存失效策略。
• IN 列表過長（>1000）可能影響性能，分批查詢或使用臨時表 JOIN。
• 臨時表需注意權限與並發創建衝突，建議表名含唯一 token。