Java에서 JDBC 일괄 삽입을 구현하는 방법

1. 설명

JDBC에서는 여러 개의 dml 문을 일괄적으로 실행할 수 있는 메소드가 있는데, 개별적으로 실행하는 것보다 효율성이 훨씬 높습니다. mysql과 oracle에서 일괄 실행을 구현하는 방법은 무엇입니까? 이 기사에서는 이에 대한 원리를 소개합니다.

2. 실험 소개

이 실험은 다음 세 단계를 통해 진행됩니다

a. mysql에서 jdbc의 일괄 실행에 소요되는 시간을 기록합니다

b. 그리고 oracle

c에서 jdbc의 단일 실행을 기록합니다. oracle plsql 일괄 실행 및 단일 실행에 소요되는 시간을 기록합니다.

관련 Java 및 데이터베이스 버전은 다음과 같습니다: Java17, Mysql8, Oracle11G

3. 각각 mysql과 oracle의 테이블

create table t (  -- mysql中创建表的语句
    id    int,
    name1 varchar(100),
    name2 varchar(100),
    name3 varchar(100),
    name4 varchar(100)
);

create table t (  -- oracle中创建表的语句
    id    number,
    name1 varchar2(100),
    name2 varchar2(100),
    name3 varchar2(100),
    name4 varchar2(100)
);

실험 전에 데이터베이스 감사를 설정해야 합니다.

mysql이 감사를 설정합니다.

set global general_log = 1;

oracle이 감사를 설정합니다.

alter system set audit_trail=db, extended;  
audit insert table by scott;  -- 实验采用scott用户批量执行insert的方式

java 코드는 다음과 같습니다.

import java.sql.*;

public class JdbcBatchTest {

    /**
     * @param dbType 数据库类型，oracle或mysql
     * @param totalCnt 插入的总行数
     * @param batchCnt 每批次插入的行数，0表示单条插入
     */
    public static void exec(String dbType, int totalCnt, int batchCnt) throws SQLException, ClassNotFoundException {
        String user = "scott";
        String password = "xxxx";
        String driver;
        String url;
        if (dbType.equals("mysql")) {
            driver = "com.mysql.cj.jdbc.Driver";
            url = "jdbc:mysql://ip/hello?useServerPrepStmts=true&rewriteBatchedStatements=true";
        } else {
            driver = "oracle.jdbc.OracleDriver";
            url = "jdbc:oracle:thin:@ip:orcl";
        }

        long l1 = System.currentTimeMillis();
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        connection.setAutoCommit(false);
        String sql = "insert into t values (?, ?, ?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= totalCnt; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "red" + i);
            preparedStatement.setString(3, "yel" + i);
            preparedStatement.setString(4, "bal" + i);
            preparedStatement.setString(5, "pin" + i);

            if (batchCnt > 0) {
                // 批量执行
                preparedStatement.addBatch();
                if (i % batchCnt == 0) {
                    preparedStatement.executeBatch();
                } else if (i == totalCnt) {
                    preparedStatement.executeBatch();
                }
            } else {
                // 单条执行
                preparedStatement.executeUpdate();
            }
        }
        connection.commit();
        connection.close();
        long l2 = System.currentTimeMillis();
        System.out.println("总条数：" + totalCnt + (batchCnt>0? ("，每批插入："+batchCnt) : "，单条插入") + "，一共耗时："+ (l2-l1) + " 毫秒");
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        exec("mysql", 10000, 50);
    }
}

코드에서 주의할 몇 가지 사항은

mysql url에 useServerPrepStmts= true&rewriteBatchedStatements=true 매개변수를 추가해야 합니다.
batchCnt는 각 배치에서 실행된 SQL 문 수를 나타내고, 0은 단일 실행을 나타냅니다.
첫 번째 mysql 테스트

exec("mysql", 10000, batchCnt);

실행 시간을 확인하려면 다른 배치Cnt 값을 입력하세요

batchCnt=50 총 항목 수: 10000, 배치당 삽입: 50, 총 소요 시간: 4369밀리초

batchCnt= 100 총 항목 수: 10000, 각 삽입 배치: 100, 총 소요 시간: 2598밀리초

batchCnt=200 총 항목 수: 10000, 각 삽입 배치: 200, 총 소요 시간: 2211밀리초
batchCnt=1000 합계 항목 수: 10000, 각 배치 삽입: 1000, 총 소요 시간: 2099밀리초
batchCnt=10000 총 항목 수: 10000, 각 배치 삽입: 10000, 총 소요 시간: 2418밀리초
batchCnt=0 총 항목 수 : 10000, 단일 삽입, 총 소요 시간: 59620밀리초

일반 로그 보기

batchCnt=5

batchCnt=0

몇 가지 결론을 내릴 수 있습니다.

일괄 실행의 효율성이 이전에 비해 크게 향상되었습니다. 단일 실행으로.
mysql의 일괄 실행은 실제로 SQL을 다시 작성하고 여러 삽입을 삽입 xx 값(),()...에 병합하여 실행합니다.
batchCnt를 50에서 100으로 변경하면 기본적으로 시간이 절반으로 단축되지만 값을 확장하면 시간 단축이 확실하지 않고 실행 시간도 늘어납니다.
분석 이유:

