CentOS7下MySQL5.7.21简单UPDATE查询随机卡顿问题排查

在CentOS 7系统上使用MySQL Server 5.7.21时，执行以下UPDATE查询会随机出现卡顿数分钟的情况，且该现象与表内行数及其他外部因素无关：

UPDATE `Main` SET `Cat`=0 WHERE `Cat` NOT IN (SELECT `Uin` FROM `Cat1`)

涉及表结构 #

Main表 #

CREATE TABLE `Main` (
  `Code` varchar(20) NOT NULL,
  `Nt` varchar(250) NOT NULL DEFAULT '',
  `Ss` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Nac` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Ei` int(2) NOT NULL DEFAULT '0',
  `Quant` decimal(10,3) NOT NULL DEFAULT '0.000',
  `Dateadded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Sh` int(5) NOT NULL DEFAULT '0',
  `Post` int(11) NOT NULL DEFAULT '0',
  `Crit` decimal(10,3) NOT NULL DEFAULT '0.000',
  `Cat` int(5) NOT NULL DEFAULT '0',
  `Noskid` int(2) NOT NULL DEFAULT '0',
  `C2` int(2) NOT NULL DEFAULT '0',
  `Dopinf` int(2) NOT NULL DEFAULT '0',
  `Incloud` int(2) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Main`
  ADD PRIMARY KEY (`Code`),
  ADD KEY `Crit` (`Crit`),
  ADD KEY `Ei` (`Ei`),
  ADD KEY `Nac` (`Nac`),
  ADD KEY `Post` (`Post`),
  ADD KEY `Quant` (`Quant`);
COMMIT;

Cat1表 #

CREATE TABLE `Cat1` (
  `Uin` int(11) NOT NULL,
  `Cat1` varchar(255) NOT NULL DEFAULT '',
  `Level` int(11) NOT NULL DEFAULT '0',
  `Puin` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Cat1`
  ADD PRIMARY KEY (`Uin`),
  ADD UNIQUE KEY `ind_UNIQUE` (`Uin`),
  ADD UNIQUE KEY `idx_name` (`Cat1`);

可能的问题及解决方向 #

• 子查询执行逻辑低效：原语句的NOT IN子查询可能被优化器处理为逐行重复执行，导致性能波动。建议改用LEFT JOIN改写查询，减少重复计算：

  UPDATE Main m
  LEFT JOIN Cat1 c ON m.Cat = c.Uin
  SET m.Cat = 0
  WHERE c.Uin IS NULL;
  

• 缺少关键索引：Main表的Cat字段未建立索引，UPDATE时需全表扫描匹配条件，容易引发性能不稳定。给该字段添加索引：

  ALTER TABLE Main ADD INDEX idx_Cat (Cat);
  

• InnoDB锁等待：即使排除外部因素，随机卡顿可能源于事务锁竞争。通过SHOW ENGINE INNODB STATUS;查看当前锁等待状态，确认是否有其他事务持有目标行的锁导致等待。

• 优化器执行计划不合理：MySQL 5.7优化器对NOT IN子查询的处理可能未利用Cat1表的Uin主键索引。用EXPLAIN查看执行计划：

  EXPLAIN UPDATE `Main` SET `Cat`=0 WHERE `Cat` NOT IN (SELECT `Uin` FROM `Cat1`);
  

  若子查询未走主键索引，可强制指定索引：

  UPDATE `Main` SET `Cat`=0 WHERE `Cat` NOT IN (SELECT `Uin` FROM `Cat1` FORCE INDEX (PRIMARY));
  

• 表统计信息过时：不准确的统计信息会导致优化器选择错误执行计划。手动更新统计信息：

  ANALYZE TABLE Main, Cat1;
  

内容的提问来源于stack exchange，提问作者Сергей Соколов