清雨影的Blog
  • README
  • 机器学习
    • 一般话题
      • 再谈正则化项
      • 论文阅读:“快把卷积神经网络中的平移不变性带回来”
      • 半监督/无监督学习收集
      • 收藏夹
    • 推荐系统
      • Understanding LightGCN in a visualized way
      • Learning To Rank 之 RankNet
      • 随想: BPR Loss 与 Hinger Loss
      • 关于AA测试和AB测试的一些思考
      • 无采样的矩阵分解
      • 收藏夹
    • 强化学习
      • Re:从零开始的Multi-armed Bandit
  • 高级物理引擎实战指南笔记
    • 弹簧质点系统
    • 光滑粒子法
    • 专题:线性方程组求解
  • 有限单元法
    • 1. 引论
    • 2. 基于直接刚度法的杆系有限元方法
    • 3. 针对复杂几何形状变形体的力学描述(1)
  • Web开发相关技术
    • JWT简介
  • 技术杂文
    • React-Script转Vite时引用路径的问题
    • Let's encrypt -- 让我们一起愉快的使用HTTPS
    • 干掉吸血雷,重塑和谐P2P环境
    • 开源CAN总线信号可编程台架
    • Linux下利用mdadm设置软件 RAID
    • 互不联网时代的自给自足
    • 为什么我劝你不要使用云计算?
    • 科学的公司内网连接技术选型
    • 构建家用NAS过程中的碎碎念
    • 简易的Linux迁移指北
    • 记录一次rsync命令引起的异常
    • 为FFMPEG添加Intel QSV支持
    • 备忘录
    • 福冈外免切替(中国驾照换日本驾照)攻略
    • 记一个离谱的MySQL语句的性能问题
    • 拯救变砖的OpenWRT路由器
    • 使用FRP进行内网穿透
  • 政治不正确
    • 吃屎系列:资本家如何喂员工吃屎
      • 华为251事件记忆
    • 吃屎系列:资本家如何喂用户吃屎
      • 互不联网公司是如何强奸用户的(持续更新)
    • 吃屎系列:大学如何喂学生吃屎
    • 推荐系统如何让我们变得极端
    • 互联网政治圈观察日志
    • 中国网络防火长城简史
    • 《线性代数》(同济版)——教科书中的耻辱柱
    • 杂谈
      • 访谈:为什么毛泽东时代工人的积极性很高?
      • 90年代到21世纪初的商业环境
    • 为什么不应该用国产手机
    • “救救孩子”
  • 随园食单
    • ボロネーゼ
    • 甜酒酿的制作
    • 香草与香料
    • 皮塔饼
    • 韭菜鸡蛋饼
    • 牛肉蔬菜汤
由 GitBook 提供支持
在本页
  • 故障现象
  • 故障重现
  • 分析与修复
  • 所以MySQL到底更新了什么
  • 这个故事告诉我们什么

这有帮助吗?

  1. 技术杂文

记一个离谱的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到底更新了什么

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

上一页福冈外免切替(中国驾照换日本驾照)攻略下一页拯救变砖的OpenWRT路由器

最后更新于2年前

这有帮助吗?

其实MySQL的更新说来也简单,就是让优化器变得更加聪明一点,会物化子查询了:

Changes in MySQL 8.0.21 (2020-07-13, General Availability) Optimizer Notes