postgre提升insert效率 post insert

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

如何提升MySQL批量插入的效率

需要将大量数据(大概5W条)插入MySQL数

据库，用普通的SQL

Statement执行,时间大概是几分钟。于是想到用PreparedStatement，但是改了之后发现效率并没有很大的提升。不成，想到了

load data local

infile...命令，以前一直认为这条命令仅限MySQL终端上使用而不是标准的SQL语句，今天看了几篇文章之后有了很大的收获。

1. 使用PreparedStatement batch operation

以前使用PreparedStatement性能没有很大提升的原因在于：

没有使用批处理方法

在语句执行之前应关闭事务自动提交，语句执行完之后再提交

public

void batchLoad(Connection connection)

{

try

{

connection.setAutoCommit(false);

BufferedReader reader =

new BufferedReader(new

FileReader("tfacts_result"));

String sqlString =

"insert into test(node1, node2, weight) values(?, ?, ?)";

PreparedStatement pstmt = connection.prepareStatement(sqlString);

String line =

null;

while(true)

{

line = reader.readLine();

if(line == null)

{

break;

}

String[] columns = line.split("\t");

for(int

i = 1; i = columns.length; i++)

{

pstmt.setString(i, columns[i-1]);

}

pstmt.addBatch();

}

pstmt.executeBatch();

connection.commit();

pstmt.close();

reader.close();

}

catch (FileNotFoundException e) {

e.printStackTrace();

}catch

(SQLException e){

e.printStackTrace();

}catch

(IOException e){

e.printStackTrace();

}

2.使用load data local infile into tabel XXX（注意在文件中用\t将每列数据隔开）

public

void loadData(Connection connection)

{

long

starTime = System.currentTimeMillis();

String sqlString =

"load data local infile ? into table test";

PreparedStatement pstmt;

try

{

pstmt = connection.prepareStatement(sqlString);

pstmt.setString(1,

"tfacts_result");

pstmt.executeUpdate();

pstmt.close();

}

catch (SQLException e) {

e.printStackTrace();

}

long

endTime = System.currentTimeMillis();

System.out.println("program runs "

+ (endTime - starTime) + "ms");

}

测试了5W条数据，PreparedStatement耗时10s，而load data infile耗时3s。

oracle 大量数据insert操作怎么提高效率

一般实际使用过程中，很少用大批量的INSERT 操作的

INSERT操作会占用数据库的REDO空间，没插入一条会写一条回滚记录，所以很慢

如果是从一个数据库导入到另外一个数据库，可以用dmp文件来实现导入导出，

如果是从EXCEL里面导入到数据库里面

可以直接只用复制粘贴的方式来走

如果是从一张表转移到另外一张表，可以通过insert into 表1 select * from 表2 的方式

关于INSERT效率的问题引发的同步延迟

最近研发侧在做数据导入的改造，改造的方式是将原来LOAD DATA的方式修改为INSERT插入的方式，主要基于如下两方面考虑：

1、修改成INSERT后能更方便地在程序侧控制写入的线程数，更好地保护后端DB，防止后端DB雪崩

2、减少大批量导入带来DB的延迟（单个LOAD拆分成多个INSERT，多线程同步）

另外监控DB大部分的load数据量都比较小，改成INSERT对导入延迟影响不会太大。

下面是对INSERT和LOAD DATA的方式做的简单的测试结果：

从上面的测试结果，批量INSERT 30000条数据只比LOAD DATA的方式慢了2倍多一点，如果将30000的数据拆分成1000一个批量INSERT的SQL，并发写入，耗时比单线程LOAD要有提升。

上线后，平均耗时增长了很多，如下图所示：

并且深入去看，耗时比较高的都是跨IDC的机器（上海写深圳），而且高得离谱。

从上图中，可以看到，耗时比较大的都是上海的机器。

并且监控DB的主从延迟也有比较大的飙升，如下图：

奇怪，按照之前的计算评估，不应该会有那么大的延迟，而且同步延迟应该有缓解才对，不可能越来越厉害。

查看监控发现INSERT出现了暴增，如下图：

针对insert，改写成1000条一次的并发插入，正常也不应该有那么大的增长，怀疑是INSERT INTO的方式不是采用批量的方式，而是一条一条insert的方式。分析binlog发现执行方式如下：

这种方式和单个INSERT的方式性能差别其实并不算大，下面是简单的测试结果：

测试场景：在跨机房场景下，延迟相对比较大，30ms左右，测试INSERT本身的性能

1、INSERT 单行

2、INSERT 单行多values

3、INSERT 多行（27行）

4、INSERT 多行（27行）在一个事务中

测试接入过如下图：

可以看出：

采用INSERT 多values的形式和插入单行差别不算大（主要是行数不多）；

采用INSERT多行和把多个INSERT放到一个大事务中性能相差不大。

解决办法就很简单了，直接把大事务修改为INSERT多values的方式。

修改后，INSERT的量大幅度下降：

平均耗时也大幅下降：

同步延迟也逐渐恢复：

在写入效率上，对比关系如下：

1、LOAD DATA效率最高

2、INSERT 多values次之

3、INSERT 多行大事务再次之

4、INSERT 单行最差

有什么方法可以提高insert的效率

1、搞清楚你的数据库允许的最大并发连接数P

2、搞清楚你当前程序和你数据库之间带宽限制B

3、搞清楚你当前一条记录所用的数据量大小Q

4、用公式X = MIN(B/Q, P)得到允许并发线程数X

5、用并发线程去插入数据，速度最多会得到X倍提高

qstringlist的insert和append效率

1、python中insert和append方法都可以向列表中插入数据只不过append默认插入列表的末尾，insert可以指定位置插入元素。测试一下插入数据的效率。

2、测试同时对一个列表进行插入字符’a'，插入10000次，先来insert的方式插入。

3、使用append追加。

4、因为在python中的列表并不是真正意义上的链表，因为对于链表来讲插入和删除的效率是很高的，并且链表的插入只需要修改指针的指向，这里的list可以用动态数组来理解，数组按照索引来访问元素效率很高，但是插入的时候需要将你插入位置的后面元素全都向后面移动必要的时候还需要再重新分配内存空间，可能你这样会觉得insert和append就没什么区别了，但是从他们的返回值以及参数来讲，append默认插入到列表的最后，insert可以在指定位置上插入所以相比insert，append更加稳定，所以效率就高了。