先来看看今天要讲的主人翁： 涉事表结构： 定睛一看，好像没什么问题，SQL语句都有索引。 上周突然发现线上异步队列挂了，消息队列里面的数据都没有推送出去，手动重启后，不到半个小时又崩掉了，此时题主内心是崩溃的。 经过各种分析业务代码，发现之前写的一个自动运维脚本，里面的逻辑是：每5分钟给队列里面发送一条特定消息，消费者消费后写入某个标记（此处即写入上面用户消息表，实际就是发送一条消息），下一个5分钟检测这个标记是否存在，如果不存在，则认为队列消费者出了异常，强制重启。猜测是这里由于某些原因，自动重启，然后没启动过来。因此题主迅速改掉此处逻辑，每5分钟发送消息并检测上一条消息是否已经消费成功，如果没有成功则只报警，不再重启，果然线上恢复正常消费，但是出现了新的问题，发现上那个消息经常延迟。延迟非常大，有时候10多分钟，但是看消费者日志，别的消息都是正常的。题主又开始一堆分析代码，搜索。 正当题主无解的时候，这时前端业务部门反馈，用户端上报，消息中心经常502，隔一会儿自动恢复。于是通过Nginx日志找到502的请求，拿到日志ID，去业务日志中查询，发现每次502请求都是执行到开篇那条SQL，业务就中断了，一直到nginx 网关超时。 于是我们拿到刚才那条SQL，先到mysql里面explain: Mysql Explain 可以看到SQL用到了索引,ref 是const, rows是2，单看这个，这条SQL执行应该是非常快的。但是题主同时注意到了Extra里面有个Using temporary。这就是原因了，这张表有400多万记录，创建临时表的开销是非常大的。 为什么一条UPDATE语句，有索引还会用到临时表呢？ 求助同事无果，求助万能的群无果，求助百度无果，求助谷歌，反复更换关键词后，终于找到一个文章：https://stackoverflow.com/questions/36143560/mysql-update-query-using-a-temporary-table-when-an-equivalent-select-query-does A guess: The UPDATE is changing an indexed value (readyState), correct? That means that the index in question is being changed as the UPDATE is using it? So, the UPDATE may be "protecting" itself by fetching the rows (in an inefficient way, apparently), tossing them into a tmp table, and only then performing the action. "Index merge intersect" is almost always less efficient than a composite index: INDEX(readyState, productName) (in either order). Suggest you add that. Since you have no ORDER BY, which "30" will be unpredictable. Suggest you add ORDER BY the-primary-key. 大概意思是说：更新语句更新到了索引列同时where条件用又用到了该索引列，Mysql为了保证where条件查到的数据的一致性，所以用到了临时表。 既然这样那我们就来改造SQL。 首先是尝试把SQL改成子查询，子查询查询出主键： Explain下，发现不用临时表了，但是发生了全表扫描，没用到索引，原因是子查询是Mysql是先执行主查询，再执行子查询 ，由于外面的条件没有任何过滤，所以外层就是全表扫描，所以此方案放弃。 继续尝试改造SQL为联表操作： 再次explain,这次终于顺利通过。 改代码上线，速度上来了，不过前端反馈还是有零星的502，这又是什么情况？ 发现居然有事务几十分钟都没提交，而且对应的进程是sleep，居然有事务没提交？ 全部代码搜索用到事务的地方，一查，还真是，最近上线一个版本的代码，几个新人写的代码，居然有开启事务后，在事务内部逻辑处理中，如果中途return 并没有回滚或提交事务。既然问题清楚了，马上通知大家改代码，CR，上线。再次观察，经过一天的观察线上502问题得到解决。 通过这次事故的处理总结几点：UPDATE `i_msg_system` set `deliver`=1 where `uid`=10000 and `msg_group`=0 and `deliver`=0;CREATE TABLE `i_msg_system` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`fromuid` int(11) NOT NULL,
`msg_group` int(10) NOT NULL DEFAULT '0' COMMENT '消息组，0-系统消息, 1-远程工作消息, 2-技术圈赞,7-交易信息',
`notice_type` varchar(100) CHARACTER SET utf8 DEFAULT '',
`detail_id` int(11) DEFAULT NULL COMMENT '待废弃，使用extra替代',
`content` varchar(1024) DEFAULT NULL,
`url` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '跳转地址',
`time` int(10) DEFAULT NULL,
`deliver` int(1) DEFAULT NULL,
`extra` varchar(1000) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT 'json格式的额外数据',
PRIMARY KEY (`id`),
KEY `idx_u_m_d` (`uid`,`msg_group`,`deliver`),
KEY `idx_fromuid` (`fromuid`),
KEY `idx_uid_group_notice_type_url` (`uid`,`msg_group`,`notice_type`,`url`) USING BTREE,
KEY `idx_uid_deliver` (`uid`,`deliver`)
) ENGINE=InnoDB AUTO_INCREMENT=5341925 DEFAULT CHARSET=utf8mb4 COMMENT='用户消息表';事故处理过程
UPDATE `i_msg_system` SET `deliver` = 1 WHERE id in(
select id from (
select id from i_msg_system a where uid = 10000 and msg_group = 0 and deliver=0
) b
);UPDATE i_msg_system a INNER JOIN (
select id from i_msg_system c where uid = 10000 and msg_group = 0 and deliver=0
) b on a.id=b.id set a.deliver=1;show FULL PROCESSLIST;
SELECT * FROM information_schema.innodb_trx;