干货~多线程下1分钟完成1000万条数据插入到数据库中

环境：准备测试前，我查看了一下我公司的电脑是4核的(四个子窗口)

废话少说，直接附上代码和结果吧

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

package tenThreadInsert;    import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date;    public class MyThread extends Thread{                 public void run() {                      String url = "jdbc:mysql://127.0.0.1/teacher";                      String name = "com.mysql.jdbc.Driver";                      String user = "root";                      String password = "123456";                     Connection conn = null;                     try {                         Class.forName(name);                         conn = DriverManager.getConnection(url, user, password);//获取连接                         conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错                     } catch (ClassNotFoundException e1) {                         e1.printStackTrace();                     } catch (SQLException e) {                         e.printStackTrace();                     }                 // 开始时间                 Long begin = new Date().getTime();                 // sql前缀                 String prefix = "INSERT INTO test_teacher (t_name,t_password,sex,description,pic_url,school_name,regist_date,remark) VALUES ";                 try {                     // 保存sql后缀                     StringBuffer suffix = new StringBuffer();                     // 设置事务为非自动提交                     conn.setAutoCommit(false);                     // 比起st，pst会更好些                     PreparedStatement  pst = (PreparedStatement) conn.prepareStatement("");//准备执行语句                     // 外层循环，总提交事务次数                     for (int i = 1; i <= 10; i++) {                         suffix = new StringBuffer();                         // 第j次提交步长                         for (int j = 1; j <= 100000; j++) {                             // 构建SQL后缀                             suffix.append("('" +i*j+"','123456'"+ ",'男'"+",'教师'"+",'www.bbb.com'"+",'Java大学'"+",'"+"2016-08-16 14:43:26"+"','备注'" +"),");                         }                         // 构建完整SQL                         String sql = prefix + suffix.substring(0, suffix.length() - 1);                         // 添加执行SQL                         pst.addBatch(sql);                         // 执行操作                         pst.executeBatch();                         // 提交事务                         conn.commit();                         // 清空上一次添加的数据                         suffix = new StringBuffer();                     }                     // 头等连接                     pst.close();                     conn.close();                 } catch (SQLException e) {                     e.printStackTrace();                 }                 // 结束时间                 Long end = new Date().getTime();                 // 耗时                 System.out.println("100万条数据插入花费时间 : " + (end - begin) / 1000 + " s"+"  插入完成");     }  }  测试代码   package tenThreadInsert;    public class Test {        public static void main(String[] args) {         for (int i = 1; i <=10; i++) {               new MyThread().start();             }     }    }

　　

测试代码

1 2 3 4 5 6 7 8 9 10 11

package tenThreadInsert;    public class Test {        public static void main(String[] args) {         for (int i = 1; i <=10; i++) {               new MyThread().start();             }     }    }

　　

运行结果：

数据库的结果：

看到了吧，确实插入了1000万条，准确无误

然后我就想试试20个线程，每个线程插入50万条数据看看性能怎么样。结果如下:

 

 线程越多不是好事，反而更慢。。。

当线程达到100个，每个线程插入10万条数据直接是堆内存溢出

好了，测试就到此结束

 

标签: JAVA性能