mysql中，一张表里有3亿数据。

Mysql中，一张表里有3亿数据，未分表，其中一个字段是企业类型，企业类型是一般企业和个体户，个体户的数据量差不多占50%，根据条件把个体户的行都删掉。请问如何操作？

福哥答案2021-01-09：

面试的时候，说的是分批删除，没让面试官满意。

网上答案:1：

MYSQL数据库因生产原因需要删除大量数据，因数据量太大接近上亿条，用常规delete删除小数据可以，删除几千万大数据量会非常慢，并且不会释放出磁盘空间，还需要optimize或repair来压缩数据表来释放硬盘空间，时间更长，需要几天时间，太慢了！因此采用新建表，导入无需删除的数据，然后删除老表，把新表改名为老表，步骤如下：

1、基于老表新建新表！

create table sell_new like sell;

2、插入数据（几千万的数据量一定要分批插入，一次50万为最佳，毕竟mysql的数据处理能力有限），可以按ID查询后插入！

insert into sell_new select * from sell where itemid>500000 and itemid<=5500000;

新表中只保留有用的数据，硬盘空间得以释放！

3、drop删除掉老表

drop table sell;

4、重命名新表为“sell”

alter table sell_new rename to sell;

以上是mysql数据库上亿级大数据如何快速删除操作流程！

网上答案2：

假设表的引擎是 Innodb， MySQL 5.7+。

删除一条记录，首先锁住这条记录，数据原有的被废弃，记录头发生变化，主要是打上了删除标记。也就是原有的数据 deleted_flag 变成 1，代表数据被删除。但是数据没有被清空，在新一行数据大小小于这一行的时候，可能会占用这一行。这样其实就是存储碎片。

之后，相关数据的索引需要更新，清除这些数据。并且，会产生对应的 binlog 与 redolog 日志。

如果 delete 的数据是大量的数据，则会：

1.如果不加 limit 则会由于需要更新大量数据，从而索引失效变成全扫描导致锁表，同时由于修改大量的索引，产生大量的日志，导致这个更新会有很长时间，锁表锁很长时间，期间这个表无法处理线上业务。

2.由于产生了大量 binlog 导致主从同步压力变大。

3.由于标记删除产生了大量的存储碎片。由于 MySQL 是按页加载数据，这些存储碎片不仅大量增加了随机读取的次数，并且让页命中率降低，导致页交换增多。

4.由于产生了大量日志，我们可以看到这张表的占用空间大大增高。

解决方案

我们很容易想到，在 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;

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

网上答案3：

删除达标上的多行数据时，innodb会超出lock table size的限制，最小化的减少锁表的时间的方案是：

1选择不需要删除的数据，并把它们存在一张相同结构的空表里 。

2重命名原始表，并给新表命名为原始表的原始表名 。

3删掉原始表 。

总结一下就是，当时删除大表的一部分数据时可以使用 见新表，拷贝数据，删除旧表，重命名的方法。

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。