MySQL 仅追加模型 - 使用 DELETE 清理查询

Question

目前正在将我们的表移至仅追加模型，通过避免 UPDATE 和 DELETE 来提高写入性能，并使用用于 SELECT 的 memcached 前端。

所有行都带有时间戳，并使用 MAX(timestamp) 选择最新行。这很有效，尽管过了一段时间表将充满旧的不相关数据，我们可以编写一个简单的

DELETE FROM table WHERE timestamp < XXXX 

尽管这会删除在过去 XX 时间内可能尚未更新的行，因此会从表中完全删除该 ID，而不仅仅是旧行。

下面提供了一个非常简单的示例架构和要演示的数据

---------------------------
| id        |   INT       |
| name      |   VARCHAR   |
| timestamp |   TIMESTAMP |
---------------------------

初始数据

-------------------------------------------
| id        |   name       |  timestamp   |
-------------------------------------------
| 1         |   Trevor     |            1 |
| 2         |   Mike       |            1 |
-------------------------------------------

如果更新用户名，将附加一行而不是更新用户的新名称。

-------------------------------------------
| id        |   name       |  timestamp   |
-------------------------------------------
| 1         |   Trevor     |            1 |
| 2         |   Mike       |            1 |
| 1         |   Trev       |           60 |
-------------------------------------------

使用简单的 DELETE 查询删除超过 60 秒的行(实际情况可能是一个小时甚至一天)会按预期删除第 1 行上的 Trevor，但它也会删除 Mike 的唯一记录。

-------------------------------------------
| id        |   name       |  timestamp   |
-------------------------------------------
| 1         |   Trev       |           60 |
-------------------------------------------

我们需要它只删除早于 XX 的不同 ID 行，因此即使 Mike 没有更新他的姓名并且他的时间戳早于 XX 时间，我们也会留下这两个用户。

-------------------------------------------
| id        |   name       |  timestamp   |
-------------------------------------------
| 2         |   Mike       |            1 |
| 1         |   Trev       |           60 |
-------------------------------------------

我们可以遍历每个 ID，获取最新时间戳，然后删除早于该时间戳的所有行，但是随着表获得更多用户，此过程将花费更长的时间。

是否有任何 SQL 查询可以(最好是在一个或两个查询中)如上所述清理表？

谢谢

Answer 1

我不是 MySQL 专家，但我相信这个查询应该可以解决问题:

DELETE t1 FROM
  table1 AS t1, table1 AS t2
WHERE 
  t1.id = t2.id
AND 
  t1.timestamp < t2.timestamp;

您可以将这 60 分钟添加到 t1.timestamp，这样它只会删除早于 60 分钟的行。

SQL Fiddle