.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();
​
    //插进十万数据信息
    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   "ms");

十万条检测用时：

​

 上边的事例，我们都是批量导入十万条数据信息，必须联接十万次数据库查询。大家把SQL句子改成1000条拼凑为1条，那样就能降低连接数据库，完成编码改动以下：

//开始时间
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
​
    //插进十万数据信息
    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   "ms");

十万条检测用时：

​

根据提升后，本来必须十万次数据库连接，只需联接100次。从最后运作实际效果看，因为数据库查询是在同一台网络服务器，不涉及到数据传输，特性提高不显著。

2、合拼数据信息插进

在MySQL一样也适用，根据合拼数据信息来完成大批量数据信息导进。完成编码：

//开始时间
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
​
    //插进十万数据信息
    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   "ms");

十万条检测用时：

​

根据这类方法插进实际操作显著可以提升程序流程的插进高效率。尽管第一种方式 根据提升后，一样的能够降低连接数据库频次，但第二种方式 ：合拼后日志量(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));
​
    //转化成十万数据信息
    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);
​
        //五十万条一批号插进
        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   "ms");

十万条检测用时：

​

留意：MySQL数据库查询配备需打开：容许文档导进。配备以下：

secure_file_priv= 

功能测试比照

对于上边三种方式 ，各自检测十万、二十万、一百万、1000万条数据信息纪录，最后特性入以下：

​

最终

根据数据测试看，伴随着信息量的扩大，MySqlBulkLoader的方法主要表现依然优良，别的方法特性降低较为显著。MySqlBulkLoader的方法彻底能够达到大家的要求。

文章内容先发于微信公众号【程序编写快乐】，热烈欢迎大伙儿关心。