MySQL ORDER BY + LIMIT 分頁查詢資料遺失問題根因與解決方案

MySQL ORDER BY + LIMIT 分頁查詢資料遺失問題根因與解決方案

使用 ORDER BY timestamp LIMIT offset, size 分頁時，若排序欄位存在相同值，可能導致資料重複或遺失。本文複現問題、分析根因並給出最佳實踐。

問題複現環境

• MySQL 5.6.11
• 表結構：id (PK), pay_time (INT, indexed), flag (TINYINT)
• 5000 筆測試資料，大部分 flag=0，部分 pay_time 相同

錯誤查詢

SELECT id, pay_time FROM order_customer_new
WHERE flag=0 ORDER BY pay_time ASC, id ASC
LIMIT 250, 10;

現象：pay_time 相同時，相同記錄在相鄰兩頁出現或消失（如 id=465 消失、id=466 重複）。

根因分析

1. 排序不穩定：當 ORDER BY 欄位值相等，MySQL 不保證輸出順序（未顯式指定 tie-breaker）。
2. 分頁機制：LIMIT offset, size 先排序再截取。若排序順序在兩次查詢間不一致，同一筆資料可能落在不同頁，造成「遺失」或「重複」。
3. 這不是 Bug，而是 SQL 標準未定義相等值順序；InnoDB 可能因頁分裂、並發插入導致物理順序變化。

解決方案：強制確定性排序

在 ORDER BY 追加唯一鍵（主鍵或唯一索引欄位）作為第二排序鍵：

SELECT id, pay_time FROM order_customer_new
WHERE flag=0
ORDER BY pay_time ASC, id ASC   -- 唯一鍵保證全域順序唯一
LIMIT 250, 10;

加上 id ASC 後，相同 pay_time 依 id 決定順序，分頁結果穩定，不再遺失/重複。

最佳實踐清單

• 分頁查詢 永遠 在 ORDER BY 最後加上主鍵/唯一鍵。
• 避免使用 OFFSET 深分頁（大 offset 效能差），改用游標分頁：WHERE id > last_id ORDER BY id LIMIT 10。
• 若業務允許，pay_time 改為 DATETIME(3) 或 BIGINT 毫秒時間戳，減少碰撞機率。
• 使用 EXPLAIN 確認是否使用索引排序（Using index / 無 Using filesort）。

總結

分頁查詢的「資料遺失」本質是排序不確定性。加上唯一鍵作為 tie-breaker，是零成本、高可靠的標準解法。