1、前期准备

1.1、订单测试表

创建一个表，表只有id、trade_no两个字段。

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;

1.2、测试环境

文中测试环境电脑配置如下：

操作系统 ：Window 10 专业版

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

内存： 16G

MySQL版本： 5.7.26

2、实现方法

2.1、单条数据插入方式

这是最普通的方式，通过循环一条一条地插入数据到MySQL，这个方式的缺点很明显：就是每一次都需要连接一次数据库。

2.1.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 + "毫秒");

2.1.2、十万条数据测试性能

编辑

通过测试结果看，这张方式插入性能并不高，插入10条数据就需要花费快5分钟时间。

2.1.3、合并数据库链接优化

上面的例子， 我们批量导入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 + "毫秒");

2.1.4、优化后，十万条数据测试性能

编辑

通过优化后，原本需要10万次连接数据库，现在只需连接100次。 从最终运行效果看，由于数据库与程序是在同一台电脑，不涉及网络传输，所以性能提升不明显。

2.2、合并数据插入方式

下面我们测验下，通过合并数据来实现批量数据导入，我们把1000条数据合并为一条插入。

2.2.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++)
        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 + "毫秒");

2.2.2、十万条数据测试性能

编辑

通过这种方式插入，明显能够提高数据插入的效率。与普通插入方法对比：虽然第一种方法通过优化后，同样的可以减少数据库连接次数，但这种方法，不仅是减少数据库连接，更重要的是： 合并后日志量(MySQL的binlog和innodb的事务让日志)减少了，降低日志刷盘的数据量和频率，从而提高效率。 同时也能减少SQL语句解析的次数，减少网络传输的IO，所以性能有极大的提升。

2.3、MySqlBulkLoader插入方式

下面我们采用MySQLBulkLoader方法测试插入，MySQLBulkLoader也称为LOAD DATA INFILE，它的原理是从文件读取数据。所以我们需要将我们的数据集保存到文件，然后再从文件里面读取导入。

2.3.1、实现代码：

//开始时间
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 + "毫秒");

2.3.2、十万条数据测试性能

通过测试看性能有极大的提升，10万数据基本是秒导入。

编辑

注意： MySQL数据库配置需开启： 允许文件导入， 配置如下：

secure_file_priv=

3、性能测试对比

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

编辑

4、总结

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

php开发实战分析(8)：mysql类库的操作高级使用API开发(字段操作、权限分配、日期格式、跨表查询，数据分表)

数据可视化大屏电商数据展示平台开发实录(Echarts柱图曲线图、mysql筛选统计语句、时间计算、大数据量统计)

漏刻有时API接口实战开发系列(7)：PHP将API获取的json数据自动同步到mysql数据库的解决方案

Echarts实战案例代码(15)：月收入年龄分段等MYSQL分类统计PHP后台数据管理接口API数据的解决方案