MySQL 溢出页、页分裂、表空间碎片

MySQL中溢出页、页分裂和表空间碎片式几个有相似特征的术语，不细究的话很容易搞混，所以记录一下。

---

页分裂

简介

页分裂主要出现在数据乱序插入时，需要插入到数据页的中间位置，并且数据页已经没有办法存下插入的数据（页的大小是16K，并没有很大），就需要考虑页分裂，拉取一个新的空白页，并将新纪录和原数据页的部分内容迁移。

---

页分裂执行流程

1 插入前的检查

• 当一个插入操作被发起，首先，InnoDB会定位到要插入记录的位置。这通常涉及到B+树索引的搜索。

• 在找到插入点后，InnoDB检查目标页是否有足够空间容纳新记录。这一步是通过比较页内剩余空间和新记录大小来完成的。

2 判断是否需要页分裂

• 如果页内有足够空间，则直接插入记录，无需页分裂。

• 如果没有足够空间，InnoDB决定进行页分裂。

3 执行页分裂

• 创建新页：系统分配一个空白页，这个页的数据结构与被分裂的页相同。

• 记录的迁移和分配：选择一部分记录从原页迁移到新页。InnoDB试图平均分配两个页的空间利用，同时保持B+树的顺序性不变。

• 插入新记录：根据新记录的键值决定它是插入到原页还是新页中。

• 调整B+树索引：页分裂可能导致父节点的键值需要更新，以反映新页的加入。如果父节点没有足够空间，这个过程可能递归到更高层次的节点，甚至到根节点，可能导致树的高度增加。

4 更新系统元数据和日志

• 元数据更新：包括页的元数据，如页链表、空闲列表等。

• 写入重做日志（Redo Log）：每次页分裂操作都会记录在重做日志中，确保在系统故障时可以恢复数据。

案例可以参考：MySQL 页分裂_mysql页面拆分-CSDN博客

---

页分裂的影响

1 页分裂往往出现在随机插入（如主键非自增时），将数据插入到数据页的中间位置，不可避免地要先将部分数据移出去再插入数据，之后再重新维护页之间的链表关系。这显然对数据库的性能是非常不利的。

2 页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

---

溢出页

溢出页的产生原因

页的大小是固定的（16K），如果有一条记录非常大（如text类型），则一个页无法存储这条记录，需要借助溢出页。

---

溢出页执行过程

对于不同的行的类型，处理方法略有不同，

• 对于默认的 Compact 行格式，用一个空白的溢出页来保存溢出的数据，并会在原数据页的记录行拿出20字节来保存溢出页的地址。
• 对于Compressed 和 Dynamic 的行格式，则不再在原记录中存储数据，而是只存储溢出页的地址，所有数据通通移至溢出页。

---

表空间碎片

表空间碎片是指数据页中没有被填满数据，总是留有一些不连续的空隙，且这些空隙往往不能被利用。

---

表空间碎片的产生原因

1 数据删除产生的碎片

在InnoDB中，删除记录只是将这些行标记为“已删除”，不是真的从索引中物理删除了，磁盘的文件大小不会收缩。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。

数据是按页存储的，除非删除整个表或整个数据页不然只删除单条记录的话没有必要移动其他数据来填补这个空缺。

当执行插入操作时，MySQL会尝试使用这些被逻辑删除的空闲空间，如果插入的数据刚好按照索引排序落在这个区间，可能会复用这个位置，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

2 数据插入产生的碎片

前面的页分裂已经提到了

3 数据更新产生的碎片

更新可以理解为删除+插入，一样可能产生碎片。

Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂，频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。如果原始数据长度是varchar(100)，原有数据50，由于长度是变长的，更新后数据为100个字符，可能会产生页分裂。

---

表空间碎片的影响

浪费磁盘空间：由于碎片空间是不连续的，导致这些空间不能充分被利用；

查询性能下降：由于碎片的存在，导致数据库的磁盘 I/O 操作变成离散随机读写，加重了磁盘 I/O 的负担。查询需要扫描的磁盘空间也更大了导致查询速度下降。

---

如何清理表空间碎片

可以参考：MySQL表空间碎片—产生原因、带来的问题以及如何解决_mysql 查看碎片-CSDN博客