Postgresql单表【插入】/【更新】百万数据

一、插入数据

说到插入数据，一开始就想到：

insert int A values(*******************)

插入多条数据，最多想到：写成这样：

insert into A values(**********),(*************),(*****************)

但是在百万数据面前，都太慢了。

1、用脚本的方式

#!/bin/bash
strsql="insert into tbl_devaccess8021x (uidrecordid, dtaccesstime, strmac, strusername, strswitchip, strifname, iisauthsuc,iisantipolicy,iisaccessed,strmachinecode,strrandomcode,iaccesstype,straccessfailedcode,uidroleid ,struserdes) values('d71803axxx1','2019-08-02 20:37:35', '1:2:3:4:5:6', 'criss0', '192.168.2.146','FastEthernet0/1',0,0,1,'000000000020A0B01','020A0B01',1,0,'研发','crissxu10')"

for ((i=1; i <=3000000; i++))
do
    strsql=$strsql",('d71803axxx$i',$(date +%s), '1:2:3:4:5:$i', 'criss$i', '192.168.2.$i','FastEthernet0/1',0,0,1,'000000000020A0B01','020A0B01',1,0,'研发','crissxu10')"

done
echo $strsql
#psql -d xxx -U xxx -c "$strsql"

上述在数据量小的时候，可以采用，数据量大的话特别耗时。

2、postgresql提供了copy函数，方便批量导入数据。

copy_from的参数说明：copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None)

import sys
import psycopg2
if sys.version_info.major == 2:
    import StringIO as io
else:
    import io
from datetime import datetime
if __name__=='__main__':
    s = ""
    start_time = datetime.now()
    for i in range(0,10):
        str_i = str(i)
        temp = "d71803axxx{0}\t{1}\t1:2:3:4:5:{2}\tcriss{3}\t192.168.2.{4}\tFastEthernet0/1\t0\t0\t1\t000000000020A0B01\t020A0B01\t1\t0\t研发\tcrissxu10\n".format(str_i, datetime.now(),str_i,str_i,str_i)
        s +=temp
    conn = psycopg2.connect(host='127.0.0.1',user="xxx",password="xxx",database="xxx")
    cur = conn.cursor()
    cur.copy_from(io.StringIO(s),'tbl_devaccess8021x',columns=('uidrecordid', 'dtaccesstime', 'strmac', 'strusername', 'strswitchip', 'strifname', 'iisauthsuc','iisantipolicy','iisaccessed','strmachinecode','strrandomcode','iaccesstype','straccessfailedcode','uidroleid' ,'struserdes'))
    conn.commit()
    cur.close()
    conn.close()
    end_time = datetime.now()
    print ('done. time:{0}'.format(end_time - start_time))

用copy_from 函数执行三百万的数据，时间大概7分钟左右。

3、先往临时表中插入，然后再同步

insert into source_table select  temporary_table

 

 

二、更新数据

update table set col = value where col_condition=value;

更新数据的步骤是先找到符合条件的col_condition的数据，然后再执行更新。少量数据的时候，查询速度快，当表里的数据达到一定量的时候，查询性能受到影响，从而导致更新效率降低。

解决办法：

1、对查询条件加索引。

2、将多条数据合并成一条sql语句

update target_table set c2 = t.c2 from (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1=t.c1

 

 

Reference:

【1】 http://www.voidcn.com/article/p-stwpqgta-bdq.html

 

"后来看到葛班长的日志，他通过Python在SQLite中插入100万条数据只用了4秒，原因在于Python对所有的这100万条插入语句进行了优化，将所有的插入操作放到了同一个事务中，这样极大的减少了开启和取消事务的时间，而正是这部分操作会消耗大量的时间"

这应该可以解释为什么方法2

【2】http://www.voidcn.com/article/p-vvuwvbyw-yu.html

【3】https://help.aliyun.com/knowledge_detail/59076.html