mysql - MySQL 中 "almost sequential"主键的性能(InnoDB 索引)

Question

据我所知，当记录是顺序记录时，InnoDB 主键索引的性能要比随机记录高得多。

但是，我很好奇 InnoDB 主索引对于几乎顺序值的性能。假设我没有使用 auto_increment，而是一些存储为 BINARY(16) 的应用程序端顺序 UUID 方案，如 this excellent article 中所述.

如果记录几乎但不完全是顺序的，这对存储和查询性能意味着什么？

Answer 1

“计算磁盘命中数”。

情况 1:所有数据和索引都在 innodb_buffer_pool_size 之内。
行和/或索引的顺序并不重要。 (没有磁盘命中，除非最终将所有内容持久保存到磁盘。)

情况 2:数据和/或索引太大，无法放入 buffer_pool。

情况 2a:数据/索引是顺序的，或接近顺序的，或具有有限数量的“热点”。
将有一个或“有限数量”的 block 获得所有事件。这些(大概)很容易适合 buffer_pool。因此，不存在性能问题。

情况 2b:数据/索引是随机的(例如，UUID 或 MD5)。
现在，由于数据/索引太大而放不下，而且插入内容随机跳动，完成工作需要大量 I/O。 I/O 是性能 killer 。 (由于包含“下一个”UUID 的 block 不太可能缓存在 buffer_pool 中，因此需要读取、修改并最终写回磁盘。)

数据(在 InnoDB 中)在一个 BTree 中按 PRIMARY KEY 排序。每个“二级”索引都在自己的 BTree 中。

现在让我们以不同的方式分解问题...

情况1.你只有一个PK，没有二级 key 。
如果在加载前很容易按PK对数据进行排序，这使得加载运行速度很快。 (填充一个 block ，写入磁盘；重复。不能得到更少的磁盘命中。)

情况 2。您有一个 AUTO_INCREMENT PK，外加一个辅助 key 。
在这种情况下，如果您按辅助键进行预排序并让 AUTO_INCREMENT 执行其操作，它将运行得很快。

情况 3:如果您有两个具有不同特征(例如时间戳和 UUID)的 key (PK 和/或辅助 key )，则
你卡住了。您可以尝试按一个进行预排序以优化它，但是，另一个将是随机的并且速度很慢。 (在一个指标上高效，在另一个指标上低效。)

回到你的问题。 “几乎连续”可能意味着您没有碰到许多不同的 block 。比如你有一年的数据，PK是时间戳，但是每一天的数据都是乱七八糟的，那就是“差不多排序”了。在任何时候，您都只是在一天的时间里跳来跳去，而不是一整年。