Postgre数据库Insert 、Query性能优化详解

一、前言
以前的系统由于表设计比较复杂（多张表，表与表直接有主从关系），这个是业务逻辑决定的。 插入效率简直实在无法忍受，必须优化。在了解了Postgre的Copy,unlogged table 特性
之后，决定一探究竟。

二、测试用例

1.数据表结构:表示一个员工工作绩效的表（work_test）：共15个字段
id,no,name,sex,tel,address,provice,city,post,mobile,department,work,start_time,end_time,score
索引（b-tree的集群索引或者叫聚集索引）：id,no,name,sex,tel,address,provice,city,post,mobile,department,work

2.测试环境：win7，四核，2GB内存；postgre版本9.3；Npgsql连接Postgre数据库。

三、insert/ transaction/ copy/unlogged table

1.insert 一个10W数据大概需要120s，虽然已经提升“不少”，但是还是不尽如人意。以前用SQLite时发现Transaction可以大幅提升性能，于是在Postgre中试试，发现并没有明显变
化。不知何故。

2. copy可以将文件（csv）中的数据复制进数据库中，当然数据表的结构和要数据类型要与文件一一对应。据说可以大幅提升插入性能。


代码如下:

COPY 'work_test' from 'c:\temp.csv' WITH DELIMITER AS ','

　　
使用Copy后发现插入的性能立马提升至30s，相当于1s插入3300条记录。这中间还包含生成csv文件的时间。

3.unlogged table
unlogged table，网上的文章说可以10倍于insert。使用方法也很简单:Create unlogged table ...
但是unlogged table 在遇到Postgre服务器异常重启后会丢失全部数据，所以如果你的数据不允许丢失，请不要使用。
使用后发现，大概20s，1s插入5000条记录。

下面是三种插入方式的走势图：

说明：x轴表示数据库中已有的记录数，单位百万，每个点是10W.Y轴表示每次插入所耗时间，单位秒。
蓝色线：insert;之所以后面比较稳定是因为电脑没有运行其他程序。所以说，电脑工作状态对Postgre效率有一定的影响。
粉红色：copy;
黄色线：copy+unlogged
 虽然建了索引，并且表中的数据一直累加进来，对于后续插入数据性能并没有任何影响，这个结果就是我想看到的。

四、查询测试
按name字段搜索：


代码如下:

select * from work_test where name='1'

说明：x轴表示数据库中已有的记录数，单位百万，每个点是10W.Y轴表示每次查询所耗时间，单位毫秒。

蓝色线：insert;
粉红色：copy;
黄色线：copy+unlogged

由于三种插入方式结果都是一样的，所以对比并没有意义，这里主要看查询耗时。平均下来：500ms,并且随着Row count的增加，查询效率并没有降低。这主要得益于良好的索引。
另外发现：条件越多，查询效率越高，因为扫描的行数在减少，后面的图就不贴出来了。

赞 (0)

