我希望在不到一秒的时间内将大约10K条记录更新到MySQL DB中。我已经写了下面的代码,这需要大约6-8秒来更新到数据库的记录列表。
public void updateResultList(List<?> list) {
String user = "root";
String pass = "root";
String jdbcUrl = "jdbc:mysql://12.1.1.1/db_1?useSSL=false";
String driver = "com.mysql.jdbc.Driver";
PreparedStatement pstm = null;
try {
Class.forName(driver);
Connection myConn = DriverManager.getConnection(jdbcUrl, user, pass);
myConn.setAutoCommit(false);
for(int i=0; i<list.size(); i++) {
Object[] row = (Object[]) list.get(i);
int candidateID = Integer.valueOf(String.valueOf(row[0]));
String result = String.valueOf(row[14]);
int score = Integer.valueOf(String.valueOf(row[19]));
String uploadState = (String) row[20];
String sql = "UPDATE personal_info SET result = ?, score = ?, uploadState = ? "
+ " WHERE CandidateID = ?";
pstm = (PreparedStatement) myConn.prepareStatement(sql);
pstm.setString(1, result);
pstm.setInt(2, score);
pstm.setString(3, uploadState);
pstm.setInt(4, candidateID);
pstm.addBatch();
pstm.executeBatch();
}
myConn.commit();
myConn.setAutoCommit(true);
pstm.close();
myConn.close();
}
catch (Exception exc) {
exc.printStackTrace();
try {
throw new ServletException(exc);
} catch (ServletException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
请让我知道你的意见,以优化这段代码,以提高性能。
发布于 2018-06-12 22:28:04
您可以使用rewriteBatchedStatements=true
对临时表进行批量插入,然后使用单个UPDATE语句更新主表,而不是对单个更新进行批处理。在我的机器上有一个本地MySQL实例,下面的代码大约需要2.5秒...
long t0 = System.nanoTime();
conn.setAutoCommit(false);
String sql = null;
sql = "UPDATE personal_info SET result=?, score=?, uploadState=? WHERE CandidateID=?";
PreparedStatement ps = conn.prepareStatement(sql);
String tag = "X";
for (int i = 1; i <= 10000; i++) {
ps.setString(1, String.format("result_%s_%d", tag, i));
ps.setInt(2, 200000 + i);
ps.setString(3, String.format("state_%s_%d", tag, i));
ps.setInt(4, i);
ps.addBatch();
}
ps.executeBatch();
conn.commit();
System.out.printf("%d ms%n", (System.nanoTime() - t0) / 1000000);
..。而这个版本大约需要1.3秒:
long t0 = System.nanoTime();
conn.setAutoCommit(false);
String sql = null;
Statement st = conn.createStatement();
st.execute("CREATE TEMPORARY TABLE tmp (CandidateID INT, result VARCHAR(255), score INT, uploadState VARCHAR(255))");
sql = "INSERT INTO tmp (result, score, uploadState, CandidateID) VALUES (?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
String tag = "Y";
for (int i = 1; i <= 10000; i++) {
ps.setString(1, String.format("result_%s_%d", tag, i));
ps.setInt(2, 400000 + i);
ps.setString(3, String.format("state_%s_%d", tag, i));
ps.setInt(4, i);
ps.addBatch();
}
ps.executeBatch();
sql =
"UPDATE personal_info pi INNER JOIN tmp ON tmp.CandidateID=pi.CandidateID "
+ "SET pi.result=tmp.result, pi.score=tmp.score, pi.uploadState=tmp.uploadState";
st.execute(sql);
conn.commit();
System.out.printf("%d ms%n", (System.nanoTime() - t0) / 1000000);
发布于 2018-06-12 19:40:53
首先,您只需要初始化一次prepareStatement
,需要在for
循环之前初始化它
其次,你应该避免每次循环执行pstm.executeBatch();
,它会消耗更多的资源,你需要执行指定的数量,比如100,500或更多,也不要只在for
循环外执行一次,因为它会消耗更多的内存资源
Class.forName(driver);
Connection myConn = DriverManager.getConnection(jdbcUrl, user, pass);
myConn.setAutoCommit(false);
String sql = "UPDATE personal_info SET result = ?, score = ?, uploadState = ? "
+ " WHERE CandidateID = ?";
pstm = (PreparedStatement) myConn.prepareStatement(sql);
for(int i=0; i<list.size(); i++) {
Object[] row = (Object[]) list.get(i);
int candidateID = Integer.valueOf(String.valueOf(row[0]));
String result = String.valueOf(row[14]);
int score = Integer.valueOf(String.valueOf(row[19]));
String uploadState = (String) row[20];
pstm.setString(1, result);
pstm.setInt(2, score);
pstm.setString(3, uploadState);
pstm.setInt(4, candidateID);
pstm.addBatch();
if(i%500==0){//execute when it meet a specified amount
pstm.executeBatch();
}
}
pstm.executeBatch();
myConn.commit();
myConn.setAutoCommit(true);
发布于 2018-06-12 18:23:01
您的pstm.executeBatch()
应该在for
循环之后
https://stackoverflow.com/questions/50814572
复制相似问题