我想要删除数据库三百万的数据怎么办？

一夜过去我插入了3百多万条脏数据到了数据库，里面有4百多条正常的数据。

我第一想法是 delete from table where id>400,后来发现是我天真了，后面会分析一下delete进行了那些操作。

可是遇到上述情况我应该怎么处理呢？，我对那 400 条数据进行备份，然后直接使用 truncate table table_name 进行删除，

delete 操作

MySQL delete 都做了哪些操作？

1. 根据where条件对删除表进行索引/全表扫描，检查是否符合where条件，该阶段会对扫描中所有行进行加锁。该阶段是最大的资源消耗隐患，若表的数据量大且delete操作无法有效利用索引减少扫描数据量，该步骤对于数据库带来的锁争用、cpu/io资源的消耗都是巨大的
2. 对不能够被where条件匹配的行施加的锁会在条件检查后予以释放，InnoDB仅锁定需要删除的行。这可以有效地降低锁争用，但是我们仍需要关注的一点是，一次性删除大批量的数据，该操作将会产生巨大的binlog事务日志，这对于MySQL自身以及主从架构中的从库都是不友好的，可能带来叫的复制延迟。

如何优化delete操作？

• delete全表删除操作需要谨慎，可考虑使用truncate操作
• delete … where … 中，where过滤条件尽量保证可有效利用索引减少数据扫描量，避免全表扫描
• 对于大批量数据删除且where条件无索引的情况，delete操作可额外增加自增长主键或者含索引的时间字段，进行分批删除操作，每次删除少量数据，分多批次执行。
• 对于保留近期数据删除历史数据的经典场景，可创建同结构的xxx_tmp表并通过insert xxx_tmp select …操作将需要的数据保留至tmp表中、然后通过rename操作将当前业务表xxx替换为xxx_bak表，xxx_tmp表替换为当前业务表名xxx，后续手动删除无用的大表xxx_bak

Truncate 操作

从逻辑意义上来讲，truncate操作就是删除表中所有记录行，但是又与delete from table_name wehre 1=1这种操作不一样。MySQL为了提高删除整张表数据的性能，truncate操作其本质上其实是先drop table然后在re-create table。也真因如此，truncate操作是一个不可回滚的DDL操作。

MySQL truncate 都做了哪些操作？

1. truncate操作实际上分为drop、re-create两步
2. drop操作的第一个阶段，是对Buffer pool页面进行清除的过程，将表相关的数据页从flush链中删除，而不需要做flush操作。该步骤的瓶颈点主要在于flush队列的删除操作必须持有对应buffer pool instance的锁并进行遍历搜索，如果buffer pool instance比较大且flush链中需要删除的数据页很多，该操作会导致其他事务在获取buffer pool instance的锁时被阻塞，从而影响数据库的性能
3. drop操作的第二个阶段，是删除ibd磁盘文件的过程。删除数据库物理文件越大I/O资源消耗越大，删除操作耗时越久
4. re-create操作阶段，只要删除表的.frm文件完好无损，在drop table之后就可以按照原表结构信息进行重建，重建后表的auto_increment值会被重置

如何优化truncate操作带来的资源消耗

1. 对于truncate操作中的drop表第一阶段，当分配给MySQL实例的innodb_buffer_pool_size超过1GB时，合理的设置innodb_buffer_pool_instances参数，提高并发的同时也变相的减少扫描buffer pool instance时锁资源占用耗时
2. 对于truncate操作中的drop表第二阶段，在删除对应表之前，先对改表的.ibd文件创建一个硬连接，加快MySQL层面的drop操作执行效率，减少对数据库层面的性能损耗。后续手动对操作系统层面我们做的硬连接进行清理

解决办法最好的解决办法就是，先备份一个表，然后删除原有的表，再将备份的表重命名就好

-- 备份400条数据
create table newtable_20220304 as select * from table where id>400;
-- 删除含有脏数据的表
delete from table;
-- 给表重命名
rename table newtable_20220304 to table;

参考：
MySQL如何优雅的做大表删除？_三思的博客-程序员ITS404