文档章节

DB2优化之:delete和update对性能的影响

Goopand
 Goopand
发布于 2015/04/20 17:38
字数 1907
阅读 76
收藏 0
点赞 0
评论 0

删除和更新操作对性能的影响

删除和更新操作的开销往往比插入高,所以一个好的设计需要减少对数据库的更新和删除操作。


1.1 更新操作

数据库的更新操作会带来一连串的“效应”:更新操作需要记录日志(以便错误时回滚);更新可变长字段(如,varchar类型)会带来数据物理存储的变化(记录的移动);更新索引字段会导致索引重建;更新主键会导致数据重组等。这一切不但会造成更新操作本身效率低,而且由于磁片碎片的产生会造成以后查询性能的降低。为了应对这一情况,有两种策略:一、减少更新次数,把多个字段的更新写到同一个语句里;二、避免更新。这两种策略分别适用于不同的情况,下面将举例说明两种情况。

1.1.1减少更新次数

在整合库里有个代码清洗过程,就是通过连接代码表给业务数据的自编码字段赋值。代码清洗其实是通过关联代码表来更新业务数据表的一个过程,需要连接多个代码表,更新多个自编码字段。完成此更新,有两种更新语句的写法:一种是写成多个SQL语句,每个语句更新一个自编码字段;另一种写法是将所有更新写在一个语句中。更新银行代码的更新语句如下所示:

update TBL_INCOME_TMP A

set BANKCODESELF = (

    select SELFCODE

    from

        TBL_BANKINFO B

    where A.BANKCODE = B.BANKCODE )

通过一个更新语句实现多个自编码字段更新的语句示意如下:

update TBL_INCOME_TMP

set 代码1自编码 = 通过关联代码1表得到自编码,

    代码2自编码 =通过关联代码2表得到自编码,

    ...,

    代码n自编码 =通过关联代码n表得到自编码

利用两千万的测试数据。两种方法的测试结果如下表所示。从测试结果看出,一次更新方法性能提高了十倍,大大提高了性能。

处理过程

多次更新方法耗时

一次更新方法耗时

代码清洗

0:29:48

0:02:59

1.1.2避免更新

下面举个通俗的例子,这类情况是经常遇到的。某公司有一套系统员工考勤系统,为了提高查询统计的性能,在原有系统基础上建立了一些包含冗余信息的表。以员工表为例,它获得数据的过程如图12所示。第一步把员工信息放到新表中,然后连接通过字段“部门ID”连接更新“部门名称”。


图12. 关联更新

一般,为了节省存储开支把部门名称这样的字段设计成可变长的。所以在对它进行更新时会造成磁盘数据的重新组织,形成磁盘碎片,影响查询性能。

为了避免这样的情况发生,我们可以使用如图13所示的方法避免更新。这种方法一步完成了冗余数据表的插入,再插入时连接部门表获得“部门名称”,从而避免了更新操作。


图13. 避免更新


1.2 删除操作

初学者可能认为删除操作很简单,可以快速完成。其实这是一个错误的理解,删除过程需要大量扫描磁盘;需要记录数据库日志;而且删除过程不释放磁盘空间,浪费磁盘,并且使磁盘上的数据支离破碎,这对后续查询的性能是一个致命的打击。通常用两种方式来应对:一、对经常做删除操作的表进行重组(reorg);二、避免删除。

1.2.1 重组

重组(reorg)操作会重新排列表数据的物理顺序,并除去碎片数据中的空闲空间。

由于删除操作不释放磁盘空间,在执行删除操作后,表会成为碎片状,这导致性能严重下降,在多次更新操作之后也会出现这种情况。若收集了统计信息,但看不出有明显的性能改进,则重组表数据可能会有帮助。重组表数据时,根据指定的索引重新安排数据的物理顺序,并除去碎片数据中的空闲空间。这使该数据可以更快速的被存取,从而改进性能。

1.2.2 避免删除——中间表和正式表模式

在数据需要比较复杂的处理的时候经常会用到中间表和正式表模式。数据在中间表中被处理,然后把满足条件的数据转移至正式表,不满足条件的数据保留在中间表中。图14示意了数据从中间表转移到正式表的过程:在完成数据处理之后,需要把中间表temp1flag = 1的数据插入到正式表,并删除中间表temp1flag = 1的数据。


图14. 从中间表向正式表转移数据

