代码怎么实现类似mysql主键auto increment功能,在主键上使用AUTO_INCREMENT的MySQL性能...

I ran a comparison INSERTing rows into an empty table using 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.

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

;

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.

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

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.