许多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%。