MySQL表碎片是如何产生的

在使用MySQL数据库一段时间后，ibd文件会变的很大，数据会膨胀或者说是表数据中有垃圾数据。一般会定期的进行垃圾数据的清理，这时就需要执行 optimize table table_name;来清理垃圾数据，也就是重建表。

在MySQL中执行delete语句时，该条记录并没有立即从ibd文件中删除，而是给该条记录打上了一个delete_flag删除的标签，如果一张表有5000w条记录时，那么执行其他SQL语句时会导致额外的访问该删除的列，因此导致效率低下。SQL语句：

mysql> create table t11(i int primary key, j varchar(200));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t11 values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t11 values(3,'c'),(4,'d');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from t11;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> delete from t11 where i=3;

mysql> select * from t11;
+---+------+
| i | j    |
+---+------+
| 1 | a    |
| 2 | b    |
| 4 | d    |
+---+------+
3 rows in set (0.01 sec)

一张表中的数据被删除的流程如下：

使用innodb存储引擎时，表数据都是用B+树的结构组织的。

例如，我们要删掉 R4 这条记录，InnoDB 引擎只会把 R4 这条记录标记为删除(delete_flag=1)状态。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，当前磁盘文件的大小并不会缩小，如果再查询时，会导致额外的磁盘I/O。

删除一条记录，仅仅是把这个记录标记为删除(这里跟undo.log/数据恢复有关)。

如果符合条件可以被复用（比如上面的这个例子，R4 这条记录被删除后，如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了），但一般主键是自增id，几乎不会被复用。

InnoDB 的数据是按页存储的，如果删除一个数据页上记录，那么一整个数据页就可以被复用，并且可以被复用到任何位置。
所以如果我们用 delete 命令把整个表的数据删除，仅仅是，把所有的数据页都标记为可复用，磁盘上的文件大小并不会改变。

因此，执行delete语句时，就会产生碎片，导致额外过多的磁盘IO。所以需要定期的进行清理。