在InnoDB中可视化有序与随机索引插入的影响

[This post refers to innodb_ruby version 0.9.11 as of October 2, 2014.]

[此帖子是关于innodb_ruby版本0.9.11，截至2014年10月2日。]

Many DBAs know that building indexes in “random” order (or really any order that greatly differs from ordered by key) can be much less efficient. However, it’s often hard to really understand why that is. With the “-illustrate” visualization modes available in innodb_ruby, it’s possible to quite easily visualize the structure of indexes. The space-lsn-age-illustrate mode to innodb_space allows visualization of all pages in a space file by “LSN age”, generating something like a heatmap of the space file based on how recently each page was modified.

许多数据库管理员知道，按照“随机”顺序（或者实际上与按键顺序大不相同的任何顺序）构建索引可能效率要低得多。然而，很难真正理解其中的原因。使用innodb_ruby中提供的“-illustrate”可视化模式，可以很容易地可视化索引的结构。innodb_space的space-lsn-age-illustrate模式允许根据每个页面的最近修改时间生成类似于热力图的空间文件的可视化。

(Note that a small Ruby script generate_data_simple.rb was used to generate the test tables used below. You’ll also want to have a reasonable monospace font supporting Unicode block characters correctly, for which I’d heartily recommend Adobe’s Source Code Pro.)

（请注意，使用了一个小的Ruby脚本generate_data_simple.rb来生成下面使用的测试表。您还需要一个支持正确显示Unicode块字符的合理等宽字体，我强烈推荐使用Adobe的Source Code Pro。）

Building an index by insertion in key order

按键顺序插入构建索引

Inserting rows into an index in key order is much more efficient primarily for two reasons:

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

Pages can be filled completely, and the database (with sufficient optimizations present) can detect the “bulk loading” behavior and efficiently split pages by creating new, empty pages, rather than splitting pages in half.
页面可以完全填充，数据库（在存在足够的优化的情况下）可以检测到“批量加载”行为，并通过创建新的空页面而不是将页面分成两半来高效地分割页面。
Only the “edge” of the index is being written to; once a page has filled it will not be revisited. This can make the buffer pool, which caches database pages, much more effective.
只写入索引的“边缘”部分；一旦页面填满，就不会再次访问。这可以使缓冲池（缓存数据库页面）更加有效。

Here’s an example of a table with only a PRIMARY KEY clustered index, built by inserting rows in key order:

下面是一个只有主键聚集...