MySQL之表碎片化的标题解决

目次

• 1. 媒介
• 2. InnoDB表碎片
• 3. 扫除表碎片
• 3.1 查找碎片化严峻的表
• 3.2 扫除碎片
• 4. 小结
• 5. 参考

1. 媒介

周一在对线上表进行数据扫除时，发现一个标题，我要扫除的单表大概有2500w条数据，扫除数据大概在1300w条左右，扫除之前通过查询语句获取到的表大小约为7000MB。

通过脚本扫除之后，再通过查询语句获取表大小，发现表仍然有6000MB的数据剩余。感觉肯定是有对应的一些索引数据没有被删除掉，仍然生存在表中，导致表空间仍然很大。

背面相识到这个是MySQL的数据碎片，加上利用的是MySQL的InnoDB引擎，导致纵然我们删除数据，表空间也不会缩小，需要通过一些额外的表优化手段来扫除这些数据碎片，因为用的是InnoDB引擎，所以就看了下关于InnoDB引擎表碎片相干的知识。

2. InnoDB表碎片

InnoDB表的数据存储在页(page)中，每个页可以存放多条纪录，InnoDB默认利用B+树作为索引结构，表中的数据和辅助索引都是利用B+树结构，每个InnoDB表中都有一个称为聚簇索引的特别索引，用于存储行数据。通常聚簇索引与主键索引同义。

通过聚簇索引访问行的速度很快，以为索引搜索会直接找到包罗行数据的页面，假如表很大，与利用与索引纪录不同的页面存储行数据的存储构造相比，聚簇所以架构通常可以节省磁盘I/O操作。

除了聚簇索引之外，另有一个二级索引，我们也叫做辅助索引。在InnoDB中，辅助索引中的每个纪录都包罗行的主键列以及为二级索引指定的列，InnoDB利用此主键值在聚簇索引中搜索行。假如主键很长，则辅助索引将利用更多的空间，因此利用较短的主键是比较好的。

对于InnoDB而言，随机插入或者删除辅助索引大概会导致索引碎片化，碎片化意味着磁盘上索引页的物理次序与页面上纪录的索引次序不靠近，或者分配给索引的64页块中有很多未利用的页面。

碎片的一个症状是表占用的空间比它“应该”占用的空间要多，**详细会多多少很难确定。所有InnoDB数据和索引都存储在B树种，它们的添补因子大概从50%到100%不等。碎片的另一个症状是表扫描花费的时间比它“应该”花费的时间要多。

在InnoDB中，删除一些行，InnoDB并不会真正的删除它们，只是会将这些行标志为“已删除”(同时也称为可复用的位置，即后续假如有对应的主键数据插在这段地区，会复用位置)，而不是真的从索引中物理删除，因此存储空间也没有真的被释放。

删除数据会导致页中出现空缺空间，大量随机的DELETE操作会在数据文件中造成不连续的空缺空间，当插入数据的时间，这些可复用的空缺空间会被利用起来，但这会造成数据存储位置的不连续，即物理存储次序与逻辑上的排序次序不同，于是就产生了表数据碎片。

对表进行大量的UPDATE操作也大概会导致页分裂，频繁地页分裂，页会变得稀疏，而且被不规则的添补，继而产生表碎片，

另外，表的数据存储也大概会碎片化，数据存储的碎片化比索引更加复杂，主要有三种类型的数据碎片：

• 行碎片(Row fragmetation)

  指数据行被存储在多个地方的片断中，纵然查询只从索引中访问一行纪录，行碎片也会导致性能降落

• 行间碎片(Intra-row fragmetaion)

  行间碎片是指逻辑上次序的页或者行在磁盘上不是次序存储的，行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作本来能从磁盘次序存储的数据中获益

• 剩余空间碎片(Free space fragmenation)

  指数据页中有大量的空余空间，会导致服务器读取大量不需要的数据，从而造成浪费。

对于MyISAM表，上述三类碎片化都有大概发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行并重写到一个片断中。

3. 扫除表碎片

删除了数据而空间没有得到释放，于是我们需要接纳一些手段来扫除删除的数据留下的表碎片，从而释放存储空间，同时提升查询服从。

3.1 查找碎片化严峻的表

对于表中是否含有碎片，可以通过下面的命令直接查看表信息。

3.2 扫除碎片

清算碎片主要有两种方式：

