在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%。