MySQL索引维护

Question

我对如何在MySQL中维护索引以防止碎片化以及如何优化某些查询的执行进行了大量的研究。

我熟悉计算表的最大可用空间与数据和索引所使用的空间之间的比率的公式。

然而，我的主要问题仍未得到回答。这可能是因为我熟悉Server中的索引维护，而且我倾向于认为在MySQL中应该有类似之处。

在SQL server中，可以有几个索引，而且每个索引都可以具有不同的碎片级别。然后，您可以在该特定索引中选择一个并执行“重新组织”或“重建”操作，而不会影响其余的操作。

据我所知，没有这样的“表碎片”，Server也没有提供任何工具来修复“表碎片”。它提供了检查索引碎片的工具(理解为索引使用的页数与页面的完整度与相邻度之间的比率)，以及内部和外部的碎片。

所有这些都很容易理解，至少对我来说是这样。

现在，当转向在MySQL中维护索引时，只存在‘表碎片’的概念，正如上面所提到的。

MySQL中的一个表可以有几个索引，但是当我用这个著名的公式检查“碎片比率”时，我看不到每个索引的碎片，而是整个表。

当我想优化MySQL中的索引时，我不选择要操作的特定索引(如Server中的索引)。相反，我在整个表中执行“优化”操作，这可能会影响所有索引。

当在MySQL中对表进行优化时，数据+索引所使用的空间与总体空间之间的比率会降低，这意味着硬盘中的某种物理重组，这意味着物理空间的减少。但是，索引碎片不仅仅是关于物理空间，而且是由于插入和更新而随着时间的推移而改变的树的结构。

最后，我在InnoDB/MySQL中找到了一个表。该表有300万条记录、105列和55个索引。它是1.5GB的不包括索引，这是2.1GB。

为了更新、插入该表，每天都会碰到数千次(我们实际上并不删除记录)。

该表已创建多年，我确信没有人维护任何索引。

我本想在里面找到一个巨大的碎裂，但当我按照规定进行碎裂计算时

free_space / (data_length + index_length)

原来我只有0.2%的碎片。这是非常不现实的。

所以最大的问题是：

1. 如何检查MySQL中特定索引的分段性，而不是整个表
2. 优化表是否确实修复了索引的内部/外部碎片，就像Server中的那样？
3. 当我在MySQL中优化一个表时，它实际上是否重新构建了表上的所有索引？
4. 是否现实地认为减少索引的物理空间(而不重建树本身)实际上可以转化为更好的性能？

Answer 1

指数分割度被高估了很多。别担心。

两个相邻的、有点空的块作为自然处理被InnoDB合并在一起.

BTree上的随机操作会使它自然地吸引到平均69%的满足率。当然，这并不是100%，但是“修复”的开销并不值得。

SHOW TABLE STATUS给出了一些度量标准，但它们存在缺陷-- "Data_free“包括某些”空闲“空间，但不包括其他”免费“空间。

每个块中都有未使用的空间；空闲的16 to块；空闲的“区段”(nMB块)；等待收割的MVCC行；非叶节点有自己的碎片；等等。

Percona和Oracle有不同的方法来观察索引的大小(块数)。由于“自由”的定义有限，我觉得它们都没有用。似乎块(每个块16 MB)以块(几MB)分配，从而导致人们相信存在各种各样的碎片。实际上，它通常只是这些多MB块中的大部分。OPTIMIZE TABLE不一定能收回任何空间。

如果Server使用的是BTrees，那么说“没有碎片”是在撒谎。想想在“分块”上会发生什么。或者想想不断碎片整理的开销。无论哪种方式你都会输。

还请注意，表和索引本质上是相同的结构：

• B+Tree，基于某些索引
• “数据”基于主键；每个辅助索引都是基于其索引的B+Tree。
• "data“的叶节点包含表的所有列。
• 辅助索引的叶节点包含该辅助索引的列以及主键的列。

如果您有innodb_file_per_table = ON，那么通过查看.ibd文件的大小，可以清楚地看到优化表之后的收缩(如果有的话)。对于OFF，信息隐藏在ibdata1中，但是SHOW TABLE STATUS可能相当准确，因为所有“空闲”空间都属于每个表。好吧，除了预先分配的块。

您可能会注意到，一个新优化的文件-每个表都有4米、5米、6米或7米的Data_free。再一次，这是预分配，没有给你详细的细节.

我使用InnoDB已经十多年了；我使用了成千上万个不同的表，无论大小。我说，只有千分之一的桌子真正需要OPTIMIZE TABLE。在其他桌子上使用它是一种浪费。

105栏是很多，但也许不是太多。

一张桌子上有55个索引吗？那太糟了。这是每个INSERT的55次更新。让我们进一步讨论这个问题。请记住，如果您也有INDEX(a)，那么INDEX(a,b)是无用的。而INDEX(flag)是无用的，因为基数低。(但INDEX(flag, foo)可能有用。)

Q1:没有一种很好的方法来检查数据或辅助索引中的所有形式的碎片。

Q2，Q3：OPTIMIZE TABLE通过CREATEing重新构建表--一个新表，并对所有行进行INSERTing，然后是RENAMEing和DROPping。按PK顺序重新插入数据，可以确保数据被很好地分解。指数是另一回事。

Q4:您可以DROP和reCREATE每个索引来清理它。但这是一个极其缓慢的过程。5.6有一些加速，但我不知道它们是否有助于碎片整理。

这也是有可能的ALTER TABLE ... DISABLE KEYS，然后ENABLE他们。这可以一次更有效地重建所有二级索引。

Answer 2

如何检查MySQL中特定索引的分段性，而不是整个表

经过。

优化表是否确实修复了索引的内部/外部碎片，就像Server中的那样？

它完全重新构建了表及其索引。

当我在MySQL中优化一个表时，它实际上是否重新构建了表上的所有索引？

同样的问题，同样的答案。

是否现实地认为减少索引的物理空间(而不重建树本身)实际上可以转化为更好的性能？

认为在不重建树的情况下可以减少空间是不现实的。他们一起去。