MYSQL高并发批量更新

  • 表结构和初始化数据量

 

  • 参数配置和服务器配置

 

  • 程序

import java.sql.*;
import java.util.Date;
import java.util.Random;

public class Test7 {

    public static void main(String[] args)  throws Exception{

        for(int j=1;j<=Integer.parseInt(args[0]);j++) {

            final int tt = j;

            new Thread(){

                @Override
                public void run() {



                    try {
                        Connection con = null;
                        ResultSet rs = null;
                        PreparedStatement ps = null;
//        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
                        String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
                 
                        String CONNECTION_URL = "jdbc:mysql://192.168.7.193:3306/test?rewriteBatchedStatements=true";

                        Class.forName(JDBC_DRIVER);
                        con = DriverManager.getConnection(CONNECTION_URL, "root", "123456");

                        con.setAutoCommit(false);
                        String sql = "insert into moci values (?,?,?,?,?,?,?,?,?,?,?)";

                        System.out.println(Thread.currentThread()+":start:"+ new Date());

                        sql = "update moci3 set p1 = ?,p2=?,p3=?,p4=?,p5=?,p6=?,p7=?,p8=?,p9=?,p0=? where vin=?";
                        ps = con.prepareStatement(sql);
                        Random rand = new Random();

                        for (int i = 1 +((tt-1)*10000); i < 1 +((tt-1)*10000) + 10000; i++) {
                            long t1 = new Date().getTime();
                            ps.setDouble(1, 17);
                            ps.setDouble(2, 8);
                            ps.setDouble(3, 8);
                            ps.setDouble(4, 8);
                            ps.setDouble(5, 8);
                            ps.setDouble(6, 8);
                            ps.setDouble(7, 8);
                            ps.setDouble(8, 8);
                            ps.setDouble(9, 8);
                            ps.setDouble(10, 8);
                            ps.setString(11, String.valueOf(i));

                            ps.addBatch();

                            if (i % 999 == 0){
                                ps.executeBatch();
                                ps.clearBatch();
                            }

                      }

                        ps.executeBatch();
                        ps.clearBatch();

                        con.commit();
                        System.out.println(Thread.currentThread()+":end:"+ new Date());
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }


                }
            }.start();
        }



    }
}

  • 并发度10

  • 并发度20

  • 并发度50

  • 并发度100

[root@ecs-test ~]# java -cp .:mysql-connector-java-8.0.26.jar Test7 100
Thread[Thread-57,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-27,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-23,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-67,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-39,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-18,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-4,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-52,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-47,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-48,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-43,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-59,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-55,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-49,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-21,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-44,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-3,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-25,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-97,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-58,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-83,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-86,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-82,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-96,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-14,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-12,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-98,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-7,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-76,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-11,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-42,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-53,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-9,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-45,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-8,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-16,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-2,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-33,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-34,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-36,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-73,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-10,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-30,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-95,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-56,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-41,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-37,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-71,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-28,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-64,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-65,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-38,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-74,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-51,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-92,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-15,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-72,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-54,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-0,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-13,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-32,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-68,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-17,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-75,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-40,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-88,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-46,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-1,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-77,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-6,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-20,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-84,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-94,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-93,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-24,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-87,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-91,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-80,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-50,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-29,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-89,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-5,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-85,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-22,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-31,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-78,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-19,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-81,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-62,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-60,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-79,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-70,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-69,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-90,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-99,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-26,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-66,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-63,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-35,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-61,5,main]:start:Thu Nov 04 17:21:23 CST 2021
Thread[Thread-78,5,main]:end:Thu Nov 04 17:21:29 CST 2021
Thread[Thread-40,5,main]:end:Thu Nov 04 17:21:29 CST 2021
Thread[Thread-90,5,main]:end:Thu Nov 04 17:21:29 CST 2021
Thread[Thread-61,5,main]:end:Thu Nov 04 17:21:29 CST 2021
Thread[Thread-50,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-42,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-72,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-99,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-29,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-30,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-15,5,main]:end:Thu Nov 04 17:21:30 CST 2021
Thread[Thread-27,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-69,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-60,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-63,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-89,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-67,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-98,5,main]:end:Thu Nov 04 17:21:31 CST 2021
Thread[Thread-80,5,main]:end:Thu Nov 04 17:21:32 CST 2021
Thread[Thread-20,5,main]:end:Thu Nov 04 17:21:32 CST 2021
Thread[Thread-79,5,main]:end:Thu Nov 04 17:21:32 CST 2021
Thread[Thread-39,5,main]:end:Thu Nov 04 17:21:32 CST 2021
Thread[Thread-82,5,main]:end:Thu Nov 04 17:21:32 CST 2021
Thread[Thread-14,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-75,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-10,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-68,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-25,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-88,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-37,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-11,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-91,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-70,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-32,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-41,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-83,5,main]:end:Thu Nov 04 17:21:33 CST 2021
Thread[Thread-97,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-87,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-66,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-35,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-31,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-33,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-13,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-34,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-38,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-18,5,main]:end:Thu Nov 04 17:21:34 CST 2021
Thread[Thread-12,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-19,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-16,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-51,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-24,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-71,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-81,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-62,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-23,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-36,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-55,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-43,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-28,5,main]:end:Thu Nov 04 17:21:35 CST 2021
Thread[Thread-17,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-96,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-73,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-26,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-85,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-92,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-86,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-21,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-52,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-49,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-57,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-58,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-64,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-59,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-84,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-74,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-44,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-76,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-53,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-94,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-77,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-47,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-22,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-65,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-46,5,main]:end:Thu Nov 04 17:21:36 CST 2021
Thread[Thread-56,5,main]:end:Thu Nov 04 17:21:37 CST 2021
Thread[Thread-54,5,main]:end:Thu Nov 04 17:21:37 CST 2021
Thread[Thread-45,5,main]:end:Thu Nov 04 17:21:37 CST 2021
Thread[Thread-48,5,main]:end:Thu Nov 04 17:21:37 CST 2021
Thread[Thread-93,5,main]:end:Thu Nov 04 17:21:37 CST 2021
Thread[Thread-95,5,main]:end:Thu Nov 04 17:21:37 CST 2021
Thread[Thread-4,5,main]:end:Thu Nov 04 17:21:39 CST 2021
Thread[Thread-9,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-1,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-8,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-5,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-2,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-3,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-7,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-6,5,main]:end:Thu Nov 04 17:21:40 CST 2021
Thread[Thread-0,5,main]:end:Thu Nov 04 17:21:40 CST 2021
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lilei2blog/p/15509208.html