V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
aeo13
V2EX  ›  MySQL

mysql delete 操作速度慢!

  •  
  •   aeo13 · 2021-06-16 10:26:31 +08:00 · 6262 次点击
    这是一个创建于 1017 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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 顶不住

    37 条回复    2021-06-17 10:19:11 +08:00
    duanxianze
        1
    duanxianze  
       2021-06-16 10:29:37 +08:00
    互联网公司,从不真正删除任何数据,打个标记也就行了,mysql 的数据结构删除本来就是慢,非要删除就每晚空闲的时候对打了标记的统一删除
    AngryPanda
        2
    AngryPanda  
       2021-06-16 10:30:03 +08:00
    4.3 + 异步删除?
    sunnyday123
        3
    sunnyday123  
       2021-06-16 10:40:24 +08:00
    order_id IN () AND status IN (): IN 会让索引失效,应该是走了全表扫描.尝试去掉 in,status 和 order_id 建一个联合索引
    SmartKeyerror
        4
    SmartKeyerror  
       2021-06-16 10:40:26 +08:00
    InnoDB 存储引擎的话,删除数据可能会造成 B+Tree 中存在数据空洞,如果不进行表整理的话,随着频繁的插入和删除,.ibd 文件可能会越来越大,导致 delete from 执行时扫描了许多原本已经删除的行。最好方法当然是使用 is_deleted 标志位,如果一定要进行物理删除的话,可以在删除之前将可能被删除的数据 select 到 buffer pool 中,然后在进行删除,相当于做了一层缓存
    lostSoul
        5
    lostSoul  
       2021-06-16 10:42:26 +08:00
    还真有 delete 的? 我们都是 update 的 如果数据太多 更新也会慢 上队列异步慢慢慢处理吧
    aeo13
        6
    aeo13  
    OP
       2021-06-16 10:57:20 +08:00
    @sunnyday123 先查询出主键 id 的集合再 in 主键删除都不行。。
    aeo13
        7
    aeo13  
    OP
       2021-06-16 11:01:14 +08:00
    @SmartKeyerror 请问你指的是表的碎片率?
    sunnyday123
        8
    sunnyday123  
       2021-06-16 11:02:35 +08:00
    @aeo 只要有 in 索引就失效,orde_id =id and status in() 还是全表扫描,explain 看下扫描行数吧.
    aeo13
        9
    aeo13  
    OP
       2021-06-16 11:04:06 +08:00
    @sunnyday123 但是我在 4.2 也尝试使用过 where userid=用户 id 的条件,userid 是有设置索引,同样也很慢
    SmartKeyerror
        10
    SmartKeyerror  
       2021-06-16 11:04:42 +08:00
    @aeo13 对,从 EXPLAIN 的结果上来看,应该不至于这么慢才对,而且数据只有 300 万
    aeo13
        11
    aeo13  
    OP
       2021-06-16 11:05:47 +08:00
    @SmartKeyerror 查看了一下阿里云的表分析,表空间 3.04 GB,索引空间 943.88 MB,碎片率是 0.19%
    MoYi123
        12
    MoYi123  
       2021-06-16 11:20:33 +08:00
    看看触发器? 我之前有个 nt 同事,会在表 delete 的时候,把整个表备份一次。
    aeo13
        13
    aeo13  
    OP
       2021-06-16 11:22:59 +08:00
    @MoYi123 没有创建任何触发器哦
    CEBBCAT
        14
    CEBBCAT  
       2021-06-16 11:26:02 +08:00
    你这个 EXPLAIN 是 EXPLAIN 的按主键删除吧?其他几种删除方法呢?比如你说的 STATUS IN () 的那个。

    另外,建表语句可以给一下吗?

    如果表上的索引不少,我怀疑删除数据的时候也会被重建索引拖慢速度
    aeo13
        15
    aeo13  
    OP
       2021-06-16 11:35:28 +08:00
    @CEBBCAT
    EXPLAIN 的是 DELETE FROM order WHERE order_id IN () AND status IN ()这个的
    建表语句: https://imgur.com/undefined
    是的,索引不少,数据空间 2.11 GB,索引空间是 943.88 MB
    aeo13
        16
    aeo13  
    OP
       2021-06-16 11:35:53 +08:00
    @CEBBCAT 建表语句重新贴一下: https://imgur.com/2Nw4acp
    MoGeek
        17
    MoGeek  
       2021-06-16 15:22:49 +08:00
    推荐一楼的方法,直接打标识就好。可以定期删除
    westoy
        18
    westoy  
       2021-06-16 15:32:03 +08:00
    打 flag 然后半夜里定时清啊, 哪有生产环境在线硬删数据的......
    keepeye
        19
    keepeye  
       2021-06-16 15:35:17 +08:00
    实在不行呢,可以按 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
        20
    wolfie  
       2021-06-16 15:39:55 +08:00
    @sunnyday123
    只要有 in 索引就失效?上面不是贴了 explain 吗。
    beitayongguo
        21
    beitayongguo  
       2021-06-16 16:47:14 +08:00
    用 in 为啥会索引失效?

    从 explain 看没啥问题 让 dba 帮忙抓包看看呢
    vindac
        22
    vindac  
       2021-06-16 16:48:01 +08:00
    是不是在用多线程操作
    myd
        23
    myd  
       2021-06-16 16:53:35 +08:00
    创建联合索引 order_id, status
    aeo13
        24
    aeo13  
    OP
       2021-06-16 17:01:56 +08:00
    @myd 应该不是索引问题啊,查询后用 where 主键 in 删除都巨慢。。
    aeo13
        25
    aeo13  
    OP
       2021-06-16 17:02:09 +08:00
    @vindac 没有哦
    billccn
        26
    billccn  
       2021-06-16 17:04:56 +08:00
    有其他表外键到这个表吗?如果其他表相应的列不做索引,删除的时候只有查全表才能确定外键是不是还有效。
    aeo13
        27
    aeo13  
    OP
       2021-06-16 17:14:38 +08:00
    @billccn 没有外键
    offswitch
        28
    offswitch  
       2021-06-16 17:19:47 +08:00
    @sunnyday123 有 in 索引就失效?这是哪门子的说法? 4.0 以下才有这种情况。从上面抛出的执行计划上看是 range,走了 orderid,用了主键。
    offswitch
        29
    offswitch  
       2021-06-16 17:27:35 +08:00
    阿里云的 RDS,是不是配置比较低呢?事务的问题,用定时任务一条条删除不行吗?
    rockyliang
        30
    rockyliang  
       2021-06-16 19:00:30 +08:00
    感觉两个图片对不上,EXPLAIN 显示扫描行数是 1,但 RDS 慢日志显示扫描行数几千行。建议用 SHOW PROFILE 命令查看语句的详细执行情况,看到底慢在哪里
    huazaige
        31
    huazaige  
       2021-06-16 19:46:06 +08:00
    大致思路:
    可以创建一个结构一样的新表,把需要留下的数据 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
        32
    littlewing  
       2021-06-16 23:09:54 +08:00
    300 多万数据就算扫全表也不可能 6s 多,除非你单行数据特别大(几十 MB 这种)
    rekulas
        33
    rekulas  
       2021-06-16 23:10:53 +08:00
    1 检查是否索引导致删除慢
    2 分析下表试试
    不清楚你 tps 多少
    pgfourwell2020
        34
    pgfourwell2020  
       2021-06-16 23:14:42 +08:00
    @billccn 666
    aeo13
        35
    aeo13  
    OP
       2021-06-17 09:18:42 +08:00
    @offswitch 是的,配置较低。请问事务的问题指的是?
    Leigg
        36
    Leigg  
       2021-06-17 09:41:56 +08:00 via Android
    @aeo13 直接执行 delete from where order_id in order_ids...不可能慢吧,order_id 是有索引的,你创建组合索引来试试。
    raaaaaar
        37
    raaaaaar  
       2021-06-17 10:19:11 +08:00
    额,不都是软删除么
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2750 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 12:25 · PVG 20:25 · LAX 05:25 · JFK 08:25
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.