当前位置: 首页>>技术问答>>正文


DELETE命令未在30,000,000行表上完成

webfans 技术问答 , , , , , 去评论

问题描述

我继承了一个数据库,我希望清理并加快速度。我有一个包含30,000,000行的表,其中许多是由于代表程序员的错误而插入的垃圾数据。在我添加任何新的,更优化的索引之前,我将表从MyISAM转换为InnoDB,并且我希望删除包含垃圾数据的许多行。

数据库是MySQL 5.0,我有对服务器的root访问权限。我首先通过Adminer和phpMyAdmin运行这些命令,两者都具有相同的结果。

我正在运行的命令是,

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'

基本上,删除此列中以短划线-开头的任何内容。

它运行大约3-5分钟,然后当我查看进程列表时,它已经消失了。

然后我跑了,

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'

它返回数百万行。

为什么我的删除声明没有完成?

PS,我知道out-of-date MySQL 5.0是怎样的。我正在努力将数据库移动到MySQL 5.6 w InnoDB(可能是MariaDB 10 w XtraDB),但在此之前,我希望用DB来回答这个问题。

编辑已删除,请参阅我的回答。

最佳解决方案

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

mysql,innodb,myisam,delete,mysql-5.0,database

您要删除的行正在写入撤消日志。在删除期间,文件ibdata1现在应该正在增长。根据mysqlperformanceblog.com的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。

次佳解决方案

我想我们可能过于复杂了我的案例所要求的答案。我毫不怀疑Roland& Rick James对他们创建一个临时表是正确的,只注入通过过滤器NOT LIKE '-%'的行,但我的解决方案是”easier”,因为有一个重要的错误,我直到现在都没有意识到,为此我道歉。

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

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

通过谷歌错误,我found the solution通过/etc/my.cnf文件增加innodb_buffer_pool_size并重启mysql守护进程。对于我的服务器,它被设置为默认的8M,我将其增加到1G(服务器有32GB,这是目前唯一的InnoDB表)。

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

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

对于那些应该设置innodb_buffer_pool_size的好奇者,请注意你有多少RAM,然后是take a look at this thread,它给出了以GB为单位的建议估计值。

第三种解决方案

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

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;

但是这里有一个博客,解释了如何在块中进行大DELETE,而不是看似永远:http://mysql.rjweb.org/doc.php/deletebig要点是通过PK遍历表,一次做1K行。 (当然还有更多细节需要注意。)

此博客解决了转换为InnoDB的潜在问题:http://mysql.rjweb.org/doc.php/myisam2innodb

第四种方案

我的第一直觉是通过限制查询结果的数量来执行多次较小的删除,并多次运行查询:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%' LIMIT 1000000

第五种方案

最简单的解决方案就是不要这样做 – 做一个较小的删除,这可以更容易处理。

在这种情况下,我会建议尝试连续删除表单:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-a%'

第六种方案

也许你可以这样做:

  • 添加一个名为deleted的新字段。

  • 做一个像UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'的更新。

  • 设置cron以在夜间删除它。

参考资料

本文由朵颐IT整理自网络, 文章地址: https://duoyit.com/article/3050.html,转载请务必附带本地址声明。