MySQL主键生成机制性能测试实例

linesky

最近有些MySQL管理人员在讨论应该是使用自增序列（即AUTO_INCREMENT类型）还是用UUID函数来生成主键的问题。其中有个人推荐使用UUID函数来生成主键，其中一个理由是使用自增序列来生成主键会导致所有插入都集中在一个页面上的情况，从而可能出现并发上的瓶颈。另外还有人推荐使用多个自增序列生成主键来解决这一问题。但这些技术实际上有没有作用却并没有给出证据来支持，因此我决定来做一些实验。

MySQL主键生成机制性能测试实验环境如下：内存: 4G；IO: RAID 1+0, SCSI Ultra 320，带BBWC；MySQL: 5.0.32；OS: 2.6.16-2-amd64-k8-smp x86_64 GNU/Linux；InnoDB Buffer: 128M。

MySQL主键生成机制性能测试测试程序很简单，开20个线程，每个线程向测试表中插入数据，测试表都包含两个字段，一个是主键，其类型与值的生成方式在不同的测试中会不同，另一个为字符串，所有测试中都插入长度为200，内容全是字符'a'的内容。

参与测试的有三个方案：
方案一：使用一个自增序列
测试表定义:
CREATE TABLE testpk_auto (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(200)) engine = innodb;
插入数据语句:
INSERT INTO testpk_auto (b) VALUES('aaa...');

方案二：使用多个自增序列
测试表定义:
CREATE TABLE testpk_auto (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(200)) engine = innodb;
DELIMITER |
CREATE TRIGGER trans_auto BEFORE INSERT ON testpk_auto
FOR EACH ROW BEGIN
   set new.a = (new.a % 16 << 2 + (new.a / 16);
END;
|
DELIMITER ;

表定义中的trans_auto解发器用于将一个AUTO_INCREMENT序列转化为16个AUTO_INCREMENT序列。从而使数据的插入点从一个页面分散到16个页面，有望减少更新同一个页面导致的竞争。插入数据语句如下:

INSERT INTO testpk_auto (b) VALUES('aaa...');

方案三：使用UUID函数生成主键。
测试表定义:
CREATE TABLE testpk_uuid(a VARCHAR(64) PRIMARY KEY, b VARHCAR(255)) engine = innodb;
插入数据语句:
INSERT INTO testpk_auto (a, b) VALUES(uuid(), 'aaa...');

测试结果如下：

方案 插入速度(记录/秒) 占用空间(MB)
一  3965 245
二 3777 245
二 2580 571

根据MySQL主键生成机制性能测试测试结果，使用自增序列产生主键的性能明显优于使用UUID()函数，占用空间也只是使用UUID()时的一半不到。使用UUID()函数来产生主键导致插入点过于分散，使得插入的速度和空间占用上都不如使用自增序列。使用多个自增序列则与使用一个自增序列性能相差无几，占用空间则几乎完全相同，两者都是比较好的方案。但根据这一结果看，使用一个自增序列时会导致并发上瓶颈这一说法并不成立，一般情况下应该不用担心此问题。

PS: 若使用方案二中的触发器来实现将一个自增序列转化为16个自增序列的方法，需要注意在通过last_insert_id来获得生成的键值时返回的是被触发器修改之前的结果，若要得到真正生成的主键值也要按触发器的定义上那样计算一下。