mysql - Innodb 的随机主键

Question

文章Slow INSERT into InnoDB table with random PRIMARY KEY column's value描述如果您使用随机 PRIMARY KEY 列，插入会很慢。和 MySQL docs说:

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full.

为什么插入会导致大量页面拆分(1/2 满)和随机 PRIMARY KEY 列？

非常感谢您的帮助。

Answer 1

MySQL 不会在每次插入时“重建”索引。

MySQL 的默认页大小是 16K。它以 1MB 的增量(称为范围)分配这些页面。

首次创建表(重建索引)时，页面会填满 15/16，为一些随机插入留出空间(1k 空间)。如果您的索引条目每个为 500 字节(主键大小 + 聚集索引的行数据)，则在必须拆分页面之前为插入 2 个新行留出空间。

MySQL在页眉中保留了最高记录和最低记录的值，因此一定范围内的记录在同一页上。

当MySQL需要在一个完整的页面上插入一行时，必须对页面进行拆分。 MySQL会添加一个新页面，并将一半的页面数据移动到新页面。

在一个页面中，记录实际上可能不是按物理顺序排列的。它们将按照插入的顺序排列。它们通过链表的形式按顺序链接。因此，即使是不需要拆分页面的随机插入，也不会导致数据物理移动。

多次随机插入后，您的页面将从 1/2 满到满。包含许多半页的索引会对读取性能产生负面影响(您必须读取两个半页才能读取与 15/16 整页相同数量的记录)。

现在，如果您按索引顺序插入行，则 MySQL 会简单地继续添加到页面的末尾，将它们填满 15/16，并在页面的时间添加一个范围。由于没有页面拆分，因此性能损失要小得多，因此不涉及数据移动，更不用说几乎完整页面的读取性能优势了。

随机插入还会增加页面碎片，如果您经常读取大量顺序记录(很少见)，这可能会影响读取性能。

此外，change buffering可能会影响你。