mysql delete 操作速度慢！

[DoraemonWOO]

1 、由于业务需要，需要对接第三方的数据，如某个用户访问时会执行同步接口，之后按顺序对数据执行删除、修改和新增。目前增改都没有问题，但是删除会很慢，导致每分钟都会有好几条慢日志生成。

2 、该表数据目前是三百多 W 。

3 、目前的删除 sql 是 DELETE FROM order WHERE order_id IN () AND status IN ()，order_id 有几个到几十个不等，status 有几个。

阿里云 RDS 的慢日志（截取某一分钟）： https://imgur.com/S5N6w2v EXPLAIN 结果： https://imgur.com/dhCSC3a

4 、所作尝试 1 ）改用 select 所有主键 id 再 in 删除主键 id，发现执行时间更长 2 ）改用 delete where userid = 用户 id，发现执行时间更长 3 ）改用循环删除操作，CPU 顶不住

[duanxianze]

互联网公司，从不真正删除任何数据，打个标记也就行了，mysql 的数据结构删除本来就是慢，非要删除就每晚空闲的时候对打了标记的统一删除

[AngryPanda]

4.3 + 异步删除？

[sunnyday123]

order_id IN () AND status IN (): IN 会让索引失效,应该是走了全表扫描.尝试去掉 in,status 和 order_id 建一个联合索引

[SmartKeyerror]

InnoDB 存储引擎的话，删除数据可能会造成 B+Tree 中存在数据空洞，如果不进行表整理的话，随着频繁的插入和删除，.ibd 文件可能会越来越大，导致 delete from 执行时扫描了许多原本已经删除的行。最好方法当然是使用 is_deleted 标志位，如果一定要进行物理删除的话，可以在删除之前将可能被删除的数据 select 到 buffer pool 中，然后在进行删除，相当于做了一层缓存

[lostSoul]

还真有 delete 的？ 我们都是 update 的 如果数据太多 更新也会慢 上队列异步慢慢慢处理吧

[DoraemonWOO]

@sunnyday123 先查询出主键 id 的集合再 in 主键删除都不行。。

[DoraemonWOO]

@SmartKeyerror 请问你指的是表的碎片率？

[sunnyday123]

@aeo 只要有 in 索引就失效,orde_id =id and status in() 还是全表扫描,explain 看下扫描行数吧.

[DoraemonWOO]

@sunnyday123 但是我在 4.2 也尝试使用过 where userid=用户 id 的条件，userid 是有设置索引，同样也很慢

[SmartKeyerror]

@aeo13 对，从 EXPLAIN 的结果上来看，应该不至于这么慢才对，而且数据只有 300 万

[DoraemonWOO]

@SmartKeyerror 查看了一下阿里云的表分析，表空间 3.04 GB，索引空间 943.88 MB，碎片率是 0.19%

[MoYi123]

看看触发器？ 我之前有个 nt 同事，会在表 delete 的时候，把整个表备份一次。

[DoraemonWOO]

@MoYi123 没有创建任何触发器哦

[CEBBCAT]

你这个 EXPLAIN 是 EXPLAIN 的按主键删除吧？其他几种删除方法呢？比如你说的 STATUS IN () 的那个。

另外，建表语句可以给一下吗？

如果表上的索引不少，我怀疑删除数据的时候也会被重建索引拖慢速度

[DoraemonWOO]

@CEBBCAT
EXPLAIN 的是 DELETE FROM order WHERE order_id IN () AND status IN ()这个的
建表语句： https://imgur.com/undefined
是的，索引不少，数据空间 2.11 GB，索引空间是 943.88 MB

[DoraemonWOO]

@CEBBCAT 建表语句重新贴一下： https://imgur.com/2Nw4acp

[MoGeek]

推荐一楼的方法，直接打标识就好。可以定期删除

[westoy]

打 flag 然后半夜里定时清啊， 哪有生产环境在线硬删数据的......

[keepeye]

实在不行呢，可以按 status 来遍历删除，这样 order_id 索引大概能生效
DELETE FROM order WHERE order_id IN (?) AND status = 1;
DELETE FROM order WHERE order_id IN (?) AND status = 2;
...
DELETE FROM order WHERE order_id IN (?) AND status = N;

[wolfie]

@sunnyday123
只要有 in 索引就失效？上面不是贴了 explain 吗。

[beitayongguo]

用 in 为啥会索引失效？

从 explain 看没啥问题 让 dba 帮忙抓包看看呢

[vindac]

是不是在用多线程操作

[myd]

创建联合索引 order_id, status

[DoraemonWOO]

@myd 应该不是索引问题啊，查询后用 where 主键 in 删除都巨慢。。

[DoraemonWOO]

@vindac 没有哦

[billccn]

有其他表外键到这个表吗？如果其他表相应的列不做索引，删除的时候只有查全表才能确定外键是不是还有效。

[DoraemonWOO]

@billccn 没有外键

[offswitch]

@sunnyday123 有 in 索引就失效？这是哪门子的说法？ 4.0 以下才有这种情况。从上面抛出的执行计划上看是 range，走了 orderid，用了主键。

[offswitch]

阿里云的 RDS，是不是配置比较低呢？事务的问题，用定时任务一条条删除不行吗？

[rockyliang]

感觉两个图片对不上，EXPLAIN 显示扫描行数是 1，但 RDS 慢日志显示扫描行数几千行。建议用 SHOW PROFILE 命令查看语句的详细执行情况，看到底慢在哪里

[huazaige]

大致思路：
可以创建一个结构一样的新表，把需要留下的数据 copy 到这张新表里，然后直接删除老表，重命名新表
1 、基于老表创建一张新表：create table order_new like order;
2 、把需要留下的数据 插入到新表里：insert into order_new select * from order where id>1 and id<=3000000;
3 、删除老表：drop table order;
4 、重命名新表：alter table order_new rename to order;

[littlewing]

300 多万数据就算扫全表也不可能 6s 多，除非你单行数据特别大（几十 MB 这种）

[rekulas]

1 检查是否索引导致删除慢
2 分析下表试试
不清楚你 tps 多少

[pgfourwell2020]

@billccn 666

[DoraemonWOO]

@offswitch 是的，配置较低。请问事务的问题指的是？

[Leigg]

@aeo13 直接执行 delete from where order_id in order_ids...不可能慢吧，order_id 是有索引的，你创建组合索引来试试。

[raaaaaar]

额，不都是软删除么