MySql批量插入数据优化

这里以常用的数据导入为例来说说批量导入优化的问题。常见的写法是for循环，然后一条一条执行插入操作，
数据量比较小还好说，碰到百万级别的，这个执行时间是很可怕的。

我们计算一下，即时单条语句执行时间是毫秒级别，百万条数据全部插入完成也得花费最少一天时间，这是让人很难以接受的，下面说说优化的方法。
首先我们需要了解一下SQL执行的过程，客户端先将需要执行的sql发送给mysql服务器端，服务器接收到sql语句之后，进行解析，然后执行。这里会有两部分时间比较耗时，一个是事务比较耗时，另外一个是网络传输的时间。我们可以从这两方面进行优化。

1、JDBC的addBatch批量插入操作

  使用PreparedStatement进行sql预编译可以提高效率，并且实现了addBatch方法，利用addBatch可以实现批量插入。

public void batchSave(){
        System.out.println("批量插入100w条数开始...");
        long t1 = System.currentTimeMillis()/1000;
        Connection conn = null;
        //发送的是预编译后的SQL语句，执行效率高
        PreparedStatement psst = null;
        try {
            Class.forName(name);//指定连接类型
            conn = DriverManager.getConnection(url, user, password);//获取连接
            conn.setAutoCommit(false);      //关闭事务自动提交
            //清空表数据
            conn.createStatement().execute("truncate table student");
            psst = conn.prepareStatement("insert into student(name,age,sex) values(?,?,?)");
            Random random = new Random();
            for(int i = 0; i < 1000000; i++){
                psst.setString(1,"zhangsan" + i);
                psst.setInt(2, random.nextInt(100) + 1);
                psst.setInt(3,(i+1)%2);
                psst.addBatch();
                //每隔1000条数据执行一次,避免内存不够的情况
                if((i+1) % 1000 == 0){
                    psst.executeBatch();
                    conn.commit();
                    //清空缓冲区，防止内存泄露
                    psst.clearBatch();
                }
            }
            //执行剩下的
            psst.executeBatch();
            psst.close();
            conn.commit();
            long t2 = System.currentTimeMillis()/1000;
            System.out.println("批量插入100w条数据结束，共耗时:" + (t2 -t1));
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (Exception e1) {
            }
            e.printStackTrace();
        }finally {
            try {
                if (psst != null) {
                    psst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

机器I3处理器，8G内存，测试花费188s，大概3分钟。

2、Mybatis批量插入数据

<insert id="insertByBatch" parameterType="java.util.List">
    insert into user (name, age,address)
    values
    <foreach collection="list" item="user" index="index" separator=",">
        (#{user.name,jdbcType=VARCHAR}, #{user.age,jdbcType=INTEGER},#{user.address,jdbcType=VARCHAR})
    </foreach>
</insert>

3、Hibernate批量插入数据

@Test
public void testAdd(){
long begin = System.currentTimeMillis();
Configuration config = new Configuration();
config.configure();
SessionFactory factory = config.buildSessionFactory();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
Random random = new Random();
for ( int i=0; i<1000000; i++ ) { //插入100万条数据
User user = new User(“zhangsan-“+i,random.nextInt(100) + 1,(i+1)%2);
session.save(user);
//每隔1000条数据执行一次,避免内存不够的情况
if((i+1) % 1000 == 0){
session.flush();
session.clear();
}
}
tx.commit();
session.close();
long end = System.currentTimeMillis();
System.out.println((end-begin)/1000.0);
}