1 Description

Dans JDBC, la méthodeexecuteBatch peut exécuter plusieurs instructions DML par lots, ce qui est plus efficace que une seule instruction L'exécution de executeUpdate est beaucoup plus élevée. Quel est le principe ? Comment implémenter l'exécution par lots dans MySQL et Oracle ? Cet article vous présentera le principe derrière cela.

2. Introduction à l'expérience

Cette expérience se déroulera à travers les trois étapes suivantes

a. exécution unique de jdbc dans mysql Time

b. Enregistrez le temps nécessaire à l'exécution par lots et à l'exécution unique de jdbc dans Oracle

c. exécution d'Oracle plsql

# ????#Les versions pertinentes de Java et de la base de données sont les suivantes?: Java17, Mysql8, Oracle11G

3. dans mysql et oracle respectivement

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

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

Vous devez activer l'audit de la base de données avant l'expérience

mysql active l'audit?:

set global general_log = 1;

oracle active l'audit?:

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

le code java est le suivant?:

import java.sql.*;

public class JdbcBatchTest {

    /**
     * @param dbType 數(shù)據(jù)庫類型，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) : "，單條插入") + "，一共耗時："+ (l2-l1) + " 毫秒");
    }

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

Quelques points à noter dans le code,

# ???? L'URL #mysql doit ajouter des paramètres useServerPrepStmts=true&rewriteBatchedStatements=true.

• batchCnt représente le nombre d'instructions SQL exécutées dans chaque lot et 0 représente une exécution unique.

• Premier test de mysql

  exec("mysql", 10000, batchCnt);

Mettez différentes valeurs batchCnt pour voir le temps d'exécution

#?? ??#batchCnt= 50 Nombre total d'éléments : 10000, insertion par lot : 50, consommation de temps totale : 4369 millisecondes

batchCnt=100 Nombre total d'éléments : 10000, insertion par lot : 100, consommation de temps totale : 2598 millisecondes

batchCnt= 200 Nombre total d'éléments : 10000, chaque lot d'insertions : 200, temps total passé : 2211 millisecondes

batchCnt=1000 Nombre total d'éléments : 10000, chaque lot d'insertions : 1000, temps total dépensé : 2099 millisecondes

batchCnt= 10000 Nombre total d'éléments : 10000, chaque insertion par lot : 10000, consommation de temps totale : 2418 millisecondes

batchCnt=0 Nombre total d'éléments : 10000, insertion unique, consommation de temps totale : 59620 millisecondes

# ????#Voir le journal général

batchCnt=5

batchCnt=0

Plusieurs conclusions peut être dessiné?:

#???? #

L'efficacité de l'exécution par lots est grandement améliorée par rapport à l'exécution unique.

L'exécution par lots de MySQL réécrit en fait SQL et fusionne plusieurs insertions dans les valeurs d'insertion xx(),()... pour l'exécution.

• Lors du changement de batchCnt de 50 à 100, le temps est fondamentalement réduit de moitié, mais lorsque la valeur est augmentée, la réduction du temps n'est pas évidente. Le temps d'exécution. peut même augmenter.

• Raison de l'analyse?:

• Après que le client ait envoyé l'instruction SQL à exécuter au serveur de base de données, la base de données exécute le Instruction SQL et renvoyer les résultats au client. Temps total pris = temps d'exécution de la base de données + temps de transmission réseau. La réduction du nombre d’allers-retours grace à l’exécution par lots réduit le temps de transfert réseau et donc le temps global. Cependant, lorsque batchCnt devient plus important, même si le temps de transmission sur le réseau n'est plus le principal goulot d'étranglement, la réduction du temps total ne sera pas aussi évidente. Surtout lorsque batchCnt=10 000, c'est-à-dire que les 10 000 instructions sont exécutées en même temps, le temps devient plus long. Cela peut être d? au fait que le programme et la base de données doivent demander plus de mémoire lors de la préparation de ces paramètres d'entrée, ce qui prend donc plus de temps (. Je suppose).

Encore une chose, la valeur de batchCnt peut-elle être infinie?? Supposons que je doive insérer 100 millions d'éléments, puis-je insérer par lots 100 millions d'éléments à la fois?? Bien s?r que non, nous ne prenons pas en compte le problème d'espace de l'annulation. Premièrement, votre ordinateur ne dispose pas d'une mémoire aussi grande pour sauvegarder tous les 100 millions de paramètres d'entrée SQL à la fois. Deuxièmement, MySQL a également un paramètre max_allowed_packet à limiter. la longueur d’une seule instruction. Le maximum est de 1 Go. Lorsque l'instruction est trop longue, "Le paquet pour la requête est trop volumineux (1 773 901 > 1 599 488). Vous pouvez modifier cette valeur sur le serveur en définissant la variable 'max_allowed_packet'" sera signalé.

Prochain testez l'oracle

exec("oracle", 10000, batchCnt);

Mettez différentes valeurs batchCnt pour voir le temps d'exécution

batchCnt=50 Nombre total d'entrées : 10000, Insérez chaque lot : 50, temps total passé : 2055 millisecondes

batchCnt=100 Nombre total d'éléments : 10000, insérez chaque lot : 100, temps total passé : 1324 millisecondes

batchCnt=200 Nombre total d'articles : 10000, Insertion par lot : 200, consommation de temps totale : 856 millisecondes

batchCnt=1000 Nombre total d'entrées : 10000, insertion par lot : 1000, consommation de temps totale : 785 millisecondes

batchCnt =10000 Nombre total d'entrées : 10000, Chaque lot d'insertions : 10000, temps total passé : 804 millisecondes

batchCnt=0 Nombre total d'éléments : 10000, insertion unique, temps total passé : 60830 millisecondes

# ????#

Exécuté dans Oracle L'effet est fondamentalement le même que celui de MySQL, et l'efficacité des opérations de traitement par lots est nettement supérieure à celle d'une exécution unique. Le problème est qu'il n'existe pas de syntaxe d'insertion de valeurs xx(),()... dans Oracle, alors comment réalise-t-il l'exécution par lots ?

Afficher la vue d'audit dba_audit_trail
lorsque batchCnt=50 est exécuté

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

四、存儲過程

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

首先將t表清空 truncate table t;

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

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

以下兩個過程的意圖一致，均為將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í)行上述存儲過程

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

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

存儲過程的這個關(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.

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!