对mysql数据库批量插入数据的速度测试

    今天，对mysql数据库的插入数据的速度做了个简单的测试。
   JdbcUtil.java

  package utils; import java.sql.*; public class JdbcUtil { private final static String DB_DRIVER = "com.mysql.jdbc.Driver"; private final static String DB_CONNECTION = "jdbc:mysql://localhost:3306/"; private final static String DB_NAME = "root"; private final static String DB_PWd = "root"; static { try { Class.forName(DB_DRIVER); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() { Connection conn = null; try { conn = DriverManager .getConnection(DB_CONNECTION, DB_NAME, DB_PWd); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static Statement getStatement(Connection conn) { Statement stmt = null; try { stmt = conn.createStatement(); } catch (SQLException e) { e.printStackTrace(); } return stmt; } public static PreparedStatement getPreparedStatement(Connection conn, String sql) { PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql); } catch (SQLException e) { e.printStackTrace(); } return pstmt; } public static ResultSet getRs(Statement stmt, String sql) { ResultSet rs = null; try { rs = stmt.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); } return rs; } public static void close(Statement stmt) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

   SqlTest.java

 package test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import utils.JdbcUtil; public class SqlTest { private final String DBNAME = "test"; private final String TABLENAME = "mytest"; public void createTable(){ //String sql_select_db = "use "+ DBNAME; String sql_drop = "drop table if exists " + DBNAME + "." + TABLENAME; String sql_create = "create table "+ DBNAME + "." + TABLENAME+"(id varchar(10) primary key,name varchar(10))"; Connection conn = JdbcUtil.getConnection(); Statement stmt = JdbcUtil.getStatement(conn); try { //System.out.println(stmt.execute(sql_select_db)); stmt.execute(sql_drop); stmt.execute(sql_create); } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(conn); JdbcUtil.close(stmt); } } public void add(){ String sql_add = "insert into " + DBNAME + "."+ TABLENAME + " values(?,?)"; Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = JdbcUtil.getPreparedStatement(conn, sql_add); try { pstmt.setString(1, "1006"); pstmt.setString(2, "q1006"); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(conn); JdbcUtil.close(pstmt); } } public void addBatchTest(){ String sql_add = "insert into "+ DBNAME + "." +TABLENAME + " values(?,?)"; Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = JdbcUtil.getPreparedStatement(conn, sql_add); long startTime = System.currentTimeMillis(); try { int count = 0; for(int i = 0; i < 100000; i++){ pstmt.setString(1, i+""); pstmt.setString(2, "q"+ i); conn.setAutoCommit(false); //若改为true 插入中遇到主键冲突还会继续插入，具体看需求 pstmt.addBatch(); count ++; if(count >= 10000){ count = 0; pstmt.executeBatch(); conn.commit(); } } pstmt.executeBatch(); conn.commit(); System.out.println( (System.currentTimeMillis() - startTime) + "ms"); } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtil.close(conn); JdbcUtil.close(pstmt); } } /** * @param args */ public static void main(String[] args) { SqlTest sqlTest = new SqlTest(); sqlTest.createTable(); //sqlTest.add(); sqlTest.addBatchTest(); } }

背景：
   1、mysql数据库
   2、表结构为
      id varchar(10)
      name varchar(10)
   3、采用非自动提交的PrepareStatement批处理

   测试结果：
   数据量（条）   插入所需时间（ms）
     1                 0
     10                15
     100               62
     1,000             422
     10,000            2,922
     100,000           26,922
     1000,000          272,219

测试过程学习到的：
     1、批处理要conn.setAutoCommit(false)（默认会自动提交，不能达到批处理的目的，速度极慢！）。
     2、pstmt.executeBatch();
          conn.commit();
        要提交，数据库才会有数据。
     3、避免内存溢出，应每x（如：万）条提交一次数据。
     4、可用“数据库名.表名”的方法来访问数据库表
        这样，则在写JdbcUtil的DB_CONNECTION = "jdbc:mysql://localhost:3306/"时，
        可不具体到数据库，方便跨数据库的数据操作。
     5、Statement和PrepareStatement不仅可以操作DDL，添加删除表和数据库的sql都可以操作，用execute(String sql)方法。