mysql5.7数据库表空间释放

应用环境：表存在longblob类型字段存储了pdf等图片，占用了大的存贮空间需要清理。

测试数据库版本：mysql5.7.41

搜集mysql表空间释放方法

1、drop table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM ; 2、truncate table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度; 3、delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间; 4、对于delete from table_name where xxx带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间; 5、delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。 6、delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

涉及工作表信息不便列出，假设库名：testdata 表名：students 列名：id, name, age, pdf, bz

方案一：optimize对表进行优化，可以回收表空间与提高I/O性能。

情况一：

InnoDB引擎的表分为共享表空间和独享表空间表，可以通过show variables like 'innodb_file_per_table'来查看是否开启独享表空间。 独享表空间的表是无法进行optimize操作的， 因为独享表空间的表当数据删除时会重组索引并释放对应的空间。

mysql>show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

对于开启了独享表空间不需要进行清理写入空值表空间就会释放，执行optimize table students;

返回：Table does not support optimize, doing recreate + analyze instead

情况二：

mysql>show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

OFF代表开启共享表空间没有打开，即采用的是默认的共享表空间。这个时候可以在mysql的datadir路径下看到一个非常大的文件ibdata1，这称此个文件存储了所有InnoDB表的数据与索引。 对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

设置存pdf文件字段为null update students set pdf = null;

重建表并锁表 optimize table students;

注意：OPTIMIZE TABLE运行过程中，MySQL会锁定表，并且会先重建表，要用剩余足够的表空间。

方案二：使用复制表、删除表释放表空间方案。

复制表： create table students_copy (like students); 插入新表 insert into students_copy(id,name,age,bz) select id,name,age,bz from students; 删除表 drop table able students; 修改表名称 rename table able students_copy to able students;

备注：删除操作需谨慎有条件的请先做好备份。

  1. 由于共享表空间所有表的数据与索引都存放于ibddata1文件中，随着数据量的增长会导致该文件越来越大。超过10G的时候查询速度就非常慢，因此在编译的时候最好开启独享表空间。因为mysql默认是关闭了独享表空间，下面有两个解决方案
  2. 方案一：先逻辑备份所有的数据库，将配置文件中innodb_file_per_table参数=1，再将备份导入
  3. 方案二：只要修改innodb_file_per_table参数，然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后，原来库中的表中的数据会继续存放于ibdata1中，新建的表才会使用独立表空间

如果对您有所帮助请《点赞》、《收藏》、《转发》，您的支持是我持续更新的动力，有疑问请留言