首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >以最小锁定删除大量级联记录

问以最小锁定删除大量级联记录
EN

Database Administration用户
提问于 2015-08-27 10:15:01
回答 2查看 1.5K关注 0票数 3

我正在寻找一种从MySQL数据库中清除大量逻辑删除数据的策略。被删除的记录位于一个相对较小的表中,但在多个引用表上有许多关于删除CASACDE记录的引用,这使得删除过程非常缓慢和耗时,在此期间,一些较小但更频繁访问的表显然是被锁定的(这些是InnoDB表)。

由于这些清除是在高负载数据库上定期进行的,所以我试图找出清除这些数据的最佳策略,同时对其他进程的影响最小,也就是对父表的锁定最小。

举个例子,我有一个类似于此的表结构:

代码语言:javascript
复制
+------------+    +------------+    +------------+    +-------------+
| accounts   |    | users      |    | messages   |    | attachments |
+------------+    +------------+    +------------+    +-------------+
| id         |    | id         |    | id         |    | id          |
| name       |    | account_id |    | user_id    |    | message_id  |
| is_deleted |    | name       |    +------------+    +-------------+
+------------+    +------------+

users.account_id REFERENCES accounts.id ON DELETE CASCADE
messages.user_id REFERENCES users.id ON DELETE CASCADE
attachments.message_id REFERENCES messages.id ON DELETE CASCADE

假设每个帐户有几十个用户,每个用户有数千条消息,并且可能有几十个附件和其他相关数据(例如标签、元数据等)。每条消息-删除一个帐户将导致数万条或数十万条记录被删除,跨越多个表。

在这种情况下,清除数据时应该采取什么好的策略?

EN

回答 2

Database Administration用户

回答已采纳

发布于 2015-08-28 11:12:14

您可以从某些脚本中批量删除:

1)将帐户标记为删除(似乎您正在做此操作)

2)在脚本中--为某个标记帐户选择一个用户并将其删除--正如您所期望的每个帐户中的许多用户一样,这将只删除所有邮件和附件中的一小部分,因此应该足够快。

3)对给定帐户的剩余用户重复2次,每次一个小批,其他进程可以在锁之间完成工作。

4)删除帐户-没有用户这么快

如果删除一个用户仍然需要太长时间,您可以向下删除一个级别,删除批处理(比如1000条消息),然后删除所有用户的帐户,现在没有消息的速度太快。

问题是,您会丢失ACID属性,因为它都在多个事务中运行,因此在提交第一个delete后无法回滚,而在此之间运行的其他查询将看到用户的部分数据(消息/用户的不完整列表取决于删除的粒度)。但是,只有你可以说这是一个真正的问题,或者如果is_deleted=1意味着该帐户不再被使用,所以它是安全的。

票数 1
EN

Database Administration用户

发布于 2018-01-28 21:40:55

我也有过同样的问题。批量删除会有所帮助,但由于将delete复制到其他节点的延迟,仍然倾向于锁定Percona集群。

在我的例子中,大多数级联删除在实践中并不存在--理论上,这些表中可能有记录,但在实践中,大多数表都不存在。因此,我所做的是查询模式以查找级联可能影响哪些表,然后对要删除的每个项进行删除:

  • 设置FOREIGN_KEY_CHECKS=0;
  • 查询受影响的表以查找是否有任何受影响的记录;如果有,请删除它们。
  • 删除主记录。
  • 设置FOREIGN_KEY_CHECKS=1;

这意味着实际锁定大大减少。在我的例子中,这使我能够更快地删除大约一个数量级。

这种方法并不是原子化的,但如果出现崩溃,则不会使表处于糟糕的状态。

票数 2
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/112387

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档