MySQL批量Insert，HikariCP、线程池参数优化测试

MySQL批量Insert，HikariCP、线程池参数优化测试

参考

作者: leizhimin

源自: http://blog.51cto.com/lavasoft/185010

转载: https://blog.csdn.net/wwd0501/article/details/45056631

发布: 2009-07-29

环境

• MySQL 5.6.38
• CentOS 7
• JavaSE 1.8
• HikariCP数据库连接池

测试方案

执行10万次Insert语句，使用不同方式。

• C组：静态SQL，不自动提交，有事务控制（InnoDB引擎）
  1、逐条执行10万次
  2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

• D组：预编译模式SQL，不自动提交，有事务控制（InnoDB引擎）
  1、逐条执行10万次
  2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

测试代码

HikariPool.java

package helloworld;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class HikariPool {
   
    private static HikariDataSource ds;
	public static void Start(int PoolSize) {
   
		HikariConfig config = new HikariConfig();
		config.setJdbcUrl("jdbc:mysql://192.168.1.1:3306/test");
		config.setUsername("xxxx");
		config.setPassword("xxxx");
		// config.setDriverClassName("com.mysql.jdbc.Driver");
		config.addDataSourceProperty("cachePrepStmts", "true");
		config.addDataSourceProperty("prepStmtCacheSize", "250");
		config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

		config.addDataSourceProperty("rewriteBatchedStatements", "true");

		config.setMaximumPoolSize(PoolSize <= 0 ? 10 : PoolSize);
		config.setValidationTimeout(3000);
		config.setConnectionTimeout(60000);
		try {
   
			ds = new HikariDataSource(config);
			Properties prop = ds.getDataSourceProperties();
		} catch (Exception e) {
   
			ds = null;
		}
	}

    public static Connection getConn() throws SQLException {
   
        return null == ds ? null : ds.getConnection();
    }
    public static int getMaxPoolSize()  {
   
        return ds.getMaximumPoolSize();
    }
    public static void Close() {
   
    	if (null != ds) {
   
    		ds.close();
    	}
    }
}

TestBatch.java

参考: http://blog.51cto.com/lavasoft/185010 的实现，添加了线程池、HikariCP等

package helloworld;

import java.io.IOException; 
import java.sql.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger; 

/** 
* JDBC批量Insert优化（下） 
* 
* @author leizhimin 2009-7-29 10:03:10 
*/ 
public class TestBatch  {
    
		public static int  recs_total = 300000;  //总记录数  
		public static AtomicInteger tasks_done = new AtomicInteger(0);
		public static AtomicInteger conn_elapsed = new AtomicInteger(0);
		public static AtomicInteger  conn_attched = new AtomicInteger(0);
		public static ExecutorService execService;
		
        /*public static DbConnectionBroker myBroker = null; 

        static { 
                try { 
                        myBroker = new DbConnectionBroker("com.mysql.jdbc.Driver", 
                                        "jdbc:mysql://192.168.104.163:3306/test", 
                                        "vcom", "vcom", 2, 4, 
                                        "c:\\testdb.log", 0.01); 
                } catch (IOException e) { 
                        e.printStackTrace(); 
                } 
        }
         */
		public static String generateString(int length) {
   
			String s = "";
			for (int i = 0; i < length && s.length() < length; i++) {
   
				s = s + Math.random()%37;
			}
			return s.substring(0,length);
		}

        /** 
         * 初始化测试环境 
         * 
         * @throws SQLException 异常时抛出 
         */ 
        public static void init() throws SQLException {
    
                Connection conn = HikariPool.getConn();
                conn.setAutoCommit(false); 
                Statement stmt = conn.createStatement(); 
                stmt.addBatch("DROP TABLE IF EXISTS tuser"); 
                /*
                stmt.addBatch("CREATE TABLE tuser (\n" + 
                                "    id bigint(20) NOT NULL AUTO_INCREMENT,\n" + 
                                "    name varchar(12) DEFAULT NULL,\n" + 
                                "    remark varchar(24) DEFAULT NULL,\n" + 
                                "    createtime datetime DEFAULT NULL,\n" + 
                                "    updatetime datetime DEFAULT NULL,\n" + 
                                "    PRIMARY KEY (id)\n" + 
                                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"); 
                */
                stmt.addBatch("CREATE TABLE tuser (\n" + 
                        "    id bigint(20) NOT NULL,\n" + 
                        "    name varchar(12) DEFAULT NULL,\n" + 
                        "    remark varchar(24) DEFAULT NULL,\n" + 
                        "    createtime datetime DEFAULT NULL,\n" + 
                        "    updatetime datetime DEFAULT NULL,\n" + 
                        "    PRIMARY KEY (id)\n" + 
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8"); 
                stmt.executeBatch(); 
                conn.commit(); 
//                myBroker.freeConnection(conn);
                conn.close();
        } 

        /** 
         * 100000条静态SQL插入 
         * 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsert(