如何原子性地将行从一张表移动到另一张表？

Question

我正在从数千个传感器收集读数并将它们存储在MySQL数据库中。每秒有几百个插入操作。为了提高插入性能，我先将这些值存储到MEMORY缓冲表中。每分钟，我运行一个存储过程将插入的行从内存缓冲区移动到永久表中。

基本上，在我的存储过程中，我想要执行以下操作，从临时缓冲区中移动行。

INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;

很遗憾，之前的方法不可用，因为数据收集过程在上述INSERT和DELETE之间向"data_buffer"插入了额外的行。因此这些行将在未被插入到"data"表中的情况下被删除。

我该如何使操作具有原子性或使DELETE语句仅删除在前面语句中被SELECT和INSERT选择的行？

如果可能的话，我希望以标准方式执行此操作，以便在不同的数据库引擎上运行。

由于性能开销和存储要求较高，我不想添加任何额外的"id"列。

希望标准SQL中有SELECT_AND_DELETE或MOVE语句或类似的东西……

- snap

---

你能提供data_buffer表的结构吗？ - Darius Kucinskas

创建数据表 data_buffer ( time int(11) NOT NULL, sensor smallint(6) NOT NULL, value float NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1; - snap

我有一个MySQL特定的解决方案，但似乎在8小时过去之前我不能在答案部分发布它。我真的很讨厌stackoverflow中的这些限制... - snap

如何设置一个行ID，在插入记录之前获取最大值，然后删除ID小于等于max(id)的记录？ - niktrs

@niktrs，这是一个聪明的想法！由于不需要的id列，可能会有一些额外的开销，但另一方面，在任何DB引擎上都应该以相同的方式实现（除了定义AUTO_INCREMENT列的语法）。太糟糕了，你没有在答案部分发布它。 :) - snap

Answer 1

我认为这个方法可以达到效果，但会一直阻塞直到插入完成。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO data (SELECT * FROM data_buffer FOR UPDATE); 
DELETE FROM data_buffer; 
COMMIT TRANSACTION;

- Sherif elKhatib

---

不行。"SELECT FOR UPDATE" 仅锁定所选行，但不能防止插入新行。 - snap

抱歉。实际上，它可以在MEMORY表中工作（如果转换为单个事务），因为它们目前仅支持表级锁定。我宁愿不依赖当前存储引擎的特性，谁知道MySQL的下一个版本是否会为内存表实现行级锁定，或者有人决定将临时表放入NDB内存表中（支持行级锁定）。 - snap

是的。谢谢！编辑后的修订版可以工作，并且不依赖于MySQL存储引擎的限制。但它并不完美，因为在过程运行时会阻止所有新的插入到"data_buffer"中。 - snap

是的，它会阻止提交，但不完全阻止。只是阻止了提交，所以插入操作仍然可以正常工作。 - Sherif elKhatib

Answer 2

如果有一个行ID，获取插入前的最大值，进行插入，然后删除记录<= max(id)，这个方案怎么样？

- niktrs

Answer 3

避免所有这些问题并保持快速的一种可能的方法是使用两个数据缓冲区表（我们称之为data_buffer1和data_buffer2）; 当收集过程插入到data_buffer2时，您可以在data_buffer2上执行insert和delete操作; 然后您切换，因此收集的数据进入data_buffer2，而从data_buffer1插入+删除数据进入data。

- Erich Kitzmueller

---

最终，我实现了这个解决方案的变体：我在自己的答案中写到的RENAME TABLE解决方案。但是由于它基于这个答案中的想法，我选择这个答案作为我的采纳答案。在我的情况下，使用交替表是最好的解决方案，因为没有额外的记录-保留ID列的开销，并且传感器的插入不会由于锁定而被阻止。 - snap

Answer 4

这是与@ammoQ答案类似的解决方案。不同之处在于，您可以通过在计划过程中透明地交换表格来代替INSERT过程找出要写入哪个表格。

使用RENAME在计划过程中交换表格：

CREATE TABLE IF NOT EXISTS data_buffer_new LIKE data_buffer;
RENAME TABLE data_buffer TO data_buffer_old, data_buffer_new TO data_buffer;
INSERT INTO data SELECT * FROM data_buffer_old;
DROP TABLE data_buffer_old;

这是因为RENAME语句以原子方式交换表，因此INSERT进程不会因“找不到表”而失败。但这仅适用于MySQL。

- snap

Answer 5

我假设这些表是相同的，具有相同的列和主键？如果是这样，你可以在where子句中嵌套选择...像这样：

DELETE FROM data_buffer 
WHERE primarykey IN (SELECT primarykey FROM data)

- Andrew

---

如果有"primarykey"，这个代码就能够运行。目前因为其他用途不需要，所以没有。然而，由于data表中有大量的行，我认为这样很慢。 - snap

Answer 6

这是一个针对MySQL的解决方案。您可以使用锁定来防止INSERT进程在您移动行时添加新行。

移动行的过程应如下：

LOCK TABLE data_buffer READ;
INSERT INTO data SELECT * FROM data_buffer;
DELETE FROM data_buffer;
UNLOCK TABLE;

应更改向缓冲区插入新行的代码如下：

LOCK TABLE data_buffer WRITE;
INSERT INTO data_buffer VALUES (1, 2, 3);
UNLOCK TABLE;

当锁定被放置时，INSERT过程显然会被阻塞。

- snap

---

看起来这个解决方案是错误的，因为无法像(http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html)中描述的那样在存储过程中使用LOCK TABLES。不幸的是，我不能对自己的答案进行投票。 :) - snap