如何在毫秒内将数千条记录更新到mysql数据库

9udxz4iz 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(293)

我想在不到一秒钟的时间内将大约10k条记录更新到mysql数据库中。我已经写了下面的代码，大约需要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();
                }
            }   
       }

请让我知道您的意见，以优化此代码的性能改进。

Java mysql jdbc

来源：https://stackoverflow.com/questions/50814572/how-do-i-update-thousands-of-records-into-mysql-db-in-milliseconds

3条答案

按热度按时间

ou6hu8tu1#

您可以使用批处理将插入操作批处理到临时表中，而不是批处理单个更新 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);

赞(0）回复(0）举报 2021-06-21

a11xaf1n2#

首先，你需要初始化 prepareStatement 只有一次，你需要在 for 环
第二，你应该避免执行死刑 pstm.executeBatch(); 对于每个循环，它将花费更多的资源，您需要执行指定的数量，如100500或更多，也不要在外部执行它 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);

赞(0）回复(0）举报 2021-06-21

ibps3vxo3#

你的 pstm.executeBatch() 应该在之后 for 环
请参阅如何将列表插入数据库

赞(0）回复(0）举报 2021-06-21