记一个离谱的MySQL语句的性能问题

故障现象

我司有一个系统里存在某个SQL，定期会需要把一些数据标记为删除，这个SQL是这样写的：

-- 注：本文的SQL都进行了脱敏处理，但是绝对不影响理解
UPDATE production_embedding_map
SET deleted = true
WHERE production_id IN (SELECT production_id FROM t_delete_production_ids);

Beta环境里测试过了后，就扔到线上去跑了（当时开发的急，只有做功能测试，性能测试则没有做）。 结果有一天，突然告警，说任务超时了，然后就被自动干掉重新执行，但是重试了几十次仍然是失败的。

故障重现

看到这个SQL的第一反应就是IN写的不好（因为已经确认过，production_id字段是有索引的），这个没什么证据，就是SQL写多了的直觉（但是仍然写出了这样的SQL，丢人啊）。

于是我跑到线上去下载了一份生产数据，本地（其实是另一个服务器）用Docker起了一个MySQL8实例，然后把数据导入本地进行测试。

打开一个SQL会话，然后随机抽取一些数据来模拟标记删除的过程：

create temporary table t_delete_production_ids
(
    production_id INT UNSIGNED
);

insert into t_delete_production_ids (production_id)
select production_id
from (select distinct production_id
      from production) eids
order by RAND()
limit 3000;

然后执行该SQL，期待该SQL直接卡死，然而发现完！全！没！问！题！！

多执行了几次发现的确没问题以后，怀疑是MySQL小版本的问题，线上跑的是MySQL 8.0.19而我Docker里面拉的是最新的8.0.30。 使用二分法最小版本进行测试后发现，该问题在8.0.21被修复了。

分析与修复

其实问题出在执行计划上，如果用EXPLAIN解释原始的SQL，可以看到8.0.20及以前的执行计划是：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	production_embedding_map	null	index	null	PRIMARY	4	null	6201234	100	Using where
2	DEPENDENT SUBQUERY	t_delete_production_ids	null	ALL	null	null	null	null	2818	10	Using where

如果更新到8.0.21及以后，哦，它把SUBQUERY的结果物化了，然后就变成了和INNER JOIN（最后的解决方案）一样的执行计划（MATERIALIZED这一步骤的执行成本可忽略）。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	<subquery2>	null	ALL	null	null	null	null	null	100	Using where
1	UPDATE	production_embedding_map	null	ref	production_id	production_id	5	<subquery2>.production_id	44	100	null
2	MATERIALIZED	t_delete_production_ids	null	ALL	null	null	null	null	3311	100	null

可是我们没法升级MySQL的版本，所以只能绕开它，让MySQL生成和新版一样的执行计划。不讨论Optimizer的骚操作，一般SQL如果在IN上遇到瓶颈，最好的办法是换成JOIN，如果换成INNER JOIN，执行计划就是：

UPDATE production_embedding_map pem
INNER JOIN t_delete_production_ids tdpi ON pem.production_id = tdpi.production_id
SET deleted = true
WHERE tdpi.production_id is not null;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tdpi	null	ALL	null	null	null	null	2818	90	Using where
1	UPDATE	pem	null	ref	production_id	production_id	5	duplicated_production_detection.tdpi.production_id	46	100	null

所以MySQL到底更新了什么

其实MySQL的更新说来也简单，就是让优化器变得更加聪明一点，会物化子查询了：Changes in MySQL 8.0.21 (2020-07-13, General Availability) Optimizer Notes

A single-table UPDATE or DELETE statement that uses a subquery having a [NOT] IN or [NOT] EXISTS predicate can now in many cases make use of a semijoin transformation or subquery materialization.

这个故事告诉我们什么

• Beta/测试环境的所有版本最好都和生产做到小版本一致

• 对于赶工的项目不可能逐一确认，但是保险起见，所有的SQL最好都扫一眼执行计划

  • 我觉得更好的方式是封装执行SQL的部分，仅在Beta环境输出Explain的结果

• 不要完全相信Optimizer