对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)方法。