平时使用mysql插入、查询数据都没有注意过效率,今天在for循环中使用JDBC插入1000条数据居然等待了一会儿
就来探索一下JDBC的批量插入语句对效率的提高
create table `user1`( `id` int primary key auto_increment, `phoneNumber` int not null , `indentity` int not null , `address` varchar(100), index (id,phoneNumber,indentity) )engine =innoDB default charset = utf8mb4;
使用的是MySql8.0
Class.forName("com.mysql.cj.jdbc.Driver"); //不需要建立 SSL 连接,需要显示关闭。 //allowPublicKeyRetrieval=true 允许客户端从服务器获取公钥。 //时区为UTC String dburl = "jdbc:mysql://localhost:3306/myTest?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"; String username = "root"; String password = "123456"; Connection connection = DriverManager.getConnection(dburl,username,password);
String sql = "insert into `user1`(`phoneNumber`, `indentity`, `address`) values (?,?,?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); Date date = new Date(); System.out.println(date.toString()); for (int i = 1000; i < 2000; i++) { preparedStatement.setInt(1,i+2); preparedStatement.setInt(2,i+101); preparedStatement.setString(3,"dizhi"); preparedStatement.addBatch(); } preparedStatement.executeBatch();
运行结果显示出,插入1000条数据只用了16秒
Date date = new Date(); System.out.println(date.toString()); for (int i = 4000; i < 5000; i++) { preparedStatement.setInt(1,i+2); preparedStatement.setInt(2,i+101); preparedStatement.setString(3,"dizhi"); preparedStatement.execute(); } Date date1 = new Date(); System.out.println(date1.toString());
运行结果显示用了17秒,用for循环插入和批量插入用的时间差距并不大的,这明显有问题,所以进行第二次批量插入查询
Date date = new Date(); System.out.println(date.toString()); // for (int i = 3000; i < 4000; i++) { preparedStatement.setInt(1,i+2); preparedStatement.setInt(2,i+101); preparedStatement.setString(3,"dizhi"); preparedStatement.addBatch(); } Date date1 = new Date(); System.out.println(date1.toString());
结果很神奇,第二次批量插入立即完成,完全没有延迟
具体原因还需要多多百度,我猜测是MySQL缓存的作用,省去了很多交互过程,减少了网络传输
现在距离第二次已经过去了很长时间,这次测试是想看看MySQL缓存还在不在
Date date = new Date(); System.out.println(date.toString()); for (int i = 5000; i < 6000; i++) { preparedStatement.setInt(1,i+2); preparedStatement.setInt(2,i+101); preparedStatement.setString(3,"dizhi"); preparedStatement.addBatch(); } preparedStatement.executeBatch(); Date date1 = new Date(); System.out.println(date1.toString());
显然,又是16秒,看来是时间过去太久了
总的来说,MySQL是真个神奇的玩意