Java で JDBC バッチ挿入を実装する方法

1. 説明

JDBC では、executeBatch メソッドは複数の dml ステートメントをバッチで実行でき、executeUpdate を個別に実行するよりもはるかに効率的です。原理は何ですか? mysqlとoracleでバッチ実行を実装するにはどうすればよいですか?この記事ではその原理を紹介します。

2. 実験の概要

この実験は次の 3 つの手順で実行されます

a. jdbc のバッチ実行と単一実行にかかる時間を mysql で記録します

b. Oracle での jdbc のバッチ実行と単一実行にかかる時間を記録します

c. Oracle plsql

のバッチ実行と単一実行にかかる時間を記録します関連する Javaデータベースのバージョンは次のとおりです: Java17、Mysql8、Oracle11G

3. 正式な実験

mysqlとoracleにそれぞれテーブルを作成します

rrreerrree

監査を有効にする必要があります実験前のデータベースの内容

Mysql は監査をオンにします:

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

oracle は監査をオンにします:

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

Java コードは次のとおりです:

set global general_log = 1;

いくつかコードの注意点

• mysql の URL には useServerPrepStmts=true&rewriteBatchedStatements=true パラメータを追加する必要があります。

• batchCnt は各バッチで実行される SQL ステートメントの数を表し、0 は 1 回の実行を表します。

最初のテスト mysql

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

さまざまなbatchCnt値を入力して実行時間を確認します

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、1 回の挿入、合計所要時間: 59620 ミリ秒

一般ログの表示

batchCnt=5

batchCnt=0

いくつかの結論を導き出すことができます:

• バッチ実行の効率は、以前と比べて大幅に向上しています。単一の実行に。

• mysql のバッチ実行では、実際には SQL が書き換えられ、複数の挿入が insert xx value(),()... にマージされて実行されます。

• batchCnt を 50 から 100 に変更すると、基本的に時間は半分に短縮されますが、この値を拡張すると、時間の短縮は明らかではなく、実行時間はさらに増加し​​ます。 。 高い。

分析理由:

クライアントが実行する SQL ステートメントをデータベース サーバーに送信した後、データベースは SQL ステートメントを実行し、結果をサーバーに返します。クライアント。合計所要時間 = データベースの実行時間、ネットワーク送信時間。バッチ実行による往復回数が減ると、ネットワーク転送時間が短縮され、全体の時間が短縮されます。ただし、batchCnt が大きくなると、ネットワーク送信時間が主なボトルネックでなくなったとしても、合計時間の短縮はそれほど顕著ではなくなります。特に、batchCnt=10000、つまり 10,000 個のステートメントをすべて一度に実行する場合、時間が長くなります。これは、これらの入力パラメータを準備するときにプログラムとデータベースがより多くのメモリを適用する必要があるため、時間がかかることが考えられます。私の推測）。

もう 1 つ、batchCnt の値は無限にできますか? 1 億項目を挿入する必要があると仮定すると、一度に 1 億項目をバッチで挿入できますか?もちろんそうではありません。元に戻すときのスペースの問題は考慮していません。第一に、コンピュータには 1 億個の SQL 入力パラメータをすべて一度に保存できるほど大きなメモリがありません。第二に、mysql には制限するためのパラメータ max_allowed_pa​​cket もあります。単一ステートメントの長さ、最大は 1G バイトです。ステートメントが長すぎる場合、「クエリのパケットが大きすぎます (1,773,901 > 1,599,488)。'max_allowed_pa​​cket' 変数を設定することで、サーバー上でこの値を変更できます。」と報告されます。

次のテスト oracle

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);
    }
}

実行時間を確認するには、別の batchCnt 値を代入します。

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、1 回の挿入、合計消費時間: 60830 ミリ秒

Oracle での実行の影響は、基本的に次のとおりです。 MySQLと同様で、バッチ処理の効率が明らかに高く、一行で実行できます。問題は、Oracle にはそのような insert xx value(),()... 構文がないことです。では、どうやってバッチ実行を実現するのでしょうか?

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 中国語 Web サイトの他の関連記事を参照してください。