Friday, March 10, 2023

[MYSQL][PHP] 如何不影響使用者操作 完成大資料表的更新

有些資料表需要每日更新,而更新過程需要一段時間,一般的作法是刪掉舊資料,然後寫入新資料,過程中使用者可能會因為舊資料被刪掉又還沒寫入新資料,導致系統產生錯誤。

為了避免發生這種情形,較好的更新資料策略為:

步驟一、 建立臨時表
CREATE TABLE mydb.mytable_temp LIKE mydb.mytable

步驟二、 寫入臨時表
$pdo->beginTransaction();
迴圈 {
  $sql = "insert into ...";
  $pdo->exec($sql);
}
$pdo->commit();

步驟三、 刪除正式表
DROP TABLE mydb.mytable

步驟四、 更名臨時表為正式表
RENAME TABLE mydb.mytable_temp TO mydb.mytable

步驟五、 最佳化
OPTIMIZE TABLE mydb.mytable

備註:
1. 大量資料的寫入 建議先Transaction,最後在一次commit,速度快非常多。
2. 最佳化建議放在最後,因為最佳化需要時間,有試過把最佳化放在第三步驟,但最佳化還沒完成,程式就會繼續往下執行,導致產生錯誤。
3. 如果要檢查資料表是否有更新可使用下列語法:
SELECT IFNULL(update_time, create_time) AS dt FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name = 'mytable'

No comments:

Post a Comment