最近上线一个功能,进行一串业务处理,最后对数据进行一串update操作,然后上线后发现,数据各种update故障,查询日志,最后发现罪魁祸首。
类似这么一段sql:test1表大概有50w数据,test2 大概有15万。
update test1 set name ='w' where id in (select num from test2 where id=3) ;
看第一眼,不可能啊,这怎么可能出错,应该很快才对
先执行子查询,查到自己想要修改的数据,然后在执行外面查询,对不,是不是很合理?
然后拿出来放数据库执行下,运行后就没反应了,跟死锁差不多样子。毁三观啊
死锁吗?但是看来看去都不满足死锁条件,innodb的存储引擎,换成随便一种存储引擎,也不会死锁。
然后explain一下,发现数据库版本太低,不支持update的执行计划,重新下载一个新版本, ok。
发现这是mysql内部优化的坑,
首先说下测试场景
test1 总共五条数据
test2总共六条数据
然后再来看下执行计划图:
现实和我想象的完全不一样,
先看第一行,他竟然对test1进行了全表扫描,还是利用where字句,我用的是主键额。
在看第二行 dependent subquery !! 问题出来了,这个在我想来应该是一个子查询,先执行一个结果出来,然后给test1用来做约束。结果竟然成了从属查询了,要先根据test1所有数据,每条都和test2进行一次组合查询,几十万条数据,关联下来自然慢成狗了。
优化:
update test1 LEFT JOIN test2 on test2.num = test1.id set test1.name ='w' where test2.id=3
这次又起飞了,这速度杠杠的。