碎片是一个常见的问题在某些数据库系统。高度分散的表会影响性能和资源分配。但减少碎片通常包括表完全重建。这篇文章将讨论碎片及其对InnoDB的影响。

碎片是什么?

我们说的东西是分散的,当它是由部分单独或放置在一个不同的顺序比自然。在数据库中,我们可以尝试不同类型的分裂:

• 段破碎:片段支离破碎;他们不遵循的顺序存储数据,或者有空白页数据页之间的差距。
• 表空间碎片:表空间存储在连任的文件系统块。
• 表碎片:数据存储不是主键顺序(堆表)后,或表页面有大量的自由空间(堆表或索引表)。
• 索引碎片:索引通常将数据存储在索引顺序(b - tree)或在一个随机的顺序(散列)。在这两种情况下,分裂意味着自由空间内的索引页。
• 行分裂:一行是跨越多个页面。

并不是所有类型的碎片可以发生在所有的数据库技术,有时候你不能避免一定程度的分化。InnoDB表索引表,这意味着数据组织的主键。

位置的原则

而局部性原理通常是相关的处理器和缓存访问模式,也适用于一般的数据访问。这一原则描述两种数据访问模式:空间和时间位置。

时间局部性意味着检索数据,最近更容易在短时间内再次需要。空间位置告诉我们,某种程度上相关数据(附近)往往是一起访问。如果你的数据是有组织的局部性原理后,数据访问将会更有效率。

碎片如何影响当地的数据?

表和索引碎片往往会导致数据库页面包含重要的自由空间。这减少了频繁访问的数据存储在一起,违背概率时间局部性原理。

表碎片也会影响空间位置相关数据存储在不同的数据库页面。关于表空间和部分碎片,现代存储系统倾向于减少这种类型的分化的影响。

行分裂的情况略有不同。我们通常有三种可能性:行不支持分裂,它支持通过行链接(偏行有一个指针指向另一个页面的行继续),或是只对大数据类型的支持。在最后一种情况下,斑点通常是带外存储在一个单独的区域。这种类型的碎片可以提高性能和效率。

InnoDB的分裂

InnoDB,一切都是一个索引。主键很重要,因为它们定义如何排序表中的数据。这个设计的影响之一是,没有碎片由于无序的数据。但是我们仍然可以有碎片引起的自由空间内表页面。

分手还是不分裂,这是个问题。

InnoDB的行存储在页面。一个新行是放置在一个特定的页面基于主键。但是当一个页面变得满了吗?InnoDB必须分配一个新的页面,将存储的新行。这里InnoDB相当聪明。大部分RDBMS执行页面分裂:创建一个新页面,半整页的内容是最近搬到分配页面,使两个半满的页面。相反是分析什么InnoDB插入模式,如果是连续的,创建一个页面,并将新行。这是非常有效的序贯主键插入。

插入不需要纯粹的顺序;他们需要遵循一个方向:递增或递减。我们不涉及内部;只是告诉你,每个索引离开页面元数据显示最近的方向插入和多少行之后插入它。

随机和顺序插入,对分散的影响

正如我们之前解释的,InnoDB有一个聪明的方法来确定如果一个新行插入一个空的页面或者执行是有意义的。这个方法是非常有效的顺序插入,因为它生成最小数量的额外的页面,传统上,一直被认为是有害的非时序的插入(当主键是随机的或未知)。

我们将审查过程顺序和随机插入了解行插入。但是,首先,让我们看到一个空的内容页面。最初,我们有一些新数据的元数据和自由空间。

一旦我们开始将数据插入到这个页面,它并不重要,如果数据是否顺序;页面将开始灌装。

但是一旦我们走到百亿资金的自由的空间,我们必须分配新的页面。如何分配新页面,取决于插入顺序或随机的。顺序插入,我们有这个模式:

新数据插入新叶子页面。但是对于随机插入,我们有不同的行为:

我们必须储备自由空间在两个页面因为我们不能假设,将插入新行。

插入新行插入,顺序将继续以低分化:

但用随机插入,会发生什么?为简单起见,我们假定主键是均匀分布的。

在这里,我们看到一个有趣的现象,当分裂较低;新插入可能触发页面分裂,增加碎片。但是一旦我们达到一定程度的分化,几乎所有页面将有足够的空闲空间来接受新行不执行分裂。直到达到阈值和新的分裂将再次发生。

这意味着随机插入导致暂时的分裂。

随机插入和删除

前面的情况下覆盖的情况下,我们只插入数据,但是当我们也删除吗?我们经常有表老行定期清除。如果他们是按主键,没有任何问题:空白页将被删除索引表的完全从一开始。

我们在这里看到的是旧行属于同一页面,一旦删除,可以返回页面表空间。后来这个页面将再次分配用于新行。

但当插入和删除是随机的时候会发生什么?假设删除也随机是正确的,因为数据是随机分布的。

我们可以看到,只要删除和插入的行数大致相等,这种模式不会显著增加(或减少)的碎片。

随机插入和删除的理想状态是有足够的空间没有达到分裂点插入新行。

额外的分裂的原因

三个因素定义额外的分裂的原因。之前我们一直在分析第一个:如何插入数据。另外两个数据修改(更新)和数据删除(删除)。

