目录
- 3、mysqlbulkloader插入
最近在工作中，涉及到一个数据迁移功能，从一个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的资料请关注www.887551.com其它相关文章！
