.Net Core導入千萬級數據至Mysql數據庫的實現方法
最近在工作中,涉及到一個數據遷移功能,從一個txt文本文件導入到MySQL功能。
數據遷移,在互聯網企業可以說經常碰到,而且涉及到千萬級、億級的數據量是很常見的。大數據量遷移,這裡面就涉及到一個問題:高性能的插入數據。
今天我們就來談談MySQL怎麼高性能插入千萬級的數據。
我們一起對比以下幾種實現方法:
前期準備
訂單測試表
CREATE TABLE `trade` ( `id` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `trade_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', UNIQUE INDEX `id` (`id`), INDEX `trade_no` (`trade_no`) ) COMMENT='訂單' COLLATE='utf8_unicode_ci' ENGINE=InnoDB;
測試環境
操作系統:Window 10 專業版
CPU:Inter(R) Core(TM) i7-8650U CPU @1.90GHZ 2.11 GHZ
內存:16G
MySQL版本:5.7.26
實現方法:
1、單條數據插入
這是最普通的方式,通過循環一條一條的導入數據,這個方式的缺點很明顯就是每一次都需要連接一次數據庫。
實現代碼:
//開始時間 var startTime = DateTime.Now; using (var conn = new MySqlConnection(connsql)) { conn.Open(); //插入10萬數據 for (var i = 0; i < 100000; i++) { //插入 var sql = string.Format("insert into trade(id,trade_no) values('{0}','{1}');", Guid.NewGuid().ToString(), "trade_" + (i + 1) ); var sqlComm = new MySqlCommand(); sqlComm.Connection = conn; sqlComm.CommandText = sql; sqlComm.ExecuteNonQuery(); sqlComm.Dispose(); } conn.Close(); } //完成時間 var endTime = DateTime.Now; //耗時 var spanTime = endTime - startTime; Console.WriteLine("循環插入方式耗時:" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
10萬條測試耗時:
上面的例子,我們是批量導入10萬條數據,需要連接10萬次數據庫。我們把SQL語句改為1000條拼接為1條,這樣就能減少數據庫連接,實現代碼修改如下:
//開始時間 var startTime = DateTime.Now; using (var conn = new MySqlConnection(connsql)) { conn.Open(); //插入10萬數據 var sql = new StringBuilder(); for (var i = 0; i < 100000; i++) { //插入 sql.AppendFormat("insert into trade(id,trade_no) values('{0}','{1}');", Guid.NewGuid().ToString(), "trade_" + (i + 1) ); //合並插入 if (i % 1000 == 999) { var sqlComm = new MySqlCommand(); sqlComm.Connection = conn; sqlComm.CommandText = sql.ToString(); sqlComm.ExecuteNonQuery(); sqlComm.Dispose(); sql.Clear(); } } conn.Close(); } //完成時間 var endTime = DateTime.Now; //耗時 var spanTime = endTime - startTime; Console.WriteLine("循環插入方式耗時:" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
10萬條測試耗時:
通過優化後,原本需要10萬次連接數據庫,隻需連接100次。從最終運行效果看,由於數據庫是在同一臺服務器,不涉及網絡傳輸,性能提升不明顯。
2、合並數據插入
在MySQL同樣也支持,通過合並數據來實現批量數據導入。實現代碼:
//開始時間 var startTime = DateTime.Now; using (var conn = new MySqlConnection(connsql)) { conn.Open(); //插入10萬數據 var sql = new StringBuilder(); for (var i = 0; i < 100000; i++) { if (i % 1000 == 0) { sql.Append("insert into trade(id,trade_no) values"); } //拼接 sql.AppendFormat("('{0}','{1}'),", Guid.NewGuid().ToString(), "trade_" + (i + 1)); //一次性插入1000條 if (i % 1000 == 999) { var sqlComm = new MySqlCommand(); sqlComm.Connection = conn; sqlComm.CommandText = sql.ToString().TrimEnd(','); sqlComm.ExecuteNonQuery(); sqlComm.Dispose(); sql.Clear(); } } conn.Close(); } //完成時間 var endTime = DateTime.Now; //耗時 var spanTime = endTime - startTime; Console.WriteLine("合並數據插入方式耗時:" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
10萬條測試耗時:
通過這種方式插入操作明顯能夠提高程序的插入效率。雖然第一種方法通過優化後,同樣的可以減少數據庫連接次數,但第二種方法:合並後日志量(MySQL的binlog和innodb的事務讓日志)減少瞭,降低日志刷盤的數據量和頻率,從而提高效率。同時也能減少SQL語句解析的次數,減少網絡傳輸的IO。
3、MySqlBulkLoader插入
MySQLBulkLoader也稱為LOAD DATA INFILE,它的原理是從文件讀取數據。所以我們需要將我們的數據集保存到文件,然後再從文件裡面讀取。
實現代碼:
//開始時間 var startTime = DateTime.Now; using (var conn = new MySqlConnection(connsql)) { conn.Open(); var table = new DataTable(); table.Columns.Add("id", typeof(string)); table.Columns.Add("trade_no", typeof(string)); //生成10萬數據 for (var i = 0; i < 100000; i++) { if (i % 500000 == 0) { table.Rows.Clear(); } //記錄 var row = table.NewRow(); row[0] = Guid.NewGuid().ToString(); row[1] = "trade_" + (i + 1); table.Rows.Add(row); //50萬條一批次插入 if (i % 500000 != 499999 && i < (100000 - 1)) { continue; } Console.WriteLine("開始插入:" + i); //數據轉換為csv格式 var tradeCsv = DataTableToCsv(table); var tradeFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "trade.csv"; File.WriteAllText(tradeFilePath, tradeCsv); #region 保存至數據庫 var bulkCopy = new MySqlBulkLoader(conn) { FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = "\r\n", FileName = tradeFilePath, NumberOfLinesToSkip = 0, TableName = "trade" }; bulkCopy.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList()); bulkCopy.Load(); #endregion } conn.Close(); } //完成時間 var endTime = DateTime.Now; //耗時 var spanTime = endTime - startTime; Console.WriteLine("MySqlBulk方式耗時:" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
10萬條測試耗時:
註意:MySQL數據庫配置需開啟:允許文件導入。配置如下:
secure_file_priv=
性能測試對比
針對上面三種方法,分別測試10萬、20萬、100萬、1000萬條數據記錄,最終性能入如下:
最後
通過測試數據看,隨著數據量的增大,MySqlBulkLoader的方式表現依舊良好,其他方式性能下降比較明顯。MySqlBulkLoader的方式完全可以滿足我們的需求。
以上就是.Net Core導入千萬級數據至Mysql數據庫的詳細內容,更多關於.Net Core導入千萬級數據至Mysql的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- 詳解MySqlBulkLoader的使用
- C# GUID ToString的使用總結
- c#如何利用定時器自動備份數據庫詳解
- c# 在Emit代碼中如何await一個異步方法
- Mysql 如何查詢時間段交集