MySQL主鍵生成機制性能測試實例
下文對MySQL主鍵生成機制性能進行了測試，如果您對MySQL主鍵方面感興趣的話，不妨一看，相信對您學習MySQL主鍵會有所幫助。
最近有些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 << 28) + (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來獲得生成的鍵值時返回的是被觸發器修改之前的結果，若要得到真正生成的主鍵值也要按觸發器的定義上那樣計算一下。
【編輯推薦】
