MySQL存储碎片

1、为什么会有碎片

MySQL删除或者插入数据时可能会产生碎片，由于删除或者更新操作，产生的空闲空间，这些空闲空间并不能被重新利用，从而降低了数据库的整体效率。
Delete语句实际只是给数据打个标记，并且记录到一个链表中，这样就形成了留白空间。
Delete删除的空白空间执行插入操作时，MySQL会尝试使用空白空间，如果某个空白空间一直没有被合适的数据占用，该空间无法被释放就形成了碎片。

MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。
MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片，定期进行表空间整理，消除碎片可以提高访问表空间的性能。
碎片不仅额外增加了存储代价，同时也因为数据碎片化降低了表的扫描效率，碎片若不整理，那么可能会长期占据磁盘空间，导致磁盘使用率越来越高。

2、删除数据

执行速度：drop > truncate >>delete
delete
    一行一行执行删除，并且记录该行的操作日志redo log和undo log以便进行回滚操作，产生大量日志占用磁盘空间。
    把记录的位置，或者数据页标记为可复用，但磁盘文件的大小是不会变，通过 delete 命令是不能回收表空间的。
    删除全部数据，对于MyISAM会立刻释放磁盘空间，InnoDB 不会释放磁盘空间。
    InnoDB数据是按页存储的，如果删除了数据页上的所有记录，整个数据页就可以被复用。
    记录的复用，只限于符合范围条件的数据，如果插入行在符合的范围直接复用这个空间。
truncate
    属于数据库DDL定义语言，不走事务，需要慎用，因为元数据直接就没了，执行后立即生效，无法恢复。
    删除表会立刻释放磁盘空间，不管是 InnoDB和MyISAM  truncate也会重置auto_increment的值为1，delete后表仍然保持auto_increment。
drop
    属于数据库DDL定义语言，同Truncate，执行后立即生效，无法找回 立刻释放磁盘空间，不管是 InnoDB 和 MyISAM， drop语句将删除表的结构。

3、碎片查询

查看表的碎片情况
select concat(table_schema,'.',table_name),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';
查看指定表的碎片情况
show table status like 'table_name'  
Data_free: 代表碎片的byte数，如果该字段不为0，则说明产生了碎片。
碎片化查询排序
select table_schema, table_name, concat(data_free/1024/1024, 'M') from information_schema.tables where engine = 'innodb' order by data_free desc

4、碎片清理

alter table tb_test engine=innodb; （本质上是 recreate 重新整理表）
alter table tb_test engine=innodb,ALGORITHM=inplace;
alter table tb_test engine=innodb,ALGORITHM=copy;
optimize table tb_test； （本质上是 recreate 重新整理会锁表，很重量级和耗时的过程）
alter table tb_test force  (InnoDB表中等价于 alter table tb_test engine=innodb )
mysqlcheck 批量表空间优化
gh-ost/pt-osc
pt-online-schema-change （本质上也是先备份旧表数据，然后 truncate 旧表）