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
-----------------------------------分
------------------------------------隔
------------------------------------线
--------------------------------------
分享到:
相关推荐
Mybatis与JDBC批量插入MySQL数据库性能测试,资源包含文档、代码和数据库。
针对oracle中blob字段的操作,能批量快速的插入大字段,效率非常高
用JSP+SERVLET实现的简单的数据库增删改查。本方法适用小型项目。SQL语句和代码没有分离。不方便数据库工程师维护项目。不能有效防止SQL注入。
主要介绍了Java实现批量向mysql写入数据的方法,涉及java基于JDBC连接mysql数据库及写入数据的相关操作技巧,非常简单实用,需要的朋友可以参考下
JDBC调用MySQL5存储过程[文].pdf
mysq增删改查,jdbc 驱动加载 各个类和接口详解,与ibatis对比。批量查询,分页处理。
spring jdbc Templatetest 访问mysql数据库,批量插入数据
主要介绍了JDBC连接MySQL数据库批量插入数据过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
基于jsp+js+servlet+jdbc+mysql开发产品后台管理系统,实现用户增删改查、分页,登录,注册,图片上传等功能。 包括以下5个功能: 1、登录 用户默认主页index.jsp,可选择【登录】功能,若登录成功,则进入产品管理中...
第十二章 JDBC批处理操作批量处理允许您将相关的SQL语句分组到批处理中,并通过对数据库的一次调用提交它们。当您一次向数据库发送多个SQL语句时,可以减少连接
该资源主要实现如下功能,利用poi获取excel中大量数据,然后利用jdbc批量插入到mysql
最近在深入学习hibernate,在进行批量操作时,发现hibernate批量操作性能非常低.于是就想找一个性能较高的方法,在对jdbc、jdbcTemplate、hibernate进行测试后,发现jdbc的执行效率是最高的,jdbcTemplate也很相近,...
利用poi获取excel中大量数据,然后利用jdbc批量插入到mysql,直接运行main方法
主要介绍了使用JDBC在MySQL数据库中如何快速批量插入数据,可以有效的解决一次插入大数据的方法,
本实例采用c3p0作为线程池工具包,讲解了jdbc基本用法,同时给出了Oracle以及mysql增(单插入、批量插入)、删、查、改等功能,可以直接复制使用。
kivikbasetest MySQL 数据库的批量测试数据生成器
使用JDBC连接MySQL数据库进行数据插入的时候,特别是大批量数据连续插入(100000),如何提高效率呢?今天小编通过本教程给大家介绍下
该资源中包含 JDBC 连接 MySQL 完整代码、常规 select、update 语句完整代码,以及 JDBC 批量处理数据的几种不同方式完整代码,且附带 使用说明!
###批量操作 - Statement批量操作: statement.addBatch(sql1); statement.addBatch(sql2); statement.addBatch(sql3); //执行批量操作 statement.executeBatch(); - PreparedStatement批量操作: ...