目录
下面我们通过对比3种方法，来谈谈MySQL怎么高性能插入千万级的数据。
文中讲解的方法，都是MySQL本身支持的，只是涉及的代码，采用C#作为例子。
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的方式完全可以满足我们的需求。
