最近嘗試插入1百萬條數(shù)據(jù)進(jìn)db，以mysql為例。

1. 順序insert

先寫了個(gè)無腦的for循環(huán)作為base-line，插1萬條耗時(shí)1m53s，根本不敢插1百萬。

foreach(var student in students){var sql = string.Format("insert into student ... ");cmd.CommandText = sql;cmd.ExecuteNonQuery(); }

2. batch insert

上面這種方式有2個(gè)問題：

在DB端，每次執(zhí)行都會(huì)以1個(gè)單獨(dú)的事務(wù)執(zhí)行；

在網(wǎng)絡(luò)上，傳輸?shù)拇螖?shù)過多、每次傳輸?shù)男瘦^差。

相應(yīng)的解決方法是：

在執(zhí)行前后套BeginTransaction/Commit，保證所有的insert都是在一個(gè)大事務(wù)里； // 光是這樣，1萬條只要不到2s，1百萬條只要75s

每1萬條數(shù)據(jù)，拼接成1個(gè)大sql，只要不超過 max_allowed_packet=1M 的默認(rèn)限制即可。具體多少行拼成1條，視字段多少而定，拼成的sql如下。 // 這樣的效果也很顯著，1百萬條只要13s

insert into table (fields...) values (1...), (2...), ... , (10000...);

可以修改mysql的默認(rèn)設(shè)置，在my.ini里添加如下配置。但試下來效果并不明顯，改成10M、每次拼接10萬條數(shù)據(jù)，總時(shí)間仍為13s，可以想見這時(shí)瓶頸已經(jīng)不是傳輸時(shí)間了，而是對(duì)表的操作。

[mysqld]max_allowed_packet=10M // 1M default

3. MultiThread insert

試了下在方法2(batch insert)的基礎(chǔ)上，采用4個(gè)線程同時(shí)insert，1百萬條數(shù)據(jù)耗時(shí)16s，反而慢了。估計(jì)時(shí)間都耗在創(chuàng)建connection、單表加鎖上了。在這個(gè)場(chǎng)景下，MultiThread對(duì)解決問題無益。

4. ibdata1無限增長(zhǎng)的問題

每個(gè)Student對(duì)象大約是50字節(jié)，每insert1百萬大約是50M。多insert幾次后執(zhí)行刪除操作，發(fā)現(xiàn)ibdata1文件反而接近翻倍的增長(zhǎng)。解決方法如下：

• 關(guān)閉mysqld服務(wù)
• 刪除ibdata1、ib_logfile0/1、對(duì)應(yīng)的database文件夾
• my.ini的[mysqld]里添加 innodb_file_per_table=1 ，這樣就會(huì)給每個(gè)表創(chuàng)建一個(gè)單獨(dú)的ibd文件

最后是Demo的源碼，如果你有更快的方法，不妨留言~

轉(zhuǎn)載于:https://www.cnblogs.com/AlexanderYao/p/5330634.html

總結(jié)

以上是生活随笔為你收集整理的batch insert 1 million datas into mysql的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。