mysql8 表空间回收是自动管理的到达阈值释放回收磁盘空间

攻心的子乐

已于 2023-11-22 08:54:28 修改

阅读量3.2k

点赞数 1

分类专栏： Mysql 文章标签： mysql

于 2018-05-23 16:01:28 首次发布

本文链接：https://blog.csdn.net/qq_29883183/article/details/80421117

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

表空间
InnoDB其实是使用页为基本单位来管理存储空间的，默认的页的大小为 16KB。对于InnoDB存储引擎来讲，每个索引都对应着一棵B+树，该B+树的每个节点都是一个数据页，数据页之间不必要是物理连续的，因为数据页之间有双向链表来维护着这些页的顺序。

InnoDB的聚簇索引(也就是主键索引文件)的叶子节点存储了完整的用户记录。

为了更好的管理这些页，InnoDB提出了一个表空间或者文件空间(table space or file space)的概念。这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件(不同表空间对应的文件数量可能不同)。每一个表空间可以被划分为很多个页，我们的表数据就存放在某个表空间下的某些页里

在MySQL 8中，表空间回收是自动管理的，不需要手动干预。当表中的数据被删除时，表空间会自动释放被使用的空间。

在MySQL 8中，表空间回收不再由数据库管理员手动管理，而是由MySQL自动管理。从MySQL 8开始，InnoDB存储引擎引入了一个新的特性，称为"自动表空间碎片整理"。

在之前的MySQL版本中，当删除或更新表中的数据时，可能会导致表空间的碎片产生。这些碎片会占用额外的磁盘空间，并且需要进行手动整理以回收这些空间。然而，在MySQL 8中，InnoDB引擎会自动检测和整理这些表空间的碎片，无需手动干预。

当表空间的碎片达到一定数量时，InnoDB引擎会自动进行整理操作。这个过程是透明的，对数据库的日常操作没有影响。你只需要确保你有足够的磁盘空间来存储整理过程中产生的临时文件。

需要注意的是，虽然MySQL 8会自动管理表空间的回收，但你仍然可以手动执行表空间的维护和优化操作。例如，你可以使用ALTER TABLE语句进行表的重组织或重建，或者使用OPTIMIZE TABLE语句进行表空间的优化。这些操作可以帮助你更好地管理数据库的性能和空间使用。

MySQL8.0自带工具ibd2sdi解析ibd文件

MySQL8.0自带工具ibd2sdi解析ibd文件_ibd文件怎么打开_HumoChen99的博客-CSDN博客

MySQL5.6中默认是独立表空间Tablespace(表空间管理类型就这2种)
独立表空间就是采用和MyISAM 相同的方式, 每个表拥有一个独立的数据文件( .idb )

InnoDB引擎 frm ibd文件说明：
1.frm ：描述表结构文件，字段长度等

2.ibd文件是表数据文件

mysql5.6既有D:\java\mysql5.6\data\ipvacloudreport_site_day.frm 又有 D:\java\mysql5.6\data\ipvacloud\report_site_day.ibd文件

一，小结

结合mysql官方网站的信息，个人是这样理解的。当你删除数据时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频烦的表，要定期进行optimize，一个月一次，看实际情况而定了。

举个例子来说吧。有100个php程序员辞职了，但是呢只是人走了，php的职位还在那里，这些职位不会撤销，要等新的php程序来填补这些空位。招一个好的程序员，比较难。我想大部分时间会空在那里。哈哈。

当我们使用mysql进行delete数据，delete完以后，发现空间文件ibd并没有减少，这是因为碎片空间的存在，举个例子，一共公司有10号员工，10个座位，被开除了7个员工，但这些座位还是保留的，碎片整理就像，让剩下的3个员工都靠边坐，然后把剩下的7个作为给砸掉，这样就能释放出空间了

好处除了减少表数据与表索引的物理空间，还能降低访问表时的IO，这个比较理解，整理之前，取数据需要跨越很多碎片空间，这时需要时间的，整理后，想要的数据都放在一起了，直接拿就拿到了，效率提高
-- 清除碎片操作会暂时锁表，数据量越大，耗费的时间越长可以做个脚本，例如每月凌晨3点，检查DATA_FREE字段，
-- 大于自己认为的警戒值(碎片空间占数据和数据索引空间之和的百分比>0.30)的话，就清理一次

/*
清理mysql下实例下表碎片(当碎片字节空间占数据字节与索引字节空间之和大于0.30时, 这些表的碎片都需要清理,使用游标遍历清理) 定时任务事件每月凌晨4点调用此清理表碎片的任务
table_schema是数据库名 OPTIMIZE TABLE ipvacloud.article;
*/

DROP PROCEDURE IF EXISTS `optimize_table`;
DELIMITER ;;
CREATE  PROCEDURE `optimize_table`()
BEGIN
DECLARE tableSchema VARCHAR(100);
DECLARE tableName VARCHAR(100);
DECLARE stopFlag INT DEFAULT 0;
-- 大于30%碎片率的清理
DECLARE rs CURSOR FOR SELECT table_schema,table_name FROM information_schema.tables WHERE ((data_free/1024)/((data_length+index_length+data_free)/1024)) > 0.30;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;
OPEN rs;
WHILE stopFlag <> 1 DO 
FETCH NEXT FROM rs INTO tableSchema,tableName;
IF stopFlag<>1 THEN 
-- SET @table_optimize = CONCAT('ALTER TABLE `',tableName,'` ENGINE = INNODB');
SET @table_optimize = CONCAT('OPTIMIZE TABLE `',table_schema,'`.`',tableName,'`');
PREPARE sql_optimize FROM @table_optimize;    
EXECUTE sql_optimize;
END IF;
END WHILE;
CLOSE rs;
    END
;;
DELIMITER ;



/*
此定时任务 事件每月凌晨4点清理mysql实例下的表碎片
*/
DROP EVENT IF EXISTS `event_optimize_table`;
DELIMITER ;;
CREATE EVENT `event_optimize_table` ON SCHEDULE EVERY 1 MONTH STARTS '2017-12-15 04:00:00' ON COMPLETION PRESERVE ENABLE DO CALL optimize_table()
;;

DELIMITER ;