mysql - 在 MariaDB/MySQL 中无锁地删除？`(InnoDB)

Question

据我所知，DELETE FROM ... WHERE 会锁定表。

现在，我在 MariaDB 中有一个巨大的 InnoDB 表，其大小为 1TB+，正在积极使用，并且在整个数据库中搜索要删除的行之前锁定它是不可能的。

有什么方法可以删除符合某些条件的行而不在删除发生时锁定它？

这里有一些关于这个案例的更多细节:

• 服务器正在运行来自 Debian Stretch 存储库的 MariaDB 10.1.22-3
• 服务器有 32 GB 内存和 innodb_buffer_pool_size = 20G
• 数据库的大小超过 1TB，并且始终有大量事件的 INSERT 和 SELECT
• 整个数据库只包含 2 个表:
  • 实际数据的一个表(基本上具有类似 data (BIGINT id, LONGTEXT data) 的结构(其中 data 是一大块 JSON。我知道这不是一个完美的关系数据库模型，但 JSON 来自第三方，它非常复杂，并且可能随时包含来自第三方的结构更改，恕不另行通知)
  • 还有一个用于某种“索引”的表以满足SELECT。 (简化示例，这可能具有类似 data_index (BIGINT id, INT userId, INT itemId, BIGINT timestamp) 的结构，因此我可以在 userId 和 itemId 上使用 SELECT，并且加入实际数据。(时间戳是以毫秒为单位的 unix 时间戳)
• 正如我所说，数据只能在有限的时间内存储。所以基本上现在我想创建一个每天运行一次的 cronjob 来删除超过 7 天的行。

为了完成任务，我自然会想出这个简单的查询:

DELETE `data`, `data_index`
FROM `data_index`
LEFT JOIN `data` ON `data`.`id` = `data_index`.`id`
WHERE `timestamp` > (NOW() * 1000) - (7 * 24 * 60 * 60 * 1000)

但这可能会锁定表很长时间。我如何不锁定表来完成相同的任务，使数据库对其他SELECT 和INSERT 查询保持功能？

Answer 1

不，如果不锁定检查的行就不能删除。

但是您可以通过在要搜索的 timestamp 列上创建索引来最大程度地减少检查的行数。

这也会创建 gap locks针对可能尝试在表末尾插入的潜在行，以确保新行不会影响 DELETE。

在 InnoDB 中，像 DELETE 创建的普通写锁不会阻塞读。并发事务仍然可以读取行——甚至是您要删除的行。

普通的写锁不会锁定整个表。好吧，它用 intention lock 锁定了表这只是防止其他表锁，如 ALTER TABLE 或 DROP TABLE 所需的锁。换句话说，当表正在进行任何读取或写入时，您不能更改/删除表。

您可能喜欢我的介绍:InnoDB Locking Explained with Stick Figures .