我运行的是mysql 5.6.33,有一个更新查询运行得很慢,我不明白为什么。
我有两个表:alltranscor有35908行,acctnumcust有86103行。
查询:
update alltranscur a inner join acctnumcust d
on a.acctNumber=d.acctNum
set
a.custID=d.custID
需要很长时间(5分钟58.29秒,匹配约22000行)。我也曾写过:
update alltranscur a, acctnumcust d
set
a.custID=d.custID
where a.acctNumber=d.acctNum
同样的结果。
表CREATE语句是:
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上使用主键,甚至在acctnum/custid上使用组合键。但事实并非如此。
这和这样的查询速度一样快吗?在过去,我已经加入了类似大小表的更新,而且它们不会花太长时间。我是不是错过了这些桌子?有办法让它跑得更快吗?
