Visualizing the impact of ordered vs. random index insertion in InnoDB (16 顺序插入和随机插入索引的影响可视化分析)

许多dba都知道，按照“随机”顺序(或者实际上与按键排序有很大区别的任何顺序)构建索引的效率会低得多。然而，通常很难真正理解为什么会这样。通过innodb_ruby中的“- examples”可视化模式，可以很容易地可视化索引的结构。innodb_space的space-lsn-age-illustrate模式允许通过“LSN age”可视化空间文件中的所有页面，根据每个页面最近修改的情况生成类似空间文件的热图。 注意一个小的Ruby脚本generate_data_simple。rb用于生成下面使用的测试表。您还需要一个合理的monospace字体来正确地支持Unicode块字符，为此我衷心推荐Adobe的源代码专业版.

按键顺序插入来建立索引

在索引中按键顺序插入行的效率更高，主要有两个原因:

• 1.页面可以被完全填满，数据库(经过充分的优化)可以检测“批量加载”行为，并通过创建新的空页面来有效地分割页面，而不是将页面分成两半。
• 2.只有索引的“边缘”被写入;一旦页面被填满，它将不会被重新访问。这可以使缓存数据库页面的缓冲池更加有效。

下面是一个只有主键聚集索引的表的例子，通过按键顺序插入行来构建:

$ innodb_space -s ibdata1 -T test/t space-lsn-age-illustrate

在这里插入图片描述

正如您在上面的图像中看到的，索引页的写入顺序几乎是完美的，因为它们是从文件的开头到结尾分配的。 第一个区段(图像的第一行)被分配为片段区段，并包含为不同目的分配的单个页面。您可以清楚地看到表空间的簿记页，FSP_HDR位于第0页，INODE位于第2页，紧跟其后的是第3页的根索引页，这些都是最近修改的。接下来是32个单独分配的“片段”页，它们首先在索引中分配，然后才切换到分配完整的区段。然后，索引页会一直运行到使用空间的末尾。 还请注意LSN年龄直方图(在底部的彩色图例上方以白色打印)显示所有桶都是同等填充的。

按随机顺序插入建立索引

在这里插入图片描述

：因为这些行是按照完全随机的顺序插入的，所以每个页面都有相同的插入机会。这意味着在实践中，每一页都是最近修改的，这是很明显的，整个热图是紫色的。这还意味着整个表必须持续地出现在缓冲池中，如果不合适，性能将受到很大影响。这就是随机顺序插入性能糟糕的主要原因。 此外，你还可以看到InnoDB的一些错误行为:注意从1088、1152和1216页开始的区段。你觉得他们为什么会这样?请在评论中告诉我你的想法。

按照主键顺序构建主索引和次索引

如果有多个索引呢?看一个非常简单和典型的情况，按键顺序插入数据的主键，而二级索引存在:

在这里插入图片描述

注意，这看起来像之前的例子完全交错在一起，因为它确实是这样。由于主键和辅助索引包含完全不同的数据，因此插入是按主键排序的，但按辅助索引的顺序完全错误，从而导致辅助索引的构建效率低下。

向现有表添加辅助索引

对于前一个问题，一个明显的答案是在加载数据之后添加索引，这实际上会产生预期的结果:

在这里插入图片描述

当索引构建在现有表上时(通过ALTER table…添加索引)，它是通过在插入到索引之前扫描和排序数据来构建的，从而产生一个最优(而且非常快)的索引构建。

可视化页面填充率

虽然上面的插图显示了索引构建过程中每个页面最近修改的情况，但是也可以使用空格-区段-举例说明来可视化每个页面有多满。键排序索引是这样的:

$ innodb_space -s ibdata1 -T test/t space-extents-illustrate

在这里插入图片描述

与随机排序索引相比:

$ innodb_space -s ibdata1 -T test/t_shuffle space-extents-illustrate

在这里插入图片描述

随机排序的插入导致页面分割得更频繁，在某些情况下会严重填充不足，导致平均的页面填充率非常低。 那些特别善于观察的人可能已经注意到，上面第一张图片中的索引插图表明，有序插入索引明显小于随机插入索引。您可以在这里看到，随机插入索引在1043个页面上比有序插入索引在737个页面上大41%。另外，还有206个页面没有使用，使得实际磁盘空间使用增加了57%。