php - MySQL删除大量行并重新插入新数据而不中断

Question

我有一个遗留 API 应用程序，它通过特定日期的 API 调用获取大量 JSON 数据。

为了用新信息更新数据库，我必须删除与该日期相关的所有行，然后循环并插入所有新行。每个日期大约有 1200 行。插入需要作为“循环”完成，因为 JSON 数据需要在每一行上完成额外的工作。

这种情况经常发生 - 大约每 5 分钟一次。

在此期间 - 有用户定期 24x7 访问当前数据。我收到报告，有时他们加载报告时没有数据，或者数据似乎不完整。

我有根据的猜测是他们正在删除和“重新加载”数据之间访问报告。我怎样才能确保这种情况无缝发生？

我需要知道的是数据库事务是否可以解决这个问题？我知道 DB Transactions 允许在操作期间发生故障时回滚 - 但它们是否也允许在一个事务中进行无缝删除和更新？

即像这样

try {
    $db->beginTransaction();

    $db->query('delete query');
    $db->query('insert query');

    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

Answer 1

首先，你需要使用InnoDB。 MyISAM 不处理除 LOCK TABLE 之外的任何类型的事务，这会扰乱您的用户。

其次，确保您的表已正确建立索引，这样您的 DELETE 查询就不会太慢了——也就是说，不会进行全表扫描。可能这意味着索引一些 DATE 或 DATETIME 字段。

第三，不要像这样删除:

DELETE FROM table WHERE DATE(timestampcol) = '2014-01-01'

改为这样做:

DELETE FROM table WHERE timestampcol  = '2014-01-01'

或者，如果您的 timestampcol 包含日期和时间(即，它包含非午夜时间)，请确保您可以像这样使用索引:

DELETE FROM table WHERE timestampcol >= '2014-01-01'
                    AND timestampcol < '2014-01-01' + INTERVAL 1 DAY

第五，一定要为这个操作使用事务。你的问题基本上是正确的。

第六，有什么方法可以UPDATE 行而不是DELETE 和INSERT 它们？您能否在不破坏用户读取的数据完整性的情况下逐行完成这项工作？如果可以，这可能是一种“在用户眼皮底下”更改日期的方法，而不会让他们在交易完成时等待。

第七:这更难:您可以使用分区表，并在您更新的那一天换出分区。如果每个分区只有 1200 行，这对于您正在做的事情来说可能是巨大的矫枉过正。但它会以编程和系统管理员麻烦为代价进行扩展。