MySQL InnoDB删除500,000条记录减慢整个服务器

Question

每周都会有一个cronjob触发，从使用MySQL引擎的InnoDB表中删除大约50万条记录。该表包含用于每周处理的web日志数据，然后删除不再需要的数据。这个表上有3个索引(没有外键)，包括时间，它是unix时间戳。

从global_viewlog中删除time<1354391592

问题是，这个查询在上周运行时花了两个多小时才运行，在这段时间内，我的整个服务器都落后了。“‘iotop”显示硬盘被MySQL引用大量写入，服务器负载急剧增加到不健康的水平。

该表将被写入到每次单击。房间里有我没看见的大象吗？比如，一些盲目的显而易见的东西来优化这个每周的查询？

谢谢

Answer 1

如果定期根据时间删除旧数据，则根据同一列对表进行分区将使其更快。它将将不同的时间段分隔到磁盘上的不同区域，因此删除将只是删除整个块，而不是查看单个行。

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

Answer 2

您使用的是InnoDB，因此有一个隐式事务。你要删除一整堆记录，所以交易很大。@ Ekman是对的，如果您可以让分区工作，那么这是一个很好的处理方法。

但这是另一个好办法。尝试以较小的批次执行删除操作，如下所示。

  DELETE 
    FROM global_viewlog 
   WHERE time<135439159
   LIMIT 1000

继续发出此语句，直到没有任何可删除的内容为止。大多数客户端库(JDBC等)返回受影响的记录数。或者，如果您想以廉价的方式执行此操作，则可以从脚本中发出上千次语句。

(您可以篡改LIMIT语句中的记录数量。)它可能需要比单个delete语句花费更多的时间，也可能不会占用更多的时间，但是它不会占用服务器太多。

尝试将此表的访问方法更改为MyISAM，并使用以下方法：

  DELETE LOW_PRIORITY QUICK
    FROM global_viewlog 
   WHERE time<135439159

这将防止您的清理操作干扰生产。

把你的工作频率从一周一次改为一天一次。这将减少删除批次的大小，这样您就不会有这么大的混乱需要清理。