MySQL效能測試分析

測試環境

作業系統：Linux AS release 4，核心2.6.9-42.ELsmp #1 SMP

CPU：Intel(R) Xeon(TM) CPU 3.00GHz，4核

記憶體：1G，主要運行MySQL服務

硬碟：cciss 37G

檔案系統：ext3，支援大檔案

MySQL：MySQL-5.0.51a

網卡：100M

網路環境：100M交換區域網路

測試方法

資料規模分為1萬條、10萬條、100萬條和1000萬條記錄層級。

在每種資料規模下分別執行2000次隨機查詢，這樣可以防止資料庫直接從緩衝中返回查詢結果，更加接近真實的查詢情況。以系統時間為隨機種子，在現有的記錄範圍內隨機執行查詢操作。

查詢類型分為：單表select、雙表select、insert、單表delete和雙表delete。

因為MyISAM和InnoDB是MySQL使用最多的兩種引擎，也是我們公司目前使用最頻繁的引擎，因此分別做對比測試有一定的參考價值。

每個執行條目都為單次查詢的平均執行時間，單位為微妙。如果要看整體執行時間，則乘以2000即可。

由於指令碼插入資料很慢，採用mysql用戶端工具測試效率很低且不能保證足夠的隨機性，所以插入資料和測試效能都是自己編寫相應的程式來完成。

開啟二進位更新日誌。

測試記錄
測試資料

MyISAM引擎跨主機查詢操作平均執行時間（單位：微秒）

項目記錄數	單表select	雙表select	insert	update	單表delete	雙表delete
1萬條	358	484	1188	269	298	464
10萬條	375	505	1227	283	322	521
100萬條	3174	5293	1242	4142	7981	17212
1000萬條	12302	19920	1261	12355	20449	39890

MyISAM引擎本地查詢操作平均執行時間（單位：微秒）

項目記錄數	單表select	雙表select	insert	update	單表delete	雙表delete
1萬條	178	247	694	141	170	345
10萬條	195	271	705	157	186	585
100萬條	3055	5164	746	4308	12373	22533
1000萬條	8665	15259	803	11033	17224	35485

發現對innodb的配置參數進行最佳化後，更新和插入速度可以提高10倍多。

InnoDB引擎跨主機單次查詢操作平均執行時間（單位：微秒）

項目記錄數	單表select	雙表select	insert	update	單表delete	雙表delete
1萬條	387	521	1215	327	293	519
10萬條	399	567	1231	338	322	663
100萬條	2255	4950	1320	1988	3832	9550
1000萬條	10556	18702	1362	10823	19078	36666

InnoDB引擎本地單次查詢操作平均執行時間（單位：微秒）

項目記錄數	單表select	雙表select	insert	update	單表delete	雙表delete
1萬條	180	264	709	165	156	312
10萬條	226	340	754	204	205	590
100萬條	1853	4530	746	1782	2771	9816
1000萬條	9275	17609	889	6941	14727	36282

效能比較

單表select

雙表select

Insert操作

update操作

單表delete

雙表delete

效能分析

1、可以看出在MySQL 5.0裡面，MyISAM和InnoDB儲存引擎效能差別並不是很大，與官方的說法基本接近。

2、1萬和10萬的select、delete或update操作都很快，1毫秒以下。

3、Insert操作效能降低較慢，受資料規模影響相對較小。

4、效能從10萬條規模升到100萬條時降低非常明顯，從100萬到1000萬效能降低更明顯。

5、InnoDB引擎在預設參數配置下效能較差。

6、推薦InnoDB的配置（1G記憶體情況，主要運行mysql伺服器）：

innodb_buffer_pool_size = 600M
innodb_additional_mem_pool_size = 64M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50
innodb_file_per_table

其中innodb_flush_log_at_trx_commit和innodb_file_per_table對I/O效能影響最大。

7、my-large.cnf等檔案只是最佳化myisam引擎的參數，這一點在最佳化配置時特別要注意。

8、在資料規模較大（100萬條以上），InnoDB引擎效能有相對優勢。

9、在資料規模較小（10萬條以下）,MyISAM引擎效能有相對優勢。