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=; i <=; 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
最新文章
- 查询oracle版本信息
- TJI读书笔记17-字符串
- Swift语言与Objective-C语言混合编程
- log4j发送邮件乱码
- 实战MySQL集群,试用CentOS 6下的MariaDB-Galera集成版
- C++ builder 生成以管理员身份运行的exe
- Exchange Server 2010升级到Exchange Server 2013概览
- 使用【百度云推送】第三方SDK实现推送功能具体解释
- JavaEE(18) - JPA原生SQL查询和存储过程
- webstrom 快捷键(Idea可用)
- 分布式事务实现-Spanner
- 解决初次安装PyCharm后 No Python interpreter selected的问题
- vuejs自定义过滤器根据搜索框输入的值,筛选复杂的列表数据
- MVC 动态菜单
- php window系统 xdebug+phpstorm 本地断点调试使用教程
- XHR简介
- 第二个Sprint冲刺第 七天(燃尽图)
- Spark2 oneHot编码--标准化--主成分--聚类
- 解决 EntityFrameworkCore 执行 Add-Migration命令提示无法识别转义符的错误
- socket编程小问题:地址已经被使用——Address already in use
热门文章
- excel打开提示 文件格式和扩展名不匹配。文件可能已损坏或不安全。除非您信任其来源,否则请勿打开。是否仍要打开它?
- C#串口关闭SerialPort.Close()导致的卡死
- Python库的使用之-Sys模块
- MongoDB 几种查询嵌套数据(Embedded)的方式(转载)
- go中的字符串数值转化
- [Python] Python忽略warning警告错误
- Python逆向(二)—— pyc文件结构分析
- [技术博客]nginx 部署 apt 源
- 使用MeadCo&#39;s ScriptX控件做WEB打印
- MySQL事务隔离级别(二)