mysql - 大索引(MYSQL)的插入性能下降

Question

最近，我发现其中一台服务器的磁盘 I/O 流量很高。一些诊断后在某些表上写入索引导致的高 I/O。我做了几次评估测试，发现mysql在向具有大索引的表中插入记录时需要大量写入。

索引列的数据类型是 varchar(15) 和 varchar(17) ，都是非唯一索引如果我将 20000 条记录加载到具有 10000 条记录的表中，则磁盘上只有 80 次写入，而当表增长到 2000 万时，磁盘上有 1700 次写入(索引列上有大约 100 万个不同的值)甚至插入的记录数也是相同的。

引擎是 MyISAM。

增加索引的大小也会增加每次插入时写入磁盘的数量。

是 BTREE 索引行为吗？我该如何解决这个问题？

Answer 1

使用 InnoDB 而不是 MyISAM。

InnoDB 通过缓冲对二级索引的写入、在可能的情况下合并它们以及延迟昂贵的 I/O 来提供帮助。您可以在 Controlling InnoDB Change Buffering 下的 MySQL 手册中阅读有关此功能的更多信息.

---

回复你的评论:

向 B 树中插入一个新值的代价可能很大。如果叶级没有空间，则插入可能会导致拆分树的非叶节点的级联效应，可能一直到树的顶部。这会导致大量 I/O，因为树的不同节点可能在磁盘上彼此相距很远。

其他缓解策略是通过将较少使用的数据移动到另一个表来缩小表。或者使用 MySQL table partitioning使一个逻辑表由许多单独的物理表组成。每个这样的子表必须具有相同的索引，但是每个单独的索引将更小。

---

这里有一个动画示例: http://www.bluerwhite.org/btree/

查看示例“将键 33 插入 B 树(带拆分)”，其中显示了将值插入到溢出它的 B 树节点中的步骤，以及 B 树如何响应.

现在想象一下示例图只显示了更深的 B 树的底部部分(如果您的索引 B 树有数百万个条目就会出现这种情况)，并且填充父节点本身可以​​是一个溢出，并强制 split 操作继续向上树中的更高级别。如果树顶部的所有祖先节点都已填充，则这可以一直继续到树的最顶部。