mysql 大表批量删除大量数据的实现方法
问题参考自：https://www.zhihu.com/question/440066129/answer/1685329456，mysql中，一张表里有3亿数据，未分表，其中一个字段是企业类型，企业类型是一般企业和个体户，个体户的数据量差不多占50%，根据条件把个体户的行都删掉。请问如何操作？答案为个人原创
假设表的引擎是Innodb，MySQL5.7+
删除一条记录，首先锁住这条记录，数据原有的被废弃，记录头发生变化，主要是打上了删除标记。也就是原有的数据deleted_flag变成1，代表数据被删除。但是数据没有被清空，在新一行数据大小小于这一行的时候，可能会占用这一行。这样其实就是存储碎片。
之后，相关数据的索引需要更新，清除这些数据。并且，会产生对应的binlog与redolog日志。
如果delete的数据是大量的数据，则会：
- 如果不加limit则会由于需要更新大量数据，从而索引失效变成全扫描导致锁表，同时由于修改大量的索引，产生大量的日志，导致这个更新会有很长时间，锁表锁很长时间，期间这个表无法处理线上业务。
- 由于产生了大量binlog导致主从同步压力变大
- 由于标记删除产生了大量的存储碎片。由于MySQL是按页加载数据，这些存储碎片不仅大量增加了随机读取的次数，并且让页命中率降低，导致页交换增多。
- 由于产生了大量日志，我们可以看到这张表的占用空间大大增高。
解决方案
我们很容易想到，在delete后加上limit限制控制其数量，这个数量让他会走索引，从而不会锁整个表。
但是，存储碎片，主从同步，占用空间的问题并没有解决。可以在删除完成后，通过如下语句，重建表：
altertable你的表engine=InnoDB,ALGORITHM=INPLACE,LOCK=NONE;
注意这句话其实就是重建你的表，虽然你的表的引擎已经是innodb了，加上后面的,ALGORITHM=INPLACE,LOCK=NONE可以不用锁表就重建表。
还有一种方案是，新建一张同样结构的表，在原有表上加上触发器：
createtriggerperson_trigger_updateAFTERUPDATEon原有表foreachrow beginset@x="triggerUPDATE"; Replaceinto新表SELECT*from原有表where新表.id=原有表.id; ENDIF; end;
这样可以保证线上业务有新数据会同步。之后，将所有企业类型的数据，插入新表，同时如果已存在则证明发生了更新同步就不插入。个体户数据由于业务变化，并不在这个表上更新，所以这样通过了无表锁同步实现了大表的数据清理
到此这篇关于mysql大表批量删除大量数据的实现方法的文章就介绍到这了,更多相关mysql大表批量删除内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！