百万级数据插入优化
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(...)
这样不用校验是否存在了,有则忽略,无则添加