MySQL如何解决delete大量数据后空间不释放的问题

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

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

MySQL 中 insert 与 update 都可能导致页分裂，这样就存在碎片。

对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。

delete 语句实际上只是给数据打个标记，并且记录到一个链表中，这样就形成了留白空间。

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

当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

总结：

drop ，truncate 立刻释放磁盘空间 ，不管是 Innodb和MyISAM ;

delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;

对于 delete from table_name where xxx; 带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;

delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。

delete 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)

Data_free: 4194304 就代表碎片的byte数。如果经常删改数据表，会造成大量的Data_free 频繁 删除记录 或修改有可变长度字段的表。

找到碎片化最严重的表

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 tb_test engine=innodb; （本质上是 recreate）

optimize table tb_test；（本质上是 recreate，但是在不同创建下会有区别）

ALTER TABLE tablename FORCE (在InnoDB表中等价于 alter table tb_test engine=innodb; )

mysqlcheck批量表空间优化

gh-ost/pt-osc

pt-online-schema-change （本质上也是 先备份旧表数据，然后 truncate 旧表）

1. alter table tb_test engine=innodb 原理介绍

这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间.

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 15.11.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

    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. optimize table xxx；

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

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

使用语法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_n说ame [, tbl_name] …

注意：

需要有足够的空间才能进行OPTIMIZE TABLE。(剩余空间必须 > 被 OPTIMIZE 的表的大小)

OPTIMIZE 只对独立表空间(innodb_file_per_table=1)才有用，对共享表空间不起作用。

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

对于InnoDB的表，OPTIMIZE TABLE 的工作原理如下

对于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE … FORCE（或者这样翻译：在InnoDB表中等价 ALTER TABLE … FORCE），它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。

+----------+----------+----------+---------------------------------------+ | Table | Op| Msg_type | Msg_text| +----------+----------+----------+---------------------------------------+ | test.foo | optimize | note| Table does not support optimize, doing recreate + analyze instead | | test.foo | optimize | status | OK| +----------+----------+----------+---------------------------------------+ # 但这个提示语可以忽略，从严格的意义讲，说InnoDB不支持optimize table，其实不太准确。因为 MYSQL的文档说明了，当INNODB 的表，MYSQL会以 ALTER TABLE force + analyze 去执行这个命令(相当于做了recreate和analyze)。所以最终还是会看到 OK 的状态。#

对于MYISAM表，OPTIMIZE TABLE 的工作原理：

1. 如果表已删除或分隔行，就修复该表。

2. 如果索引页没有排序，就排序它们。

3. 如果表的统计信息不是最新的(而且修复不能通过对索引进行排序)，就更新它们。

**执行时也可以发现报错：Temporary file write failure. **

建议参考这片文章：

Mysql optimize table 时报错 Temporary file write failure. 的解决方案

optimize 语句的官网介绍

Mysql 5.6 之前 在OPTIMIZE TABLE运行过程中，MySQL会锁定表，5.6之后有了 Online DDL 则大大减少了锁表时间。

alter table、analyze table和optimize table区别

analyze table tb_test ；

optimize table tb_test ；

4. OPTIMIZE TABLE 和ALTER TABLE xxxx ENGINE= INNODB哪个更好

另外：对于MyISAM类型表，使用ALTER TABLE xxxx ENGINE= INNODB 是明显要优于 OPTIMIZE TABLE这种方法的。

2.3 官方建议

MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可(我们现在是每月凌晨4点清理mysql所有实例下的表碎片)