클라이언트가 실행할 SQL 문을 데이터베이스 서버로 보낸 후 데이터베이스는 해당 SQL 문을 실행하고 그 결과를 클라이언트에 반환합니다. 총 소요 시간 = 데이터베이스 실행 시간 + 네트워크 전송 시간. 일괄 실행을 통해 왕복 횟수를 줄이면 네트워크 전송 시간이 줄어들어 전체 시간도 줄어듭니다. 그러나 배치Cnt가 커지면 네트워크 전송 시간이 더 이상 주요 병목 현상이 아니더라도 전체 시간의 감소는 그리 뚜렷하지 않습니다. 특히 BatchCnt=10000, 즉 10,000개의 문을 모두 동시에 실행하는 경우에는 시간이 더 길어집니다. 이는 이러한 입력 매개변수를 준비할 때 프로그램과 데이터베이스가 더 큰 메모리를 적용해야 하므로 시간이 더 많이 소요되기 때문일 수 있습니다. 내 추측).

한 가지 더, 1억 개의 항목을 삽입해야 한다고 가정하면 한 번에 1억 개의 항목을 일괄 삽입할 수 있나요? 물론 아닙니다. 실행 취소의 공간 문제는 고려하지 않습니다. 우선 컴퓨터에는 1억 개의 SQL 입력 매개변수를 모두 한 번에 저장할 만큼 큰 메모리가 없습니다. 두 번째로 mysql에는 제한할 매개변수 max_allowed_packet이 있습니다. 단일 문의 길이는 최대 1GB입니다. 문이 너무 길면 "쿼리용 패킷이 너무 큽니다(1,773,901 > 1,599,488). 'max_allowed_packet' 변수를 설정하여 서버에서 이 값을 변경할 수 있습니다."라는 메시지가 보고됩니다.

다음으로 oracle을 테스트

exec("oracle", 10000, batchCnt);

실행 시간을 확인하려면 다른 배치Cnt 값을 입력하세요

batchCnt=50 총 항목 수: 10000, 각 배치 삽입: 50, 총 시간 소모: 2055밀리초

batchCnt=100 총 항목 수: 10000, 각 삽입 배치: 100, 총 소비 시간: 1324밀리초

batchCnt=200 총 항목 수: 10000, 각 삽입 배치: 200, 총 소비 시간: 856밀리초
batchCnt=1000 총 개수 항목 수: 10000, 각 배치 삽입: 1000, 총 소요 시간: 785밀리초
batchCnt=10000 총 항목 수: 10000, 각 배치 삽입: 10000, 총 소요 시간: 804밀리초
batchCnt=0 총 항목 수: 10000, 단일 삽입, 총 소요 시간: 60830밀리초

Oracle에서 실행 효과는 기본적으로 MySQL과 동일하며 일괄 작업의 효율성은 단일 실행보다 훨씬 높습니다. 문제는 Oracle에 xx 값 삽입(),()... 구문이 없다는 것입니다. 그러면 일괄 실행을 어떻게 달성합니까?

batchCnt=50이 실행될 때 감사 뷰 dba_audit_trail 보기

从审计的结果中可以看到，batchCnt=50的时候，审计记录只有200条（扣除登入和登出），也就是sql只执行了200次。sql_text没有发生改写，仍然是"insert into t values (:1 , :2 , :3 , :4 , :5 )"，而且sql_bind只记录了批量执行的最后一个参数，即50的倍数。根据awr报告可以看出，实际只执行了200次（由于篇幅限制，省略了awr截图）。那么oracle是怎么做到只执行200次但插入1万条记录的呢？我们来看看oracle中使用存储过程的批量插入。

四、存储过程

准备数据：

首先将t表清空 truncate table t;

用java往t表灌10万数据 exec("oracle", 100000, 1000);

创建t1表 create table t1 as select * from t where 1 = 0;

以下两个过程的意图一致，均为将t表中的数据导入t1表。nobatch是单次执行，usebatch是批量执行。

create or replace procedure nobatch is
begin
  for x in (select * from t)
  loop
    insert into t1 (id, name1, name2, name3, name4)
    values (x.id, x.name1, x.name2, x.name3, x.name4);
  end loop;
  commit;
end nobatch;
/

create or replace procedure usebatch (p_array_size in pls_integer)
is
  type array is table of t%rowtype;
  l_data array;
  cursor c is select * from t;
begin
  open c;
  loop
    fetch c bulk collect into l_data limit p_array_size;
    forall i in 1..l_data.count insert into t1 values l_data(i);
    exit when c%notfound;
  end loop;
  commit;
  close c;
end usebatch;
/

执行上述存储过程

SQL> exec nobatch;  
Elapsed: 00:00:32.92

SQL> exec usebatch(50);
Elapsed: 00:00:00.77

SQL> exec usebatch(100);
Elapsed: 00:00:00.47

SQL> exec usebatch(1000);
Elapsed: 00:00:00.19

SQL> exec usebatch(100000);
Elapsed: 00:00:00.26

存储过程批量执行效率也远远高于单条执行。查看usebatch(50)执行时的审计日志，sql_bind也只记录了批量执行的最后一个参数，即50的倍数。与使用executeBatch方法在记录内容方面相同。因此可以推断，JDBC的executeBatch和存储过程的批量执行都采用了相同的方法

存储过程的这个关键点就是forall。查阅相关文档。

The FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses.
The different values come from existing, populated collections or host arrays. The FORALL statement is usually much faster than an equivalent FOR LOOP statement.
The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time.

翻译过来就是forall很快，原因就是不需要每次执行的时候等待参数。

위 내용은 Java에서 JDBC 일괄 삽입을 구현하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!