删除旧记录 - MySQL

Question

我目前正在寻找解决一个基本问题的方案：删除旧记录。

为了说明情况，我有一张表，称之为table1，有少量记录。通常情况下，它是空的，因为它用于中转消息。这些消息在添加到数据库后的两秒内被读取并删除，以便不再读取。

然而，如果接收table1消息的客户端离线，一些消息可能会变成未完成状态，有时数百条，有时数千条，甚至数十万条以上。

这不仅会损害客户端的性能，因为它必须处理大量的消息，而且也会对数据库产生影响，因为它应该保持最少量的记录。

考虑到客户端每秒都会检查新消息，那么删除旧记录的最佳方法是什么？我考虑过添加时间戳，但这样做会影响性能：插入时必须计算时间戳。我尝试过这种方法，但所有查询最终都进入了慢查询日志。

最好的解决方案是什么？我考虑过像检查过去5秒内是否更改了表，如果没有，我们可以确定应该中转的所有消息已经中转完毕，然后可以清除。但是如何实现？

我考虑过每隔几分钟运行一次事件，但我不确定如何实现对select/insert/delete查询没有（或无意义的）影响的内容。

PS：当我发现有些客户端离线，并且有800万条未完成消息时，出现了这种情况。

编辑：

我忘记提到存储引擎是MEMORY，因此所有记录都保存在RAM中。这就是我想要摆脱这些记录的主要原因：因为数百万个不该存在的记录保存在RAM中，会影响系统资源。

以下是错误日志的摘录：

# Query_time: 0.000283  Lock_time: 0.000070 Rows_sent: 0  Rows_examined: 96
SET timestamp=1387199997;
DELETE FROM messages WHERE clientid='100';
[...]
# Query_time: 0.000178  Lock_time: 0.000054 Rows_sent: 0  Rows_examined: 96
SET timestamp=1387199998;
DELETE FROM messages WHERE clientid='14';

所以我想他们确实有一个很小的延迟，但在MySQL中有意义吗？我的意思是，在“现实生活”中，0.0003可能会因其微不足道而完全被忽略，那么对于大约10ms的ping和MySQL连接也可以这样说吗？

- Nuno

---

通常，您可以使用时间戳来完成此操作。在插入记录时添加时间戳，并进行清理过程以删除所有早于N秒的记录。我真的很想知道插入时间戳为什么会变慢。它可能只是一个Unix时间戳，很容易进行比较。 - Bart Friederichs

为什么要使用 SET TIMESTAMP=something？MySQL 在 SQL 语句中通过 NOW() 访问了一个完美的内部时间戳设置。此外，您的第一次查询时间是 283 纳秒，锁定时间（独占访问时间）为 70 纳秒。这似乎不是很多。 - O. Jones

那些 SET TIMESTAMP 是自动的，我没有自己执行任何 SET TIMESTAMP 查询。 - Nuno

Answer 1

你的问题很有趣，但缺乏详细信息，所以我只能给出一些一般性的观点。

首先，已经存在许多消息队列解决方案，可以直接满足您的需求。它们隐藏了数据存储、清理等底层实现，并允许您专注于应用程序逻辑。RabbitMQ是一个流行的开源选项。

其次，除非您使用的是受限制的硬件，在大多数情况下，在MySQL表中有数十万条记录并不是性能问题，插入时生成时间戳也不是问题。因此，我建议构建一个明显且简单的解决方案（因此错误更少）-在您的消息表中添加一个时间戳列，并找到一种删除早于5分钟的消息的方法。您可以将其添加到传递后清除记录的逻辑中。只要您的查询命中索引列，我认为您不必担心数十万条记录。

我会投入一些精力创建一个性能测试套件，让您可以尝试解决方案并查看哪个更快。这可能有点棘手，特别是如果您想测试具有多个客户端的情况，但通过处理这些场景，您将学到更多关于应用程序的性能特征。

编辑：

