JDBC에서는 여러 개의 dml 문을 일괄적으로 실행할 수 있는 메소드가 있는데, 개별적으로 실행하는 것보다 효율성이 훨씬 높습니다. mysql과 oracle에서 일괄 실행을 구현하는 방법은 무엇입니까? 이 기사에서는 이에 대한 원리를 소개합니다.
이 실험은 다음 세 단계를 통해 진행됩니다
a. mysql에서 jdbc의 일괄 실행에 소요되는 시간을 기록합니다
b. 그리고 oracle
c에서 jdbc의 단일 실행을 기록합니다. oracle plsql 일괄 실행 및 단일 실행에 소요되는 시간을 기록합니다.
관련 Java 및 데이터베이스 버전은 다음과 같습니다: Java17, Mysql8, Oracle11G
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)
);실험 전에 데이터베이스 감사를 설정해야 합니다.
mysql이 감사를 설정합니다.
set global general_log = 1;
oracle이 감사를 설정합니다.
alter system set audit_trail=db, extended; audit insert table by scott; -- 实验采用scott用户批量执行insert的方式
java 코드는 다음과 같습니다.
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);
}
}코드에서 주의할 몇 가지 사항은
exec("mysql", 10000, batchCnt);batchCnt= 100 총 항목 수: 10000, 각 삽입 배치: 100, 총 소요 시간: 2598밀리초batchCnt=200 총 항목 수: 10000, 각 삽입 배치: 200, 총 소요 시간: 2211밀리초
일반 로그 보기
batchCnt=1000 합계 항목 수: 10000, 각 배치 삽입: 1000, 총 소요 시간: 2099밀리초
batchCnt=10000 총 항목 수: 10000, 각 배치 삽입: 10000, 총 소요 시간: 2418밀리초
batchCnt=0 총 항목 수 : 10000, 단일 삽입, 총 소요 시간: 59620밀리초
batchCnt=5
batchCnt=0
몇 가지 결론을 내릴 수 있습니다.
한 가지 더, 1억 개의 항목을 삽입해야 한다고 가정하면 한 번에 1억 개의 항목을 일괄 삽입할 수 있나요? 물론 아닙니다. 실행 취소의 공간 문제는 고려하지 않습니다. 우선 컴퓨터에는 1억 개의 SQL 입력 매개변수를 모두 한 번에 저장할 만큼 큰 메모리가 없습니다. 두 번째로 mysql에는 제한할 매개변수 max_allowed_packet이 있습니다. 단일 문의 길이는 최대 1GB입니다. 문이 너무 길면 "쿼리용 패킷이 너무 큽니다(1,773,901 > 1,599,488). 'max_allowed_packet' 변수를 설정하여 서버에서 이 값을 변경할 수 있습니다."라는 메시지가 보고됩니다.
다음으로 oracle을 테스트
exec("oracle", 10000, batchCnt);batchCnt=100 총 항목 수: 10000, 각 삽입 배치: 100, 총 소비 시간: 1324밀리초batchCnt=200 총 항목 수: 10000, 각 삽입 배치: 200, 총 소비 시간: 856밀리초
Oracle에서 실행 효과는 기본적으로 MySQL과 동일하며 일괄 작업의 효율성은 단일 실행보다 훨씬 높습니다. 문제는 Oracle에 xx 값 삽입(),()... 구문이 없다는 것입니다. 그러면 일괄 실행을 어떻게 달성합니까?
batchCnt=1000 총 개수 항목 수: 10000, 각 배치 삽입: 1000, 총 소요 시간: 785밀리초
batchCnt=10000 총 항목 수: 10000, 각 배치 삽입: 10000, 총 소요 시간: 804밀리초
batchCnt=0 총 항목 수: 10000, 단일 삽입, 총 소요 시간: 60830밀리초
batchCnt=50이 실행될 때 감사 뷰 dba_audit_trail 보기
从审计的结果中可以看到，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很快，原因就是不需要每次执行的时候等待参数。
위 내용은 Java에서 JDBC 일괄 삽입을 구현하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
