从大型 MySql 表中删除单行会导致“锁定超时”

Question

我正在运行 MySql 5.0.22，并且有一个非常笨重的表，其中包含大约 500 万行。

某些（但不是所有）行由另一个表的外键引用。

到目前为止，所有剔除未引用行的尝试都失败了，每次都会导致锁定超时。

将我想要的行复制到备用表也因锁定超时而失败。

令人怀疑的是，即使是像下面这样应该立即完成的语句也会因“锁定超时”而失败：

DELETE FROM mytable WHERE uid_pk = 1 LIMIT 1;

......此时我已经没有想法了。

编辑：不管怎样，我一直在我的开发系统上解决这个问题，所以此时只有我实际使用数据库，所以在我运行的 SQL 之外不应该有任何锁定。

任何 MySql 专家对如何驯服这个流氓表有建议吗？

编辑#2：根据要求，表结构：

CREATE TABLE `tunknowncustomer` (
  `UID_PK` int(11) NOT NULL auto_increment,
  `UNKNOWNCUSTOMERGUID` varchar(36) NOT NULL,
  `CREATIONDATE` datetime NOT NULL,
  `EMAIL` varchar(100) default NULL,
  `CUSTOMERUID` int(11) default NULL,
  PRIMARY KEY  (`UID_PK`),
  KEY `IUNKNOWCUST_CUID` (`CUSTOMERUID`),
  KEY `IUNKNOWCUST_UCGUID` (`UNKNOWNCUSTOMERGUID`),
  CONSTRAINT `tunknowncustomer_ibfk_1` FOREIGN KEY (`CUSTOMERUID`) REFERENCES `tcustomer` (`UID_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

注意，尝试删除 FK 也会超时。

Answer 1

我对 innodb 表也有同样的问题。优化表纠正了它。

Answer 2

好吧，我终于找到了一种方法，可以从我的大型 InnoDB 表中删除不需要的行！我是这样做的：

1. 停止使用 MySQL Workbench（它们的硬编码执行超时为 30 秒）
2. 打开命令提示符
3. 使用 ALTER TABLE 重命名“完整”表
4. 使用原始表名称和结构创建一个空表
5. 重新启动 MySQL
6. 已关闭OFF 'autocommit' with SET AUTOCOMMIT = 0
7. 一次删除有限数量的行，每次成功后增加我的
8. 限制在删除语句之间，因为关闭自动提交确实让我陷入了一个大事务中

整个工作看起来有点像这样：

ALTER TABLE `ep411`.`tunknowncustomer` RENAME TO  `ep411`.`tunknowncustomer2`;

...很奇怪，重命名表是唯一会立即完成的 ALTER TABLE 命令。

delimiter $

CREATE TABLE `tunknowncustomer` (
    ...
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8$

...然后重新启动，以防万一我之前的失败尝试可能会阻止任何新的工作完成...

SET AUTOCOMMIT = 0;

delete from tunknowncustomer2 where customeruid is null limit 1000;

delete from tunknowncustomer2 where customeruid is null limit 100000;

commit;

delete from tunknowncustomer2 where customeruid is null limit 1000000;

delete from tunknowncustomer2 where customeruid is null limit 1000000;

commit;

...一旦我一次删除 100k，InnoDB 的执行时间就会随着每个成功的命令而下降。我假设 InnoDB 开始对大型扫描进行预读。进行提交会重置预读数据，因此我将 COMMIT 间隔为每 200 万行，直到作业完成。

我通过将剩余的行复制到“空”克隆表中，然后删除旧的（重命名的）表来完成任务。

这不是一个优雅的解决方案，它没有解决为什么从大表中删除一行会失败的任何原因，但至少我得到了我正在寻找的结果！