MySQL:在事务中截断表?

2022-01-06 00:00:00 transactions mysql truncate

我有一个 InnoDB 表，需要在 60k 到 200k 记录内每十分钟重新填充一次.到目前为止，我们的方法如下:

I have an InnoDB table that needs to be re-populated every ten minutes within anywhere from 60k to 200k records. Our approach up to this point has been as follows:

  1. 关闭自动提交
  2. 截断表格
  3. 执行选择查询附加计算(使用 PHP)
  4. 插入新记录
  5. 提交

执行截断操作后，数据会立即删除，并且不再可从用户界面访问.对于我们的用户来说，这非常令人不安，即使在大约 30 秒左右的时间内脚本遇到了 Commit 操作并重新填充了表.

After the Truncate operation is performed though, the data is immediately deleted, and is no longer available from the User Interface. To our users, this has been pretty disconcerting, even though within about 30 seconds or so the script encounters the Commit operation and the table is repopulated.

我想也许我可以将整个操作(包括Truncate)包装在一个事务中，这可能会减少表对用户来说是空的.所以我将 SET AUTOCOMMIT=0 改为 START TRANSCATION.

I thought that perhaps I could wrap the whole operation, including the Truncate, in a transaction, and that this might cut down on the length of time during which the table appears empty to users. So I changed SET AUTOCOMMIT=0 to START TRANSCATION.

哎呀！这与预期的效果相反！现在 TRUNCATE 操作仍然发生在脚本的开头，但是在事务中实际执行 INSERT 操作需要更长的时间，所以到 COMMIT 操作发生并且表中的数据再次可用时，已经将近十分钟！

Yikes! This had the opposite of the desired effect! Now the TRUNCATE operation still occurs at the beginning of the script, but it takes much longer to actually execute the INSERT operations within the transaction, so that by the time the COMMIT operation takes place and the data in the table is available again, it has been nearly ten minutes!

什么可能导致这种情况?老实说，我根本没想到有任何变化，因为我的印象是启动事务基本上只是关闭了Autocommit??

What could possibly cause this? Truthfully, I wasn't expecting any change at all, because I was under the impression that initiating a transaction basically just turns off Autocommit anyway??

推荐答案

实现此目的的更好方法可能是将数据插入新表中，然后使用 重命名 在两个表上以交换它们.交换只需要一次重命名，这是一个原子操作，这意味着用户甚至无法检测到它发生了，除了显示的新数据.然后您可以截断/删除旧数据.

A better way to accomplish this might be to insert the data into a new table, and then use rename on both tables in order to swap them. A single rename is all that's needed for the swap, and this is an atomic action, which means the users won't even be able to detect that it happened, except for the new data showing up. You can then truncate/delete the old data.

相关文章