DB2数据库数据统计和分析优化

摘要:以DB2数据库统计和分析系统为例,探讨实际运行中由于各种原因导致的数据库的性能不能达到最佳状态的主要因素,并讨论如何在数据库运行阶段从操作系统和数据库实例级别上调整内存和I/O来达到数据库性能优化的各种技术。

关键词:DB2;数据库优化;统计报表分析

中图分类号:TP311.13 文献标识码:A文章编号:1007-9599 (2010) 01-0000-02

DB2是IBM公司的一款优秀的大型关系数据库,他的性能非常优秀。一个选用了DB2为数据库的应用系统的规模一般比较大,用户数比较多,因此效率对于这样的系统来说是非常重要的。如何设计一个能够满足用户当前与可预见的未来的各项应用要求、性能良好的数据库成为数据库应用系统设计中的一个核心问题。

一、DB2数据库优化理论基础

DB2数据库[1]的优化是指通过对吞吐量、响应时间、硬件、操作系统和应用程序的优化来避免存储器I/O瓶颈、提高CPU利用率[2]和减少资源的竞争。但是我们不能用绝对的数量作为绝对指标来定义DB2数据库的优化。实际上,我们应当用优化前后数据库的各种性能指标的对比来衡量数据库优化的结果。特别是SQL[2]语句的执行速度,SQL语句带来的系统负担,应用的响应速度,甚至应用所服务的终端用户的直接感受都是我们对比的对象。于是数据库实例级的调整,大多用来解决数据库结构性故障,相应的也能解决因为结构性故障带来的普遍性的性能问题。然而我们注意到,用户所真正关心的,切身感受到的单点数据库响应慢的问题往往是不良的SQL语句,过期的统计数据和其导致的不良的执行计划,不良的数据表结构,过渡的触发器使用,不良的应用同步锁造成的。

二、影响DB2数据库性能的主要因素

影响DB2数据库系统运行效率的主要因素有如下几点:

1.硬件性能

高性能的CPU为数据库提供高承载[2]和吞吐能力,来确保充分的I/O并行性,以支持大容量的并发事件;足够的内存,以支持大量并发用户所需的缓冲池、程序和其他共享内存对象;以及足够大的网络带宽以支持工作负载。

2.操作系统(OS)性能

DB2数据库服务器的整体性能很大程度上依赖于操作系统的性能,可以说操作系统是数据库的根本,如果操作系统不能提供优越的性能,那么调整数据库也失去了意义。

3.存储结构的设计

DB2数据库物理文件及逻辑对象的合理分布,将影响数据库的读写速度。优秀的存储结构[4]和合理的存取路径可以使系统在开销最小的情况下达到最佳性能。例如可以通过静态数据和业务数据相分离、将大的对象分割存放在多个磁盘上等措施来加快访问速度、减少I/O竞争[4]。

4.SQL语句优化

SQL是用于存取数据的根本手段。SQL语句的执行效率对数据库系统的性能起着决定性影响。高效的SQL语句能够成倍地提高应用程序能力。一个正确设计的数据库,由于不合理的程序设计及SQL语句任然会带来大多数性能问题。因此,要根据数据操作类型对程序员写的SQL语句进行分类,还应该对数据操作进行跟踪,找到那些最耗费资源和时间的操作,并对这些操作进行分析,找到优化的办法。

5.DB2数据库碎片处理

过多的碎片会在执行数据库的功能时要耗费额外的资源(磁盘I/O[5]、动态扩展[5]、连接块[5]等),并浪费大量的磁盘空间。当两个或多个数据对象在相同的表空间中,会发生区间交叉。为消除区间交叉,将静态的或只有小增长的表放置在一个表空间中,而把动态增长的对象分别放在各自的表空间中。

6.并行性

高速并行执行使得DB2数据库的某些功能由多个服务器进程同时处理成为可能。这些功能包括创建索引、加载数据和恢复数据库等[6]。在数据库查询过程中使用并行机制将大大提高查询速度,减少等待时间。

三、DB2数据库数据统计和分析优化策略

