在主键上使用AUTO_INCREMENT的MySQL性能 [英] MySQL performance using AUTO_INCREMENT on a PRIMARY KEY

问题描述

我进行了一个比较，使用MySQL 5.6将行插入到空表中.

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

每个表均包含一列(升序)，该列按AUTO_INCREMENT顺序递增；还有一对列( random_1 ， random_2 )会收到随机的唯一数字.

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.

在第一次测试中，升序是主键，( random_1 ， random_2 )是KEY.在第二个测试中，( random_1 ， random_2 )是PRIMARY KEY，而 ascending 是KEY.

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.

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 
   ;

始终如一，第二个测试(自动递增列为 not PRIMARY KEY)的运行速度稍快-5-6％.任何人都可以推测为什么吗?

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 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.

我知道这对于其他DBMS来说是正确的；我冒昧地猜测MySQL在这方面的工作方式与此类似.

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

更新

如@BillKarwin在下面的评论中所述，该理论不适用于MyISAM表.作为后续理论，我将在下面参考@KevinPostlewaite的答案(此后他已删除)，问题是主键上缺少AUTO_INCREMENT-该键必须是唯一的.使用AUTO_INCREMENT可以更轻松地确定值的唯一性，因为可以保证它们是递增的.对于随机值，可能需要一些时间才能实际遍历索引来进行此确定.

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.

这篇关于在主键上使用AUTO_INCREMENT的MySQL性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！