mysql - MySQL innoDB 更新缓慢(无知)

Question

我有一个相当简单的查询，大多数运行速度非常快，在其他极少数情况下最多需要 30 秒。我不知道是什么原因造成的。这是我正在讨论的示例。

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (3.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (1.88 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

在此查询中，我为所有 msm.matchNo、ms.matchNo、ms.status、msm.status 和 msm.memberNo 设置了索引。

我运行了相同的查询 6 次，其中有 4 个案例的查询在一秒内完成，而有 2 个案例的处理时间超过一秒。我做了更多测试并获取了其中一种更严重情况的配置文件数据，执行这个愚蠢的查询需要长达 30 秒的时间。

mysql> SHOW PROFILE FOR QUERY 3;  
+---------------------------+-----------+  
| Status                    | Duration  |  
+---------------------------+-----------+  
| starting                  |  0.000075 |  
| checking permissions      |  0.000013 |  
| checking permissions      |  0.000012 |  
| Opening tables            |  0.000073 |  
| checking permissions      |  0.000011 |  
| checking permissions      |  0.000013 |  
| System lock               |  0.000023 |  
| init                      |  0.000021 |  
| updating main table       |  0.000014 |  
| optimizing                |  0.000020 |  
| statistics                |  0.000117 |  
| preparing                 |  0.000028 |  
| executing                 |  0.000011 |  
| Sending data              | 31.940094 | <==
| updating reference tables |  0.000039 |  
| end                       |  0.000016 |  
| end                       |  0.000015 |  
| query end                 |  0.000048 |  
| closing tables            |  0.000039 |  
| freeing items             |  0.000036 |  
| logging slow query        |  0.000011 |  
| cleaning up               |  0.000014 |  
+---------------------------+-----------+  

我还有一些其他具有类似症状的更新查询。尽管实际上没有任何行被更新，为什么这个查询需要这么长时间才能执行？这个“发送数据”瓶颈可能是什么？

**编辑:为正在使用的两个表添加了 CREATE TABLE 代码

CREATE TABLE matchSessionMembers (
matchNo int(11) NOT NULL,
memberNo int(11) NOT NULL,
status char(1) NOT NULL DEFAULT 'M' COMMENT 'M=match, P=pending, Y=saidYes, N=saidNo, D=deleted',
muted char(1) NOT NULL DEFAULT 'N',
deleted char(1) NOT NULL DEFAULT 'N',
regDatetime datetime DEFAULT NULL,
exitDatetime datetime DEFAULT NULL,
PRIMARY KEY (matchNo,memberNo),
KEY status (status),
KEY FK_matchSessionMembers_memberNo (memberNo),
KEY regDatetime (regDatetime),
KEY FK_matchSessionMembers_matchNo (matchNo),
CONSTRAINT FK_matchSessionMembers_matchNo FOREIGN KEY (matchNo) REFERENCES matchSessions (matchNo),
CONSTRAINT FK_matchSessionMembers_memberNo FOREIGN KEY (memberNo) REFERENCES members (memberNo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE matchSessions (
matchNo int(11) NOT NULL AUTO_INCREMENT,
memberCount int(11) NOT NULL,
status char(1) NOT NULL DEFAULT 'P' COMMENT 'P=pending, Y=successful, N=unsuccessful, X=expired',
isQuickMatch char(1) NOT NULL DEFAULT 'N',
open char(1) NOT NULL DEFAULT 'N',
regDatetime datetime DEFAULT NULL,
activeDate date DEFAULT NULL,
expireDate date DEFAULT NULL,
PRIMARY KEY (matchNo),
KEY status (status),
KEY activeDate (activeDate),
KEY expireDate (expireDate)
) ENGINE=InnoDB AUTO_INCREMENT=113912 DEFAULT CHARSET=utf8

Answer 1

我明白了。

我犯了一个愚蠢的错误。但识别起来并不容易。我有一个不同的超慢查询，大约每小时运行一次，其中一个更新查询的执行时间超过 30 秒。我不太确定，但我认为该查询必须锁定所有内容并将所有其他查询放入队列中。

我没有在慢速查询日志中注意到它，因为它被埋在由这个愚蠢查询导致的所有其他慢速查询之下。

我希望这可以帮助其他遇到类似问题的人。