歡迎光臨
每天分享高質量文章

如何優化MySQL insert性能

對於一些資料量較大的系統,面臨的問題除了是查詢效率低下,還有一個很重要的問題就是插入時間長。我們就有一個業務系統,每天的資料匯入需要4-5個鐘。這種費時的操作其實是很有風險的,假設程式出了問題,想重跑操作那是一件痛苦的事情。因此,提高大資料量系統的MySQL insert效率是很有必要的。

經過對MySQL的測試,發現一些可以提高insert效率的方法,供大家參考參考。

1. 一條SQL陳述句插入多條資料。

常用的插入陳述句如:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (‘0’, ‘userid_0’, ‘content_0’, 0);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (‘1’, ‘userid_1’, ‘content_1’, 1);

修改成:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (‘0’, ‘userid_0’, ‘content_0’, 0), (‘1’, ‘userid_1’, ‘content_1’, 1);

修改後的插入操作能夠提高程式的插入效率。這裡第二種SQL執行效率高的主要原因有兩個,一是減少SQL陳述句解析的操作, 只需要解析一次就能進行資料的插入操作,二是SQL陳述句較短,可以減少網絡傳輸的IO。

這裡提供一些測試對比資料,分別是進行單條資料的匯入與轉化成一條SQL陳述句進行匯入,分別測試1百、1千、1萬條資料記錄。

2. 在事務中進行插入處理。

把插入修改成:

START TRANSACTION;

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (‘0’, ‘userid_0’, ‘content_0’, 0);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (‘1’, ‘userid_1’, ‘content_1’, 1);

COMMIT;

使用事務可以提高資料的插入效率,這是因為進行一個INSERT操作時,MySQL內部會建立一個事務,在事務內進行真正插入處理。通過使用事務可以減少創建事務的消耗,所有插入都在執行後才進行提交操作。

這裡也提供了測試對比,分別是不使用事務與使用事務在記錄數為1百、1千、1萬的情況。


性能測試:

這裡提供了同時使用上面兩種方法進行INSERT效率優化的測試。即多條資料合併為同一個SQL,並且在事務中進行插入。

從測試結果可以看到,insert的效率大概有50倍的提高,這個一個很客觀的數字。

註意事項:

1. SQL陳述句是有長度限制,在進行資料合併在同一SQL中務必不能超過SQL長度限制,通過max_allowed_packet配置可以修改,預設是1M。

2. 事務需要控制大小,事務太大可能會影響執行的效率。MySQL有innodb_log_buffer_size配置項,超過這個值會日誌會使用磁盤資料,這時,效率會有所下降。所以比較好的做法是,在事務大小達到配置項資料級前進行事務提交。

來源:tigernorth 的博客

鏈接:http://blog.csdn.net/tigernorth/article/details/8094277

赞(0)

分享創造快樂