插入一百万数据的最优解分析和耗时

nkc777

已于 2022-09-05 20:12:01 修改

阅读量4.9k

点赞数 3

分类专栏： java 文章标签： java 开发语言

于 2022-09-04 13:13:12 首次发布

本文链接：https://blog.csdn.net/weixin_41605123/article/details/126688101

版权

java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

很多业务情况会有导入功能，可能是提交一批数据进行插入，所以现在需要用一种可行性高的读取以及插入的实现方案。

本文gitee项目地址：BigDataImportProject: 从文件读取大数据量，并写入到数据库中

在dev分支中

机器：cpu是Amd1500x，内存16G，固态硬盘

第一步，有数据量

使用WriteTxtDataService类去生成一百万的数据，创建文件后，使用FileOutputStream写入到文件中。

private static void writeData() throws IOException {
		try (FileOutputStream fos = new FileOutputStream(Constant.getFilePath())) {
			byte[] c = new byte[2];
			c[0] = 0x0d;
			c[1] = 0x0a;
			String value = new String(c);
			int a = Constant.allNum;
			Random random = new Random();
			while (a > 0) {
				long str = random.nextLong();
				fos.write((str + value).getBytes());
				a--;
			}
		}
}

第二步，分批读取数据

将所需的数据集分批读取，这里采用了BufferReader去读取文件，这个读取的过程非常快，因为这个jdk下的包，直接操作字节数据的，从跳跃万行数据到写入仅需十多毫秒，看机器情况。

（前提：文件内容不会发生改变，是只读文件）

步骤如下：
①指定当前批次batchNum，每批次所读取的数据量大小batchCount。

②根据这两个值，跳跃到需读取的行

②读取batchCount行后，写入到list中

private static List<String> getListByBatch(int batchNum) throws IOException {
		Integer batchCount = Constant.batchCount;
		File file = new File(Constant.getFilePath());
		BufferedReader bufferedReader = new BufferedReader(new FileReader(file));
		int currentNode = (batchNum - 1) * batchCount;
		// 移动到所要读取的行
		for (int i = 0; i < currentNode; i++) {
			bufferedReader.readLine();
		}
		int count = 0;
		String s = null;
		List<String> list = new LinkedList<>();
		// 读取批次
		while ((s = bufferedReader.readLine()) != null && count < batchCount) {
			count++;
			list.add(s);
		}
		return list;
}

第三步，开启数据库连接的批处理

这一步非常重要，rewriteBatchedStatements=true，可以让MySQL接收批量提交的sql，这样我们在进行事务的批量提交时，就能够一次性把sql发送给MySQL。否则就是一条条发送给MySQL，效率极低。

Connection conn = 
DriverManager.getConnection("jdbc:mysql://localhost:3306/" + 
Constant.databaseName + "?rewriteBatchedStatements=true", 
Constant.userName, 
Constant.passWord);

第四步，批量插入

获取到构建好的数据集，关闭事务的自动提交，并且添加多批数据，一次性提交事务给MySQL，结合前面的rewriteBatchedStatements，这样MySQL就能够一次性处理大批数量的sql语句。

public static void saveInfoByBatch(List<String> list) {
		PreparedStatement pstmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into " + Constant.tableName + " values (?)";
			pstmt = MySqlOptUtil.getPreparedStmt(conn, sql);
			for (String aList : list) {
				pstmt.setString(1, aList);
				// 添加数据
				pstmt.addBatch();
			}
			// 执行事务
			pstmt.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			MySqlOptUtil.closeStmt(pstmt);
		}
	}

有朋友就好奇了，我组装一条大sql去插入会怎么样？

前提是要将MySQL的max_allowed_packet设置的很大，默认是1MB的，我改成了100MB，实际业务场景传输的数据量的包大小可能会更大~

这里也有实现方式，只不过组装数据的时候比较麻烦，但是设置一批10000条数据去提交的情况下，批量提交和组装大sql的提交，效率是差不多的，以下是实现代码：

public static void saveInfo(List<String> list) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = MySqlOptUtil.getConn();
			StringBuilder builder = new StringBuilder();
			builder.append("insert into ").append(Constant.tableName).append(" (big_value) values ");
			list.forEach(t -> builder.append("('").append(t).append("'),"));
			String sql = builder.toString();
			sql = sql.substring(0, sql.length() - 1);
			pstmt = MySqlOptUtil.getPreparedStmt(conn, sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			log.error("执行失败", e);
		} finally {
			MySqlOptUtil.closeStmt(pstmt);
			MySqlOptUtil.closeConn(conn);
		}
	}

还有的大兄弟会说，我们业务都是用mybatis的呀，哪有人用jdbc，所以这里我也引入了mybatis-pus的依赖，做了插入，批量条数设置为集合的长度了，因为默认的一千条太慢了。

private static void saveInfoByMybatisPlus(List<String> list) {
		List<BigTable> tableList = list.stream().map(t -> new BigTable().setBigValue(t)).collect(Collectors.toList());
		BigTableSerive bigTableSerive = SpringUtil.getBean(BigTableSerive.class);
		bigTableSerive.saveBatch(tableList, tableList.size());
	}

当然rewriteBatchedStatements也是要开启的