mysql/oracle 数据库delete操作太慢（where ... in ...），不加索引，一招让性能提升百倍

背景

delete操作应用虽然不多，但是有些场景使用起来还是更方便。比如 在数仓项目中，软删虽然更快更安全，但是缺点也很多：
1、软删造成数据冗余，甚至快速膨胀的后果。比如一些中间表，只是作为中转站，过两天数据就分配其他表了，不硬删的话就会造成数据快速膨胀。
2、使用起来不便，难道select 或者 join 每张表都需要加一个 有效位 的判断 ？上层开发人员稍不留神就会出错，同时也会造成性能下降

所以对于一些大表，有时还是需要 硬删操作，也就是delete操作。

最近就遇到这样的问题：

delete from table_a where id in (select id from table_b);

table_a：17w条数据，id为主键，索引
table_b: 9K条数据，没有索引。

执行了25分钟 还没执行完。

解决办法

优化sql， 原来的sql并没有走索引，优化后的sql如下：

delete a from table_a a, table_b b  where a.id = b.id;

执行 2 s 就执行完成了。

原理分析

这里通过explain来分析不同SQL的性能。
先来分析第一种写法：

explain delete from table_a where id in (select id from table_b);

ref 为空，表示没有走索引
rows 分别等于两张表行数，说明都是全表扫描
难怪执行时间很慢！

第二种写法：

explain delete a from table_a a, table_b b  where a.id = b.id;

根据 ref 和 rows 这两个字段可以看出，table_b 全量扫描，table_a 命中索引，所以会快很多。经过实际测试，2s就可以执行完了。

第三种写法：

explain delete from table_a where exists(select 1 from table_b b where id = b.id)

通过分析，这种写法同样没有走索引，实际执行也是很慢。

至于为啥 第一种跟第三种没走索引，有知道的伙伴 欢迎留言~~

如果对你有帮助，可以点个赞哦~~