最近需要测试日志查询性能的,感觉比较慢所有需要进行大数据量的数据进行测试,发现mysql的批量导入数据很强悍,导入10万条数据,只要15596ms。
其中需要注意的一下几点:
1.提交方式需要设置为手动提交:
conn.setAutoCommit(false); // 设置手动提交
2. 采用mysql的批量导入语句:
pstmt.addBatch();
pstmt.executeBatch(); // 执行批量处理
之后再提交:
conn.commit(); // 提交
3.将mysql批量导入功能开启:
在mysql的jdbc.url地址中添加参数:
useServerPrepStmts=false&rewriteBatchedStatements=true&useSSL=false
结果展示:
代码如下:
/**
* 查询数据库中配置的分馆信息
*/
public static void insertTestData(){
long begint = System.currentTimeMillis();
System.out.println("========开始导入时间:"+begint+"=================");
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
int count = 100000;
try {
String insertSql = "";
insertSql = "INSERT INTO user_access_log(uid,cn,appName,port,serverIP,userIP,createDate,operation,details,signature) VALUES (?,?,?,?,?,?,?,?,?,?)";
conn = getMysqlConnect("audit");
conn.setAutoCommit(false); // 设置手动提交
System.out.println("============insertSql:"+insertSql+"======================");
pstmt = conn.prepareStatement(insertSql,Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < count; i++) {
pstmt.setString(1, "test02");
pstmt.setString(2, "test02");
pstmt.setString(3, "sso");
pstmt.setInt(4, 80);
pstmt.setString(5, "127.0.0.1");
pstmt.setString(6, "172.16.208.99");
pstmt.setDate(7, new java.sql.Date(System.currentTimeMillis()));
pstmt.setInt(8, 0);
pstmt.setString(9, "test02正常登录"+i);
pstmt.setString(10, null);
pstmt.addBatch();
if( i%1000 == 0 ){
pstmt.executeBatch(); // 执行批量处理
}
}
pstmt.executeBatch(); // 执行批量处理
conn.commit(); // 提交
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(pstmt != null){
pstmt = null;
}
if(rs != null){
rs = null;
}
if(conn != null){
conn = null;
}
}
long endt = System.currentTimeMillis();
System.out.println("========结束导入时间:"+endt+"=================");
System.out.println("========导入数据"+count+"条共耗时间:"+(endt-begint)+"ms=================");
}
/**
* 根据mysql配置文件获取数据库连接
*/
public static Connection getMysqlConnect(String dbname){
Connection con = null;
String username = "";
String password = "";
String url = null;
String address = "";
String port = "";
try {
PropertyParser pParser = new PropertyParser(Const.ROOTPATH
+ "logconfig.properties");
url = pParser.getValue("db.url");
address = url.substring((url.indexOf("/") + 2), url
.lastIndexOf(":"));
port = url.substring((url.lastIndexOf(":") + 1), url
.lastIndexOf("/"));
username = pParser.getValue("db.user");
password = pParser.getValue("db.password");
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://" + address + ":"+ port + "/"+dbname+"?useServerPrepStmts=false&rewriteBatchedStatements=true&useSSL=false&user="+username+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8");
} catch (IOException e1) {
e1.printStackTrace();
return null;
} catch (ClassNotFoundException e2) {
e2.printStackTrace();
return null;
} catch (SQLException e3) {
e3.printStackTrace();
return null;
}
return con;
}