?最近在工作中，涉及到一個數(shù)據(jù)遷移功能，從一個txt文本文件導(dǎo)入到MySQL功能。

數(shù)據(jù)遷移，在互聯(lián)網(wǎng)企業(yè)可以說經(jīng)常碰到，而且涉及到千萬級、億級的數(shù)據(jù)量是很常見的。大數(shù)據(jù)量遷移，這里面就涉及到一個問題：高性能的插入數(shù)據(jù)。

今天我們就來談?wù)凪ySQL怎么高性能插入千萬級的數(shù)據(jù)。

我們一起對比以下幾種實現(xiàn)方法：

前期準備

訂單測試表

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;

測試環(huán)境

操作系統(tǒng)：Window 10 專業(yè)版

CPU：Inter(R) Core(TM) i7-8650U CPU @1.90GHZ 2.11 GHZ

內(nèi)存：16G

MySQL版本：5.7.26

實現(xiàn)方法：

1、單條數(shù)據(jù)插入

這是最普通的方式，通過循環(huán)一條一條的導(dǎo)入數(shù)據(jù)，這個方式的缺點很明顯就是每一次都需要連接一次數(shù)據(jù)庫。

實現(xiàn)代碼：

//開始時間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
  conn.Open();
?
  //插入10萬數(shù)據(jù)
  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("循環(huán)插入方式耗時：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

10萬條測試耗時：

?

上面的例子，我們是批量導(dǎo)入10萬條數(shù)據(jù)，需要連接10萬次數(shù)據(jù)庫。我們把SQL語句改為1000條拼接為1條，這樣就能減少數(shù)據(jù)庫連接，實現(xiàn)代碼修改如下：

//開始時間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
  conn.Open();
?
  //插入10萬數(shù)據(jù)
  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("循環(huán)插入方式耗時：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

10萬條測試耗時：

?

通過優(yōu)化后，原本需要10萬次連接數(shù)據(jù)庫，只需連接100次。從最終運行效果看，由于數(shù)據(jù)庫是在同一臺服務(wù)器，不涉及網(wǎng)絡(luò)傳輸，性能提升不明顯。

2、合并數(shù)據(jù)插入

在MySQL同樣也支持，通過合并數(shù)據(jù)來實現(xiàn)批量數(shù)據(jù)導(dǎo)入。實現(xiàn)代碼：

//開始時間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
  conn.Open();
?
  //插入10萬數(shù)據(jù)
  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("合并數(shù)據(jù)插入方式耗時：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

10萬條測試耗時：

?

通過這種方式插入操作明顯能夠提高程序的插入效率。雖然第一種方法通過優(yōu)化后，同樣的可以減少數(shù)據(jù)庫連接次數(shù)，但第二種方法：合并后日志量(MySQL的binlog和innodb的事務(wù)讓日志)減少了，降低日志刷盤的數(shù)據(jù)量和頻率，從而提高效率。同時也能減少SQL語句解析的次數(shù)，減少網(wǎng)絡(luò)傳輸?shù)腎O。

3、MySqlBulkLoader插入

MySQLBulkLoader也稱為LOAD DATA INFILE，它的原理是從文件讀取數(shù)據(jù)。所以我們需要將我們的數(shù)據(jù)集保存到文件，然后再從文件里面讀取。

實現(xiàn)代碼：

//開始時間
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萬數(shù)據(jù)
  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);
?
      //數(shù)據(jù)轉(zhuǎn)換為csv格式
      var tradeCsv = DataTableToCsv(table);
      var tradeFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "trade.csv";
      File.WriteAllText(tradeFilePath, tradeCsv);
?
      #region 保存至數(shù)據(jù)庫
      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數(shù)據(jù)庫配置需開啟：允許文件導(dǎo)入。配置如下：

secure_file_priv=

性能測試對比

針對上面三種方法，分別測試10萬、20萬、100萬、1000萬條數(shù)據(jù)記錄，最終性能入如下：

?

最后

通過測試數(shù)據(jù)看，隨著數(shù)據(jù)量的增大，MySqlBulkLoader的方式表現(xiàn)依舊良好，其他方式性能下降比較明顯。MySqlBulkLoader的方式完全可以滿足我們的需求。

以上就是.Net Core導(dǎo)入千萬級數(shù)據(jù)至Mysql數(shù)據(jù)庫的詳細內(nèi)容，更多關(guān)于.Net Core導(dǎo)入千萬級數(shù)據(jù)至Mysql的資料請關(guān)注服務(wù)器之家其它相關(guān)文章！

原文鏈接：https://www.cnblogs.com/chingho/p/14789466.html