面试官：使用 MySQL 时，你们是怎样做大表清理的？

本文链接：https://blog.csdn.net/zjj2006/article/details/148655397

大家好，我是君哥。

使用 MySQL 时，我们经常会遇到大表清理的情况。做大表清理的目的，一般是为了减表空间使用，提高表的操作性能。今天来聊一聊怎样做大表清理。

1.SQL 清除

最直接的方式就是使用 delete 语句来删除，可以使用主键：

delete from test1 where id in(...) order by id;

delete 语句存在的问题是 InnoDB 引擎只会把 SQL 中删除的记录标记为删除，并不会回收磁盘空间，也就是说磁盘数据文件大小并不会减小。当然删除的这条数据在磁盘文件中的位置是可以复用的，比如删除一条 id 为 4 的记录，就可以成功插入一条 id 为 4 的记录。

delete 语句删除还有一个缺点是会留下大量磁盘碎片，影响索引性能。

注意： 1.使用 delete 语句时可以使用 order by 对删除条件进行排序，这样可以保证删除顺序，避免全表扫描； 2.删除之前要做备份。

2.逻辑删除

既然使用 delete 语句做清理不能释放表空间，那我们不如对数据做逻辑删除。

//del_flag=1 表示逻辑删除
update test1 set del_flag = '1' where id in(...);

这样做的好处是并不会留下磁盘碎片，对索引性能没有影响。但也存在缺点，那就是归档的时候需要对所有数据的删除标识（del_flag）做判断。

3.使用分区表

如果业务上没有特殊要求，可以使用分区表，对分区直接做清理。比如以月为单位创建分区，对三个月以上的表做归档后直接把分区表 drop 掉。

ALTER TABLE test1 DROP PARTITION part202503;

分区表清理适合用于定期清理的场景，而且分区键必须要跟清理条件相符合，每月按周、按月、按季度，建表之前需要提前规划好清理策略。

如果分区键需要按照业务属性（比如身份证号）来定义，按照分区清理可能就不合适了。

4.使用临时表

MySQL 官方文档给的一种删除方式是先把不删除的数据备份到一张临时表，然后再把原表改名，把临时表改成原表名字，最后 drop 掉原表。

//1.备份数据到临时表
INSERT INTO test1_copy SELECT * FROM test1 WHERE id in(...) ;
//2.把原表改名，把临时表改名为原表名字
RENAME TABLE test1 TO test1_old, test1_copy TO test1_copy;
//3.删除原表
DROP TABLE test1_old;

RENAME 语句可以防止其他会话再操作 test1 表，所以这个过程不会有并发问题。

但是在写入频率高的情况下，如果服务不中断，并不能保证执行备份语句和 rename 语句之间没有数据写入。