ehxz 发表于 2006-12-28 16:08:11

基于DB2的数据库应用系统的性能优化

<p style="TEXT-INDENT: 2em"><strong>摘要</strong> 结合DB2的使用经验,从数据库设计、查询优化、并发控制、客户/服务器模式四个方面来讨论数据库应用系统性能优化的一些原则、方法等。 </p><p style="TEXT-INDENT: 2em"><strong>关键词</strong> DB2 性能优化 数据库设计 查询优化 并发控制 C/S模式 </p><p style="TEXT-INDENT: 2em"><strong>引言</strong> </p><p style="TEXT-INDENT: 2em">DB2是一种高性能的大型关系数据库管理系统,广泛的应用在客户/服务器体系结构中。评价系统性能优化的标准有:吞吐量、响应时间、并行能力等。本文从数据库的设计、查询的优化、并发控制以及客户/服务器模式这四个角度来讨论优化系统性能。 </p><p style="TEXT-INDENT: 2em"><strong>设计数据库</strong> </p><p style="TEXT-INDENT: 2em"><strong>1&#46; 熟悉业务系统</strong> </p><p style="TEXT-INDENT: 2em">对业务系统的熟悉程度对整个数据库系统的性能有很大影响,一个对业务不熟悉的设计人员,尽管有丰富的数据库知识,也很难设计出性能最佳的数据库应用系统。 </p><p style="TEXT-INDENT: 2em"><strong>2&#46; 规范化与非规范化</strong> </p><p style="TEXT-INDENT: 2em">数据库被规范化后,减少了数据冗余,数据量变小,数据行变窄。这样DB2的每一页可以包括更多行,那么每一区里的数据量更多,从而加速表的扫描,改进了单个表的查询性能。但是,当查询涉及多个表的时候,需要用很多连接操作把信息从各个表中组合在一起,导致更高的CPU和I/O花销。那么,有很多时候需要在规范化和非规范化之间保持平衡,用适当的冗余信息来减少系统开销,用空间代价来换取时间代价。有订单信息表OrderDetail,它里面记录了投递员信息,收款员信息,物品信息,价格策略,客户信息…&#46;&#46;这些信息分别在投递员信息表、收款员信息表、物品信息表、价格策略表、客户信息表中存放。如果按照规范化的要求,OrderDetail查询时就必须要与这么多个表进行连接或者嵌套查询。如果OrderDetail表中的数据量是在百万级的,那么一次查询所需要的时间可能会达到好几个小时。事实上,只要在设计时保证数据的逻辑有效性,很多信息都可以直接冗余在OrderDetail表中,这些冗余的数据能够极大的提高查询的效率,从而减少CPU和I/O操作。 </p><p style="TEXT-INDENT: 2em"><strong>3&#46; 数据条带化</strong> </p><p style="TEXT-INDENT: 2em">如果一个表的记录条数超过一定的规模,那么最基本的查询操作也会受到影响,需要将该表根据日期水平划分,把最近、最经常用的数据和历史的、不经常用的数据划分开来,或是根据地理位置、部门等等进行划分。还有一种划分方式――垂直划分,即把一个属性列很多的表分割成好几个小表,比如把经常用到的属性放在一个表里,不经常用到的属性放在另一个表里,这样可以加快表的扫描,提高效率。 </p><p style="TEXT-INDENT: 2em"><strong>4&#46; 选择数据类型</strong> </p><p style="TEXT-INDENT: 2em">对每一属性选择什么样的数据类型很大程度上依据表的要求,但是在不违背表要求的前提下,选择适当的数据类型可以提高系统性能。比如有text列存放一本书的信息,用BLOB而不是character(1024),BLOB存放的是指针或者文件参照变量,真正的文本信息可以放在数据库之外,从而减少数据库存储空间,使得程序运行的速度提高。DB2提供了UDT(User Defined Datatypes)功能,用户可以根据自己的需要定义自己的数据类型。 </p><p style="TEXT-INDENT: 2em"><strong>5&#46; 选择索引</strong> </p><p style="TEXT-INDENT: 2em">索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。使用索引可以快速、直接、有序的存取数据。索引的建立虽然加快了查询,另一方面却将低了数据更新的速度,因为新数据不仅要增加到表中,也要增加到索引中。另外,索引还需要额外的磁盘空间和维护开销。因此,要合理使用索引: </p><p style="TEXT-INDENT: 2em">●在经常进行连接,但是没有指定为外键的属性列上建立索引。 </p><p style="TEXT-INDENT: 2em">●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。按索引来排序或分组,可以提高效率。 </p><p style="TEXT-INDENT: 2em">●在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。 </p><p style="TEXT-INDENT: 2em">●如果待排序的列有多个,可以在这些列上建立复合索引(compound index),即索引由多个字段复合而成。 </p><p style="TEXT-INDENT: 2em"><strong>查询优化</strong> </p><p style="TEXT-INDENT: 2em">现在的数据库产品在系统查询优化方面已经做得越来越好,但由于用户提交的SQL语句是系统优化的基础,很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效,因此用户所写语句的优劣至关重要。下面重点说明改善用户查询计划的解决方案。 </p><p style="TEXT-INDENT: 2em">1. 排序 </p><p style="TEXT-INDENT: 2em">在很多时候,应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,可以避免排序的步骤,当以下的情况发生时,排序就不能省略: </p><p style="TEXT-INDENT: 2em">●索引中不包括一个或几个待排序的列; </p><p style="TEXT-INDENT: 2em">●group by或order by子句中列的次序与索引的次序不一样; </p><p style="TEXT-INDENT: 2em">●排序的列来自不同的表。 </p><p style="TEXT-INDENT: 2em">为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表,尽管有时可能影响表的规范化,但相对于效率的提高是值得的。如果排序不可避免,那么应当试图简化它,如缩小排序列的范围等。 </p><p style="TEXT-INDENT: 2em">2. 主键 </p><p style="TEXT-INDENT: 2em">主键用整型会极大的提高查询效率,而字符型的比较开销要比整型的比较开销大很多,用字符型数据作主键会使数据插入、更新与查询的效率降低。数据量小的时候这点降低可能不会被注意,可是当数据量大的时候,小的改进也能够提高系统的响应速度。 </p><p style="TEXT-INDENT: 2em">3. 嵌套查询 </p><p style="TEXT-INDENT: 2em">在SQL语言中,一个查询块可以作为另一个查询块中谓词的一个操作数。因此,SQL查询可以层层嵌套。例如在一个大型分布式数据库系统中,有订单表Order、订单信息表OrderDetail,如果需要两表关联查询: </p><p style="TEXT-INDENT: 2em"></p><center><ccid_nobr /></ccid_nobr /><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="#000000" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code /></ccid_code />SELECT CreateUser
FROM Order
WHERE OrderNo IN
( SELECT OrderNo
FROM OrderDetail
WHERE Price=0&#46;5)</pre></td></tr></tbody></table></center><p style="TEXT-INDENT: 2em">在这个查询中,找出报纸单价为0&#46;5元的收订员名单。下层查询返回一组值给上层查询,然后由上层查询块再根据下层块提供的值继续查询。在这种嵌套查询中,对上层查询的每一个值OrderNo,下层查询都要对表OrderDetail进行全部扫描,执行效率显然不会高。在该查询中,有2层嵌套,如果每层都查询1000行,那么这个查询就要查询100万行数据。在系统开销中,对表Order的扫描占82%,对表OrderDetail的搜索占16%。如果我们用连接来代替,即: </p><p style="TEXT-INDENT: 2em"></p><center><ccid_nobr /></ccid_nobr /><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="#000000" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code /></ccid_code />SELECT CreateUser
FROM Order,OrderDetail
WHERE Order&#46;OrderNo=OrderDetail&#46;OrderNo AND Praice=0&#46;5</pre></td></tr></tbody></table></center><p style="TEXT-INDENT: 2em">那么对表Order的扫描占74%,对表OrderDetail的搜索占14%。 </p><p style="TEXT-INDENT: 2em">而且,一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。 </p><p style="TEXT-INDENT: 2em">4. 通配符 </p><p style="TEXT-INDENT: 2em">在SQL语句中,LIKE关键字支持通配符匹配,但这种匹配特别耗费时间。例如:SELECT * FROM Order WHERE CreateUser LIKE ‘M_ _ _’ 。即使在CreateUser字段上建立了索引,在这种情况下也还是采用顺序扫描的方式,Order表中有1000条记录,就需要比较1000次。如果把语句改为SELECT * FROM Order WHERE CreateUser >’M’ AND CreateUser <’N’,在执行查询时就会利用索引来查询,显然会大大提高速度。 </p><p style="TEXT-INDENT: 2em">5. distinct </p><p style="TEXT-INDENT: 2em">使用distinct是为了保证在结果集中不出现重复值,但是distinct会产生一张工作表,并进行排序来删除重复记录,这会大大增加查询和I/O的操作次数。因此应当避免使用distinct关键字。 </p><p style="TEXT-INDENT: 2em">6. 负逻辑 </p><p style="TEXT-INDENT: 2em">负逻辑如!=、<>、not in等,都会导致DB2用表扫描来完成查询。当表较大时,会严重影响系统性能,可以用别的操作来代替。 </p><p style="TEXT-INDENT: 2em">7. 临时表 </p><p style="TEXT-INDENT: 2em">使用临时表时数据库会在磁盘中建立相应的数据结构,因为内存的访问速度远远大于外部存储器的访问速度,在复杂查询中使用临时表时,中间结果会被导入到临时表中,这种磁盘操作会大大降低查询效率。另外,在分布式系统中,临时表的使用还会带来多个查询进程之间的同步问题。所以,在进行复杂查询时最好不要使用临时表。 </p><p style="TEXT-INDENT: 2em">8. 存储过程 </p><p style="TEXT-INDENT: 2em">DB2中的Stored Procedure Builder可以产生存储过程,运行并测试存储过程。存储过程可以包含巨大而复杂的查询或SQL操作,经过编译后存储在DB2数据库中。用户在多次使用同样的SQL操作时,可以先把这些SQL操作做成存储过程,在需要用到的地方直接引用其名字进行调用。存储过程在第一次执行时建立优化的查询方案,DB2将查询方案保存在高速缓存里,以后调用运行时可以直接从高速缓存执行,省去了优化和编译的阶段,节省了执行时间,从而提高效率和系统利用率。 </p><p style="TEXT-INDENT: 2em">最优的查询方案按照某些标准选择往往不可行,要根据实际的要求和具体情况,通过比较进行选择。DB2提供的Query Patroller可以对不同的查询方案的查询代价进行比较,通过追踪查询语句,返回查询不同阶段的系统开销,从而作出最佳选择。DB2提供的Performance Monitor也对整个数据库系统的性能进行监控,包括I/O时间、查询次数、排序时间、同步读写时间等等。 </p><p style="TEXT-INDENT: 2em">数据库系统的并发控制也能影响系统性能。多个用户的同时操作可能导致数据的不一致性,DB2为了防止同时修改造成数据丢失和访问未被提交的数据,以及数据的保护读,采用Lock机制来实现控制。 </p><p style="TEXT-INDENT: 2em">DB2中可以对表空间、表、表列和索引加锁。锁的粒度越大,锁越简单,开销小,并发度低;粒度小,锁机制复杂,开销大,并发度高。大型系统在并发处理中如果遇到所要分配的资源处于锁定状态,系统会把进程挂起等待。如果一个很耗时的查询操作工作于一个经常使用的表上,此时使用表一级锁,意味着整个系统都要等待你的查询结束以后才能够继续运行。所以在复杂查询中,尽量避免使用表一级锁。如果有这一类的需要该怎么办呢?可以利用视图来解决这一类问题。视图避免了对表的直接操作,同时有能够保证数据库的高效运转。 </p>
页: [1]
查看完整版本: 基于DB2的数据库应用系统的性能优化