MySQL:无序插入主键B+树比无序插入二级索引B+树慢吗？

Question

在MySQL中使用自动增量PK的主要原因之一是，它保证了对聚集PK索引的所有插入都是有序的，因此速度很快。我能理解。

但是二级索引呢？假设我的表有一个辅助索引。插入相对于PK聚集索引是按顺序的，但相对于辅助索引B+树是乱序的。

所以，插入不是仍然很慢吗?因为随着插入的不断到来，MySQL需要不断地重新排列辅助索引B+树。

我只是想知道在这里使用自动增量是否真的在插入性能方面给我带来了任何好处。将非常感谢这里的一些澄清。

Answer 1

主键将被聚集，这意味着它直接指向磁盘上的数据。必须重新排列数据意味着必须移动完整的记录。对于二级索引，它实际上只是一堆指向磁盘上的位置的指针。辅助索引与记录的顺序无关，因此必须在辅助索引中移动指针，这就是移动指针。这是一个比必须移动完整记录快得多的操作。

Answer 2

只有当您有一个只写(或者至少是只更新)的表时，您的基本假设才是正确的。如果要删除记录，将不按顺序(以物理方式)插入新记录的PK。

索引插入的效率几乎总是次要的考虑因素，搞乱它是一种不成熟的优化反模式。您是否考虑过基数、关键字段长度、缓存大小等典型的更重要的问题？

首先，使用自动增量代理PK通常是次优的--通常有一个更有用的唯一键和实值，它以更有意义的方式聚集在一起。(并且您只能使用innodb表进行集群-您知道这一点，对吧？)

“集群”意味着索引本质上就是表。因此，它在插入代理键时有一个好处，因为所有内容都会添加到表的末尾，因为下一个索引值总是高于之前的任何索引值(正如您已经知道的那样)。

除非您是在填补已删除记录造成的漏洞。这可能是间接发生的，但可能是一个开销问题，因为必须重新定位整个记录，这显然比仅仅移动索引键值和指针要多得多。

对于单个记录的查询，集群记录并没有提供太多的好处，而是对记录范围(例如，订单、客户、用户的项目)的查询提供了更多的好处。例如，如果您可以为同一用户收集几条(或几百条)记录，那么就值得对其进行集群。为单个用户连续插入记录的可能性要小得多(在大多数情况下)，因此按时间进行聚类没有多大帮助。但您的需求可能会有所不同。

您没有指定innodb，所以我主要回答myisam (默认设置)，只有自动递增或按时间顺序的索引才能模拟集群--没有明确的选项。