MySQL 中 insert 与 update 都可能导致页分裂，这样就存在碎片。
对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
delete 语句实际上只是给数据打个标记，并且记录到一个链表中，这样就形成了留白空间。
在InnoDB中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。
当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
总结：
truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;
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删除数据几种情况以及是否释放磁盘空间:
当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；
MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能。
这种碎片不仅额外增加了存储代价，同时也因为数据碎片化降低了表的扫描效率。
碎片若不整理，那么可能会长期占据磁盘空间，导致磁盘使用率越来越高。
2. 如何清理碎片？修复问题的前提是要先找到问题，这样才能对症下药。
2.1. 查看表的碎片情况查看数据库中每个存在碎片的表
mysql>selectconcat('optimizetable',table_schema,'.',table_name,';'),data_free,enginefrominformation_schema.tableswheredata_free>0andengine!='MEMORY';+-----------------------------------------------------------+-----------+--------+|concat('optimizetable',table_schema,'.',table_name,';')|DATA_FREE|ENGINE|+-----------------------------------------------------------+-----------+--------+|optimizetableabc.t_user_answer;|2097152|InnoDB||optimizetablemysql.time_zone_transition;|4194304|InnoDB||optimizetablemysql.time_zone_transition_type;|4194304|InnoDB||optimizetablemysql.user;|4194304|InnoDB|。。。。查看指定表的碎片情况
mysql>showtablestatuslike't_user'\G***************************1.row***************************Name:t_userEngine:InnoDBVersion:10Row_format:DynamicRows:4333Avg_row_length:589Data_length:2555904Max_data_length:0Index_length:2719744Data_free:4194304Auto_increment:NULLCreate_time:2021-11-1910:13:31Update_time:2022-04-2014:28:42Check_time:NULLCollation:utf8mb4_general_ciChecksum:NULLCreate_options:Comment:1rowinset(0.00sec)
Data_free: 4194304 就代表碎片的byte数。如果经常删改数据表，会造成大量的Data_free 频繁 删除记录 或修改有可变长度字段的表。
找到碎片化最严重的表
SELECTtable_schema,TABLE_NAME,concat(data_free/1024/1024,'M')asdata_freeFROM`information_schema`.tablesWHEREdata_free>3*1024*1024ANDENGINE='innodb'ORDERBYdata_freeDESC
通常有这几种做法官方文档参考
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 旧表）
这其实是一个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查询和更新操作，语句为altertabletengine=innodb,ALGORITHM=inplace;之所以支持DML更新操作，是因为数据拷贝期间会将DML更新操作记录到Rowlog中。重建过程中最耗时的就是拷贝数据的过程，这个过程中支持DML查询和更新操作，对于整个DDL来说，锁时间很短，就可以近似认为是OnlineDDL。执行过程：1、获取MDL（MetaDataLock）写锁，innodb内部创建与原表结构相同的临时文件2、拷贝数据之前，MDL写锁退化成MDL读锁，支持DML更新操作3、根据主键递增顺序，将一行一行的数据读出并写入到临时文件，直至全部写入完成。并且，会将拷贝期间的DML更新操作记录到Rowlog中4、上锁，再将Rowlog中的数据应用到临时文件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] …
对于主从架构， LOCAL 参数可以让这个过程不写入 binlog ,这样在主库上执行时就不会同步给从库了
默认情况下，MySQL将OPTIMIZE TABLE语句写入二进制日志，以便它们复制到slave服务器。如果不想写二进制日志，使用命令时加上NO_WRITE_To_BINLOG或LOCAL关键字即可。
使用这个语句需要具有对目标表的SELECT、INSERT权限。
注意：
需要有足够的空间才能进行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），它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。
当您在InnoDB表上运行时，它会显示在OPTIMIZETABLE的输出中，如下所示：mysql>OPTIMIZETABLEfoo;+----------+----------+----------+---------------------------------------+|Table|Op|Msg_type|Msg_text|+----------+----------+----------+---------------------------------------+|test.foo|optimize|note|Tabledoesnotsupportoptimize,doingrecreate+analyzeinstead||test.foo|optimize|status|OK|+----------+----------+----------+---------------------------------------+#但这个提示语可以忽略，从严格的意义讲，说InnoDB不支持optimizetable，其实不太准确。因为MYSQL的文档说明了，当INNODB的表，MYSQL会以ALTERTABLEforce+analyze去执行这个命令(相当于做了recreate和analyze)。所以最终还是会看到OK的状态。#https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me
对于MYISAM表，OPTIMIZE TABLE 的工作原理：
1. 如果表已删除或分隔行，就修复该表。
2. 如果索引页没有排序，就排序它们。
3. 如果表的统计信息不是最新的(而且修复不能通过对索引进行排序)，就更新它们。
**执行时也可以发现报错： Temporary file write failure. **
建议参考这片文章：
Mysql optimize table 时报错 Temporary file write failure. 的解决方案
optimize 语句的官网介绍
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用 OPTIMIZE TABLE。
被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次 即可，只对特定的表运行。
Mysql 5.6 之前 在OPTIMIZE TABLE运行过程中，MySQL会锁定表，5.6之后有了 Online DDL 则大大减少了锁表时间。
alter table tb_test engine = innodb；
（也就是 recreate）MySQL 5.5以前用Offline的方式重建表，5.6以后用Online的方式重建表；
analyze table tb_test ；
重新统计表的索引信息，不会修改数据，不会重建表，整个过程加MDL读
optimize table tb_test ；
是 alter table xxx = innodb； + analyze table xxx; 的过程。
4. OPTIMIZE TABLE 和ALTER TABLE xxxx ENGINE= INNODB哪个更好OPTIMIZE TABLE 还是ALTER TABLE xxxx ENGINE= INNODB 基本上是一样的。但是在有些情况下，ALTER TABLE xxxx ENGINE= INNODB更好。
例如: old_alter_table 系统变量没有启用等等。
另外： 对于MyISAM类型表，使用ALTER TABLE xxxx ENGINE= INNODB 是明显要优于 OPTIMIZE TABLE这种方法的。
2.3 官方建议MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可(我们现在是每月凌晨4点清理mysql所有实例下的表碎片)
上一篇：mysql查询视图命令是哪个
下一篇：mysql5.7.12如何修改用户密码
mysql delete
