MySQL 複雜查詢分頁優化：模擬存儲分頁機制避免全表掃描

痛點：LIMIT m,n 的深分頁效能崩塌

有索引時 LIMIT m,n 較快，但複雜條件（如 WHERE ... ORDER BY field1+field2）會導致 MySQL 全表掃描、產生臨時表、檔案排序，再取出 m,n 行。數十萬資料、通用關鍵字搜尋、使用者翻至末頁時，磁碟 I/O 飆升、回應極慢。

解決思路：將分頁結果集 ID 預存

核心邏輯：先查出符合條件的主鍵 ID 陣列，分頁時僅按 ID 列表精準回查，避免重複複雜排序。

實作步驟

1. 預查 ID 列表：

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

   可限制最大回傳行數，控制記憶體佔用。
2. ID 列表持久化（二選一）：
   • 方案 A：MySQL 臨時表，建立 page_1 ... page_n 欄位存分頁 ID，加 token (時間/隨機數) 做查詢標識，定時清理過期資料。
   • 方案 B：PHP Session，將二維陣列 $IDs[token][page_num] 存入 Session（檔案/記憶體），同樣需設定 TTL 清理。
3. 分頁精準回查：

   SELECT * FROM `table` WHERE id IN ($ids_csv);

   IN 列表查詢極快，且不受 OFFSET 影響。
4. 自動清理策略：臨時表加 created_at 定時刪除；Session 依賴 GC 機制或自訂 Cron 清理。
5. 進階優化：SELECT ... INTO TEMP TABLE 合併步驟 1/2，減少網路往返。

適用性與局限

• 針對 MySQL 補丁方案，期待原生支援 CURSOR / WINDOW FUNCTION。
• 其他資料庫（SQL Server/Oracle）已有更成熟分頁關鍵字（ROW_NUMBER()、OFFSET/FETCH）。
• 超大數據量（億級）建議遷移至支援分散式查詢的引擎。

拋磚引玉，歡迎共同探討更優雅的分頁架構。