MySQL和Oracle怎么导入千万级大量数据

zhhhhh
835 阅读4分钟

一次性导入千万级数据到Mysql（附源码）

一，前期准备

订单测试表 创建一个表，表只有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.单条数据插入方式
//开始时间
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 + "毫秒");

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

十万条数据测试性能

image.png

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

2.合并数据库链接优化

上面的例子，我们批量导入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 + "毫秒");
优化后，十万条数据测试性能

image.png

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

3.合并数据插入方式

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

//开始时间
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 + "毫秒");
十万条数据测试性能

image.png

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

普通插入和合并插入的不同如图：

image.png

4.MySqlBulkLoader插入方式

下面我们采用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 + "毫秒");
十万条数据测试性能

image.png

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

secure_file_priv=
5.性能测试对比

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

image.png

二，可以考虑使用图形化界面的导入导出功能，这里用sqlyog举例。

导出

1.右键需要导出的表->选择备份/导出->再选择导出表数据为需要的格式。

image.png 2.如图可以进行我们的导出设置。

image.png 3.sql导出：将数据以sql的语句形式导出，需要导入数据的时候再执行sql语句即可达到导入的目的，但是这种方法比较慢，效率不高。导出文件如图：

image.png

4.csv导出：将数据以csv文件的形式导出，类似于将数据存储到文档上。我们还需要进行几个配置，勾选前端添加列名称，在导出的时候第一列会是字段名，数据更清晰明了，另外再导入的时候还需要把第一列忽略掉。

image.png

5.其次点击更改，字段被终止填写为\t,,再将取带空值去掉，我这里是去掉的，可以根据自己需要设置。然后点击确认即可。再点击导出即可将这张表的数据导出为我们的csv文件。

image.png

6.导出csv文件如图：

image.png

导入

1.先将表数据清空

image.png

2.右键->导入->导入使用本地加载的csv数据->选中刚刚导出的csv文件即可导入成功。

image.png

3.可以看到导入数据成功。值得注意的是，导出的数据中会有第一行的字段名，自己手动删除

image.png

三，使用命令进行导入操作，导入文件就用sqlyog导出的csv文件进行导入操作。

导入

1.查看导入文件位置，我们执行导入的时候需要将文件放到设置的位置才能被mysql读到并且导入数据。可以通过命令

SHOW VARIABLES LIKE '%secure%';

2.如图：一般是会有一个默认位置的，但是我这里没有，我们需要设置secure_file_priv值。

image.png

3.开始尝试使用如下命令进行设置。

SET GLOBAL secure_file_priv='F:\mysql\daoru'

但是： 这个字段只读，那就只有去配置文件里面设置了。

image.png

4.找到安装mysql目录下的my.ini文件。在配置文件中添加：

secure_file_priv="F:\mysql\daoru"

注意：这个路径必须是mysql有权访问的路径，换句话来说，要放到mysql的安装目录下。

注意：这个路径不能使用你直接复制的地址栏里面的地址，F:\mysql\daoru是不可以的，会被识别为F:mysqldaoru，而F:\mysql\daoru是可以的。

5.重启mysql，再次使用命令

SHOW VARIABLES LIKE '%secure%';

查看是否设置成功，可以看到已经设置成功。

image.png

6.查看本地文件导入开关是否打开。

SHOW VARIABLES LIKE 'local_infile';

没有打开的话使用命令进行打开。

SET GLOBAL local_infile=ON;

如果说设置了之后查看参数local_infile还是OFF的话，关闭sqlyog，再次打开再查询就可以了。

7.使用语句进行导入操作。

LOAD DATA LOCAL INFILE "F:/mysql/daoru/mysql.csv"  #导入本地文件
INTO TABLE bysj.user_follow                        #到数据库bysj的user_follow表
FIELDS TERMINATED BY','                            #字段以,结束
LINES TERMINATED BY '\n'                           #每行以\n结束
IGNORE 1 LINES (id,user_id,spu_id);                #忽略第一行，因为第一行是字段名

8.可以看到数据已经导入成功。

image.png

9.如果说导入数据千万级别的话，其实早就应该要分库分表了。倒是如果真的一张表里面有好几千万的数据的话我们可以将csv文件再进行一个拆分，然后进行导入。导入的话可以通过代码多线程进行插入，或者合成为一句sql进行插入，但是速度最快的无疑是直接操作数据库导入。

亲，有收获的话，赏个赞吧！

avatar
文章
阅读
粉丝