相关推荐

  • 数据库性能优化一：数据库自身优化提升性能

    数据库性能优化一：数据库自身优化提升性能

    数据库优化包含以下三部分,数据库自身的优化,数据库表优化,程序操作优化.此文为第一部分 优化①:增加次数据文件,设置文件自动增长(粗略数据分区) 1.1:增加次数据文件 从SQLSERVER2005开始,数据库不默认生成NDF数据文件,一般情况下有一个主数据文件(MDF)就够了,但是有些大型的数据库,由于信息很多,而且查询频繁,所以为了提高查询速度,可以把一些表或者一些表中的部分记录分开存储在不同的数据文件里 由于CPU和内存的速度远大于硬盘的读写速度,所以可以把不同的数据文件放在不同的物理硬盘

  • asp.net程序优化 尽量减少数据库连接操作

    项目以我自己的设计编码完成,并整合测试.初始化数据时,问题出现了.刚开始体现在客户端接受数据很慢.测试环境环境下,数据库服务器部署在国外,网站部署在公司内部,而且我一直认为我的程序在数据库数据处理这里已经做了足够的优化,包括索引和主键已经做到了合理使用.综上所述,起初的速度问题一直没有引起我的关注. 然而最后问题的关键恰恰出在数据库连接查询方面,频繁查询导致数据初始化速度很慢.刚开始我采取的方法是即用即查:需要数据的时候就从数据库查,有比较多的单表查询返回单个字段的情况.假如我有大概3000条左

  • 数据库学习建议之提高数据库速度的十条建议

    数据库学习建议之提高数据库速度的十条建议

    很多网站的重要信息都是保存在数据库中的,用户通过提交访问数据库来获取用户信息.如果数据库速度非常的快,有助于节省服务器的资源,在这篇文章中,我收集了十个优化数据库速度的技巧. 0. 小心设计数据库 第一个技巧也许看来理所当然,但事实上大部分数据库的问题都来自于设计不好的数据库结构. 譬如我曾经遇见过将客户端信息和支付信息储存在同一个数据库列中的例子.对于系统和用数据库的开发者来说,这很糟糕. 新建数据库时,应当将信息储存在不同的表里,采用标准的命名方式,并采用主键. 来源: http://www

  • 开启SQLSERVER数据库缓存依赖优化网站性能

    很多时候,我们服务器的性能瓶颈会是在查询数据库的时候,所以对数据库的缓存非常重要,那么有没有一种方法,可以实现SQL SERVER数据库的缓存,当数据表没有更新时,就从缓存中读取,当有更新的时候,才从数据表中读取呢,答案是肯定的,这样的话我们对一些常用的基础数据表就可以缓存起来,比如做新闻系统的新闻类别等,每次就不需要从数据库中读取了,加快网站的访问速度. 那么如何开启SQLSERVER数据库缓存依赖,方法如下: 第一步:修改Web.Config的<system.web>节的配置,代码如下,让

  • mysql中优化和修复数据库工具mysqlcheck详细介绍

    一.mysqlcheck简介 mysqlcheck客户端可以检查和修复MyISAM表.它还可以优化和分析表. mysqlcheck的功能类似myisamchk,但其工作不同.主要差别是当mysqld服务器在运行时必须使用mysqlcheck,而myisamchk应用于服务器没有运行时.使用mysqlcheck的好处是不需要停止服务器来检查或修复表.使用myisamchk修复失败是不可逆的. Mysqlcheck为用户提供了一种方便的使用SQL语句CHECK TABLE.REPAIR TABLE.

  • MySQL数据库优化详解

    mysql表复制 复制表结构+复制表数据 mysql> create table t3 like t1; mysql> insert into t3 select * from t1; mysql索引 ALTER TABLE用来创建普通索引.UNIQUE索引或PRIMARY KEY索引 ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list)

  • MySQL 联合索引与Where子句的优化 提高数据库运行效率

    网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了.涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,"深圳-枪手"热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~~~ 在群友的分析指点下,尝试把排序.条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是

  • mysql 数据库中my.ini的优化 2G内存针对站多 抗压型的设置

    物理内存越大,设置就越大.默认为2402,调到512-1024最佳 innodb_additional_mem_pool_size=4M 默认为2M innodb_flush_log_at_trx_commit=1 (设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1) innodb_log_buffer_size=2M 默认为1M innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,建议用默认一般为8 key_buff

  • SQL Server 数据库优化

    在开发工具.数据库设计.应用程序的结构.查询设计.接口选择等方面有多种选择,这取决于特定的应用需求以及开发队伍的技能.本文以SQL Server为例,从后台数据库的角度讨论应用程序性能优化技巧,并且给出了一些有益的建议.1 数据库设计 要在良好的SQL Server方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案.在实际工作中,许多SQL Server方案往往是由于数据库设计得不好导致性能很差.所以,要实现良好的数据库设计就必须考虑这些问题. 1.1 逻辑库规范化问题 一般来说,逻辑

  • 海量数据库的查询优化及分页算法方案

    海量数据库的查询优化及分页算法方案  原出处不详 摘自:www.21php.com 随着"金盾工程"建设的逐步深入和公安信息化的高速发展,公安计算机应用系统被广泛应用在各警种.各部门.与此同时,应用系统体系的核心.系统数据的存放地――数据库也随着实际应用而急剧膨胀,一些大规模的系统,如人口系统的数据甚至超过了1000万条,可谓海量.那么,如何实现快速地从这些超大容量的数据库中提取数据(查询).分析.统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题. 在以下

  • Codeigniter操作数据库表的优化写法总结

    用codeigniter也有一段时间了,一直没有做什么总结.现在总结一些Codeigniter操作数据库表的优化写法,虽说不全,但是也确实可以帮助那些刚刚上手CI的同学. 链接数据库 复制代码 代码如下: $this->load->database();//手动连接数据库//连接多数据库$DB1 = $this->load->database('group_one', TRUE);$DB2 = $this->load->database('group_two', TRU

  • 优化Mysql数据库的8个方法

    1.创建索引对于查询占主要的应用来说,索引显得尤为重要.很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致.如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降.但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引.2.复合索引比如有一条语句是这样的:select * from users

  • Oracle SQL tuning 数据库优化步骤分享(图文教程)

    Oracle SQL tuning 数据库优化步骤分享(图文教程)

    SQL Turning 是Quest公司出品的Quest Central软件中的一个工具.Quest Central是一款集成化.图形化.跨平台的数据库管理解决方案,可以同时管理 Oracle.DB2 和 SQL server 数据库. 一.SQL Tuning for SQL Server简介 SQL语句的优化对发挥数据库的最佳性能非常关键.然而不幸的是,应用优化通常由于时间和资源的因素而被忽略.SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能,提出智能化的

  • oracle数据库sql的优化总结

    一:使用where少使用having; 二:查两张以上表时,把记录少的放在右边: 三:减少对表的访问次数: 四:有where子查询时,子查询放在最前: 五:select语句中尽量避免使用*(执行时会把*依次转换为列名): 六:尽量多的使用commit: 七:Decode可以避免重复扫描相同的记录或重复连接相同的表: 八:通过内部函数也可提高sql效率: 九:连接多个表时,使用别名并把别名前缀于每个字段上: 十:用exists代替in 十一:not exists代替 not in(not in 字

  • 服务器维护小常识(硬盘内容增加、数据库优化等)

    为了方便大家在维护中了解一些维护内容的同时又能避免出现错误.下面就收集了一些服务器日常维护的常识供大家参考. 服务器日常维护常识之硬件维护 服务器日常维护常识硬件维护1.储存设备的扩充 当资源不断扩展的时候,服务器就需要更多的内存和硬盘容量来储存这些资源.所以,内存和硬盘的扩充是很常见的.增加内存前需要认定与服务器原有的内存的兼容性,最好是同一品牌的规格的内存.如果是服务器专用的ECC内存,则必须选用相同的内存,普通的SDRAM内存与ECC内存在同一台服务器上使用很可能会引起统严重出错.在增加硬

  • 数据库性能优化二：数据库表优化提升性能

    数据库性能优化二：数据库表优化提升性能

    数据库优化包含以下三部分,数据库自身的优化,数据库表优化,程序操作优化.此文为第二部分 优化①:设计规范化表,消除数据冗余 数据库范式是确保数据库结构合理,满足各种查询需要.避免数据库操作异常的数据库设计方式.满足范式要求的表,称为规范化表,范式产生于20世纪70年代初,一般表设计满足前三范式就可以,在这里简单介绍一下前三范式 先给大家看一下百度百科给出的定义: 第一范式(1NF)无重复的列 所谓第一范式(1NF)是指在关系模型中,对域添加的一个规范要求,所有的域都应该是原子性的,即数据库表的每

  • 用实例详解Python中的Django框架中prefetch_related()函数对数据库查询的优化

    用实例详解Python中的Django框架中prefetch_related()函数对数据库查询的优化

    实例的背景说明 假定一个个人信息系统,需要记录系统中各个人的故乡.居住地.以及到过的城市.数据库设计如下: Models.py 内容如下: from django.db import models class Province(models.Model): name = models.CharField(max_length=10) def __unicode__(self): return self.name class City(models.Model): name = models.Ch

随机推荐