MySQL：无序插入PK B +树比次级索引B +树中的无序插入更慢？ [英] MySQL: is out-of-order inserts into PK B+ Tree slower than out-of-order inserts into Secondary Index B+ Tree?

问题描述

在MySQL中使用自动增量PK的主要原因之一是它保证所有插入到集群PK索引中的顺序将会顺利进行。我明白了。

但是二次索引呢？说我的表有一个辅助索引。关于PK聚集索引的插入将是有序的，但是对于次级索引B + Tree而言是无序的。

所以不要插入仍然很慢，因为MySQL需要不断重新安排辅助索引B + Tree，因为插入进来？

我只是想知道在这里使用自动增量是否真的是在插入性能方面向我买东西。将非常感谢这里的一些澄清。

解决方案

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

One of the main reasons given for using auto-increment PK in MySQL is that it guarantees all inserts into the clustered PK index will be in order and hence fast. I understand that.

But what about secondary indexes? Say my table has a secondary index. Inserts will be in-order with respect to the PK clustered index, but out-of-order with respect to the secondary index B+ Tree.

So wouldn't the inserts still be slow because MySQL needs to be constantly re-arranging the secondary index B+ Tree as inserts are coming in?

I just wondered if using auto-increment here really is buying me anything in terms of insert performance. Would greatly appreciate some clarifications here.

解决方案

The primary key will be clustered, which means that it directly points to the data on disk. Having to rearrange that data means that full records must be moved around. For a secondary index, it is really just a bunch of pointers to locations on disk. The secondary index has nothing to do with the ordering of the records, so having to shift pointers around in a secondary index is just that, moving pointers. This is a much faster operation than having to move full records.

这篇关于MySQL：无序插入PK B +树比次级索引B +树中的无序插入更慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！