问题参考自:https://www.zhihu.com/question/440066129/answer/1685329456 ,mysql中,一张表里有3亿数据,未分表,其中一个字段是企业类型,企业类型是一般企业和个体户,个体户的数据量差不多占50%,根据条件把个体户的行都删掉。请问如何操作?答案为个人原创
假设表的引擎是 Innodb, MySQL 5.7+
删除一条记录,首先锁住这条记录,数据原有的被废弃,记录头发生变化,主要是打上了删除标记。也就是原有的数据 deleted_flag 变成 1,代表数据被删除。但是数据没有被清空,在新一行数据大小小于这一行的时候,可能会占用这一行。这样其实就是存储碎片。
之后,相关数据的索引需要更新,清除这些数据。并且,会产生对应的 binlog 与 redolog 日志。
如果 delete 的数据是大量的数据,则会:
- 如果不加 limit 则会由于需要更新大量数据,从而索引失效变成全扫描导致锁表,同时由于修改大量的索引,产生大量的日志,导致这个更新会有很长时间,锁表锁很长时间,期间这个表无法处理线上业务。
- 由于产生了大量 binlog 导致主从同步压力变大
- 由于标记删除产生了大量的存储碎片。由于 MySQL 是按页加载数据,这些存储碎片不仅大量增加了随机读取的次数,并且让页命中率降低,导致页交换增多。
- 由于产生了大量日志,我们可以看到这张表的占用空间大大增高。
解决方案
我们很容易想到,在 delete 后加上 limit 限制控制其数量,这个数量让他会走索引,从而不会锁整个表。
但是,存储碎片,主从同步,占用空间的问题并没有解决。可以在删除完成后,通过如下语句,重建表:
alter table 你的表 engine=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
注意这句话其实就是重建你的表,虽然你的表的引擎已经是 innodb 了,加上后面的, ALGORITHM=INPLACE, LOCK=NONE 可以不用锁表就重建表。
还有一种方案是,新建一张同样结构的表,在原有表上加上触发器:
create trigger person_trigger_update AFTER UPDATE on 原有表 for each row begin set @x = "trigger UPDATE"; Replace into 新表 SELECT * from 原有表 where 新表.id = 原有表.id; END IF; end;
这样可以保证线上业务有新数据会同步。之后,将所有企业类型的数据,插入新表,同时如果已存在则证明发生了更新同步就不插入。个体户数据由于业务变化,并不在这个表上更新,所以这样通过了无表锁同步实现了大表的数据清理
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
白云城资源网 Copyright www.dyhadc.com
暂无“mysql 大表批量删除大量数据的实现方法”评论...
更新日志
2024年10月07日
2024年10月07日
- 群星《前途海量 电影原声专辑》[FLAC/分轨][227.78MB]
- 张信哲.1992-知道新曲与精丫巨石】【WAV+CUE】
- 王翠玲.1995-ANGEL【新艺宝】【WAV+CUE】
- 景冈山.1996-我的眼里只有你【大地唱片】【WAV+CUE】
- 群星《八戒 电影原声带》[320K/MP3][188.97MB]
- 群星《我的阿勒泰 影视原声带》[320K/MP3][139.47MB]
- 纪钧瀚《胎教古典音乐 钢琴与大提琴的沉浸时光》[320K/MP3][148.91MB]
- 刘雅丽.2001-丽花皇后·EMI精选王【EMI百代】【FLAC分轨】
- 齐秦.1994-黄金十年1981-1990CHINA.TOUR.LIVE精丫上华】【WAV+CUE】
- 群星.2008-本色·百代音乐人创作专辑【EMI百代】【WAV+CUE】
- 群星.2001-同步过冬AVCD【环球】【WAV+CUE】
- 群星.2020-同步过冬2020冀待晴空【环球】【WAV+CUE】
- 沈雁.1986-四季(2012梦田复刻版)【白云唱片】【WAV+CUE】
- 纪钧瀚《胎教古典音乐 钢琴与大提琴的沉浸时光》[FLAC/分轨][257.88MB]
- 《国语老歌 怀旧篇 3CD》[WAV/分轨][1.6GB]