MySQL 分頁查詢數據丟失問題深度分析與解決方案

MySQL 分頁查詢數據丟失問題深度分析與解決方案

使用 LIMIT offset, size 進行大表分頁時，若並發存在數據刪除，會導致**數據漏讀**（丟失）或**重複讀取**。本文通過具體案例剖析根因，並提供基於**游標/記憶最大值**的穩健解決方案。

一、問題復現場景

• 表 system_user 約 400 萬行
• 需導出所有 userid，分頁大小 20,000
• 代碼邏輯：

for (curPage = 1; ; curPage++) {
    offset = (curPage - 1) * pageSize;
    sql = "SELECT userid FROM system_user ORDER BY userid ASC LIMIT " + offset + "," + pageSize;
    rows = fetchAll(sql);
    if (empty(rows)) break;
    foreach (rows as row) process(row['userid']);
}

二、根因分析：OFFSET 機制與數據變動的競態

假設初始數據：u1, u2, u3, u4, u5, u6, u7, u8, u9, u10（10行），每頁 5 行。

本質：OFFSET 是「跳過前 N 行」。當前 N 行發生刪除，原本屬於第 2 頁的數據（u6,u7）前移至第 1 頁位置，被跳過導致丟失。

三、解決方案：游標式分頁

核心思想：**記住上一頁最大值，下一頁查「大於該值」的數據**，而非「跳過固定行數」。

改進代碼

$pageSize = 20000;
$pageMaxId = 0;  // 記錄上一頁最大 userid

while (true) {
    // 關鍵：WHERE userid > 上一頁最大值
    $sql = "SELECT userid FROM system_user WHERE userid > {$pageMaxId} ORDER BY userid ASC LIMIT {$pageSize}";
    $rows = $systemUserTable->fetchAll($sql);
    
    if (empty($rows)) break;
    
    foreach ($rows as $row) {
        $userid = $row['userid'];
        // 更新最大值
        if ($userid > $pageMaxId) $pageMaxId = $userid;
        echo $userid . "\r\n";
    }
}

執行流程對比

結果：零丟失、零重複，完美解決並發變動導致的分頁異常。

四、方案適用條件與優化建議

用戶翻頁場景替代方案

1. 延遲關聯：SELECT * FROM tbl WHERE id IN (SELECT id FROM tbl ORDER BY id LIMIT offset, size)
2. 覆蓋索引子查詢：SELECT * FROM tbl WHERE id >= (SELECT id FROM tbl ORDER BY id LIMIT offset, 1) ORDER BY id LIMIT size
3. Redis 緩存頁碼：熱門列表首頁緩存，深分頁引導搜索/篩選
4. 禁止深分頁：UI 限制最大頁碼（如 100 頁），超出引導條件查詢

五、總結

• LIMIT offset, size 在並發寫入環境下**非安全分頁**，會導致數據丟失/重複
• 游標式分頁（WHERE id > last_max_id LIMIT size）是**流式處理、數據同步、全量導出**的黃金標準
• 關鍵前提：排序列唯一、有序、單向遍歷
• 用戶交互式分頁請採用延遲關聯或業務妥協方案