MySQL加入更新运行缓慢

Question

我正在运行 MySQL 5.6.33，并且有一个更新查询运行得相当慢，我不明白为什么。

我有两个表:alltranscur有35908行，acctnumcust有86103行。

查询:

update alltranscur a inner join acctnumcust d
on a.acctNumber=d.acctNum
set
a.custID=d.custID

需要很长时间(5 分 58.29 秒，匹配约 22,000 行)。我也可以将其写为:

update alltranscur a, acctnumcust d
set
a.custID=d.custID
where a.acctNumber=d.acctNum

同样的结果。

建表语句是:

CREATE TABLE `alltranscur` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`custID` varchar(200) DEFAULT NULL,
`acctNumber` int(11) DEFAULT NULL,
... other columns
PRIMARY KEY (`id`),
KEY `acctNumber` (`acctNumber`),
... other keys
CONSTRAINT `alltranscur_ibfk_8` FOREIGN KEY (`custID`) REFERENCES `custdata` (`custID`) ON UPDATE CASCADE
... other foreign keys
) ENGINE=InnoDB AUTO_INCREMENT=5226303 DEFAULT CHARSET=latin1

和

CREATE TABLE `acctnumcust` (
`acctnum` varchar(50) NOT NULL,
`custid` varchar(200) NOT NULL,
PRIMARY KEY (`acctnum`),
KEY `custid` (`custid`),
KEY `acctnum` (`acctnum`,`custid`),
CONSTRAINT `acctnumcust_ibfk_1` FOREIGN KEY (`custid`) REFERENCES `custdata` (`custID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

如果我

explain
update alltranscur a inner join acctnumcust d
on a.acctNumber=d.acctNum
set
a.custID=d.custID

我得到:

+----+-------------+-------+-------+-----------------+------------+---------+---------------+-------+-------------+
| id | select_type | table | type  | possible_keys   | key        | key_len | ref           | rows  | Extra       |
+----+-------------+-------+-------+-----------------+------------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | d     | index | PRIMARY,acctnum | custid     | 202     | NULL          | 85152 | Using index |
|  1 | SIMPLE      | a     | ref   | acctNumber      | acctNumber | 5       | pcb.d.acctnum |     1 | Using where |
+----+-------------+-------+-------+-----------------+------------+---------+---------------+-------+-------------+

我原以为 MySQL 会在 acctnum 上使用 PRIMARY 键，或者甚至可能在 acctnum/custID 上使用组合键。但事实并非如此。

这和这样的查询一样快吗？过去，我已经加入了类似大小的表的更新，但他们并没有花费那么长的时间。我是否遗漏了这些表格的某些内容？有没有办法让它运行得更快？

Answer 1

问题的根本原因很可能是您将 2 个表连接到具有不同数据类型的列上

`acctNumber` int(11) DEFAULT NULL,
`acctnum` varchar(50) NOT NULL,

where a.acctNumber=d.acctNum

这意味着 MySQL 不能直接使用索引并以全表扫描结束。

将 acctnumcust 表中 acctnum 列的数据类型更改为 INT(11)，您的性能问题应该得到解决