MySQL主鍵生成機(jī)制性能測(cè)試實(shí)例
下文對(duì)MySQL主鍵生成機(jī)制性能進(jìn)行了測(cè)試，如果您對(duì)MySQL主鍵方面感興趣的話，不妨一看，相信對(duì)您學(xué)習(xí)MySQL主鍵會(huì)有所幫助。
最近有些MySQL管理人員在討論應(yīng)該是使用自增序列（即AUTO_INCREMENT類型）還是用UUID函數(shù)來(lái)生成主鍵的問(wèn)題。其中有個(gè)人推薦使用UUID函數(shù)來(lái)生成主鍵，其中一個(gè)理由是使用自增序列來(lái)生成主鍵會(huì)導(dǎo)致所有插入都集中在一個(gè)頁(yè)面上的情況，從而可能出現(xiàn)并發(fā)上的瓶頸。另外還有人推薦使用多個(gè)自增序列生成主鍵來(lái)解決這一問(wèn)題。但這些技術(shù)實(shí)際上有沒(méi)有作用卻并沒(méi)有給出證據(jù)來(lái)支持，因此我決定來(lái)做一些實(shí)驗(yàn)。
MySQL主鍵生成機(jī)制性能測(cè)試實(shí)驗(yàn)環(huán)境如下：內(nèi)存: 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主鍵生成機(jī)制性能測(cè)試測(cè)試程序很簡(jiǎn)單，開(kāi)20個(gè)線程，每個(gè)線程向測(cè)試表中插入數(shù)據(jù)，測(cè)試表都包含兩個(gè)字段，一個(gè)是主鍵，其類型與值的生成方式在不同的測(cè)試中會(huì)不同，另一個(gè)為字符串，所有測(cè)試中都插入長(zhǎng)度為200，內(nèi)容全是字符'a'的內(nèi)容。
參與測(cè)試的有三個(gè)方案：
方案一：使用一個(gè)自增序列
測(cè)試表定義:
CREATE TABLE testpk_auto (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(200)) engine = innodb;
插入數(shù)據(jù)語(yǔ)句:
INSERT INTO testpk_auto (b) VALUES('aaa...');
方案二：使用多個(gè)自增序列
測(cè)試表定義:
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 << 28) + (new.a / 16);
END;
|
DELIMITER ;
表定義中的trans_auto解發(fā)器用于將一個(gè)AUTO_INCREMENT序列轉(zhuǎn)化為16個(gè)AUTO_INCREMENT序列。從而使數(shù)據(jù)的插入點(diǎn)從一個(gè)頁(yè)面分散到16個(gè)頁(yè)面，有望減少更新同一個(gè)頁(yè)面導(dǎo)致的競(jìng)爭(zhēng)。插入數(shù)據(jù)語(yǔ)句如下:
INSERT INTO testpk_auto (b) VALUES('aaa...');
方案三：使用UUID函數(shù)生成主鍵。
測(cè)試表定義:
CREATE TABLE testpk_uuid(a VARCHAR(64) PRIMARY KEY, b VARHCAR(255)) engine = innodb;
插入數(shù)據(jù)語(yǔ)句:
INSERT INTO testpk_auto (a, b) VALUES(uuid(), 'aaa...');
測(cè)試結(jié)果如下：
方案 插入速度(記錄/秒) 占用空間(MB)
一 3965 245
二 3777 245
二 2580 571
根據(jù)MySQL主鍵生成機(jī)制性能測(cè)試測(cè)試結(jié)果，使用自增序列產(chǎn)生主鍵的性能明顯優(yōu)于使用UUID()函數(shù)，占用空間也只是使用UUID()時(shí)的一半不到。使用UUID()函數(shù)來(lái)產(chǎn)生主鍵導(dǎo)致插入點(diǎn)過(guò)于分散，使得插入的速度和空間占用上都不如使用自增序列。使用多個(gè)自增序列則與使用一個(gè)自增序列性能相差無(wú)幾，占用空間則幾乎完全相同，兩者都是比較好的方案。但根據(jù)這一結(jié)果看，使用一個(gè)自增序列時(shí)會(huì)導(dǎo)致并發(fā)上瓶頸這一說(shuō)法并不成立，一般情況下應(yīng)該不用擔(dān)心此問(wèn)題。
PS: 若使用方案二中的觸發(fā)器來(lái)實(shí)現(xiàn)將一個(gè)自增序列轉(zhuǎn)化為16個(gè)自增序列的方法，需要注意在通過(guò)last_insert_id來(lái)獲得生成的鍵值時(shí)返回的是被觸發(fā)器修改之前的結(jié)果，若要得到真正生成的主鍵值也要按觸發(fā)器的定義上那樣計(jì)算一下。
【編輯推薦】
