问MySQL Inndob从超大型数据库中删除/清除行

EN

一种相对有效的方法是使用database partitions并通过删除分区来删除旧数据。它当然需要更复杂的维护，但它确实可以工作。

首先，启用innodb_file_per_table，以便每个表(和分区)转到它自己的文件，而不是单个巨大的ibdata文件。

然后，创建一个分区表，每个时间范围(日、月、周)都有一个分区，这将为您的数据集生成一些合理大小的文件。

create table foo(     
        tid INT(7) UNSIGNED NOT NULL,
        yearmonth INT(6) UNSIGNED NOT NULL,
        data varbinary(255) NOT NULL,
        PRIMARY KEY (tid, yearmonth) 
) engine=InnoDB
PARTITION BY RANGE(yearmonth) (
        PARTITION p201304 VALUES LESS THAN (201304),
        PARTITION p201305 VALUES LESS THAN (201305),
        PARTITION p201306 VALUES LESS THAN (201306)
);

查看数据库数据目录，您会发现每个分区都有一个文件。在本例中，分区'p201304‘将包含yearmonth < 201304的所有行，'p201305’将包含2013年-04年的行，'p201306‘将包含2013年-05年的所有行。

在实践中，我实际上使用了一个包含UNIX时间戳的整数列作为分区键-这样可以更容易地随着时间的推移调整分区的大小。分区边缘不需要匹配任何日历边界，它们可以每隔100000秒或任何结果产生合理数量的分区(数十个分区)，同时仍具有足够小的数据文件。

然后，设置一个维护进程，为新数据创建新分区：ALTER TABLE foo ADD PARTITION (PARTITION p201307 VALUES LESS THAN (201307))并删除旧分区：ALTER TABLE foo DROP PARTITION p201304。删除一个大分区几乎和删除文件一样快，而且它实际上会释放磁盘空间。此外，它不会通过在其他分区中留下分散的空白空间来对它们进行碎片。

如果可能，通过在WHERE子句中指定分区键(上面例子中的年月)或分区键的范围，确保您频繁的查询只访问一个或几个分区-这将使它们运行得更快，因为数据库不需要查看所有分区来查找数据。

即使你使用file_per_table选项，你仍然会有这个问题。“修复”它的唯一方法是重新构建单个表：

OPTIMIZE TABLE bloated_table

请注意，这将在重新构建操作期间锁定表，并且您必须有足够的可用空间来容纳新表。在某些系统上，这是不切实际的。

如果您经常删除数据，则可能需要定期轮换整个表。使用file_per_table在InnoDB下删除一个表几乎可以立即释放磁盘空间。如果您每个月有一个表，您可以简单地删除表示三个月前的数据的表。

使用这些工具是不是很难看？是。有没有别的选择？不怎么有意思。您可以尝试进入table partitioning兔子洞，但这通常会带来比实际价值更多的麻烦。