我正在寻找一种从MySQL数据库中清除大量逻辑删除数据的策略。被删除的记录位于一个相对较小的表中,但在多个引用表上有许多关于删除CASACDE记录的引用,这使得删除过程非常缓慢和耗时,在此期间,一些较小但更频繁访问的表显然是被锁定的(这些是InnoDB表)。
由于这些清除是在高负载数据库上定期进行的,所以我试图找出清除这些数据的最佳策略,同时对其他进程的影响最小,也就是对父表的锁定最小。
举个例子,我有一个类似于此的表结构:
+------------+ +------------+ +------------+ +-------------+
| 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假设每个帐户有几十个用户,每个用户有数千条消息,并且可能有几十个附件和其他相关数据(例如标签、元数据等)。每条消息-删除一个帐户将导致数万条或数十万条记录被删除,跨越多个表。
在这种情况下,清除数据时应该采取什么好的策略?
发布于 2015-08-28 11:12:14
您可以从某些脚本中批量删除:
1)将帐户标记为删除(似乎您正在做此操作)
2)在脚本中--为某个标记帐户选择一个用户并将其删除--正如您所期望的每个帐户中的许多用户一样,这将只删除所有邮件和附件中的一小部分,因此应该足够快。
3)对给定帐户的剩余用户重复2次,每次一个小批,其他进程可以在锁之间完成工作。
4)删除帐户-没有用户这么快
如果删除一个用户仍然需要太长时间,您可以向下删除一个级别,删除批处理(比如1000条消息),然后删除所有用户的帐户,现在没有消息的速度太快。
问题是,您会丢失ACID属性,因为它都在多个事务中运行,因此在提交第一个delete后无法回滚,而在此之间运行的其他查询将看到用户的部分数据(消息/用户的不完整列表取决于删除的粒度)。但是,只有你可以说这是一个真正的问题,或者如果is_deleted=1意味着该帐户不再被使用,所以它是安全的。
发布于 2018-01-28 21:40:55
我也有过同样的问题。批量删除会有所帮助,但由于将delete复制到其他节点的延迟,仍然倾向于锁定Percona集群。
在我的例子中,大多数级联删除在实践中并不存在--理论上,这些表中可能有记录,但在实践中,大多数表都不存在。因此,我所做的是查询模式以查找级联可能影响哪些表,然后对要删除的每个项进行删除:
这意味着实际锁定大大减少。在我的例子中,这使我能够更快地删除大约一个数量级。
这种方法并不是原子化的,但如果出现崩溃,则不会使表处于糟糕的状态。
https://dba.stackexchange.com/questions/112387
复制相似问题