当一个空领域充满了数据或varchar字段长文本内容被替换,这个额外的页面必须腾出空间数据。如果没有足够的自由空间吗?InnoDB将页面分成了两半页。这增加的碎片。为了避免这种情况,InnoDB储备数据修改的每一页。这个预订是不管插入模式。

如果你增加行数的大小,你将产生碎片。

与删除,将会发生什么?删除增加碎片通过减少数量的记录存储在受影响的页面。为了解决这个问题,如果一个页面低于50%的利用率,InnoDB会看着相邻页(指数后顺序),如果其中一个页面是低于50%,它将合并到一个页面。释放空间不会返回到文件系统,但是将由新页面重用。

探测碎片

目前,没有简单的方法来测量页面碎片。有一个表中的列information_schema。表应该包含行平均长度。

https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html

但一些验证后,我们发现这并不是正确的计算。我们开了一个bug MySQL支持和Percona支持这个固定的。雷竞技下载官网

https://bugs.mysql.com/bug.php?id=107739

计算分裂,它是可行的写一个SQL脚本返回表中的数据的总大小从MySQL文档和使用信息的实际数据长度变长列。我们可以通过比较数据和估计平均分裂为数据分配的页数。不幸的是,这不会提供足够的信息来检测碎片热点。

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

测量页面分裂

一个间接的方法来确定分段测量数量的页面发生分裂。不幸的是,它是不可能测量页面分裂期间创建插入或更新的数量在一个特定的表。

全球InnoDB页面统计信息将存储在表innodb_metrics information_schema。

这些统计数据必须启用使用innodb_monitor_enable全局变量。raybet雷竞技竞猜在线官网

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html sysvar_innodb_raybet雷竞技竞猜在线官网monitor_enable

https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-metrics-table.html

InnoDB红宝石

可以分析的结构InnoDB使用外部开源工具结构称为InnoDB Ruby。杰里米·科尔已经开发了这个工具,可以在这里:

https://github.com/jeremycole/innodb_ruby

还有一个wiki页面文件的应用程序使用方法:

https://github.com/jeremycole/innodb_ruby/wiki

概述的分裂为一个特定的表,您可以使用以下命令:

这个命令返回一个表空间使用不同格式的图形表示形式显示在每个页面空间分配。

减少碎片

一旦一个表是支离破碎的,唯一的方法来减少碎片重建表。减少碎片重建表的问题是随机插入片段表很快。这碎片似乎很快,因为新的随机插入的行,和减少碎片导致新行没有自由空间。

重建表可能导致大规模增加碎片重建后不久,页面将会把我们带到一个地方几乎所有的页面都是半满的。

Innodb_fill_factor

理想情况下,如果我们执行随机插入,我们必须分配足够的空间为新插入后全表重建。有一个全局变量,到底这个:innodb_fill_factor。

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html sysvar_innodb_fill_factor

”Innodb_fill_factor定义空间的比例在每个b - tree页面排序索引构建期间,剩下的空间留给未来的指数增长。例如,80年设置innodb_fill_factor储量20%的空间在每个未来的指数增长b - tree页面。实际的比例可能会有所不同。innodb_fill_factor设置被解释为暗示而不是硬限制。”

这意味着如果我们执行随机插入和删除重建表使用填充因数大得足以容纳所有的插入在清洗前,表将维持一个低水平的碎片。

随机插入和删除测试和推荐填充因数

我们执行多个测试用不同的填充因子。执行的测试包括以下:

1. 创建一个表。
2. 插入2000000条记录使用md5作为生成的散列函数。
3. 设置填充因数测试值。
4. 优化表。
5. 重复400次
   1. 10000行插入表中。
   2. 从表中删除10000行。
   3. 测量碎片。

我们测试了这些填补因素:75年,80年,82年,83年、85年和100年。

总空间文件大小

这个图表显示了初始的和最终的空间分配。

正如我们所看到的,使用一个填补83倍为这个测试提供了最好的结果。

页面分裂和合并

我们还分析了一些页面分裂(一行的次数并不适合在相应的页面,页面需要分成两页)和页面的数量合并(的次数,删除操作后,页面会低于50%,InnoDB试图合并相邻页)。

填充因数	页面分裂	合并的尝试	合并成功
75年	63年	4	3
80年	565年	108年	34
82年	1363年	348年	106年
83年	2063年	658年	203年
85年	4350年	1324年	318年
One hundred.	44771年	15527年	2323年

我们可以看到,有许多页面分裂为每个填充因子。100填充因数,我们有一个页面分割处理每89行,而填充因数的83年,我们有一个页面分裂每1930行处理。

分段映射

我们提供分段映射为75、83和100年400次迭代后填充因子。

结论

InnoDB表的碎片通常不是一个问题。InnoDB处理碎片很有效,和表在很少需要重建。

只有一个极端例子当数据插入后随机主键。在这种情况下,结果将取决于表的结构,键的分布和频率数据插入或删除。

在我们的测试中,innodb_fill_factor 83%左右是最佳的值。它允许控制的碎片。较小的填充因子不提供额外的好处。你的情况可能不同。

如果你有一个大表,随机插入,我们建议使用一个工具像innodb_ruby监测碎片和分析如果表需要重建用不同的填充因子。raybet雷竞技竞猜在线官网

雷竞技下载官网Percona MySQL是最完整,分布稳定、可伸缩的、安全的MySQL开源解决方案,提供企业级的数据库环境最关键业务应用程序…和它的免费使用!

raybetapp2下载