您可以在表中设置一个列自动设置时间戳值 - 我始终发现这非常快。就像 - 在非常大的表格（数千万行）上从未成为问题。

我对内存存储引擎没有太多经验 - 但是MySQL文档建议数据修改操作（例如插入、更新或删除）可能由于锁定时间而变慢 - 这在您的统计数据中得到了证实，其中锁定时间约占总时间的30%。

- Neville Kuyt

---

数十万的问题在于它们存储在使用MEMORY引擎的表中，所以越少越好，特别是因为它们是由错误引起的，因此并不是有意的。如果出现故障导致数据库服务器占用了数百MB的空间，那么这只会增加不必要的负担。至于时间戳，我也不知道它们需要花费多少时间，但既然它们出现在慢查询日志中，我想它们确实需要时间（尽管它们只需要0.2毫秒，为什么它们会出现？或者说这被认为是“很多”吗？）。 - Nuno

请更新问题并提供这些额外的信息 - 这将帮助您获得更好的答案。 - Neville Kuyt

我会运行一些人工测试，看看InnoDB是否更快，时间戳是否有任何影响，并汇报结果。 - Nuno

Answer 2

我遇到过类似的问题。

首先，有几个问题需要澄清。未送达的消息应该在系统中停留多久？永远？一天？十秒钟？

其次，错误删除未送达的消息会导致什么后果？会导致全球银行系统崩溃吗？会导致医院病人无法接受所需注射吗？还是后续的消息只是覆盖了缺失的消息？

最好的情况是停留时间短且错误后果低。如果错误后果很严重，这样做都不明智。

对我来说，设置解决方案需要几个步骤。

首先，编写一些代码从消息表中获取最大ID。

 SELECT MAX(message_id) AS max_message_id FROM message

然后，一个小时后，或者十秒钟，或者一天，或者其他时间，删除所有消息的ID号小于上次运行时记录的ID号。

 DELETE FROM message WHERE message_id <= ?max_message_id

如果一切正常运行，就不需要删除任何内容。但是如果您有许多过时的消息针对一个已经消失的客户端，那么它们将被删除。

最后，在投入生产之前，请等待系统的安静时刻，并且只需一次发出命令即可。

  TRUNCATE TABLE message

清理表内的任何旧垃圾。

您可以通过创建一个小的单行，单列表来存储max_message_id来使用事件（MySQL数据库中的存储作业）进行此操作。

编辑

您还可以更改表格以添加message_time列，以这样的方式自动设置每次插入行时。在系统空闲且您可以扔掉所有现有消息的时候同时发出这三个语句。

   TRUNCATE TABLE message; 
   ALTER TABLE message ADD COLUMN message_time TIMESTAMP 
                                  NOT NULL 
                                  DEFAULT CURRENT_TIMESTAMP;
   ALTER TABLE message ADD INDEX message_time (message_time);

那么您只需使用一个语句来清除旧记录，就像这样。

   DELETE FROM message WHERE message_time <=  NOW() - INTERVAL 1 HOUR

（或者适当的间隔）。您应该绝对修改空表或几乎为空的表，因为修改大量行需要时间。

这是一个好的解决方案，因为有可能您根本不需要修改消息处理客户端代码。（当然，如果您在任何地方使用了 SELECT *，那么您可能需要进行修改。专业提示：永远不要在应用程序代码中使用 SELECT *。）

- O. Jones

---

如果获取最大消息 ID 的查询未返回记录，则需要一些条件逻辑。 - Dan Bracuk

@DanBracuk，没错。 - O. Jones

错误后果较低，由于消息应该在添加后不到两秒钟被捕获，因此它们只应该保留几秒钟。此外，我考虑添加一个事件，而不是存储值，仅检查行数是否高于例如1000，如果是，则删除ID低于900的消息。 ID大于900的消息将是最新的，并将被保留。这在实践中可行吗？（经过测试后，值将被调整以更有意义） - Nuno