问删除命令在30,000,000行表上未完成

EN

我想我们可能把我所需要的答案弄得太复杂了。我毫不怀疑Roland和Rick在创建临时表时都是正确的，只注入了通过筛选器NOT LIKE '-%'的行，但是对于我来说，解决方案“更容易”，因为到目前为止我还没有意识到一个重要的错误，为此我道歉。

我在mysql交互式提示符中运行了查询并注意到错误消息，

mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

通过Googleing，我找到解决办法将通过/etc/my.cnf文件增加innodb_buffer_pool_size并重新启动mysql守护进程。对于我的服务器，它被设置为默认的8M，我将它增加到1G (服务器有32 is，这是当前唯一的InnoDB表)。

mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
Query OK, 23517226 rows affected (27 min 33.23 sec)

然后我能够运行命令并在27分钟内删除2300万条记录。

对于那些好奇应该将innodb_buffer_pool_size设置为什么的人，请注意您有多少内存，然后用看看这条线给出建议的GBs估计值。

请看InnoDB的架构(来自Percona CTO Vadim Tkachenko的图片)

正在将要删除的行写入撤消日志。在删除过程中，文件ibdata1现在应该正在增长。根据Mysql履约博客网站Reasons for run-away main Innodb Tablespace的说法：

• 大量的事务变化
• 很长的交易
• 滞后清除螺纹

在您的示例中，原因1将占用一个回滚段和一些撤消空间，因为您正在删除行。这些行必须位于ibdata1中，直到删除完成为止。这个空间在逻辑上被丢弃了，但是磁盘空间并没有缩小。

你得马上杀了那个删除的东西。一旦删除查询终止，它将回滚已删除的行。

你可以这样做：

CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

您可以首先对表的MyISAM版本执行此操作。然后，将其转换为InnoDB。

罗兰的建议可以通过同时做两件事来加速：

CREATE TABLE tablename_new LIKE tablename;
ALTER TABLE tablename_new ENGINE = InnoDB;
INSERT INTO tablename_new 
    SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key;
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

但是这里有一个博客，它解释了如何用块来做大删除，而不是似乎要花很长时间：http://mysql.rjweb.org/doc.php/deletebig的要点是通过PK遍历表，一次做1K行。(当然还有更多的细节需要注意。)

这个博客解决了向InnoDB：http://mysql.rjweb.org/doc.php/myisam2innodb转换过程中的潜在问题。