MySQL大数据量insert的优化

百万级数据插入优化

1. 原来程序按照主键排序再插入

由于数据库插入时，需要维护索引数据，无序的数据会增大维护索引的成本。比如innodb所用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入记录在索引中间，需要对B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

并且首先关闭自动提交：

tarConn.setAutoCommit(false);

500条记录提交一次：

tarConn.commit();

但将近90万的记录，运行了6个小时。

此处可以减少创建事务的消耗，500条记录插入都在执行后才进行提交，但是不明显。类似于：

start transaction;

insert into ..

commit;

索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。

2. 修改插入语句，一条SQL插入多条数据：

    Iterator it = batches.iterator();    

        // 读取输入，每次500条
    while (it.hasNext()) {
        Statement statement = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 

              //此处的ResultSet属性设置为TYPE_FORWARD_ONLY，只能向前读取数据。

        ResultSet results = statement.executeQuery(srcSql.getString(r) + it.next());
        ResultSetMetaData metadata = results.getMetaData();
        int columns = metadata.getColumnCount();
        StringBuffer sb = new StringBuffer();
        for(int i = 0; i < columns; i ++){
            sb.append("?,");
        }
        String str = sb.toString();
        str = str.substring(0, str.length() - 1);
        PreparedStatement ps = tarConn.prepareStatement("insert into " + tarTab.getString(r) + " values(" + str + ")");
        for (int row = 1; results.next(); row++) {
            for (int i = 1; i <= columns; i++) {
                ps.setObject(i, results.getObject(i));
            }
            ps.executeUpdate();
        }
        tarConn.commit();
        results.close();
        statement.close();
    }

修改为：

    Iterator it = batches.iterator();
    while (it.hasNext()) {
        //Statement statement = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        Statement statement = srcConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

                  //此处将ResultSet的属性修改为TYPE_SCROLL_INSENSITIVE，因为首先获取一共多少条记录，形成那些条数的占位符，而后还要重新回到游标的首部读取实际数据。
        ResultSet results = statement.executeQuery(srcSql.getString(r) + it.next());
        ResultSetMetaData metadata = results.getMetaData();
        int columns = metadata.getColumnCount();
       
        //Generate prepared statement for loading data
        //StringBuffer sb = new StringBuffer();
        StringBuilder sb = new StringBuilder();
        //
        
        for (int row = 1; results.next(); row++) {
            if(count==0) {
                sb = new StringBuilder("insert delayed into " + tarTab.getString(r) + " values(");
            }
            else {
                sb.append(",(");
            }
            for(int i = 0; i < columns-1; i ++){
                sb.append("?,");
            }
            sb.append("?)");
            count++;
        }
        String str = sb.toString();
        //System.out.println(str);
        PreparedStatement ps = tarConn.prepareStatement(str);
        results.beforeFirst();

        //此处回到游标首部
        for (int row = 1; results.next(); row++) {
            for (int i = 1; i <= columns; i++) {
                ps.setObject((row-1)*columns+i, results.getObject(i));
            }
        }
        ps.executeUpdate();
            //PreparedStatement ps = tarConn.prepareStatement("insert into " + tarTab.getString(r) + " values(" + str + ")");
        tarConn.commit();
        results.close();
        statement.close();
        count = 0;
    }
    System.gc();

    //运行一段时间，经常报错：java.sql.SQLException: java.lang.OutOfMemoryError: Java heap space， 除去增大jvm内存使用参数Xmx,Xms,我希望通过垃圾回收有所帮助。

为什么这样修改会提升性能：

合并后日质量（主要是binlog和innodb的事务日志）减少了，降低日志刷盘的数据量和频率；通过合并SQL，也能减少SQL语句解析次数，减少网络传输IO。

注意事项:

SQL语句长度不能大于max_allowed_packet设置的长度限制；

参考： http://www.360doc.com/content/13/0923/09/15643_316409560.shtml

3. 出现的问题

insert出现堵塞现象，运行一段时间后既不报错，也无法继续进行插入。

解决办法：
  优化表结构和数据结构
  更改INSERT INTO为 INSERT DELAYED INTO
  更改程序结构，让每个子进程各自打开一个MySQL连接
因为第一、第三中方法无可行性，采用第二种方法：INSERT DELAYED INTO， 为什么？
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

参考：http://blog.csdn.net/eroswang/article/details/2949275

4. 与主题无关的补充
IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字或者当STRICT模式启动后出现警告，则使用IGNORE控制ALTER TABLE的运行。
如果没有指定IGNORE，当重复关键字错误发生时，复制操作被放弃，返回前一步骤。如果指定了IGNORE，则对于有重复关键字的行，只使用第一行，其它有冲突的行被删除。并且，对错误值进行修正，使之尽量接近正确值。
insert ignore into tb(...) value(...)
这样不用校验是否存在了，有则忽略，无则添加