文档章节

如何快速安全的插入千万条数据

ksfzhaohui
 ksfzhaohui
发布于 10/15 20:01
字数 1667
阅读 5452
收藏 109

前言

最近有个需求解析一个订单文件,并且说明文件可达到千万条数据,每条数据大概在20个字段左右,每个字段使用逗号分隔,需要尽量在半小时内入库。

思路

1.估算文件大小

因为告诉文件有千万条,同时每条记录大概在20个字段左右,所以可以大致估算一下整个订单文件的大小,方法也很简单使用FileWriter往文件中插入一千万条数据,查看文件大小,经测试大概在1.5G左右;

2.如何批量插入

由上可知文件比较大,一次性读取内存肯定不行,方法是每次从当前订单文件中截取一部分数据,然后进行批量插入,如何批次插入可以使用insert(...)values(...),(...)的方式,经测试这种方式效率还是挺高的;

3.数据的完整性

截取数据的时候需要注意,需要保证数据的完整性,每条记录最后都是一个换行符,需要根据这个标识保证每次截取都是整条数,不要出现半条数据这种情况;

4.数据库是否支持批次数据

因为需要进行批次数据的插入,数据库是否支持大量数据写入,比如这边使用的mysql,可以通过设置max_allowed_packet来保证批次提交的数据量;

5.中途出错的情况

因为是大文件解析,如果中途出现错误,比如数据刚好插入到900w的时候,数据库连接失败,这种情况不可能重新来插一遍,所有需要记录每次插入数据的位置,并且需要保证和批次插入的数据在同一个事务中,这样恢复之后可以从记录的位置开始继续插入。

实现

1.准备数据表

这里需要准备两张表分别是:订单状态位置信息表,订单表;

