删除索引对大型MySQL表的性能影响

Question

假设我们有一个带有MySQL 5.6数据库的web论坛应用程序，许多用户可以24/7访问它。现在有这样一个表，用于发送给用户的通知元数据。

| notifications | CREATE TABLE `notifications` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `message_store_id` bigint(20) unsigned NOT NULL,
 `status` varchar(10) COLLATE ascii_bin NOT NULL,
 `sent_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`,`sent_date`)
) ENGINE=InnoDB AUTO_INCREMENT=736601 DEFAULT CHARSET=ascii COLLATE=ascii_bin |

这个表有一百万行。使用这个表，某个message_store_id突然由于某种原因失效，我计划用一个delete语句删除该message_store_id的所有记录，例如

DELETE FROM notifications WHERE message_store_id = 12345;

这条语句会影响表的10%，因为这条消息发送给了这么多用户。同时，成千上万的用户始终访问此通知表，因此必须存在索引。显然，索引更新在删除记录时花费很大，所以我害怕这样做，并通过最大限度地利用服务器资源来造成停机时间。但是，如果我删除索引，删除记录，然后再添加一个索引，我不得不关闭数据库一段时间，不幸的是，我们的服务是不可能的。

我希望MySQL 5.6不是那么愚蠢，以至于这一条语句可以杀死数据库，但我想这很有可能。我的问题是，对于这种情况，指数娱乐真的致命吗？如果是这样的话，对于这个操作有什么好的策略不需要我为维护而停止数据库呢？

Answer 1

根据应用程序的细节，可以使用很多技巧/策略。

1. 如果您计划定期执行这些操作(例如，这不是一次性的事情)，而且在message_store_id中几乎没有不同的值，则可以使用分区。按message_store_id值进行分区，预先创建X分区(其中X是id值的合理上限)，然后通过截断该分区，可以在瞬间删除该分区中的所有记录。几毫秒的问题。缺点：message_store_id必须是主键的一部分。注意:您必须事先创建分区，因为上次使用分区时，alter table add partition重新创建了整个表，这对大型表来说是一场灾难。
2. 即使alter table truncate partition不适合您，您仍然可以从分区中获益。如果在分区上发出DELETE，通过提供相应的where条件，表的其余部分将不会受到此DELETE op的影响/锁定。
3. 删除记录而不锁定数据库太长时间的替代方法： 当(true) { //假设自动提交模式从表中删除时，{您的条件}限制10000；//此时释放锁，其他事务有机会//做一些事情。如果(受影响的行){ == 0；} //这是插入睡眠(5)的好地方，以便在删除下一个块之前给其他事务//更多的时间处理它们的内容。}

Answer 2

一种选择是将delete作为几个较小的操作来执行，而不是一个大型操作。

MySQL提供了一个LIMIT子句，它将限制查询匹配的行数。

例如，您可以只删除1000行：

DELETE FROM notifications WHERE message_store_id = 12345 LIMIT 1000;

您可以重复这一点，为其他操作(竞争同一表上的锁)留出一个合适的时间窗口来完成。要在纯SQL中处理这个问题，我们可以使用MySQL SLEEP()函数，暂停2秒，例如：

SELECT SLEEP(2);

显然，这可以合并到一个循环中，例如，在一个MySQL过程中，继续循环直到DELETE语句影响到零行。