因为flag字段不是聚簇索引,所以当对中间表temp1进行删除后,会再磁盘中留下大量碎片,如图15所示。不但会留下那么多的磁盘碎片,而且已删除的数据的空间也不会自动释放。结果是不但浪费磁盘空间,而且查询性能会急剧下降。


图15. 删除操作后的磁盘碎片

咱们可以使用清空表的命令来避免删除操作。除了中间表temp1和正式表,添加辅助临时表temp2。如果temp1中保留的数据flag=0只占有10%,这一优化将显著提升性能。具体步骤如下:

1.         将temp1flag=0的数据,插入到temp2

2.         清空表temp1

alter table temp1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE ;

3.        将temp2中的数据插入temp1



1.3 如何使访问更高效

本小节的内容很大一部分来自《The Art of SQL》这本书,这本书里集合了数据库开发的通用经验。虽然没有局限于具体的DBMS和硬件平台,但是却是一本实践性很强的书。

1.一次连接数据库,做很多事情。直到处理完,才断开连接。

2.一个SQL语句包含尽量多的操作。形象地说:几千个语句,借助游标不断循环,很慢。换成几个语句,处理同样的数据,还是很慢。换成一个语句,解决问题,最好。

3.接近DBMS核心。尽量使用数据库自带的函数。减少自定义函数。因为再聪明的数据库优化器也不认识自定义函数。

4.一个语句不要连接太多的表,建议的上限是5个。

5.将频繁更新的列集中起来:当更新某一行时,DB2 会记录进行更改的所有列,因此将频繁更新的列放到一起可以减少 DB2 的记录工作。这只是一个有关性能的小建议,因此不应为实现它而进行重大的应用程序或数据库设计修改。

6.如果想了解,如何书写SQL语句才能更高效,可参考《The Art of SQL》。书中提出了不少有益的见解。



本文转载自:http://blog.csdn.net/zhaojianmi1/article/details/6601526

共有 人打赏支持
Goopand
粉丝 8
博文 180
码字总数 183387
作品 0
朝阳
常用的DB2命令

启动DB2服务:db2start 关闭DB2服务: db2stop 一、加载数据: 1、 以默认分隔符加载,默认为“,”号 db2 "import from btpoper.txt of del insert into btpoper" 2、 以指定分隔符“|”加载 ...

玛雅牛 ⋅ 2013/01/23 ⋅ 0

db2 command list

工作一个多月了,因为公司要用DB2数据库,所以总是努力去看这方面的书,一段时间来有点体会也总结了一些常用的DB2命令,发出来给大家分享吧!希望对大家会有所帮忙,呵呵。。 启动DB2服务:d...

jiyayun ⋅ 2013/07/19 ⋅ 0

DB2 数据库对象

数据库对象 SQL可分为三大类: 1、 DDL,数据定义语言,用于创建、修改、删除数据库对象 2、 DML,数据操纵语言,用于选择、插入、更新和删除数据库记录 3、 DCL,数据控制语言,用于提供数据...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

DB2投产建库实录

在某小县城银行的某系统终于上线了,蛋疼的DB2,看着客户的的服务器申请单,着实抑郁了一把: 中间件 中间件名称 版本 数据库 数据库类型 ■DB2 □Oracle □Informix □Sql Server 版本 32/6...

子矜 ⋅ 2014/04/22 ⋅ 0

DB2常用命令

1、 打开命令行窗口   #db2cmd 2、 打开控制中心   # db2cmd db2cc 3、 打开命令编辑器  db2cmd db2ce =====操作数据库命令===== 4、 启动数据库实例   #db2start 5、 停止数据库实例  ...

KavenSu ⋅ 2014/04/25 ⋅ 0

DB2 最佳实践: 编写并调优查询语句以优化性能最佳实践

内容提要 通过 “IBM DB2 for Linux, UNIX, and Windows 最佳实践”专题,获得最常用的 DB2 9 产品配置实践指南,并使用这些知识提高 DB2 数据服务器的价值。 这些最佳实践文章给出了最优化方...

钟小华 ⋅ 2013/03/05 ⋅ 0

DB2 常用命令

db2 ? SQL0408N 查看sqlcode对应的出错信息,0408N为sqlcode 备份数据库:在cmd命令行窗口,输入 db2 backup db S1EPDB compress 1、 打开命令行窗口   #db2cmd 2、 打开控制中心   # db2c...

wersdffg ⋅ 2014/09/28 ⋅ 0