CREATE TABLE `file_analysis` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `file_type` varchar(255) NOT NULL COMMENT '文件类型 01:类型1,02:类型2',
  `file_name` varchar(255) NOT NULL COMMENT '文件名称',
  `file_path` varchar(255) NOT NULL COMMENT '文件路径',
  `status` varchar(255) NOT NULL COMMENT '文件状态  0初始化;1成功;2失败:3处理中',
  `position` bigint(20) NOT NULL COMMENT '上一次处理完成的位置',
  `crt_time` datetime NOT NULL COMMENT '创建时间',
  `upd_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `file_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `file_id` bigint(20) DEFAULT NULL,
  `field1` varchar(255) DEFAULT NULL,
  `field2` varchar(255) DEFAULT NULL,
  `field3` varchar(255) DEFAULT NULL,
  `field4` varchar(255) DEFAULT NULL,
  `field5` varchar(255) DEFAULT NULL,
  `field6` varchar(255) DEFAULT NULL,
  `field7` varchar(255) DEFAULT NULL,
  `field8` varchar(255) DEFAULT NULL,
  `field9` varchar(255) DEFAULT NULL,
  `field10` varchar(255) DEFAULT NULL,
  `field11` varchar(255) DEFAULT NULL,
  `field12` varchar(255) DEFAULT NULL,
  `field13` varchar(255) DEFAULT NULL,
  `field14` varchar(255) DEFAULT NULL,
  `field15` varchar(255) DEFAULT NULL,
  `field16` varchar(255) DEFAULT NULL,
  `field17` varchar(255) DEFAULT NULL,
  `field18` varchar(255) DEFAULT NULL,
  `crt_time` datetime NOT NULL COMMENT '创建时间',
  `upd_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000024 DEFAULT CHARSET=utf8

2.配置数据库包大小

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set

mysql> set global max_allowed_packet = 1024*1024*10;
Query OK, 0 rows affected

通过设置max_allowed_packet,保证数据库能够接收批次插入的数据包大小;不然会出现如下错误:

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4980577 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3915)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2598)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)

3.准备测试数据

    public static void main(String[] args) throws IOException {
        FileWriter out = new FileWriter(new File("D://xxxxxxx//orders.txt"));
        for (int i = 0; i < 10000000; i++) {
            out.write(
                    "vaule1,vaule2,vaule3,vaule4,vaule5,vaule6,vaule7,vaule8,vaule9,vaule10,vaule11,vaule12,vaule13,vaule14,vaule15,vaule16,vaule17,vaule18");
            out.write(System.getProperty("line.separator"));
        }
        out.close();
    }

使用FileWriter遍历往一个文件里插入1000w条数据即可,这个速度还是很快的,不要忘了在每条数据的后面添加换行符(\n\r)

4.截取数据的完整性

除了需要设置每次读取文件的大小,同时还需要设置一个参数,用来每次获取一小部分数据,从这小部分数据中获取换行符(\n\r),如果获取不到一直累加直接获取为止,这个值设置大小大致同每条数据的大小差不多合适,部分实现如下:

ByteBuffer byteBuffer = ByteBuffer.allocate(buffSize); // 申请一个缓存区
long endPosition = batchFileSize + startPosition - buffSize;// 子文件结束位置

long startTime, endTime;
for (int i = 0; i < count; i++) {
    startTime = System.currentTimeMillis();
    if (i + 1 != count) {
        int read = inputChannel.read(byteBuffer, endPosition);// 读取数据
        readW: while (read != -1) {
            byteBuffer.flip();// 切换读模式
            byte[] array = byteBuffer.array();
            for (int j = 0; j < array.length; j++) {
                byte b = array[j];
                if (b == 10 || b == 13) { // 判断\n\r
                    endPosition += j;
                    break readW;
                }
            }
            endPosition += buffSize;
            byteBuffer.clear(); // 重置缓存块指针
            read = inputChannel.read(byteBuffer, endPosition);
        }
    } else {
        endPosition = fileSize; // 最后一个文件直接指向文件末尾
    }
    ...省略,更多可以查看Github完整代码...
}

如上代码所示开辟了一个缓冲区,根据每行数据大小来定大概在200字节左右,然后通过遍历查找换行符(\n\r),找到以后将当前的位置加到之前的结束位置上,保证了数据的完整性;

5.批次插入数据

通过insert(...)values(...),(...)的方式批次插入数据,部分代码如下:

// 保存订单和解析位置保证在一个事务中
        SqlSession session = sqlSessionFactory.openSession();
        try {
            long startTime = System.currentTimeMillis();
            FielAnalysisMapper fielAnalysisMapper = session.getMapper(FielAnalysisMapper.class);
            FileOrderMapper fileOrderMapper = session.getMapper(FileOrderMapper.class);
            fileOrderMapper.batchInsert(orderList);

            // 更新上次解析到的位置,同时指定更新时间
            fileAnalysis.setPosition(endPosition + 1);
            fileAnalysis.setStatus("3");
            fileAnalysis.setUpdTime(new Date());
            fielAnalysisMapper.updateFileAnalysis(fileAnalysis);
            session.commit();
            long endTime = System.currentTimeMillis();
            System.out.println("===插入数据花费:" + (endTime - startTime) + "ms===");
        } catch (Exception e) {
            session.rollback();
        } finally {
            session.close();
        }
        ...省略,更多可以查看Github完整代码...

如上代码在一个事务中同时保存批次订单数据和文件解析位置信息,batchInsert通过使用mybatis的<foreach>标签来遍历订单列表,生成values数据;

总结

以上展示了部分代码,完整的代码可以查看Github地址中的batchInsert模块,本地设置每次截取的文件大小为2M,经测试1000w条数据(大小1.5G左右)插入mysql数据库中,大概花费时间在20分钟左右,当然可以通过设置截取的文件大小,花费的时间也会相应的改变。

完整代码

Github

© 著作权归作者所有

ksfzhaohui

ksfzhaohui

粉丝 400
博文 156
码字总数 230285
作品 3
南京
高级程序员
私信 提问
加载中

评论(9)

青木_
青木_
生成一个sql文本不 然后import 不更快么
whatwhowhy
whatwhowhy
load data in file可行不
ksfzhaohui
ksfzhaohui 博主
大文件如果中途插入失败了,不太好处理
魂祭心
魂祭心
bulk不能用
魔力猫
魔力猫
编写成CSV,用外部表的方式在服务器本地导入是否更快呢?
ksfzhaohui
ksfzhaohui 博主
因为我们这个是一个对外提供的接口,外部商户通过在指定ftp下每天上传文件
kakai
kakai
用insert(...)values(...),(...)做批量插入一定要注意插入的量,从而评估数据量大小,数据库一次性传输的数据是有大小限制的。
ksfzhaohui
ksfzhaohui 博主
是的,mysql可以通过设置max_allowed_packet
Joyzhou
Joyzhou
能设还写什么代码
Oracle千万级记录操作总结

Oracle千万级记录进行处理并不简单,下面就为您总结了Oracle千万级记录插入和查询的技巧,希望对您能够有所启迪。 最近做了个项目,实现对存在Oracle千万级记录的库表执行插入、查询操作。原...

程序员YB
2011/12/21
422
2
MongoDB 1000W级数据 Insert和Query和Delete性能测试(分别测试 不加索引 和 加索引)

先看下测试机性能(64bit): 测试程序: 不加索引测试: ......................# MongoDB 不加索引 插入1000W条测试 #................... ......................# MongoDB 不加索引 1000...

晨曦之光
2012/04/12
3K
2
1000W 数据插入到数据库要多久

看了 社区的大牛们都在说 千万级别的数据 在各个数据库中的 速度和性能 心里痒痒的 自己没有做过你那么大的项目 最多的纪录才 300万 实在是说不上话 想想何不自己试一试 插入 1千万的数据 具...

李永波
2012/07/29
9.9K
29
mysql数据库千万级别数据的查询优化和分页测试

我原来的公司是一家网络游戏公司,其中网站交易与游戏数据库结合通过ws实现的,但是交易记录存放在网站上,级别是千万级别的数据库是mysql数据库. 可能有人会问mysql是否支持千万级数据库,还有既...

idea_biu
2012/07/02
2.3K
0
请教各位,关于企业应用中大数据量事务性业务的技术方案

各位好, 遇到一个技术性问题,希望大家支支招,给些建议。 我把业务需求简化一下:A表(主表),A1表(A表的子表),B表。 数据量:A 千万级 A1 (子表A1中1万~10万条记录对应一条A记录) B表...

王树兵
2012/02/19
1K
21

没有更多内容

加载失败,请刷新页面

加载更多

学习笔记-上传文件

<?php $upload=new Upload(); echo $upload->uploadFile('fileupload'); echo $upload->errorNumber; class Upload{ protected $path='./upload/';//路径 protected $allowSuffix=['png','j......

葬-花
17分钟前
5
0
踏破铁鞋无觅处

以前总是不知道mysql如int(9),int(11),tinyint(4)的取值范围,还以为是2^n呢,结果代表的是宽度,诶呀,原来这样啊,以前百度经常找不到,现在重新百度都有类似的博客了,看来19年博客多出...

木九天
17分钟前
3
0
spring boot 单元测试类相关注解说明

// 获取启动类,加载配置,确定装载 Spring 程序的装载方法,它会去寻找 主配置启动类(被 @SpringBootApplication 注解的)@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironm...

qimh
21分钟前
4
0
搭建企业BI,就真能逃出管理决策困境了吗?

当代企业面临怎样的管理决策困境?逃出管理决策困境真的只能依靠企业BI了吗?逃出管理决策困境的前提是及早发现问题,了解问题的前因后果和影响程度,这样才能在不影响其他正常工作的前提下快...

小奥奥
23分钟前
3
0
Xamarin图表开发基础教程(11)OxyPlot框架支持的图表类型

Xamarin图表开发基础教程(11)OxyPlot框架支持的图表类型 OxyPlot组件中支持7种类型的条型图表,分别为普通条形图、线型条形图、矩形条形图、差值图、龙卷风图、普通柱形图和柱形误差图,如...

大学霸
27分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部