Bagaimana untuk melaksanakan sisipan kelompok JDBC di Jawa

1 Penerangan

Dalam JDBC, kaedah executeBatch boleh melaksanakan berbilang penyataan dml dalam kelompok, yang jauh lebih cekap daripada melaksanakan executeUpdate secara individu Apakah prinsipnya? Bagaimana untuk melaksanakan pelaksanaan batch dalam mysql dan oracle? Artikel ini akan memperkenalkan kepada anda prinsip di sebalik ini.

2. Pengenalan eksperimen

Eksperimen ini akan dijalankan melalui tiga langkah berikut

a

b. Catatkan masa yang memakan masa untuk pelaksanaan batch dan satu pelaksanaan jdbc dalam Oracle

c. versi java dan pangkalan data yang berkaitan adalah seperti berikut: Java17, Mysql8, Oracle11G

3 Percubaan formal

Buat jadual dalam mysql dan oracle masing-masing

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

rrree

Anda perlu menghidupkan audit pangkalan data sebelum percubaan

MySQL mula mengaudit:

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

Oracle mula mengaudit:

set global general_log = 1;

kod java adalah seperti berikut:

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

A beberapa perkara yang perlu diperhatikan dalam kod,

URL mysql perlu menambah useServerPrepStmts=true&rewriteBatchedStatements=parameter benar.
batchCnt mewakili bilangan pernyataan SQL yang dilaksanakan dalam setiap kelompok, dan 0 mewakili pelaksanaan tunggal.
Ujian pertama mysql

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

Masukkan nilai batchCnt ​​yang berbeza untuk melihat masa pelaksanaan

batchCnt=50 Jumlah bilangan item : 10000, setiap kelompok Sisipan: 50, jumlah masa yang dibelanjakan: 4369 milisaat

batchCnt=100 Jumlah bilangan entri: 10000, sisipan dalam setiap kelompok: 100, jumlah masa yang dibelanjakan: 2598 milisaat

Jumlah bilangan kumpulan=2 penyertaan: 10000, sisipan dalam setiap kelompok: 200, jumlah masa yang dibelanjakan: 2211 milisaat
batchCnt=1000 Jumlah bilangan penyertaan: 10000, setiap kumpulan sisipan: 1000, jumlah masa yang dibelanjakan: 2099 milisaat
0 batch batchCnt=0 Jumlah bilangan penyertaan: 10000, sisipan tunggal, jumlah masa yang dibelanjakan: 59620 milisaat

batchCnt=5

batchCnt=0

Kita boleh membuat beberapa kesimpulan:

• Kecekapan pelaksanaan kelompok bertambah baik berbanding dengan pelaksanaan tunggal.

• Pelaksanaan kelompok mysql sebenarnya menulis semula sql dan menggabungkan berbilang sisipan ke dalam sisipan nilai xx(),()... untuk pelaksanaan.

• Apabila menukar batchCnt daripada 50 kepada 100, masa pada dasarnya dipendekkan separuh, tetapi apabila nilainya diperluaskan, pengurangan masa tidak jelas, dan masa pelaksanaan akan meningkat. tinggi.

Sebab analisis:

Selepas klien menghantar pernyataan SQL untuk dilaksanakan ke pelayan pangkalan data, pangkalan data melaksanakan pernyataan SQL dan mengembalikan hasilnya kepada pelanggan. Jumlah masa yang diambil = masa pelaksanaan pangkalan data + masa penghantaran rangkaian. Mengurangkan bilangan perjalanan pergi dan balik melalui pelaksanaan kelompok mengurangkan masa pemindahan rangkaian dan oleh itu masa keseluruhan. Walau bagaimanapun, apabila batchCnt menjadi lebih besar, walaupun masa penghantaran rangkaian tidak lagi menjadi halangan utama, pengurangan dalam jumlah masa tidak akan begitu jelas. Terutama apabila batchCnt=10000, iaitu, semua 10,000 pernyataan dilaksanakan pada satu masa, masa menjadi lebih lama Ini mungkin kerana program dan pangkalan data perlu memohon memori yang lebih besar apabila menyediakan parameter input ini, jadi ia memerlukan lebih banyak masa. Saya rasa).

Lagi satu, boleh tak nilai batchCnt tak terhingga? Sudah tentu tidak, kami tidak menganggap masalah ruang untuk membatalkan Pertama sekali, komputer anda tidak mempunyai memori yang besar untuk menyimpan semua 100 juta parameter input SQL sekaligus Kedua, mysql juga mempunyai parameter max_allowed_packet untuk dihadkan panjang satu pernyataan Maksimum ialah 1Gbyte. Apabila pernyataan terlalu panjang, "Paket untuk pertanyaan terlalu besar (1,773,901 > 1,599,488). Anda boleh menukar nilai ini pada pelayan dengan menetapkan pembolehubah 'max_allowed_packet'" akan dilaporkan.

Uji oracle seterusnya

exec("mysql", 10000, batchCnt);

Masukkan nilai batchCnt ​​yang berbeza untuk melihat masa pelaksanaan

batchCnt=50 Jumlah bilangan penyertaan: 10000, setiap kumpulan sisipan: 50, jumlah penggunaan masa: 2055 milisaat
batchCnt=100 Jumlah bilangan penyertaan: 10000, setiap sisipan kelompok: 100, jumlah penggunaan masa: 1324 milisaat
batchCnt=200 Jumlah bilangan penyertaan: 10 kumpulan000 sisipan: 200, jumlah penggunaan masa : 856 milisaat
batchCnt=1000 Jumlah bilangan entri: 10000, setiap kumpulan sisipan: 1000, jumlah masa yang dibelanjakan: 785 milisaat
batchCnt=10000: Jumlah setiap 1000 entri kumpulan sisipan: 10000, jumlah masa yang dibelanjakan: 804 Milisaat

batchCnt=0 Jumlah bilangan entri: 10000, sisipan tunggal, jumlah penggunaan masa: 60830 milisaat

Kesan pelaksanaan dalam Oracle ialah pada asasnya sama seperti dalam MySQL, dan kecekapan operasi pemprosesan kelompok jelas tinggi Dilaksanakan dalam satu baris. Masalahnya ialah tiada nilai sisipan xx(),()... sintaks dalam Oracle, jadi bagaimanakah ia mencapai pelaksanaan kelompok?

Lihat paparan audit dba_audit_trail apabila batchCnt=50 dilaksanakan

从审计的结果中可以看到，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很快，原因就是不需要每次执行的时候等待参数。

Atas ialah kandungan terperinci Bagaimana untuk melaksanakan sisipan kelompok JDBC di Jawa. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!