在MySQL中,我有两个innodb表,一个小的任务关键表,需要随时可用于读取/写入。将此称为task_critical。我有一个更大的表(>数千万行),称为big_table。我需要更新big_table,例如:

update mission_critical c, big_table b
set
b.something = c.something_else
where b.refID=c.id


该查询可能需要一个多小时,但这会在mission_critical表上创建写锁定。有没有一种方法可以告诉mysql,“我不希望对mission_critical进行锁定”,以便可以将该表写入其中?

我了解从交易的角度来看这不是理想的。我现在唯一能想到的解决方法是制作一个小的Mission_critical表的副本,并从中进行更新(我不在乎被锁定了),但是如果有一种方法,我宁愿不这样做MySQL本机可以更优雅地处理此问题。

锁定的不是表,而是task_critical中的所有记录都被锁定,因为更新基本上会扫描所有记录。我不认为这是事实;症状是,当用户登录到联机系统时,它将尝试更新mission_critical中的datetime列以更新其上次登录的时间。这些查询由于运行上述查询时超过Lock等待超时错误而终止。如果我取消了上面的查询,所有未决的查询将立即运行。

mission_critical.id和big_table.refID都已建立索引。

每个表的创建语句的相关部分是:

mission_critical:

CREATE TABLE `mission_critical` (
`intID` int(11) NOT NULL AUTO_INCREMENT,
`id` smallint(6) DEFAULT NULL,
`something_else` varchar(50) NOT NULL,
`lastLoginDate` datetime DEFAULT NULL,
PRIMARY KEY (`intID`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `something_else` (`something_else`),
) ENGINE=InnoDB AUTO_INCREMENT=1432 DEFAULT CHARSET=latin1


big_table:

CREATE TABLE `big_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`postDate` date DEFAULT NULL,
`postTime` int(11) DEFAULT NULL,
`refID` smallint(6) DEFAULT NULL,
`something` varchar(50) NOT NULL,
`change` decimal(20,2) NOT NULL
PRIMARY KEY (`id`),
KEY `refID` (`refID`),
KEY `postDate` (`postDate`),
) ENGINE=InnoDB AUTO_INCREMENT=138139125 DEFAULT CHARSET=latin1


该查询的解释是:

+----+-------------+------------------+------------+------+---------------+-------+---------+------------------------------------+------+----------+-------------+
| id | select_type |      table       | partitions | type | possible_keys |  key  | key_len |                ref                 | rows | filtered |    Extra    |
+----+-------------+------------------+------------+------+---------------+-------+---------+------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | mission_critical |            | ALL  | id            |       |         |                                    |  774 |      100 | Using where |
|  1 | UPDATE      | big_table        |            | ref  | refID         | refID |       3 | db.mission_critical.something_else | 7475 |      100 |             |
+----+-------------+------------------+------------+------+---------------+-------+---------+------------------------------------+------+----------+-------------+

最佳答案

我首先提出了一个子查询的解决方法,以便在内部临时表中创建一个副本。但是在我的测试中,小表仍被锁定以进行写操作。因此,我猜您最好的选择是手动进行复制。

此错误报告中描述了锁定的原因:https://bugs.mysql.com/bug.php?id=72005

这是Sinisa Milivojevic在回答中写的:


  update table t1,t2 ....
  
  任何具有联接的UPDATE都被视为多表更新。在那里面
  情况下,必须对读取的表进行锁定,因为行不能
  在UPDATE期间在引用表中进行更改,直到
  完成。不能同时更改行,也不能DELETE
  行,更不用说引用的任何DDL了
  表。目标很简单,那就是使所有表具有一致的
  UPDATE完成时的内容,尤其是由于多表
  UPDATE可以通过多次执行。


简而言之,这种行为是有充分理由的。

考虑编写INSERT和UPDATE触发器,这将即时更新big_table。这将延迟对mission_critical表的写入。但这对于您来说可能足够快,并且不再需要mass-update-query。

还要检查使用char(50)代替varchar(50)是否更好。我不确定,但是有可能会提高更新性能,因为行大小不需要更改。在测试中,我可以将更新性能提高约50%。

10-07 19:38
查看更多