深入了解 IBM DB2 高级企业版

简介: IBM® DB2® Advanced Enterprise Server Edition(AESE)是将 IBM DB2 Enterprise Server Edition V9.7 与优化、开发、管理工具打包在一起销售的软件包。这是用于管理 DB2 环境关键方...

IBMdW ⋅ 2011/06/24 ⋅ 1

[转]db2索引设计准则

一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE语句的性能,因为在表中的数据更改时,所有索引都须进行适当的调整。另一方面,对于不需要修改数据的查询(SELECT语句),大量索引有...

inferrrrrr ⋅ 2010/05/11 ⋅ 0

将现有的SQL工作负载迁移至hadoop竟然如此简单!

想迁移现有的数据仓库到Hadoop平台?想在Hadoop上重用其他RDMBS的SQL技能?有何方案能帮助您解决这类问题,答案是IBM Big SQL。 Big SQL是IBM的SQL on Hadoop解决方案,它充分利用了IBM在RDB...

勿忘初心321 ⋅ 2016/08/25 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Docker系列教程28-实战:使用Docker Compose运行ELK

原文:http://www.itmuch.com/docker/28-docker-compose-in-action-elk/,转载请说明出处。 ElasticSearch【存储】 Logtash【日志聚合器】 Kibana【界面】 答案: version: '2'services: ...

周立_ITMuch ⋅ 42分钟前 ⋅ 0

使用快嘉sdkg极速搭建接口模拟系统

在具体项目研发过程中,一旦前后端双方约定好接口,前端和app同事就会希望后台同事可以尽快提供可供对接的接口方便调试,而对后台同事来说定好接口还仅是个开始、设计流程,实现业务逻辑,编...

fastjrun ⋅ 今天 ⋅ 0

PXE/KickStart 无人值守安装

导言 作为中小公司的运维,经常会遇到一些机械式的重复工作,例如:有时公司同时上线几十甚至上百台服务器,而且需要我们在短时间内完成系统安装。 常规的办法有什么? 光盘安装系统 ===> 一...

kangvcar ⋅ 昨天 ⋅ 0

使用Puppeteer撸一个爬虫

Puppeteer是什么 puppeteer是谷歌chrome团队官方开发的一个无界面(Headless)chrome工具。Chrome Headless将成为web应用自动化测试的行业标杆。所以我们很有必要来了解一下它。所谓的无头浏...

小草先森 ⋅ 昨天 ⋅ 0

Java Done Right

* 表示难度较大或理论性较强。 ** 表示难度更大或理论性更强。 【Java语言本身】 基础语法,面向对象,顺序编程,并发编程,网络编程,泛型,注解,lambda(Java8),module(Java9),var(...

风华神使 ⋅ 昨天 ⋅ 0

Linux系统日志

linux 系统日志 /var/log/messages /etc/logrotate.conf 日志切割配置文件 https://my.oschina.net/u/2000675/blog/908189 logrotate 使用详解 dmesg 命令 /var/log/dmesg 日志 last命令,调......

Linux学习笔记 ⋅ 昨天 ⋅ 0

MVC——统一报文格式的异常处理响应

在我们写controller层的时候,常常会有这样的困惑,如果需要返回一个数据是,可能为了统一回去构造一个类似下列的数据格式: { status:true, msg:"保存成功!", data:[]} 而且在写...

alexzhu592 ⋅ 昨天 ⋅ 0

[知乎]SSH框架

网上图书馆管理系统包括管理员管理和图书管理,图书借阅,查询模块等等,网上商城包括前台页面和后台管理页面,两个都是以前别人的实际项目,只是别人用的不是SSH,我把他们改用SSH了,除了S...

颖伙虫 ⋅ 昨天 ⋅ 0

android -------- 打开本地浏览器或指定浏览器加载,打电话,打开第三方app

开发中常常有打开本地浏览器加载url或者指定浏览器加载, 还有打开第三方app, 如 打开高德地图 百度地图等 在Android程序中我们可以通过发送隐式Intent来启动系统默认的浏览器。 如果手机本身...

切切歆语 ⋅ 昨天 ⋅ 0

linux 安装docker

通过以下命令下载安装docker wget -qO- https://get.docker.com | sh 执行以上命令后输出以下内容说明安装成功,注意红框中的内容,docker安装成功后默认只有root能使用,红框中给出的提示是...

haoyuehong ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部