在PRIMARY KEY上使用AUTO_INCREMENT的MySQL性能

I ran a comparison INSERTing rows into an empty table using MySQL 5.6.

我运行了比较使用MySQL 5.6将行插入空表。

Each table contained a column (ascending) that was incremented serially by AUTO_INCREMENT, and a pair of columns (random_1, random_2) that receive random, unique numbers.

每个表包含一个由AUTO_INCREMENT串行递增的列(升序),以及一对接收随机唯一数字的列(random_1,random_2)。

In the first test, ascending was PRIMARY KEY and (random_1, random_2) were KEY. In the second test, (random_1, random_2) were PRIMARY KEY and ascending was KEY.

在第一个测试中,升序是PRIMARY KEY,(random_1,random_2)是KEY。在第二个测试中,(random_1,random_2)是PRIMARY KEY,而上升是KEY。

CREATE TABLE clh_test_pk_auto_increment (
   ascending_pk       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- PK
   random_ak_1        BIGINT UNSIGNED NOT NULL,                -- AK1
   random_ak_2        BIGINT UNSIGNED,                         -- AK2
   payload            VARCHAR(40),
   PRIMARY KEY        ( ascending_pk ),
   KEY                ( random_ak_1, random_ak_2 )
)  ENGINE=MYISAM 
   AUTO_INCREMENT=1 
   ;

CREATE TABLE clh_test_auto_increment (
   ascending_ak       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- AK
   random_pk_1        BIGINT UNSIGNED NOT NULL,                -- PK1
   random_pk_2        BIGINT UNSIGNED,                         -- PK2
   payload            VARCHAR(40),
   PRIMARY KEY        ( random_pk_1, random_pk_2 ),
   KEY                ( ascending_ak )
)  ENGINE=MYISAM 
   AUTO_INCREMENT=1 
   ;

Consistently, the second test (where the auto-increment column is not the PRIMARY KEY) runs slightly faster -- 5-6%. Can anyone speculate as to why?

一致地,第二个测试(其中自动增量列不是PRIMARY KEY)运行得稍快 - 5-6%。任何人都可以推测为什么?

1 个解决方案

#1

Primary keys are often used as the sequence in which the data is actually stored. If the primary key is incremented, the data is simply appended. If the primary key is random, that would mean that existing data must be moved about to get the new row into the proper sequence. A basic (non-primary-key) index is typically much lighter in content and can be moved around faster with less overhead.

主键通常用作实际存储数据的序列。如果主键递增,则简单地附加数据。如果主键是随机的,那么这意味着必须移动现有数据以使新行进入正确的序列。基本(非主键)索引的内容通常要轻得多,并且可以以更少的开销更快地移动。

I know this to be true for other DBMS's; I would venture to guess that MySQL works similarly in this respect.

我知道其他DBMS也是如此;我冒昧地猜测MySQL在这方面的工作方式类似。

UPDATE

As stated by @BillKarwin in comments below, this theory would not hold true for MyISAM tables. As a followup-theory, I'd refer to @KevinPostlewaite's answer below (which he's since deleted), that the issue is the lack of AUTO_INCREMENT on a PRIMARY KEY - which must be unique. With AUTO_INCREMENT it's easier to determine that the values are unique since they are guaranteed to be incremental. With random values, it may take some time to actually walk the index to make this determination.

正如@BillKarwin在下面的评论中所述,这个理论不适用于MyISAM表。作为一个后续理论,我将在下面提到@KevinPostlewaite的答案(他已删除),问题是PRIMARY KEY上缺少AUTO_INCREMENT - 它必须是唯一的。使用AUTO_INCREMENT,可以更容易地确定值是唯一的,因为它们保证是增量的。对于随机值,实际行走索引可能需要一些时间来进行此确定。