数据库性能相关测试.docxVIP

数据库性能相关的测试java批量insert数据库的效率比较在网上搜了下批量插入的相关文章，特进行如下测试：代码如下：/* ?* To change this template, choose Tools | Templates?* and open the template in the editor.?*/package test;?import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.Date;import java.util.TimeZone;import java.util.logging.Level;import java.util.logging.Logger;?/**?*?* @author Administrator?*/public class TestJavaBatchInsert{ public static final int commitNum=1000; public static void main(String[] args) { Connection con; try { Class.forName(com.mysql.jdbc.Driver); con = (Connection) DriverManager.getConnection(jdbc:mysql://localhost:3306/lirq, lirq, lirq); // 关闭事务自动提交 con.setAutoCommit(false);// Statement statement = con.createStatement();// statement.execute(set names gbk); SimpleDateFormat sdf = new SimpleDateFormat(HH:mm:ss:SS); TimeZone t = sdf.getTimeZone(); t.setRawOffset(0); sdf.setTimeZone(t); Long startTime = System.currentTimeMillis(); Long tmpTime = startTime; Long endTime = null; PreparedStatement pst = (PreparedStatement) con.prepareStatement(insert into lirq.test04 values (?,中国)); for (int i = 0; i = 200000; i++) { pst.setInt(1, i); // 把一个SQL命令加入命令列表 pst.addBatch(); if (i % commitNum== 0) { // 执行批量更新 pst.executeBatch(); // 语句执行完毕，提交本事务 mit(); endTime = System.currentTimeMillis(); System.out.println(每50000条耗时： + sdf.format(new Date(endTime - tmpTime))); tmpTime = endTime; } } pst.executeBatch(); mit(); System.out.println(总耗时： + sdf.format(new Date(endTime - startTime))); pst.close(); con.close(); } catch (SQLException ex) { Logger.getLogger(TestJavaBatchInsert.class.getName()).log(Level.SEVERE, null, ex); } catch (ClassNotFoundException ex) { Logger.getLogger(TestJavaBatchInsert.class.getName()).log(Level.SEVERE, null, ex); } }}?其中commitNum是每次批处理提交的记录数，通过调整这个值得，一方面对比批处理的效果，另一方面，也找出最佳提交点，测试的样本量是200000。测试结果如下：自动提交，每1条提交一次：每1000条耗时：00:00:00:63 每1000条耗时：00:00:2