快捷導(dǎo)航

Java實(shí)現(xiàn)JDBC批量插入原理詳解

更新時(shí)間：2023年03月11日 08:48:03 作者：小豹子加油

在JDBC中，executeBatch這個(gè)方法可以將多條dml語(yǔ)句批量執(zhí)行，效率比單條執(zhí)行executeUpdate高很多，這是什么原理呢？在mysql和oracle中又是如何實(shí)現(xiàn)批量執(zhí)行的呢？本文將給大家介紹這背后的原理

一、說(shuō)明

在JDBC中，executeBatch這個(gè)方法可以將多條dml語(yǔ)句批量執(zhí)行，效率比單條執(zhí)行executeUpdate高很多，這是什么原理呢？在mysql和oracle中又是如何實(shí)現(xiàn)批量執(zhí)行的呢？本文將給大家介紹這背后的原理。

二、實(shí)驗(yàn)介紹

本實(shí)驗(yàn)將通過(guò)以下三步進(jìn)行

a. 記錄jdbc在mysql中批量執(zhí)行和單條執(zhí)行的耗時(shí)

b. 記錄jdbc在oracle中批量執(zhí)行和單條執(zhí)行的耗時(shí)

c. 記錄oracle plsql批量執(zhí)行和單條執(zhí)行的耗時(shí)

相關(guān)java和數(shù)據(jù)庫(kù)版本如下：Java17，Mysql8，Oracle11G

三、正式實(shí)驗(yàn)

在mysql和oracle中分別創(chuàng)建一張表

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

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

在實(shí)驗(yàn)前需要打開(kāi)數(shù)據(jù)庫(kù)的審計(jì)

mysql開(kāi)啟審計(jì)：

set global general_log = 1;

oracle開(kāi)啟審計(jì)：

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

java代碼如下：

import java.sql.*;

public class JdbcBatchTest {