数据库优化技术的方法有很多种,不同的数据库又有着不同的方法。以上讨论了影响数据性能的主要因素,而DB2数据库数据统计和分析优化是基于DB2数据库优化的基础上进行的一种优化。下面讨论DB2数据库数据统计和分析优化的几种主要的方法。

1.表空间设计的优化

由于在DB2数据库中,数据都是存储在表空间中,所以对数据统计和分析的优化首先要对表空间设计和管理的优化。一般来说DB2表空间所跨的驱动器越多、越快,则潜在的性能将越好。另外,通过创建不止一个的用户表空间也是增强性能的好方法。在下面三种情况下,使用多个表空间就是很有用:(1)控制I/O[6],如果这些表空间可以位于不同的驱动器上的话;(2)使用大小不同的页面;(3)控制缓冲池。在DB2数据库数据统计和分析系统建立时,设计良好的数据库管理表空间能够提供更好的性能。如果要以一种连续的方式来访问数据,那么采用更大的页面大小可以获得更好的性能;相反,如果对数据的访问采用的是随机方式,那么最好使用尽可能小的页面大小。

2.调整数据库内存

通常情况下,DB2数据统计和分析系统的数据访问量和访问某一数据的用户数量都很大,对内存资源的使用存在很大的竞争。合理分配内存缓冲区,可以大大加快数据查询速度、减少系统I/O操作,从而提高数据统计和分析系统的性能。

DB2的内存结构由3个内存集组成:实例共享内存、数据库共享内存、应用程序组共享内存,在这里要保证系统有足够的内存资源。为DB2数据库分配更多的内存要以操作系统频繁的换页和交换为代价,他不仅不会产生理想的效果,还将导致计算机系统额外的I/O开销,大大降低DB2数据库系统性能。而内存区设置不合理同样会影响DB2数据库系统的性能,比如在那些随机访问或者很少访问的表之间共享一个缓冲池,就有可能将经常访问的行挤出到磁盘上,这样就会影响系统的效率。

所以对内存缓冲区的合理设置,可以大大加快数据查询速度以及内存区的命中率,提高系统性能。

3.索引的优化[7]

索引也是一种DB2对象,他由一组排好序的键组成,这些键是从相应表中的一个列或多个列抽取出来的。通常索引可以提供指向表中被请求的数据行的直接指针;如果结果集要求的顺序与索引一致,则可以消除排序;如果被请求的列都包含在索引项中,则可以避免不得不读数据行的情况。

通过以下一些原则,可以建立比较高效和合理的索引。

(1)在索引中包括WHERE子句中的所有列,这样,就可以使用索引形成的屏蔽来拒绝结果集中不合格的行。

(2)对于SQL语句中的RODER BY子句中引用到的列,适当的创建索引,这样可以避免排序。

(3)考虑到管理上的开销,应避免在索引中使用多于5个的列。

(4)对于多列索引,将查询中引用最多的列放在定义的前面。

(5)基数较大的列更适合用来做索引。

(6)不要在索引中包含那些经常修改或经常进行插入、删除的列。唯一的例外是主关键字和外来关键字。

(7)不能种“空值(null)”做索引,任何包含null值的列都不会被包含在索引中,任何在WHERE子句中使用is null或is not null的语句优化器是不会使用索引的。

4.SQL语句的优化

应用程序对DB2数据库的操作最终表现为SQL语句对数据库的操作。良好的SQL语句可以被数据库重复使用从而减少分析时间、改善一个系统的性能、对提高数据库内存区的命中率、减少I/O访问等有着非常重要的意义。

在DB2数据统计和分析系统中,数据的物理位置比逻辑位置更重要,因为数据库必须查找数据,以便返回给查询用户。因此,调整SQL的关键是如何使数据查找的路径最简化。通常可以采用下面的方法优化SQL对数据操作的表现。

(1)减少对DB2数据库的查询次数,即减少对系统资源的请求。

(2)使用临时表空间,把表的子集在临时表中进行排序,有助于避免多重排序操作。为了加速SQL查询至少应该创建一个表空间供临时段单独使用。

(3)限制动态SQL的使用,虽然动态SQL很好用,但是即使在SQL共享池中有一个完全相同的查询值,动态SQL也会重新进行语法分析。

(4)避免不带任何条件的SQL语句的执行。没有任何条件的SQL语句在执行过程中要进行全表扫描,这样的扫描往往需要很长时间,因此会大大降低检索效率。

(5)避免相关子查询一个字段的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的字段值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低。因此,应当尽量避免子查询。如果子查询不可避免,那么应在子查询中过滤掉尽可能多的行。

(6)尽量使用相同的或非常类似的SQL语句进行查询,这样不仅充分利用内存中的已经分析的语法树,还能使要查询的数据在缓冲池中命中的可能性也会大大增加。

5.DB2数据统计与分析系统优化分析

结合DB2数据库数据统计和分析系统以及以上所述的优化方法,得出了对DB2数据库统计和分析系统的优化方法。

(1)DB2数据统计与分析系统采用对后续用到的表建立索引的方法,插入之前建立的话,在表插入数据的过程中,索引也随着更新,这样的话需要较大的日志空间,因此速度会比较慢,可以采用不计日志的方式插入;数据插完之后再建立索引的话,该表的日志统计信息没有更新,因此执行计划会很差,用不到索引,更新索引统计信息,这样执行计划会考虑到使用索引,因此系统效率高。

(2)DB2数据统计与分析系统将比较大的表建立在多节点的表空间上,同时建立好索引。假如db2数据仓库每个节点使用两个CPU,4G内存,DIM表空间计划是存放维表的表空间,因此是单节点的。在使用这个表空间的中的表的时候,最多只会用到两个CPU,4G内存,加上其他的表空间也都要用到这两个CPU和这4G内存,因此资源比较有限。DB2数据统计与分析系统将较大的表不放在这个表空间中,而是建立好分区键,放在多节点的表空间中,这样检索这个表的时候32个节点同时检索,最后汇总到0节点上进行展现,系统性能当然更加优越。

(3)DB2数据统计与分析系统中将插入的表使用不计日志的方式插入。数据库为了保证数据的一致性和可回退性,插入、更新或者删除数据的时候要计日志,这样在失败的时候可以回退,但是如果并发较多或者操作非常大的话,会导致争抢日志的情况,导致操作非常缓慢。如果使用不计日志的方式进行插入、更新或者删除操作的话,日志使用极少,但是如果操作失败的话是无法回退的,这样一致性得不到保证,这个表只能删除重建。

(4)为表建立表级锁,尽量减少锁数量的使用。因为数据库的锁的最大数量是有限制的,并且每个锁都要占一定的内存,因此如果锁的数量非常多,使用的内存也就多,导致资源紧张。

(5)建立临时表的时候只插入用到的数据,而不插入用不到的数据。程序中好多地方为了提高速度,将用到的数据先插入到一个临时表中,但是插入了非常多的没有使用的数据,导致临时表也非常大,所以尽可能只向临时表中插入用的到的数据,并且尽可能的使用索引,可以系统性能得到优化。

四、结论

数据库设计受到各种各样因素的制约,有些要求往往是彼此矛盾的。因此,设计结果常常是有得有失。因此,设计者必须根据实际情况,综合应用上述技术,在基本合理的总体设计的基础上,做一些优化调整,力求最大限度地满足用户各种各样的要求,实现数据库的优化设计。

参考文献:

[1]Paulsen A.DB2UDBV8 Performance Tuning Guide.etc,2004;3

[2]Surendra P.A quick reference for tuning DB2 Universal Database EEE.IBMCorporaion,Becverton,2002

[3]McArthur F.IBM DB2 universal database administration guide Ver2 sion 8.IBM Corporation,2004

[4]Darmawan B,Groenewald G,Irving A.Database performance tuning

on A IX. IBM Corporation,January.2003

[5]萨师煊.数据库系统概论[M].高等教育出版社.2003

[6]王能斌.数据库系统原理[M].电子工业出版社.2005

[7]袁长河.Sybase SQLserver 性能优化技术初探[J].计算机系统应用.2001