PHP + MySQL 查詢優化：LIMIT 深分頁效能陷阱與解決方案

經典陷阱：深分頁掃描

資料表 test 有 30 萬筆，主鍵 id 為自增索引。執行：

SELECT * FROM `test` ORDER BY id ASC LIMIT 208888, 50;

MySQL 必須掃描前 208,888 行再丟棄，僅取後 50 行。實測耗時 40-50 秒。

優化方案：利用索引範圍查詢

SELECT * FROM `test`
WHERE id BETWEEN 208838 AND 208888
ORDER BY id ASC;

實測耗時 0.06 秒，快 600 倍。

原理深度解析

B+ 樹索引的葉子節點透過雙向鏈表連結，WHERE id > x 可直接定位到葉子節點，沿鏈表向後取 N 行，無需掃描前段資料。

實務應用模式：游標式分頁

<?php
// 第一頁
$stmt = $pdo->query('SELECT * FROM test ORDER BY id ASC LIMIT 50');
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$lastId = $rows ? end($rows)['id'] : 0;

// 後續頁（前端傳 last_id）
$lastId = (int)($_GET['last_id'] ?? 0);
$stmt = $pdo->prepare('SELECT * FROM test WHERE id > ? ORDER BY id ASC LIMIT 50');
$stmt->execute([$lastId]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 回傳 rows + next_last_id
?>

優點：無 offset、效能恆定、支援無限滾動。缺點：無法直接跳到第 N 頁（需記錄每頁首尾 ID 或接受此限制）。

其他常見優化技巧

• 避免 SELECT *：只查需要欄位，減少 I/O、利用覆蓋索引。
• 複合索引最左前綴：INDEX (a, b, c) 支援 WHERE a=1、WHERE a=1 AND b=2，不支援 WHERE b=2。
• EXPLAIN 分析：關注 type（目標：ref/range/eq_ref）、rows、Extra（Using index/Using where/Using filesort）。
• 分頁總數優化：SELECT COUNT(*) 在大表極慢，改用估算值、快取總數、或不顯示總頁數（僅「上一頁/下一頁」）。
• 延遲關聯：需 JOIN 時，先用子查詢取主鍵 ID，再回表查詳情。

  SELECT t.* FROM test t
  JOIN (SELECT id FROM test WHERE ... ORDER BY id LIMIT 100000, 20) tmp USING (id);

索引維護建議

• 定期 ANALYZE TABLE 更新統計資訊。
• 監控 Handler_read_rnd_next（全表掃描計數）暴增。
• 使用 pt-query-digest 分析慢查詢日誌。
• 大表考慮分表/分庫/歸檔冷資料。

總結

「LIMIT 100000, 20 是效能殺手」——請改用 WHERE id > last_id LIMIT N（游標分頁）或 延遲關聯。配合 EXPLAIN 驗證執行計劃，讓每個查詢都跑在索引上，是 PHP + MySQL 高效能的核心心法。