    /**
     * @param dbType 數(shù)據(jù)庫(kù)類(lèi)型，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);
    }
}

代碼中幾個(gè)注意的點(diǎn)，

mysql的url需要加入useServerPrepStmts=true&rewriteBatchedStatements=true參數(shù)。
batchCnt表示每次批量執(zhí)行的sql條數(shù)，0表示單條執(zhí)行。

首先測(cè)試mysql

exec("mysql", 10000, batchCnt);

代入不同的batchCnt值看執(zhí)行時(shí)長(zhǎng)

batchCnt=50 總條數(shù)：10000，每批插入：50，一共耗時(shí)：4369 毫秒
batchCnt=100 總條數(shù)：10000，每批插入：100，一共耗時(shí)：2598 毫秒
batchCnt=200 總條數(shù)：10000，每批插入：200，一共耗時(shí)：2211 毫秒
batchCnt=1000 總條數(shù)：10000，每批插入：1000，一共耗時(shí)：2099 毫秒
batchCnt=10000 總條數(shù)：10000，每批插入：10000，一共耗時(shí)：2418 毫秒
batchCnt=0 總條數(shù)：10000，單條插入，一共耗時(shí)：59620 毫秒

查看general log

batchCnt=5

batchCnt=0

可以得出幾個(gè)結(jié)論:

批量執(zhí)行的效率相比單條執(zhí)行大大提升。
mysql的批量執(zhí)行其實(shí)是改寫(xiě)了sql，將多條insert合并成了insert xx values(),()...的方式去執(zhí)行。
將batchCnt由50改到100的時(shí)候，時(shí)間基本上縮短了一半，但是再擴(kuò)大這個(gè)值的時(shí)候，時(shí)間縮短并不明顯，執(zhí)行的時(shí)間甚至還會(huì)升高。

分析原因：

當(dāng)執(zhí)行一條sql語(yǔ)句的時(shí)候，客戶端發(fā)送sql文本到數(shù)據(jù)庫(kù)服務(wù)器，數(shù)據(jù)庫(kù)執(zhí)行sql再將結(jié)果返回給客戶端?？偤臅r(shí) = 數(shù)據(jù)庫(kù)執(zhí)行時(shí)間 + 網(wǎng)絡(luò)傳輸時(shí)間。使用批量執(zhí)行減少往返的次數(shù)，即降低了網(wǎng)絡(luò)傳輸時(shí)間，總時(shí)間因此降低。但是當(dāng)batchCnt變大，網(wǎng)絡(luò)傳輸時(shí)間并不是最主要耗時(shí)的時(shí)候，總時(shí)間降低就不會(huì)那么明顯。特別是當(dāng)batchCnt=10000，即一次性把1萬(wàn)條語(yǔ)句全部執(zhí)行完，時(shí)間反而變多了，這可能是由于程序和數(shù)據(jù)庫(kù)在準(zhǔn)備這些入?yún)r(shí)需要申請(qǐng)更大的內(nèi)存，所以耗時(shí)更多（我猜的）。

再來(lái)說(shuō)一句，batchCnt這個(gè)值是不是能無(wú)限大呢，假設(shè)我需要插入的是1億條，那么我能一次性批量插入1億條嗎？當(dāng)然不行，我們不考慮undo的空間問(wèn)題，首先你電腦就沒(méi)有這么大的內(nèi)存一次性把這1億條sql的入?yún)⑷勘４嫦聛?lái)，其次mysql還有個(gè)參數(shù)max_allowed_packet限制單條語(yǔ)句的長(zhǎng)度，最大為1G字節(jié)。當(dāng)語(yǔ)句過(guò)長(zhǎng)的時(shí)候就會(huì)報(bào)"Packet for query is too large (1,773,901 > 1,599,488). You can change this value on the server by setting the 'max_allowed_packet' variable"。

接下來(lái)測(cè)試oracle

exec("oracle", 10000, batchCnt);

代入不同的batchCnt值看執(zhí)行時(shí)長(zhǎng)

batchCnt=50 總條數(shù)：10000，每批插入：50，一共耗時(shí)：2055 毫秒
batchCnt=100 總條數(shù)：10000，每批插入：100，一共耗時(shí)：1324 毫秒
batchCnt=200 總條數(shù)：10000，每批插入：200，一共耗時(shí)：856 毫秒
batchCnt=1000 總條數(shù)：10000，每批插入：1000，一共耗時(shí)：785 毫秒
batchCnt=10000 總條數(shù)：10000，每批插入：10000，一共耗時(shí)：804 毫秒
batchCnt=0 總條數(shù)：10000，單條插入，一共耗時(shí)：60830 毫秒

可以看到oracle中執(zhí)行的效果跟mysql中基本一致，批量執(zhí)行的效率相比單條執(zhí)行都大大提升。問(wèn)題就來(lái)了，oracle中并沒(méi)有這種insert xx values(),()..語(yǔ)法呀，那它是怎么做到批量執(zhí)行的呢？

查看當(dāng)執(zhí)行batchCnt=50的審計(jì)視圖dba_audit_trail

從審計(jì)的結(jié)果中可以看到，batchCnt=50的時(shí)候，審計(jì)記錄只有200條（扣除登入和登出），也就是sql只執(zhí)行了200次。sql_text沒(méi)有發(fā)生改寫(xiě)，仍然是"insert into t values (:1 , :2 , :3 , :4 , :5 )"，而且sql_bind只記錄了批量執(zhí)行的最后一個(gè)參數(shù)，即50的倍數(shù)。從awr報(bào)告中也能看出的確是只執(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è)procudure的目的相同，都是將t表的數(shù)據(jù)灌到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ù)。跟前面jdbc使用executeBatch批量執(zhí)行時(shí)的記錄內(nèi)容一樣。由此可知jdbc的executeBatch跟存儲(chǔ)過(guò)程的批量執(zhí)行應(yīng)該是采用的同樣的方法

存儲(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ù)。