每日一博 | 如何快速安全地插入千万条数据
前言
最近有个需求解析一个订单文件,并且说明文件可达到千万条数据,每条数据大概在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分钟左右,当然可以通过设置截取的文件大小,花费的时间也会相应的改变。
完整代码
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
OSChina 周六乱弹 —— 如果是个帅小伙你愿意和他出去吗
Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 小小编辑推荐:《Ghost 》游戏《死亡搁浅》原声 《Ghost 》游戏(《死亡搁浅》原声) - Au/Ra / Alan Walker 手机党少年们想听歌,请使劲儿戳(这里) @锦年 :你们晚上起来尿尿吗,我以前好像不起的,最近每天晚上起来,感觉影响睡眠质量啊 起来啊, 被喊起来…… 遇到厕所排队怎么办? @Cobbage :厕所排队,快喷出来了怎么办 还能怎么办 “憋尿能行千里,拉稀寸步难行。” 倒霉事也不止这一个啊, @高龄购物车驾驶员 :没买房个个都来借钱 找自己亲戚借钱算什么本事! 怂! 应该这么操作, 真是不可思议, @李太浪 :最苦涩的是什么? 就是你曾经调侃过的不可思议的事情发生在你身上了 比如有那些悲剧人生? 先自己设计一下, 看过这种漫画, 总觉得gay里gay气的。 会不会影响现实生活啊, @半缕阳光 :公司有一个漂亮妹子,最近也走的比较近,我也是大龄青年了,但是我就是觉得也就那样啊,都没有约她出去的想法,我是有什么毛病吗? 有毛病? 没毛病? 需要你自己检测一下, @小沉沦 :想想如果是个帅小伙你愿意和他出去...
- 下一篇
存在至少 4 年的 Linux 漏洞被发现:可通过 WiFi 攻击目标计算机
一位安全研究人员表示,Linux 中存在的严重安全漏洞能导致使用 WiFi 信号的附近设备崩溃,或者完全被黑客掌控。名叫 Nico Waisman 的安全研究人员发推文称,该漏洞位于 RTLWIFI 驱动程序中,而该驱动程序用于在 Linux 设备上支持 Realtek WiFi 芯片。 据悉,当具有 Realtek Wi-Fi 芯片的计算机在恶意设备的无线电范围内时,该漏洞将会在 Linux 内核中触发缓冲区溢出问题。该漏洞不仅可以引起操作系统崩溃,而且还允许黑客完全掌控计算机。这一缺陷可追溯到 2013 年发布的 Linux 内核的 3.10.1 版本。 Github 的首席安全工程师 Nico Waisman 表示:“这个漏洞非常严重,只要您使用 Realtek(RTLWIFI)驱动程序,此漏洞就可以通过 Linux 内核上的 Wi-Fi 远程触发溢出。”漏洞编号为 CVE-2019-17666。 Linux 开发人员在星期三提出了一个修复程序,很可能在未来几天或几周内将其合并到 OS 内核中。只有在此之后,该修补程序才能进入各种 Linux 发行版。 Waisman 表示目前还...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
-
Docker使用Oracle官方镜像安装(12C,18C,19C)
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS8编译安装MySQL8.0.19
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- MySQL8.0.19开启GTID主从同步CentOS8
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
推荐阅读
最新文章
- Linux系统CentOS6、CentOS7手动修改IP地址
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- CentOS6,CentOS7官方镜像安装Oracle11G
- SpringBoot2整合Redis,开启缓存,提高访问速度
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- Hadoop3单机部署,实现最简伪集群