清除碎片

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

SELECT

table_name\G通过脚本清除之后再通过查询语句获取表大小发现表仍然有6000MB的数据剩余。

感觉肯定是有对应的一些索引数据没有被删除掉仍然保存在表中导致表空间仍然很大。

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

InnoDB表碎片

InnoDB表的数据存储在页(page)中每个页可以存放多条记录InnoDB默认使用B树作为索引结构表中的数据和辅助索引都是使用B树结构每个InnoDB表中都有一个称为聚簇索引的特殊索引用于存储行数据。

通常聚簇索引与主键索引同义。

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

除了聚簇索引之外还有一个二级索引我们也叫做辅助索引。

在InnoDB中辅助索引中的每个记录都包含行的主键列以及为二级索引指定的列InnoDB使用此主键值在聚簇索引中搜索行。

如果主键很长则辅助索引将使用更多的空间因此使用较短的主键是比较好的。

对于InnoDB而言随机插入或者删除辅助索引可能会导致索引碎片化碎片化意味着磁盘上索引页的物理顺序与页面上记录的索引顺序不接近或者分配给索引的64页块中有许多未使用的页面。

**碎片的一个症状是表占用的空间比它“应该”占用的空间要多**具体会多多少很难确定。

所有InnoDB数据和索引都存储在B树种它们的填充因子可能从50%到100%不等。

碎片的另一个症状是表扫描花费的时间比它“应该”花费的时间要多。

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

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

对表进行大量的UPDATE操作也可能会导致页分裂频繁地页分裂页会变得稀疏并且被不规则的填充继而产生表碎片

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

行碎片(Row

指数据行被存储在多个地方的片段中即使查询只从索引中访问一行记录行碎片也会导致性能下降

行间碎片(Intra-row

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

剩余空间碎片(Free

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

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

清除表碎片

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

3.1

***************************Name:

user_tabEngine:

***************************Name:

user_tabEngine:

TABLE这种方式会重组表和索引的物理存储减少对存储空间的使用和提升访问表的I/O效率。

OPTIMIZE操作会暂时锁住表数据量越大则耗时越长。

对每个表所做的确切更改取决于该表使用的存储引擎。

对于InnoDB表OPTIMIZE

对刚刚的user_tab采用OPTIMIZE的命令可以看到空间被释放了。

mysql

***************************Table:

duanxi.user_tabOp:

***************************Table:

duanxi.user_tabOp:

***************************Name:

user_tabEngine:

TABLE对常规表和分区表使用在线DDL方式从而减少并发DML操作的停机时间。

由OPTIMIZE

TABLE触发的表重建会在原地完成在操作的准备阶段和提交阶段只短暂地采用排它表锁在准备阶段更新元数据并创建中间表在提交阶段提交表元数据更改。

在线DDL(MySQL

在繁忙的生产环境中提高响应能力和可用性因为让表不可用几分钟或几小时是不切实际的。

对于就地操作可以使用LOCK子句在

DDL

操作期间调整性能和并发之间的平衡。

与表复制方法相比磁盘空间使用量和

I/O

当old_alter_table系统变量启用时当服务器使用—skip-new选项启动时

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

预计会出现一定程度的碎片InnoDB仅填充93%的页面以便留出更新空间而无需拆分页面删除操作可能会留下空隙导致页面填充不足这可能会使优化表变得有价值当有足够的空间时对行的更新通常会重写同一页内的数据具体取决于数据类型和行格式。

高并发工作负载可能会随着时间的推移在索引中留下空白因为InnoDB通过其MVCC机制保留了同一数据的多个版本。

对于MyISAMOPTIMIZE

如果表有删除或拆分行则修复该表。

如果索引页未排序则对其进行排序。

如果表的统计信息不是最新的并且无法通过对索引进行排序来完成修复则更新它们。

TABLE

的方式此方式看起来没有执行什么操作实际上重新整理碎片了当执行这个优化操作时InnoDB会重建整个表并释放聚簇索引中未使用的空间。

因为删除表数据发现表使用空间未被释放继而发现有表碎片问题查找一些资料去了解表碎片的产生以及表碎片的处理最终让自己学习到了关于InnoDB表碎片相关的知识。

表碎片的产生主要是InnoDB删除非物理删除而是标记”删除”且这些被“删除”的空间后续还可复用进而导致磁盘上索引页的物理顺序与页面上记录的索引顺序不接近引发表的碎片化。

同时表的大量更新、表的数据存储页都会产生不同的表碎片。

OPTIMIZE

需要注意的是无论我们采用哪种手段清除表碎片都会有锁表的时间我们需要根据自己服务器要处理的事务的工作负载分析研判这种锁表时间对于业务是否接受如果可以接受则可以对表碎片进行优化如果不能接受则无需进行优化等待后续再进行优化。

(思考再三我选择放弃优化让碎片继续留在表中)

Defragmenting