1. 説明

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

2. 実験の概要

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

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

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

c. Oracle plsql

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

3. 正式な実験

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

監(jiān)査を有効にする必要があります実験前のデータベースの內(nèi)容

Mysql は監(jiān)査をオンにします:

create table t (  -- mysql中創(chuàng)建表的語(yǔ)句
    id    int,
    name1 varchar(100),
    name2 varchar(100),
    name3 varchar(100),
    name4 varchar(100)
);

oracle は監(jiān)査をオンにします:

create table t (  -- oracle中創(chuàng)建表的語(yǔ)句
    id    number,
    name1 varchar2(100),
    name2 varchar2(100),
    name3 varchar2(100),
    name4 varchar2(100)
);

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

set global general_log = 1;

いくつかコードの注意點(diǎn)

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

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

最初のテスト mysql

alter system set audit_trail=db, extended;  
audit insert table by scott;  -- 實(shí)驗(yàn)采用scott用戶批量執(zhí)行insert的方式

さまざまなbatchCnt値を入力して実行時(shí)間を確認(rèn)します

batchCnt=50 合計(jì)數(shù): 10000 、各バッチ挿入: 50、合計(jì)所要時(shí)間: 4369 ミリ秒
batchCnt=100 合計(jì)アイテム數(shù): 10000、バッチごとの挿入: 100、合計(jì)所要時(shí)間: 2598 ミリ秒
batchCnt=200 合計(jì)アイテム數(shù): 10000 、バッチごとの挿入: 200、合計(jì)所要時(shí)間: 2211 ミリ秒
batchCnt=1000 合計(jì)エントリー數(shù): 10000、挿入の各バッチ: 1000、合計(jì)所要時(shí)間: 2099 ミリ秒
batchCnt=10000 合計(jì)エントリー數(shù): 10000、各挿入バッチ: 10000、合計(jì)所要時(shí)間: 2418 ミリ秒
batchCnt=0 合計(jì)エントリ數(shù): 10000、1 回の挿入、合計(jì)所要時(shí)間: 59620 ミリ秒

一般ログの表示

batchCnt=5

batchCnt=0

いくつかの結(jié)論を?qū)Г訾工长趣扦蓼?

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

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

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

分析理由:

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

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

次のテスト oracle

import java.sql.*;

public class JdbcBatchTest {

    /**
     * @param dbType 數(shù)據(jù)庫(kù)類型，oracle或mysql
     * @param totalCnt 插入的總行數(shù)
     * @param batchCnt 每批次插入的行數(shù)，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) {
                // 批量執(zhí)行
                preparedStatement.addBatch();
                if (i % batchCnt == 0) {
                    preparedStatement.executeBatch();
                } else if (i == totalCnt) {
                    preparedStatement.executeBatch();
                }
            } else {
                // 單條執(zhí)行
                preparedStatement.executeUpdate();
            }
        }
        connection.commit();
        connection.close();
        long l2 = System.currentTimeMillis();
        System.out.println("總條數(shù)：" + totalCnt + (batchCnt>0? ("，每批插入："+batchCnt) : "，單條插入") + "，一共耗時(shí)："+ (l2-l1) + " 毫秒");
    }

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

実行時(shí)間を確認(rèn)するには、別の batchCnt 値を代入します。

batchCnt=50 合計(jì)數(shù): 10000、各バッチ挿入: 50、合計(jì)消費(fèi)時(shí)間: 2055 ミリ秒
batchCnt=100 アイテムの合計(jì)數(shù): 10000、各バッチ挿入: 100、合計(jì)消費(fèi)時(shí)間: 1324 ミリ秒
batchCnt=200 アイテムの合計(jì)數(shù): 10000、各バッチ挿入: 200、合計(jì)消費(fèi)時(shí)間: 856 ミリ秒
batchCnt=1000 エントリの合計(jì)數(shù): 10000、挿入の各バッチ: 1000、合計(jì)消費(fèi)時(shí)間: 785 ミリ秒
batchCnt=10000 エントリの合計(jì)數(shù): 10000、各バッチ挿入數(shù): 10000、合計(jì)所要時(shí)間: 804 ミリ秒
batchCnt=0 合計(jì)エントリ數(shù): 10000、1 回の挿入、合計(jì)消費(fèi)時(shí)間: 60830 ミリ秒

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

batchCnt=50 の実行時(shí)に監(jiān)査ビュー dba_audit_trail

從審計(jì)的結(jié)果中可以看到，batchCnt=50的時(shí)候，審計(jì)記錄只有200條（扣除登入和登出），也就是sql只執(zhí)行了200次。sql_text沒(méi)有發(fā)生改寫，仍然是"insert into t values (:1 , :2 , :3 , :4 , :5 )"，而且sql_bind只記錄了批量執(zhí)行的最后一個(gè)參數(shù)，即50的倍數(shù)。根據(jù)awr報(bào)告可以看出，實(shí)際只執(zhí)行了200次（由于篇幅限制，省略了awr截圖）。那么oracle是怎么做到只執(zhí)行200次但插入1萬(wàn)條記錄的呢？我們來(lái)看看oracle中使用存儲(chǔ)過(guò)程的批量插入。

四、存儲(chǔ)過(guò)程

準(zhǔn)備數(shù)據(jù)：

首先將t表清空 truncate table t;

用java往t表灌10萬(wàn)數(shù)據(jù) exec("oracle", 100000, 1000);

創(chuàng)建t1表 create table t1 as select * from t where 1 = 0;

以下兩個(gè)過(guò)程的意圖一致，均為將t表中的數(shù)據(jù)導(dǎo)入t1表。nobatch是單次執(zhí)行，usebatch是批量執(zhí)行。

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;
/

執(zhí)行上述存儲(chǔ)過(guò)程

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

存儲(chǔ)過(guò)程批量執(zhí)行效率也遠(yuǎn)遠(yuǎn)高于單條執(zhí)行。查看usebatch(50)執(zhí)行時(shí)的審計(jì)日志，sql_bind也只記錄了批量執(zhí)行的最后一個(gè)參數(shù)，即50的倍數(shù)。與使用executeBatch方法在記錄內(nèi)容方面相同。因此可以推斷，JDBC的executeBatch和存儲(chǔ)過(guò)程的批量執(zhí)行都采用了相同的方法

存儲(chǔ)過(guò)程的這個(gè)關(guān)鍵點(diǎn)就是forall。查閱相關(guān)文檔。

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.

翻譯過(guò)來(lái)就是forall很快，原因就是不需要每次執(zhí)行的時(shí)候等待參數(shù)。

以上がJava で JDBC バッチ挿入を?qū)g裝する方法の詳細(xì)內(nèi)容です。詳細(xì)については、PHP 中國(guó)語(yǔ) Web サイトの他の関連記事を參照してください。