核心内容摘要
“日韩二区”
0130万条数据插入数据库验证验证的数据库表结构如下CREATETABLEt_user ( idint(
NOTNULL AUTO_INCREMENT COMMENT用户id, usernamevarchar(
DEFAULTNULLCOMMENT用户名称, ageint(
DEFAULTNULLCOMMENT年龄, PRIMARY KEY (id) ) ENGINEInnoDBDEFAULTCHARSETutf8 COMMENT用户信息表;话不多说开整02实体类、mapper和配置文件User实体/** * p用户实体/p * * Author zjq */ Data publicclassUser{ privateint id; private String username; privateint age; }mapper接口publicinterfaceUserMapper { /** * 批量插入用户 * param userList */ voidbatchInsertUser(Param(list) ListUser userList); }mapper.xml文件!-- 批量插入用户信息 -- insertidbatchInsertUserparameterTypejava.util.List insert into t_user(username,age) values foreachcollectionlistitemitemindexindexseparator, ( #{item.username}, #{item.age} ) /foreach /insertjdbc.propertiesjdbc.drivercom.mysql.jdbc.Driver jdbc.urljdbc:mysql://localhost:3306/test jdbc.usernameroot jdbc.passwordrootsqlMapConfig.xml?xml version
0 encodingUTF-8? !DOCTYPE configuration PUBLIC -//mybatis.org//DTD Config
0//EN http://mybatis.org/dtd/mybatis-3-config.dtd configuration !--通过properties标签加载外部properties文件-- propertiesresourcejdbc.properties/properties !--自定义别名-- typeAliases typeAliastypecom.zjq.domain.Useraliasuser/typeAlias /typeAliases !--数据源环境-- environmentsdefaultdevelopement environmentiddevelopement transactionManagertypeJDBC/transactionManager dataSourcetypePOOLED propertynamedrivervalue${jdbc.driver}/ propertynameurlvalue${jdbc.url}/ propertynameusernamevalue${jdbc.username}/ propertynamepasswordvalue${jdbc.password}/ /dataSource /environment /environments !--加载映射文件-- mappers mapperresourcecom/zjq/mapper/UserMapper.xml/mapper /mappers /configuration03不分批次直接梭哈MyBatis直接一次性批量插入30万条代码如下Test publicvoidtestBatchInsertUser() throws IOException { InputStream resourceAsStream Resources.getResourceAsStream(sqlMapConfig.xml); SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session sqlSessionFactory.openSession(); System.out.println( 开始插入数据 ); long startTime System.currentTimeMillis(); try { ListUser userList new ArrayList(); for (int i 1; i 300000; i) { User user new User(); user.setId(i); user.setUsername(共饮一杯无 i); user.setAge((int) (Math.random() *
); userList.add(user); } session.insert(batchInsertUser, userList); // 最后插入剩余的数据 session.commit(); long spendTime System.currentTimeMillis()-startTime; System.out.println(成功插入 30 万条数据,耗时spendTime毫秒); } finally { session.close(); } }可以看到控制台输出Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 yun
. You can change this value on the server by setting the maxallowedpacket’ variable.超出最大数据包限制了可以通过调整maxallowedpacket限制来提高可以传输的内容不过由于30万条数据超出太多这个不可取梭哈看来是不行了 既然梭哈不行那我们就一条一条循环着插入行不行呢04循环逐条插入mapper接口和mapper文件中新增单个用户新增的内容如下:/** * 新增单个用户 * param user */ void insertUser(User user); !-- 新增用户信息 -- insertidinsertUserparameterTypeuser insert into t_user(username,age) values ( #{username}, #{age} ) /insert调整执行代码如下Test publicvoidtestCirculateInsertUser() throws IOException { InputStream resourceAsStream Resources.getResourceAsStream(sqlMapConfig.xml); SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session sqlSessionFactory.openSession(); System.out.println( 开始插入数据 ); long startTime System.currentTimeMillis(); try { for (int i 1; i 300000; i) { User user new User(); user.setId(i); user.setUsername(共饮一杯无 i); user.setAge((int) (Math.random() *
); // 一条一条新增 session.insert(insertUser, user); session.commit(); } long spendTime System.currentTimeMillis()-startTime; System.out.println(成功插入 30 万条数据,耗时spendTime毫秒); } finally { session.close(); } }执行后可以发现磁盘IO占比飙升一直处于高位。
等啊等等啊等好久还没执行完先不管他了太慢了先搞其他的等会再来看看结果吧。
two thousand year later …控制台输出如下总共执行了14909367毫秒换算出来是4小时八分钟。
太慢了。
。
还是优化下之前的批处理方案吧05MyBatis实现插入数据先清理表数据然后优化批处理执行插入-- 清空用户表 TRUNCATEtable t_user;以下是通过 MyBatis 实现 30 万条数据插入代码实现/** * 分批次批量插入 * throws IOException */ Test publicvoidtestBatchInsertUser() throws IOException { InputStream resourceAsStream Resources.getResourceAsStream(sqlMapConfig.xml); SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session sqlSessionFactory.openSession(); System.out.println( 开始插入数据 ); long startTime System.currentTimeMillis(); int waitTime 10; try { ListUser userList new ArrayList(); for (int i 1; i 300000; i) { User user new User(); user.setId(i); user.setUsername(共饮一杯无 i); user.setAge((int) (Math.random() *
); userList.add(user); if (i % 1000
{ session.insert(batchInsertUser, userList); // 每 1000 条数据提交一次事务 session.commit(); userList.clear(); // 等待一段时间 Thread.sleep(waitTime *
; } } // 最后插入剩余的数据 if(!CollectionUtils.isEmpty(userList)) { session.insert(batchInsertUser, userList); session.commit(); } long spendTime System.currentTimeMillis()-startTime; System.out.println(成功插入 30 万条数据,耗时spendTime毫秒); } catch (Exception e) { e.printStackTrace(); } finally { session.close(); } }使用了 MyBatis 的批处理操作将每 1000 条数据放在一个批次中插入能够较为有效地提高插入速度。
同时请注意在循环插入时要带有合适的等待时间和批处理大小以防止出现内存占用过高等问题。
此外还需要在配置文件中设置合理的连接池和数据库的参数以获得更好的性能。
在上面的示例中我们每插入1000行数据就进行一次批处理提交并等待10秒钟。
这有助于控制内存占用并确保插入操作平稳进行。
五十分钟执行完毕时间主要用在了等待上。
如果低谷时期执行CPU和磁盘性能又足够的情况下直接批处理不等待执行/** * 分批次批量插入 * throws IOException */ Test publicvoidtestBatchInsertUser() throws IOException { InputStream resourceAsStream Resources.getResourceAsStream(sqlMapConfig.xml); SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession session sqlSessionFactory.openSession(); System.out.println( 开始插入数据 ); long startTime System.currentTimeMillis(); int waitTime 10; try { ListUser userList new ArrayList(); for (int i 1; i 300000; i) { User user new User(); user.setId(i); user.setUsername(共饮一杯无 i); user.setAge((int) (Math.random() *
); userList.add(user); if (i % 1000
{ session.insert(batchInsertUser, userList); // 每 1000 条数据提交一次事务 session.commit(); userList.clear(); } } // 最后插入剩余的数据 if(!CollectionUtils.isEmpty(userList)) { session.insert(batchInsertUser, userList); session.commit(); } long spendTime System.currentTimeMillis()-startTime; System.out.println(成功插入 30 万条数据,耗时spendTime毫秒); } catch (Exception e) { e.printStackTrace(); } finally { session.close(); } }则24秒可以完成数据插入操作可以看到短时CPU和磁盘占用会飙高。
把批处理的量再调大一些调到5000在执行13秒插入成功30万条直接起飞06JDBC实现插入30万条数据JDBC循环插入的话跟上面的mybatis逐条插入类似不再赘述。
以下是 Java 使用 JDBC 批处理实现 30 万条数据插入的示例代码。
请注意该代码仅提供思路具体实现需根据实际情况进行修改。
/** * JDBC分批次批量插入 * throws IOException */ Test publicvoidtestJDBCBatchInsertUser() throws IOException { Connection connection null; PreparedStatement preparedStatement null; String databaseURL jdbc:mysql://localhost:3306/test; String user root; String password root; try { connection DriverManager.getConnection(databaseURL, user, password); // 关闭自动提交事务改为手动提交 connection.setAutoCommit(false); System.out.println( 开始插入数据 ); long startTime System.currentTimeMillis(); String sqlInsert INSERT INTO t_user ( username, age) VALUES ( ?, ?); preparedStatement connection.prepareStatement(sqlInsert); Random random new Random(); for (int i 1; i 300000; i) { preparedStatement.setString(1, 共饮一杯无 i); preparedStatement.setInt(2, random.nextInt(
); // 添加到批处理中 preparedStatement.addBatch(); if (i % 1000
{ // 每1000条数据提交一次 preparedStatement.executeBatch(); connection.commit(); System.out.println(成功插入第 i 条数据); } } // 处理剩余的数据 preparedStatement.executeBatch(); connection.commit(); long spendTime System.currentTimeMillis()-startTime; System.out.println(成功插入 30 万条数据,耗时spendTime毫秒); } catch (SQLException e) { System.out.println(Error: e.getMessage()); } finally { if (preparedStatement ! null) { try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection ! null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }上述示例代码中我们通过 JDBC 连接 MySQL 数据库并执行批处理操作插入数据。
具体实现步骤如下获取数据库连接。
创建 Statement 对象。
定义 SQL 语句使用 PreparedStatement 对象预编译 SQL 语句并设置参数。
执行批处理操作。
处理剩余的数据。
关闭 Statement 和 Connection 对象。
使用setAutoCommit(false) 来禁止自动提交事务然后在每次批量插入之后手动提交事务。
每次插入数据时都新建一个 PreparedStatement 对象以避免状态不一致问题。
在插入数据的循环中每 10000 条数据就执行一次 executeBatch() 插入数据。
另外需要根据实际情况优化连接池和数据库的相关配实现高效的大量数据插入需要结合以下优化策略建议综合使用
批处理 批量提交SQL语句可以降低网络传输和处理开销减少与数据库交互的次数。
在Java中可以使用Statement或者PreparedStatement的addBatch()方法来添加多个SQL语句然后一次性执行executeBatch()方法提交批处理的SQL语句。
在循环插入时带有适当的等待时间和批处理大小从而避免内存占用过高等问题设置适当的批处理大小批处理大小指在一次插入操作中插入多少行数据。
如果批处理大小太小插入操作的频率将很高而如果批处理大小太大可能会导致内存占用过高。
通常建议将批处理大小设置为
行这将减少插入操作的频率并降低内存占用。
采用适当的等待时间等待时间指在批处理操作之间等待的时间量。
等待时间过短可能会导致内存占用过高而等待时间过长则可能会延迟插入操作的速度。
通常建议将等待时间设置为几秒钟到几十秒钟之间这将使操作变得平滑且避免出现内存占用过高等问题。
可以考虑使用一些内存优化的技巧例如使用内存数据库或使用游标方式插入数据以减少内存占用。
总的来说选择适当的批处理大小和等待时间可以帮助您平稳地进行插入操作避免出现内存占用过高等问题。
索引: 在大量数据插入前暂时去掉索引最后再打上这样可以大大减少写入时候的更新索引的时间。
数据库连接池 使用数据库连接池可以减少数据库连接建立和关闭的开销提高性能。
在没有使用数据库连接池的情况记得在finally中关闭相关连接。
数据库参数调整增加MySQL数据库缓冲区大小、配置高性能的磁盘和I/O等。