剩余空间不足_MySQL如何解决delete大量数据后空间不释放的问题

MySQL几种碎片整理方案总结（解决大量数据后空间无法释放的问题）

1.背景知识? 1.1 为什么会有碎片?

在MySQL中，这两者都可能引起页面分裂，从而导致碎片化。

对于大量的文件来说，也会出现文件碎片，物理存储的最小分配单位是页，也可能导致页面分裂，频繁的页面分裂会使得页面变得稀疏，填充不规则，最终导致数据碎片化。

该语句实际上只是对数据进行了标记，并将其记录在一个链表中，这样就形成了一个空白的空间。

在 中，有些行被删除了，但是它们只是被标记为“已删除”，而不是从索引中物理删除，因此空间并没有真正被释放和回收。 的 Purge 线程会异步清理这些无用的索引键和行。

当执行插入操作时，MySQL会尝试使用空闲的空间，但是如果某个空闲的空间还没有被适当大小的数据占用，那么它仍然无法完全被占满，从而造成碎片；

总结：

删除，立即释放磁盘空间，无论它是否和；

from 删除表内所有数据，会立刻释放磁盘空间（应该是特殊处理，也是合理的），但是不会释放磁盘空间；

对于from where xxx;有条件的删除，不管是不是都会释放磁盘空间；

表操作结束后的使用会立即释放磁盘空间。不管是 还是 。所以为了达到释放磁盘空间的目的，在后面进行表操作。

虽然from表创建后不会释放磁盘空间，但是下次插入数据时仍然可以使用。

表的增删改操作可能会造成数据空洞，当对表进行大量的增删改操作时，出现数据空洞的可能性比较大。

MySQL删除数据几种情况以及是否释放磁盘空间：

1.2 碎片带来的问题 2.如何清理碎片？

解决问题的前提是先找到问题，这样才能找到正确的解决方法。

2.1. 检查表的碎片

查看数据库里各个碎片表

mysql> select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';+-----------------------------------------------------------+-----------+--------+| concat('optimize table ',table_schema,'.',table_name,';') | DATA_FREE | ENGINE |+-----------------------------------------------------------+-----------+--------+| optimize table abc.t_user_answer;                    		|   2097152 | InnoDB || optimize table mysql.time_zone_transition;                |   4194304 | InnoDB || optimize table mysql.time_zone_transition_type;           |   4194304 | InnoDB || optimize table mysql.user;                                |   4194304 | InnoDB |。。。。

检查指定表的碎片

 mysql> show table status like 't_user'G *************************** 1. row ***************************            Name: t_user          Engine: InnoDB         Version: 10      Row_format: Dynamic            Rows: 4333  Avg_row_length: 589     Data_length: 2555904 Max_data_length: 0    Index_length: 2719744       Data_free: 4194304  Auto_increment: NULL     Create_time: 2021-11-19 10:13:31     Update_time: 2022-04-20 14:28:42      Check_time: NULL       Collation: utf8mb4_general_ci        Checksum: NULL  Create_options:         Comment: 1 row in set (0.00 sec)

：表示碎片的字节数，如果数据表被频繁删除或者修改，会频繁删除大量的记录或者修改变长字段的表。

找到碎片最多的表

SELECT table_schema, TABLE_NAME, concat(data_free/1024/1024, 'M') as data_freeFROM `information_schema`.tablesWHERE data_free > 3 * 1024 * 1024	AND ENGINE = 'innodb'ORDER BY data_free DESC

2.2 清理碎片（回收空间）的方法

官方文档参考

通常有几种方法

更改表 =；（本质上）

表；（本质上是一样的，但在不同的创作中会有差异）

ALTER TABLE FORCE（相当于表中的 alter table =;）

批量表空间优化

gh-ost/pt-osc

pt---（本质上是先备份旧表数据，再备份旧表）

1. alter table=原理介绍

这其实就是一个NULL操作，表面上什么都没做，但实际上对碎片进行了重组。在进行优化操作时，其实执行的是一个空的ALTER命令，但这个命令也起到了优化的作用，它会重建整个表，删除未使用的空白空间。

ALTER TABLE = 对表执行“空” ALTER TABLE ，可用于对表执行操作，如 15.11.4 中的“ 表”。 ALTER TABLE FORCE 对表执行操作相同。

    MySQL5.6 开始采用 Inplace 方式重建表，Alter 期间，支持 DML 查询和更新操作，语句为 alter table t engine=innodb, ALGORITHM=inplace;之所以支持 DML 更新操作，是因为数据拷贝期间会将 DML 更新操作记录到 Row log 中。重建过程中最耗时的就是拷贝数据的过程，这个过程中支持 DML 查询和更新操作，对于整个 DDL 来说，锁时间很短，就可以近似认为是 Online DDL。执行过程：1、获取 MDL（Meta Data Lock）写锁，innodb 内部创建与原表结构相同的临时文件    2、拷贝数据之前，MDL 写锁退化成 MDL 读锁，支持 DML 更新操作    3、根据主键递增顺序，将一行一行的数据读出并写入到临时文件，直至全部写入完成。并且，会将拷贝期间的 DML 更新操作记录到 Row log 中    4、上锁，再将 Row log 中的数据应用到临时文件    5、互换原表和临时表表名    6、删除临时表

2. 表 xxx；

TABLE语句可以重新组织表和索引的物理存储，减少存储空间，提高访问I/O效率，类似于碎片整理的功能。

MySQL可以通过建表语句释放表空间，重新组织表数据和索引的物理页，减少表占用的空间，优化读写性能。

使用语法

[本地|]表[，]…

注意：

需要有足够的空间来执行TABLE。（剩余空间必须大于表的大小）

它仅对独立表空间（e=1）有用，对共享表空间没有影响。

对于共享表空间，如果需要瘦身，必须先将数据导出，然后删除，再设置为独立表空间，然后再导入数据。

对于表，TABLE 的工作方式如下

对于表，TABLE 映射到 ALTER TABLE ... FORCE（或表等效项，ALTER TABLE ... FORCE），它重建表以更新索引统计信息并释放聚集索引中未使用的空间。

+----------+----------+----------+---------------------------------------+ | Table | Op| | | +-----------+-----------+-----------+---------------------------------------+ | test.foo | | note| Table does not , doing + | | test.foo | | | OK| +----------+----------+----------+----------+--------------------------------------+ # 不过这个提示可以忽略，严格来说，说不支持 table 也不完全准确，因为 MYSQL 文档中说，对于 table，MYSQL 会用 ALTER TABLE force + （相当于 doing and）来执行这个命令。所以最后还是会看到 OK 状态。 #

对于表格，TABLE 的工作方式如下：

1. 如果表中删除或分离了行，请修复该表。

2.如果索引页未排序，请对其进行排序。

3. 如果表的统计信息不是最新的（并且无法通过对索引进行排序来修复），则更新它们。

**执行file write时也会发现错误。**

推荐参考这篇文章：

Mysql使用表时file write . 错误的解决办法

官网介绍声明

在Mysql 5.6之前，MySQL在TABLE操作过程中会锁表，5.6之后，DDL大大减少了锁表时间。

alter table、table 和 table 之间的区别

桌子;

桌子;

4. TABLE 和 ALTER TABLE xxxx = 哪个更好？

另外：对于类型表，使用ALTER TABLE xxxx=显然比TABLE方法更好。

2.3 官方建议

MySQL 官方建议不要太频繁（每小时或者每天）进行碎片整理，一般一周或者一个月只需要整理一次，根据实际情况而定（我们现在每个月凌晨四点会清理所有 MySQL 实例下的表碎片）