如何提高大型InnoDB表的DELETE FROM性能？

Question

我有一个相当大的InnoDB表，其中包含约1000万行（并且不断增长，预计它将变成该大小的20倍）。每行不是那么大（平均131
B），但是我不得不不时删除其中的一大块，这需要很长时间。这是表结构：

 CREATE TABLE `problematic_table` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `taxid` int(10) unsigned NOT NULL,
    `blastdb_path` varchar(255) NOT NULL,
    `query` char(32) NOT NULL,
    `target` int(10) unsigned NOT NULL,
    `score` double NOT NULL,
    `evalue` varchar(100) NOT NULL,
    `log_evalue` double NOT NULL DEFAULT '-999',
    `start` int(10) unsigned DEFAULT NULL,
    `end` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `taxid` (`taxid`),
    KEY `query` (`query`),
    KEY `target` (`target`),
    KEY `log_evalue` (`log_evalue`)
) ENGINE=InnoDB AUTO_INCREMENT=7888676 DEFAULT CHARSET=latin1;

从表中删除大块的查询就像这样：

DELETE FROM problematic_table WHERE problematic_table.taxid = '57';

这样的查询只花了将近一个小时的时间。我可以想象索引重写开销使这些查询非常慢。

我正在开发一个将在现有html" target="_blank">数据库上运行的应用程序。我很可能无法控制服务器变量，除非我强制对它们进行更改（我不希望这样做），所以我担心更改这些变量的建议价值不大。

我已经尝试过将INSERT ... SELECT那些我不想删除的行删除到临时表中，而只是删除其余的行，但是随着to-
delete与.keep的比率向keep转变，这不再是一个有用的解决方案。

这是一个可以经常看到一个表格INSERTS和SELECTS
IN的未来，但没有UPDATE秒。基本上，它是一个日志记录和参考表，需要不时删除其部分内容。

我可以通过限制索引长度来改进此表上的索引吗？转换为MyISAM帮助会DISABLE KEYS在交易过程中提供支持吗？我还能尝试什么来提高DELETE性能？

编辑： 一个这样的删除将在大约一百万行的顺序。

Answer 1

该解决方案完成后可以提供更好的性能，但是该过程可能需要一些时间才能实现。

BIT可以添加一个新列，默认TRUE为“活动”和FALSE“非活动”。如果状态不够，则可以使用TINYINT256个可能的值。

添加此新列可能会花费很长时间，但是一旦结束，您的更新就应该快得多，只要您PRIMARY像删除操作一样，不对这个新列编制索引就可以了。

InnoDB之所以花这么长时间DELETE在如此庞大的表上，是因为集群索引。它根据您的PRIMARY，首先UNIQUE找到的表或可以找到的合适的替代表（如果找不到PRIMARY或）将UNIQUE其物理排序，因此，删除一行后，它现在将整个表在磁盘上重新排序以提高速度，碎片整理。所以花的时间不是DELETE那么长；这是删除该行后的物理重新排序。

创建固定宽度的列并对其进行更新而不是删除时，由于行和表本身消耗的空间是恒定的，因此无需对巨大的表进行物理重新排序。

在下班时间，DELETE可以使用一个来删除不必要的行。与删除单个行相比，此操作将仍然很慢，但总体上要快得多。