MySQL之JDBC批量操作

   blog迁移至 :http://www.micmiu.com

 

         最近项目中需要周期性解析文本数据并把相关数据插入到MySQL数据库中，文本数据大概每小时12个，每个文件大概200M，解析后每个文件的入库数据量大概在30W左右,1小时大概360W数据。

 

         为了提高大量数据的入库效率于是想到了批处理，可是在实际测试过程中发现用批量操作后入库的效率并没有什么明显的提高，经搜索相关资料和自己的实际测试得出以下一些结论，以供大家参考。

 

一、基本结论

 

    1. 满足相应的前提条件后（见后面的详细描述 ），mysql执行批量插入大量数据的效率还是非常高的。

 

    2. 是否支持批量插入数据和表自身是否支持事务没有必要的关联关系，不过从测试结果看非事务型（MyISAM）比事务型（InnoDB）效率要高点

 

MySQL实现批量插入数据的前提条件：

• 需要在url中添加参数rewriteBatchedStatements=true 比如：jdbc:mysql://192.168.8.150:3306/demo?rewriteBatchedStatements=true
• MySQL的版本需要3.1.13+ 才支持参数rewriteBatchedStatements （目前基本用的都是5.0+，本人测试用的是5.0.77）
• 需要MySQL的JDBC的驱动支持 （本人测试过mysql-connector-java-5.1.13、mysql-connector-java-5.1.16、mysql-connector-java-5.1.17均支持）

二、测试数据对比

 

测试插入10W条数据的耗时图如下：

测试对比	批量插入(ms)	普通插入(ms)
参数true	参数false	参数true	参数false
MyISAM	1633	36775	34913	43610
InnoDB	2374	42427	43531	34678

ps：

1.上表格中的参数是指URL中的 rewriteBatchedStatements

2.参数rewriteBatchedStatements 的值对普通插入是没有影响的，该参数只在批操作时有效

3.测试时同样的操作耗时有浮动的，普通操作大概在36-45秒之间（这里是贴出只是某一次的完成测试结果），

   本次测试的重点是关注批量插入和普通插入的效率，从上图可以明显得出结论。

 

 

从上面的数据对比图中可见：增加参数后批量插入数据的效率明显提高了很多。

 

三、测试代码如下：

 

测试建表语句：

 

CREATE TABLE `TB_TEST` (
  `ID` int(11) NOT NULL auto_increment,
  `NAME` varchar(20) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

java代码：JdbcInsert4MysqlDemo.java

package michael.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @blog http://sjsky.iteye.com
 * @author Michael
 */
public class JdbcInsert4MysqlDemo {

    // 批量操作参数 rewriteBatchedStatements=true
    private static String url = "jdbc:mysql://192.168.8.150:3306/demo?rewriteBatchedStatements=true";
    private static String user = "root";
    private static String password = "123456";
    private static String driver = "com.mysql.jdbc.Driver";

    /**
     * @param args
     */
    public static void main(String[] args) {

        JdbcInsert4MysqlDemo handler = new JdbcInsert4MysqlDemo();

        // handler.testMysqlInsert();

        handler.testMysqlBatchInsert();

    }

    /**
     * testMysqlBatchInsert
     */
    public void testMysqlBatchInsert() {
        long startTime = System.currentTimeMillis();

        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "insert into TB_TEST(NAME)values(?)";
        int batchNum = 1000;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
            for (int i = 1; i <= 100000; i++) {
                pstmt.setString(1, "test_" + i);
                pstmt.addBatch();
                if (i % batchNum == 0) {
                    pstmt.executeBatch();
                    conn.commit();
                }
            }
            pstmt.executeBatch();
            conn.commit();

            System.out.println("mysql 批量插入数据：100000 用时(/ms)："
                    + (System.currentTimeMillis() - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != pstmt) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * testMysqlInsert
     */
    public void testMysqlInsert() {
        long startTime = System.currentTimeMillis();

        Connection conn = null;

        PreparedStatement pstmt = null;
        String sql = "insert into TB_TEST(NAME)values(?)";
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);

            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
            for (int i = 1; i <= 100000; i++) {
                pstmt.setString(1, "test_" + i);
                pstmt.execute();
            }
            conn.commit();
            System.out.println("mysql 普通插入数据：100000 用时(/ms)："
                    + (System.currentTimeMillis() - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != pstmt) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}

 

 

转载请注明来自：Michael's blog @ http://sjsky.iteye.com

 

-----------------------------------分 ------------------------------------隔 ------------------------------------线 --------------------------------------

 

评论

7 楼 twoidiots 2011-12-09  

要是有博主参考的大牛的文章链接就更好了~ 不过还是很感谢，很方便的学习了~

6 楼 sjx19871109 2011-08-30  

很好~顶一个~！

5 楼 sjsky 2011-07-12  

zhuchengzzcc 写道

•MySQL的版本需要3.1.13+ 才支持参数rewriteBatchedStatements （目前基本用的都是5.0+，本人测试用的是5.0.77）

3.1.13+ 笔误了

没有明白这么仁兄的意思

4 楼 zhuchengzzcc 2011-07-12  

•MySQL的版本需要3.1.13+ 才支持参数rewriteBatchedStatements （目前基本用的都是5.0+，本人测试用的是5.0.77）

3.1.13+ 笔误了

3 楼 ycj1013321932 2011-07-09  

很好，刚才这样批量保存发愁呢，非常感谢

2 楼 sjsky 2011-07-08  

sunxiaofeng1011 写道

GOOD!

多谢支持，很多东西都是一些大牛们发现的，本人也就实际测试调优+自我小结而已

1 楼 sunxiaofeng1011 2011-07-08  

GOOD!