本文整理自网络,侵删。
目录
- 前置说明
- 问题重现
- 删除数据原理
- 数据的复用
- 哪些操作会造成数据空洞
- 如何收缩表空间
- 小结
不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长
为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢?
本文将详细的分析上述问题,并给出正确回收表空间的方法
前置说明
目前大部分MySQL数据库都是用的 InnoDB 引擎,所以如无特殊说明,文中的实例都是基于InnoDB引擎的
在MySQL配置中有个配置项叫 innodb_file_per_table
将它设置为1之后,
每个表的数据会单独存储在一个以 .ibd
为后缀的文件中
如果 innodb_file_per_table
没有开启的话,
表的数据是存储在系统的共享表空间,这样即使删除了表,共享表空间也不会释放这部分空间
所以,通常情况下,都是将 innodb_file_per_table
选项设置为 1, 同时为了能直观的看到表数据文件的大小变化,文中的实例也都是基于开启了 此选项来说明的
问题重现
新建一张表ta
,表的结构如下
mysql> show create table ta\G *************************** 1. row *************************** Table: ta Create Table: CREATE TABLE `ta` ( `id` int(11) NOT NULL, `ia` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
使用下面的存储过程,向 ta
中批量插入数据
delimiter // create procedure multinsert(in beg int,in cnt int) begin declare icnt int default 0; declare tmp int default 0; while icnt < cnt do set icnt = icnt + 1; set tmp = beg + icnt; insert into ta(id,ia) values(tmp,tmp); end while; end// delimiter ;
在MySQL控制台执行 call multinsert(0,100000)
命令,往 ta
表插入10万条数据
mysql> call multinsert(0,100000); mysql> select count(*) from ta; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.02 sec)
查看磁盘上ta
表的数据文件 ta.ibd
的大小
[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ [root@ecs-centos-7 test]# ls -l ta.ibd -rw-r----- 1 mysql mysql 11534336 1月 3 23:14 ta.ibd
从上面的结果可以知道,ta
表插入10万条数据之后,ta.ibd
大小为 11534336 字节( 大约 11M )
现在我们使用 delete
命令删除一半儿表数据( 5万行记录 )
mysql> delete from ta where id between 1 and 50000; Query OK, 10000 rows affected (0.03 sec) mysql> select count(*) from ta; +----------+ | count(*) | +----------+ | 50000 | +----------+ 1 row in set (0.02 sec)
删除操作完成之后,再次查看磁盘上 ta.ibd
的大小
[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ [root@ecs-centos-7 test]# ls -l ta.ibd -rw-r----- 1 mysql mysql 11534336 1月 3 23:14 ta.ibd
从上面的结果可以知道,ta
表删除了一半儿,也就是5万行数据之后,ta.ibd
的大小是 11534336 字节( 约11M )
也就是说 ta
表删除数据前后,磁盘上表数据文件并没有缩小
要弄明白数据文件为什么没有缩小,就需要深入了解删除数据的原理
删除数据原理
我们都知道,InnoDB里的数据都是用B+树组织的,关于B+树的知识请参考 理解B+树
图(1)上面是InnoDB的索引示意图,其中用虚线框起来的节点是属于Page1数据页,叶子节点存储的是索引对应的数据,它们按照索引从小到大的顺序组成了一个有序数组
假如我们要删除Page1页中索引key值为 13 的数据,也即上图中红色部分
相关阅读 >>
navicat for mysql连接mysql报2005错误怎么办
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » 浅谈MySQL表空间回收的正确姿势
相关推荐
评论
管理员已关闭评论功能...