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.
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, Oracle11G3. dans mysql et oracle respectivement
create table t ( -- mysql中创建表的语句
id int,
name1 varchar(100),
name2 varchar(100),
name3 varchar(100),
name4 varchar(100)
);create table t ( -- oracle中创建表的语句
id number,
name1 varchar2(100),
name2 varchar2(100),
name3 varchar2(100),
name4 varchar2(100)
);set global general_log = 1;
alter system set audit_trail=db, extended; audit insert table by scott; -- 实验采用scott用户批量执行insert的方式
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);
}
}# 🎜🎜 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);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 millisecondesbatchCnt= 10000 Nombre total d'éléments : 10000, chaque insertion par lot : 10000, consommation de temps totale : 2418 millisecondesbatchCnt=0 Nombre total d'éléments : 10000, insertion unique, consommation de temps totale : 59620 millisecondes
batchCnt=0
# 🎜🎜#Voir le journal général
batchCnt=5
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.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 grâce à 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).
exec("oracle", 10000, batchCnt);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 millisecondesbatchCnt =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é从审计的结果中可以看到，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; /Copier après la connexioncreate 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; /Copier après la connexion执行上述存储过程
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很快，原因就是不需要每次执行的时候等待参数。
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!
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
