方法1:insert into table(field1,field2) values(value1,value2),(value3,value3)。每次提交1000条,插入10次耗时140ms,每次插入10000,插入10次,耗时818ms
这种方式是最快的,
/**
* 140ms,1w,818ms,10w
*/
public void test1(){
final String driver = "com.mysql.jdbc.Driver";
final String url = "jdbc:mysql://localhost:3306/XX?useUnicode=true&characterEncoding=UTF8";
final String user = "XXXX";
final String password = "XXXX";
Connection con = null;
PreparedStatement pstm = null;
Statement statement=null;
ResultSet rs = null;
boolean flag = false;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
StringBuilder insertSql = new StringBuilder();
insertSql.append("insert into duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");
int begin = insertSql.length();
long beginTime = System.currentTimeMillis();
for (int i = 0; i < 10; i++) {
statement=con.createStatement();
for (int j = 0; j < 10000; j++) {//每隔10000插入一次
insertSql.append(",('1','2','3','4','5')");
}
statement.execute(insertSql.toString());
con.commit();
insertSql.delete(begin,insertSql.length());
}
con.commit();
long end = System.currentTimeMillis();
System.out.println((end-beginTime));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭执行通道
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭连接通道
try {
if (con != null && (!con.isClosed())) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
方法2:每次插入execute执行一次,但是没1000条commit提交一次,1w条耗时3011。是方法1的30倍左右
/**
* 3011,1w
*/
@Test
public void test2(){
final String driver = "com.mysql.jdbc.Driver";
final String url = "jdbc:mysql://localhost:3306/kpi?useUnicode=true&characterEncoding=UTF8";
final String user = "XXX";
final String password = "XXX";
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
long beginTime = System.currentTimeMillis();
for (int i = 0; i < 10; i++) {
StringBuilder insertSql = new StringBuilder();
insertSql.append("insert into duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");
pstm=con.prepareStatement(insertSql.toString());
for (int j = 0; j < 1000; j++) {
pstm.execute();
}
con.commit();
}
con.commit();
long end = System.currentTimeMillis();
System.out.println((end-beginTime));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭执行通道
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭连接通道
try {
if (con != null && (!con.isClosed())) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
方法3,每次插入都execute和commit,插入1w条数据,耗时15秒,是方法2的5倍,方法1的150倍
/**
* 15354,1w
*
*/
@Test
public void test3(){
final String driver = "com.mysql.jdbc.Driver";
final String url = "jdbc:mysql://localhost:3306/kpi?useUnicode=true&characterEncoding=UTF8";
final String user = "root";
final String password = "135239";
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
long beginTime = System.currentTimeMillis();
for (int i = 0; i < 10; i++) {
StringBuilder insertSql = new StringBuilder();
insertSql.append("insert into duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");
pstm=con.prepareStatement(insertSql.toString());
for (int j = 0; j < 1000; j++) {
pstm.execute();
con.commit();
}
}
con.commit();
long end = System.currentTimeMillis();
System.out.println((end-beginTime));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭执行通道
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭连接通道
try {
if (con != null && (!con.isClosed())) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
方法4,使用PreparedStatement的addbatch和executeBatch方法,1w条耗时1600,是方法2的一半左右
/**
* 1w,1600
*/
@Test
public void test4(){
final String driver = "com.mysql.jdbc.Driver";
final String url = "jdbc:mysql://localhost:3306/kpi?useUnicode=true&characterEncoding=UTF8";
final String user = "root";
final String password = "135239";
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
long beginTime = System.currentTimeMillis();
StringBuilder insertSql = new StringBuilder();
insertSql.append("insert into duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");
pstm=con.prepareStatement(insertSql.toString());
for (int i = 0; i < 10; i++) {
for (int j = 0; j < 1000; j++) {
pstm.addBatch();
}
pstm.executeBatch();
con.commit();
}
con.commit();
long end = System.currentTimeMillis();
System.out.println((end-beginTime));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭执行通道
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭连接通道
try {
if (con != null && (!con.isClosed())) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
通过以上4个方法的对比,我们可以发现,execute方法和commit方法都是很耗时的方法,应该尽量减少使用他们的次数。推荐使用方法1,速度最快。方法4也可以使用,方法2,3不推荐,他们使用的execute和commit次数都过多。