最近项目中需要周期性解析文本数据并把相关数据插入到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均支持）
二、测试数据对比
| 测试对比 | 批量插入(ms) | 普通插入(ms) | ||
| 参数true | 参数false | 参数true | 参数false | |
| MyISAM | 1633 | 36775 | 34913 | 43610 |
| InnoDB | 2374 | 42427 | 43531 | 34678 |
Tips：
1.上表格中的参数true/false是指URL中的 rewriteBatchedStatements
2.参数rewriteBatchedStatements的值对普通插入是没有影响的，该参数只在批操作时有效
3.测试时同样的操作耗时有浮动的，普通操作大概在36-45秒之间（这里是贴出只是某一次的完成测试结果），本次测试的重点是关注批量插入和普通插入的效率，从上图可以明显得出结论。
从上面的数据对比图中可见：增加参数后批量插入数据的效率明显提高了很多。
三、测试代码如下：
测试用的建表SQL语句：
|
1 2 3 4 5 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 |
package michael.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * @blog http://www.micmiu.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(); } } } } |
原创文章，转载请注明： 转载自micmiu – 软件开发+生活点滴[ http://www.micmiu.com/ ]
本文链接地址: http://www.micmiu.com/j2ee/jdbc-tech/mysql-jdbc-batch/
0 条评论。