MySQL删除大表文件解决方案

最新推荐文章于 2025-02-12 16:29:40 发布

一路向南panda

最新推荐文章于 2025-02-12 16:29:40 发布

阅读量1.3k

点赞数

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/hljliao/article/details/130901568

版权

文章介绍了在MySQL中删除大表时，直接droptable可能带来的IO问题和对系统的影响。提出了解决方案，包括使用硬链接保存表文件，然后通过truncate命令逐步减小文件大小，确保低影响删除。同时，文中还展示了在Docker环境下模拟该操作的步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL删除大表文件解决方案

背景：

当生产环境有一些需要删除的临时数据表或历史表时，我们直接 drop table 会将表结构，数据，磁盘中物理文件直接删除。

如果表中数据巨大，占用空间太大。直接删除时，会产生大量IO，会对系统内业务产生影响。甚至还有可能影响主从等问题。

解决方案：

数据表.ibd文件，创建文件硬链接
drop table删除表结构，数据，磁盘中物理文件（物料文件删除的是一个文件的硬链接）
使用truncate对物理文件进行截断，直至足够小后直接删除

操作步骤：

解决方案理解的直接跳过。此步骤是模拟测试一遍实际效果。

准备环境数据

直接操作的跳过此操作步骤

拉取MySQL5.7镜像，并启动一个实例，容器内部端口3006、外部端口3307、用户名root、密码123456

docker pull mysql:5.7

docker images

docker run -itd --name mysql-5.7 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

docker ps

在这里插入图片描述

进入MySQL实例，进入MySQL命令行

docker exec -it mysql-5.7 sh

mysql -h 127.0.0.1 -P 3306 -u root -p123456

设置独立表空间参数
1. ```
-- ON 独立表空间文件参数
show variables like '%innodb_file_per_table%';
```
3. 简单说明下，innodb_file_per_table为ON时，会对每个表都生成独立的表空间文件，即ibd文件，内保存了表的数据和索引文件。
4. 若innodb_file_per_table为OFF时，会把表统一保存到innodb系统表空间文件中，只会生成单独的frm文件，内保存了表的元数据（表结构）
5. 可以简单设置开关体验下，删除单表的大文件时，此开关必须为ON

创建测试数据

-- 创建数据库,库名为liao001
CREATE DATABASE `liao001` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

-- 选择进入liao001数据库
use liao001;

-- 创建测试表
CREATE TABLE `ttest01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `creat_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 测试插入数据
INSERT INTO ttest01 ( `name`, `creat_date`) VALUES ( UUID(), NOW());

-- 过程增加200W数据

-- 设置SQL结束符
delimiter //

CREATE DEFINER=`root`@`%` PROCEDURE `addTest`()
BEGIN
	DECLARE i INT;
	set i=0;
	START TRANSACTION;
	WHILE i<2000000 DO
		INSERT INTO `ttest01` (`name`,`creat_date`) VALUES (UUID(),NOW());
		IF(i>=50000 && MOD(i,50000) = 0)THEN
			COMMIT;
			SELECT CONCAT(i/50000,'COMMIT') AS log;
		END IF;
		set i = i+ 1;
	END WHILE;
	commit;
END

//

-- 恢复SQL结束符
delimiter ;

select count(1) from ttest01;

-- 执行过程
call addTest();

select count(1) from ttest01;

操作数据文件

exit退出数据命令行。也可以重新docker exec进入MySQL实例

进入数据库物理文件目录位置

-- liao001 是数据库名字
cd /var/lib/mysql/liao001

ls -hl
-- ttest01是表的名字，存储了200W的数据
ln ttest01.ibd ttest01.ibd.bak

可以看到ttest01.ibd和ttest01.ibd.bak都是144M。这里如果有主从或主备的数据库也要创建一个硬链接。

在这里插入图片描述

下面去MySQL命令控制台执行删除表

DROP TABLE ttest01;

可以看到drop表后，表数据ibd的文件已经删除了，还有一个我们刚才创建的硬链接。其实就是一个文件。只不过在MySQL中drop中删除的是这个文件的一次链接，把大io的这次操作在后续处理。

当MySQL调用os执行rm删除操作时，其实是删掉硬链接文件，那么链接数将会被减去1，以作出变动，不过文件仍然存在，因为还有另一个连接指向硬件上的同一个物理位置。只有其所有硬链接都被删除，并且链接数被减至0时，文件才会被删除。

在这里插入图片描述

下面用truncate命令减少文件大小，这里分享一个脚本

## 设置truncate命令路径
truncate_exe=/usr/bin/truncate
remove_exe=/usr/bin/rm
file_path=/var/lib/mysql/liao001/ttest01.ibd.bak

## 获取循环次数
file_length_byte=`ls -l ${file_path} |awk '{print $5}'`
## 100M为单元的次数
let batch_count=${file_length_byte}/1024/1024/100

## 循环遍历
for batch_index in `seq ${batch_count} -1 1`;
do
echo "batch index: ${batch_index}，count：${batch_count}";
let tmp_file_mb=batch_index*100;
${truncate_exe} -s ${tmp_file_mb}M ${file_path};
sleep 0.1;
done

## 彻底删除文件	
${remove_exe} -f ${file_path}

执行命令

time sh del_text.sh

-- 结果
sh-4.2# time sh del_tesx.sh
batch index: 17，count：17
batch index: 16，count：17
batch index: 15，count：17
batch index: 14，count：17
batch index: 13，count：17
batch index: 12，count：17
batch index: 11，count：17
batch index: 10，count：17
batch index: 9，count：17
batch index: 8，count：17
batch index: 7，count：17
batch index: 6，count：17
batch index: 5，count：17
batch index: 4，count：17
batch index: 3，count：17
batch index: 2，count：17
batch index: 1，count：17

real	0m3.750s
user	0m0.040s
sys	0m1.958s