第一种是优化表，即OPTIMIZE TABLE，这种方式会重组表和索引的物理存储，减少对存储空间的利用和提升访问表的I/O服从。OPTIMIZE操作会暂时锁住表，数据量越大，则耗时越长。对每个表所做的确切更改取决于该表利用的存储引擎。

对于InnoDB表，[code]OPTIMIZE TABLE[/code]会映射到[code]ALTER TABLE … FORCE[/code], 这将重修表以更新索引统计信息并释放聚簇索引中未利用的空间。

对刚刚的user_tab接纳OPTIMIZE的命令，可以看到空间被释放了。

InnoDB的OPTIMIZE TABLE对常规表和分区表利用在线DDL方式，从而减少并发DML操作的停机时间。由OPTIMIZE TABLE触发的表重修会在原地完成，在操作的准备阶段和提交阶段，只短暂地接纳排它表锁，在准备阶段，更新元数据并创建中央表，在提交阶段，提交表元数据更改。

在线DDL(MySQL 8.0+)

在线 DDL 功能支持即时、就地表更改和并发 DML。此功能的长处包罗：

• 在繁忙的生产情况中提高相应本领和可用性，因为让表不可用几分钟或几小时是不切实际的。
• 对于就地操作，可以利用LOCK子句在 DDL 操作期间调整性能和并发之间的均衡。
• 与表复制方法相比，磁盘空间利用量和 I/O 开销更少。

OPTIMIZE TABLE在以下情况下利用表赋值方法重修表

• 当[code]old_alter_table[/code]系统变量启用时
• 当服务器利用[code]—skip-new[/code]选项启动时

InnoDB利用页面分配方法存储数据，不会像传统存储引擎(例如MyISAM)那样收到碎片的影响，在考虑是否运行优化时，请考虑你的服务器预计要处置惩罚的变乱的工作负载。

• 预计会出现一定程度的碎片，InnoDB仅添补93%的页面，以便留出更新空间，而无需拆分页面
• 删除操作大概会留下安定，导致页面添补不敷，这大概会使优化表变得有代价
• 当有富足的空间时，对行的更新通常会重写同一页内的数据，详细取决于数据类型和行格式。
• 高并发工作负载大概会随着时间的推移在索引中留下空缺，因为InnoDB通过其MVCC机制保留了同一数据的多个版本。

对于MyISAM，OPTIMIZE TABLE工作原理如下：

• 假如表有删除或拆分行，则修复该表。
• 假如索引页未排序，则对其进行排序。
• 假如表的统计信息不是最新的（而且无法通过对索引进行排序来完成修复），则更新它们。

第二种操作则是利用[code]ALTER TABLE table_name ENGINE= InnoDB;[/code] 的方式，此方式看起来没有实行什么操作，实际上重新整理碎片了，当实行这个优化操作时，InnoDB会重修整个表并释放聚簇索引中未利用的空间。

4. 小结

因为删除表数据发现表利用空间未被释放，继而发现有表碎片标题，查找一些资料去相识表碎片的产生以及表碎片的处置惩罚，终极让本身学习到了关于InnoDB表碎片相干的知识。

表碎片的产生主要是InnoDB删除非物理删除，而是标志”删除”，且这些被“删除”的空间后续还可复用，进而导致磁盘上索引页的物理次序与页面上纪录的索引次序不靠近，引发表的碎片化。同时表的大量更新、表的数据存储页都会产生不同的表碎片。

表碎片的扫除手段：

• OPTIMIZE TABLE table_name;
• ALTER TABLE table_name ENGINE = InnoDB;

需要注意的是，无论我们接纳哪种手段扫除表碎片，都会有锁表的时间，我们需要根据本身服务器要处置惩罚的变乱的工作负载分析，研判这种锁表时间对于业务是否担当，假如可以担当则可以对表碎片进行优化，假如不能担当，则无需进行优化，等候后续再进行优化。(思考再三，我选择放弃优化，让碎片继续留在表中)

5. 参考

• Defragmenting a Table
• Overview of fragmented MySQL InnoDB tables
• OPTIMIZE TABLE Statement

到此这篇关于MySQL之表碎片化的标题解决的文章就介绍到这了,更多相干MySQL 表碎片化内容请搜索脚本之家以前的文章或继续浏览下面的相干文章盼望各人以后多多支持脚本之家！