MySQL数据库碎片化：隐患与解决策略

为什么我们经常说不建议使用简单的 UUID 做 ID，当唯一索引，其实很大原因就是因为不规则的 UUID 会导致存储碎片，接下来聊一聊 MySQL 为什么会有存储碎片，影响大不大。关于 UUID 做主键还是自增主键，可参考往期文章：

被追着问 UUID 和自增 ID 做主键哪个好，为什么？

MySQL 中的数据库表常会出现物理存储碎片，特别是在频繁执行插入、删除和更新操作的情况下。这些操作会导致数据页中部分空间未被有效利用，或者导致数据在物理存储上排列不连续，进而形成碎片。

碎片的主要来源包括频繁的 DML 操作，如插入（insert）、更新（update）、删除（delete）。此外，使用可变长度字段（如 varchar 或 text）存储数据时，如果更新导致字段长度变化，也可能产生碎片问题。

insert 导致的碎片

我们都了解，InnoDB 使用 B+树索引结构来组织数据，通常按主键顺序存储。然而，当主键不是顺序自增的情况下，比如使用 UUID，新插入的数据行可能会引发页分裂现象。

页分裂会导致数据分散存储在磁盘的不同位置。新创建的页可能与原始页在物理存储上相隔甚远，导致数据在物理层面上不再连续，从而形成碎片。

页分裂通常发生在向 B+树索引插入新数据时，如果目标页已满，数据库系统就需要为新数据腾出空间。

那究竟什么是 InnoDB 的页分裂和页合并呢，mark 一下。下一篇出。

update 导致的碎片

除了插入操作可能导致碎片外，更新操作同样会产生碎片。特别是当更新操作导致数据行大小增加时，如果原始位置周围没有足够的空间容纳更新后的行，数据库可能会将这行数据移动到数据文件的其他位置。这种情况会留下原始位置的空闲空间，导致碎片的产生。

delete 导致的碎片

最容易导致碎片的操作实际上是 delete 操作，尤其在 InnoDB 中更为明显。执行 delete 后，InnoDB 仅仅是对数据行做了标记，而不是立即释放相应的空间。这样就可能导致数据页中存在大量未被使用的空间，增加了数据在物理存储上的分散程度，从而产生了碎片。

碎片的危害

表的碎片增多会导致数据在物理磁盘上存储变得不连续，从而使得数据库在查询数据时需要进行更多的磁盘 I/O 操作，进而降低查询效率。

此外，碎片化会导致数据库实际占用的存储空间比数据实际需要的空间大，造成磁盘空间的浪费，并可能影响缓存效率。

碎片化的数据还会增加备份文件的大小，同时使得备份和恢复的过程变得更为缓慢，因为这些操作也受到物理读写速度的影响。

因此，我们应该尽可能地减少碎片的产生，以提升数据库的性能和效率。

如何避免碎片

1. 使用连续自增的 ID 而不是 UUID，可以使新创建的对象在 B+树的末尾插入，从而减少页分裂的可能性。
2. 对于固定长度的字符串，应该优先选择 char 而不是 varchar，以减少存储碎片的发生。
3. 避免在高度变动的列上创建索引，因为这可能会频繁触发页分裂。
4. 使用 OPTIMIZE TABLE 命令可以重新组织表和索引的物理存储，有效减少碎片并优化表的存储和访问速度。

如有问题，欢迎微信搜索【码上遇见你】。

免费的Chat GPT可微信搜索【AI贝塔】进行体验，无限使用。

好了，本章节到此告一段落。希望对你有所帮助，祝学习顺利。

本内容转载自程序员Hollis的 《Java八股》面试课程