乐趣区

关于java:13-秒插入-30-万条数据我惊呆了

jiezi

本文次要讲述通过 MyBatis、JDBC 等做大数据量数据插入的案例和后果。

30 万条数据插入插入数据库验证

  • 实体类、mapper 和配置文件定义
    • User 实体
    • mapper 接口
    • mapper.xml 文件
    • jdbc.properties
    • sqlMapConfig.xml
  • 不分批次间接梭哈
  • 循环逐条插入
  • MyBatis 实现插入 30 万条数据
  • JDBC 实现插入 30 万条数据
  • 总结

验证的数据库表构造如下：

CREATE TABLE `t_user` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户 id',
  `username` varchar(64) DEFAULT NULL COMMENT '用户名称',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

话不多说，开整！

举荐一个开源收费的 Spring Boot 实战我的项目：

https://github.com/javastacks/spring-boot-best-practice

实体类、mapper 和配置文件定义

User 实体

/**
 * <p> 用户实体 </p>
 *
 * @Author zjq
 */
@Data
public class User {

    private int id;
    private String username;
    private int age;

}

mapper 接口

public interface UserMapper {

    /**
     * 批量插入用户
     * @param userList
     */
    void batchInsertUser(@Param("list") List<User> userList);

}

mapper.xml 文件

<!-- 批量插入用户信息 -->
<insert id="batchInsertUser" parameterType="java.util.List">
    insert into t_user(username,age) values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.username},
        #{item.age}
        )
    </foreach>
</insert>

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!-- 通过 properties 标签加载内部 properties 文件 -->
    <properties resource="jdbc.properties"></properties>

    <!-- 自定义别名 -->
    <typeAliases>
        <typeAlias type="com.zjq.domain.User" alias="user"></typeAlias>
    </typeAliases>

    <!-- 数据源环境 -->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载映射文件 -->
    <mappers>
        <mapper resource="com/zjq/mapper/UserMapper.xml"></mapper>
    </mappers>

</configuration>

不分批次间接梭哈

MyBatis 间接一次性批量插入 30 万条，代码如下：

@Test
public void testBatchInsertUser() 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 {List<User> 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() * 100));
            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 4194304). You can change this value on the server by setting the max_allowed_packet’variable.

超出最大数据包限度了，能够通过调整 max_allowed_packet 限度来进步能够传输的内容，不过因为 30 万条数据超出太多，这个不可取，梭哈看来是不行了 😅😅😅

既然梭哈不行那咱们就一条一条循环着插入行不行呢！

循环逐条插入

mapper 接口和 mapper 文件中新增单个用户新增的内容如下:

/**
 * 新增单个用户
 * @param user
 */
void insertUser(User user);
<!-- 新增用户信息 -->
<insert id="insertUser" parameterType="user">
    insert into t_user(username,age) values
        (#{username},
        #{age}
        )
</insert>

调整执行代码如下：

@Test
public void testCirculateInsertUser() 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() * 100));
            // 一条一条新增
            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 小时八分钟。太慢了。。

还是优化下之前的批处理计划吧

MyBatis 实现插入 30 万条数据

先清理表数据，而后优化批处理执行插入：

-- 清空用户表
TRUNCATE table  t_user;

以下是通过 MyBatis 实现 30 万条数据插入代码实现：

/**
 * 分批次批量插入
 * @throws IOException
 */
@Test
public void testBatchInsertUser() 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 {List<User> 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() * 100));
            userList.add(user);
            if (i % 1000 == 0) {session.insert("batchInsertUser", userList);
                // 每 1000 条数据提交一次事务
                session.commit();
                userList.clear();

                // 期待一段时间
                Thread.sleep(waitTime * 1000);
            }
        }
        // 最初插入残余的数据
        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
public void testBatchInsertUser() 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 {List<User> 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() * 100));
            userList.add(user);
            if (i % 1000 == 0) {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 万条，间接芜湖腾飞🛫🛫🛫

JDBC 实现插入 30 万条数据

JDBC 循环插入的话跟下面的 mybatis 逐条插入相似，不再赘述。

以下是 Java 应用 JDBC 批处理实现 30 万条数据插入的示例代码。请留神，该代码仅提供思路，具体实现需依据理论状况进行批改。

/**
 * JDBC 分批次批量插入
 * @throws IOException
 */
@Test
public void testJDBCBatchInsertUser() 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(100));
            // 增加到批处理中
            preparedStatement.addBatch();

            if (i % 1000 == 0) {
                // 每 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() 插入数据。

另外，须要依据理论状况优化连接池和数据库的相干配置，以避免连贯超时等问题。

总结

实现高效的大量数据插入须要联合以下优化策略（倡议综合应用）：

1. 批处理： 批量提交 SQL 语句能够升高网络传输和解决开销，缩小与数据库交互的次数。在 Java 中能够应用 Statement 或者 PreparedStatement 的addBatch()办法来增加多个 SQL 语句，而后一次性执行 executeBatch() 办法提交批处理的 SQL 语句。

  • 在循环插入时带有适当的等待时间和批处理大小，从而防止内存占用过低等问题：
    • 设置适当的批处理大小：批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小，插入操作的频率将很高，而如果批处理大小太大，可能会导致内存占用过高。通常，倡议将批处理大小设置为 1000-5000 行，这将缩小插入操作的频率并升高内存占用。
    • 采纳适当的等待时间：等待时间指在批处理操作之间期待的工夫量。等待时间过短可能会导致内存占用过高，而等待时间过长则可能会提早插入操作的速度。通常，倡议将等待时间设置为几秒钟到几十秒钟之间，这将使操作变得平滑且避免出现内存占用过低等问题。
    • 能够思考应用一些内存优化的技巧，例如应用内存数据库或应用游标形式插入数据，以缩小内存占用。
  • 总的来说，抉择适当的批处理大小和等待时间能够帮忙您安稳地进行插入操作，避免出现内存占用过低等问题。

2. 索引: 在大量数据插入前临时去掉索引，最初再打上，这样能够大大减少写入时候的更新索引的工夫。

3. 数据库连接池： 应用数据库连接池能够缩小数据库连贯建设和敞开的开销，进步性能。在没有应用数据库连接池的状况，记得在 finally 中敞开相干连贯。

数据库参数调整：减少 MySQL 数据库缓冲区大小、配置高性能的磁盘和 I / O 等。

版权申明：本文为 CSDN 博主「共饮一杯无」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。原文链接：https://blog.csdn.net/qq_35427589/article/details/129665307

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2022 最新版)

2. 劲爆！Java 协程要来了。。。

3.Spring Boot 2.x 教程，太全了！

4. 别再写满屏的爆爆爆炸类了，试试装璜器模式，这才是优雅的形式！！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

退出移动版