十年網(wǎng)站開發(fā)經(jīng)驗 + 多家企業(yè)客戶 + 靠譜的建站團隊
量身定制 + 運營維護+專業(yè)推廣+無憂售后,網(wǎng)站問題一站解決
小編給大家分享一下MySQL批量SQL插入的性能優(yōu)化示例,希望大家閱讀完這篇文章后大所收獲,下面讓我們一起去探討吧!
山亭網(wǎng)站制作公司哪家好,找成都創(chuàng)新互聯(lián)!從網(wǎng)頁設計、網(wǎng)站建設、微信開發(fā)、APP開發(fā)、響應式網(wǎng)站建設等網(wǎng)站項目制作,到程序開發(fā),運營維護。成都創(chuàng)新互聯(lián)從2013年成立到現(xiàn)在10年的時間,我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗,來保證我們的工作的順利進行。專注于網(wǎng)站建設就選成都創(chuàng)新互聯(lián)。
對于一些數(shù)據(jù)量較大的系統(tǒng),數(shù)據(jù)庫面臨的問題除了查詢效率低下,還有就是數(shù)據(jù)入庫時間長。特別像報表系統(tǒng),每天花費在數(shù)據(jù)導入上的時間可能會長達幾個小時或十幾個小時之久。因此,優(yōu)化數(shù)據(jù)庫插入性能是很有意義的。
一條SQL語句插入多條數(shù)據(jù)
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執(zhí)行效率高的主要原因是合并后日志量[mysql的binlog和InnoDB的事務讓日志]減少了,降低日志刷盤的數(shù)據(jù)量和頻率,從而提高效率。
通過合并SQL語句,同時也能減少SQL語句解析的次數(shù),減少網(wǎng)絡傳輸?shù)腎O。
測試對比數(shù)據(jù),分別是單條數(shù)據(jù)的導入與轉換成一條SQL語句進行導入。
在事務中進行插入處理
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;
使用事務可以提高數(shù)據(jù)的插入效率,這是因為進行一個insert操作時,MySQL內部都會建立一個事務,在事務內才進行真正插入處理操作。
通過使用事務減少創(chuàng)建事務的消耗,所有插入都在執(zhí)行后才進行提交操作
測試對比數(shù)據(jù),分筆試不適用事務和使用事務操作
數(shù)據(jù)有序插入
數(shù)據(jù)有序的插入是插入記錄在主鍵上的有序排序
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);INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2);
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 ('2', 'userid_2', 'content_2',2);
由于數(shù)據(jù)庫插入時,需要維護索引數(shù)據(jù),無需的記錄會增大維護索引的成本。
參照InnoDB使用的B+tree索引,如果每次插入記錄都在索引的最后面,索引的定位效率很高,并且對索引調整較少;如果插入的記錄在索引中間,需要B+tree進行分裂合并等處理,會消耗比較多計算資源,并且插入記錄的索引定位效率會下降,數(shù)據(jù)量較大時會有頻繁的磁盤操作。
測試對比數(shù)據(jù),隨機數(shù)據(jù)與順序數(shù)據(jù)的性能對比
先刪除索引,插入完成后重建索引
性能綜合測試
合并數(shù)據(jù)+事務的方法在較少數(shù)據(jù)量時,性能提升很明顯,數(shù)據(jù)量較大時,性能急劇下降,這是由于此時數(shù)據(jù)量超過了innodb_buffer的容量,每次定位索引涉及較多的磁盤讀寫操作,性能下降較快。
合并數(shù)據(jù)+事務+有序的方法在數(shù)據(jù)量達到千萬級以上表現(xiàn)依然良好,在數(shù)據(jù)量較大時,有序數(shù)據(jù)索引定位較為方便,不需要頻繁對磁盤進行讀寫操作,可以維持較高
注意事項
SQL語句是有長度限制,在進行數(shù)據(jù)合并在同一SQL中務必不能超過SQL長度限制,通過max_allowed_packet
配置可以修改,默認1M
,測試時可以修改為8M
。
事務需要控制大小,事物太大可能影響執(zhí)行的效率。MySQL有innodb_log_buffer_size
配置項,超過這個值會把innodb的數(shù)據(jù)刷到磁盤中,這時,效率會有所下降。所以較好的做法是,在數(shù)據(jù)達到這個值前執(zhí)行事務提交。
看完了這篇文章,相信你對MySQL批量SQL插入的性能優(yōu)化示例有了一定的了解,想了解更多相關知識,歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝各位的閱讀!