欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.Net Core导入千万级数据至Mysql数据库的实现方法

程序员文章站 2022-05-10 10:40:41
​最近在工作中,涉及到一个数据迁移功能,从一个txt文本文件导入到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万条测试耗时:

.Net Core导入千万级数据至Mysql数据库的实现方法

上面的例子,我们是批量导入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万条测试耗时:

.Net Core导入千万级数据至Mysql数据库的实现方法

通过优化后,原本需要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万条测试耗时:

.Net Core导入千万级数据至Mysql数据库的实现方法

通过这种方式插入操作明显能够提高程序的插入效率。虽然第一种方法通过优化后,同样的可以减少数据库连接次数,但第二种方法:合并后日志量(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万条测试耗时:

.Net Core导入千万级数据至Mysql数据库的实现方法

注意:mysql数据库配置需开启:允许文件导入。配置如下:

secure_file_priv=

性能测试对比

针对上面三种方法,分别测试10万、20万、100万、1000万条数据记录,最终性能入如下:

.Net Core导入千万级数据至Mysql数据库的实现方法

最后

通过测试数据看,随着数据量的增大,mysqlbulkloader的方式表现依旧良好,其他方式性能下降比较明显。mysqlbulkloader的方式完全可以满足我们的需求。

以上就是.net core导入千万级数据至mysql数据库的详细内容,更多关于.net core导入千万级数据至mysql的资料请关注其它相关文章!