您现在的位置是：网站首页> 编程资料编程资料

.Net Core导入千万级数据至Mysql的步骤_Mysql_

2023-05-27 384人已围观

简介 .Net Core导入千万级数据至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().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万条测试耗时：