mysql 大表批量删除大量数据的实现方法

站长资源 2024/7/4 佚名

2 0 1

兴国资源网 Design By www.nnzcdc.com

问题参考自：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;

这样可以保证线上业务有新数据会同步。之后，将所有企业类型的数据，插入新表，同时如果已存在则证明发生了更新同步就不插入。个体户数据由于业务变化，并不在这个表上更新，所以这样通过了无表锁同步实现了大表的数据清理

mysql,大表批量删除,mysql,大表批量

兴国资源网 Design By www.nnzcdc.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

兴国资源网 Design By www.nnzcdc.com

评论“mysql 大表批量删除大量数据的实现方法”

暂无评论...

P70系列延期，华为新旗舰将在下月发布

3月20日消息，近期博主@数码闲聊站透露，原定三月份发布的华为新旗舰P70系列延期发布，预计4月份上市。

而博主@定焦数码爆料，华为的P70系列在定位上已经超过了Mate60，成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢？
根据目前爆料的消息来看，华为P70系列将推出三个版本，其中P70和P70 Pro采用了三角形的摄像头模组设计，而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智，但辨识度绝对拉满。

更新日志

2024年07月04日

mysql 大表批量删除大量数据的实现方法

解决方案

MySQL数据库自动补全命令的三种方法

MySQL的DATE_FORMAT函数的使用

评论“mysql 大表批量删除大量数据的实现方法”

P70系列延期，华为新旗舰将在下月发布

更新日志

友情链接