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

## 故障现象

我司有一个系统里存在某个SQL，定期会需要把一些数据标记为删除，这个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会话，然后随机抽取一些数据来模拟标记删除的过程：

```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，执行计划就是：

```sql
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](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-optimizer)

> 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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://blog.tsingjyujing.com/tech/database/mysql-optimizer-in-clause.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.