Sybase数据库性能优化.doc

Sybase数据库性能优化在现有软硬件条件下，充分发挥数据库系统的潜能是DBA追求的最高境界，然而，数据库性能调优是一个非常复杂的问题，不仅需要精通数据库的理论知识，更需要逐渐积累实践经验。这里主要针对Sybase数据库简要介绍一下如何进行调优，及调优时所要注意的事项。Sybase数据库系统的性能的优化，是一项长期且受诸多因素影响的工作，它可划分为以下4个层次： SQL Server级：包括对内存的合理分配，锁操作和临时表的使用，与系统配置关联的磁盘的I/O性能。 数据库设计级：包括数据库对象的设计，索引的创建，表中数据类型的选择，数据库设备的分配及使用。 应用程序级：包括T_SQL查询语句的优化，应用级封锁，事务和游标的使用。 操作系统级：包括硬件、操作系统和网络对总体性能的影响。 在数据库应用系统的管理维护中，运行环境引起的性能劣化只有通过硬件的升级才能得到优化，在系统硬件配置和网络设计确定的情况下，影响系统性能的主要是其他三个层次方面的原因。在此就这三个方面进行简要讨论、总结。1 SQL Server级的调优 1.1 管理共享内存数据库性能优化的首要方面是最优管理内存。数据库占用的共享内存分成数据缓冲（data cache）、存储过程缓冲（Procedure cache）等几块。在isql 下使用 sp_configure cache 可以看到存储过程缓冲所占百分比（procedure cache percent），整个数据缓冲大小（total data cache size） 等参数。 CQ%SC ( 1.1.1 存储过程缓冲（procedure cache）存储过程缓冲保持以下对象的查询计划： x%mD6Ud 1.1.3 tempdb数据库的优化缺省情况下，tempdb数据库是放置在master设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注，缺省情况下，用于tempdb的system、default和logsegment段在主设备上分配了2MB空间。将第二个设备分配给tempdb后，即可在default和logsegment段中将主设备删除。使用这种方式，可以确保tempdb中的工作表和其它临时表不会和主设备上的其它使用相互争用。优化tempdb数据库有以下步骤：第一步：调整临时库的位置tempdb数据库缺省放在master设备上，将临时数据库发在分离的设备上是更可取的。1）初始化一个用来存放临时数据库的设备（在 SQL Advantage中）diskinitname=tempdb_dev,physname=c:sybasetempdb.dat,vdevno=3,size=10240(注意：如果将tempdb数据库放在多个设备上，需初始化多个数据库设备)2)将临时数据库扩展到该一个设备上alterdatabasetempdbontempdb_dev=33)打开tempdb数据库，从段上删除master设备sp_dropsegmentdefault,tempdb,mastersp_dropsegmentlogsegment,tempdb,master4)发出如下命令，检查default段中是否不再包含master设备selectdbid,name,segmapfromsysusages,sysdeviceswheresysdevices.low=sysusages.size+vstart-1anddbid=2and(status=2orstatus=3)说明：若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。第二步：将临时数据库与高速缓冲进行绑定。由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：1、创建命名高速缓存sp_cacheconfig“tempdb_cache”,”10m”,”mixed”2、重新启动server3、捆绑临时数据库到tempdb_cache高速缓存sp_bindcache“tempdb_cache”,tempdb4、若有大的I/O，配置内存池第三步：优化临时表大多数临时表的使用是简单的，很少需要优化。但需要对临时表进行复杂的访问则应通过使用多个过程或批处理来把表的创建和索引分开。以下两种技术可以改善临时表的优化(系统中有auths和article表)1 在临时表上创建索引 1）临时表必须存在 2）统计页必须存在（即不能在空表上创建索引）2 把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息下面的这个过程需要进行优化：createprocbase_procasselect*into#huge_resultfromauthsselect*fromarticle,#huge_resultwherearticle.author_code=#huge_result.author_codeandsex=”0”使用两个过程可以得到更好的性能1）createprocbase_procasselect*into#huge_resultfromauthsexecselect_proc2)createprocselect_procasselect*fromarticle,#huge_resultwherearticle.author_code=#huge_result.author_codeandsex=”0” 说明：在同一个存储过程或批处理中，创建并使用一个表时，查询优化器无法决定这个表的大小。2 数据库设计级的调优实现Sybase数据库的优化，首先要有一个好的数据库设计方案。在实际工作中，许多Sybase方案往往是由于数据库设计得不好导致性能很差。实现良好的数据库设计必须考虑这些问题: 2.1 逻辑数据库规范化问题一般来说，逻辑数据库设计会满足规范化的前3级标准：第1规范：没有重复的组或多值的列;第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分;第3规范: 一个非关键字段不能依赖于另一个非关键字段。 遵守这些规则的设计会产生较少的列和更多的表，因而就减少了数据冗余，也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理，这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能，非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行，通常使用以下方法来提高性能。1.如果规范化设计产生了4路或更多路合并关系，就可以考虑在数据库实体(表)中加入重复属性(列)。2.常用的计算字段(如总计、最大值等)可以考虑存储到数据库实体中。比如某一个项目的计划管理系统中有计划表，其字段为:项目编号、年初计划、二次计划、调整计划、补列计划，而计划总数(年初计划+二次计划+调整计划+补列计划)是用户经常需要在查询和报表中用到的，在表的记录量很大时，有必要把计划总数作为1个独立的字段加入到表中。这里可以采用触发器以在客户端保持数据的一致性。3.重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是:(1)把1个实体(表)分割成2个表(把所有的属性分成2组)。这样就把频繁被访问的数据同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利于并行处理，并将产生列数较少的表。(2)把1个实体(表)分割成2个表(把所有的行分成2组)。这种方法适用于那些将包含大量数据的实体(表)。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组(部门、销售分区、地理区域等)访问的，那么这种方法也是很有好处的。2.2 生成物理数据库要想正确选择基本物理实现策略，必须了解和利用好数据库访问格式和硬件资源的操作特点，特别是内存和磁盘子系统i/o。以下是一些常用技巧：与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地读取，而且可以在一个数据页上放置更多的数据行，因而也就减少了i/o操作。把一个表放在某个物理设备上，再通过sql server的段把它的部分簇索引放在一个不同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做的好处更加明显。用sql server段把一个频繁使用的大表分割开，并放在多个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。用sql server段把文本或图像列的数据存放在一个单独的物理设备上可以提高性能。一个专用的智能型的控制器能进一步提高性能。3 应用程序级调优3.1合理使用索引索引是数据库中重要的数据结构，它的根本目的就是提高查询效率。SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。通常要想建立合理的索引需遵循下面所介绍的一些原则。 对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。如果1个子句有可用的索引，那么优化器就会为它确定选择性。在设计过程中，要根据查询设计准则仔细检查所有的查询，以查询的优化特点为基础设计索引。索引的设计通常有以下原则：(1) 比较窄的索引具有比较高的效率。对于比较窄的索引来说，每页上能存放较多的索引行，而且索引的级别也较少。所以，缓存中能放置更多的索引页，这样也减少了I/O操作。(2) SQL Server优化器能分析大量的索引和合并可能性。所以与较少的宽索引相比，较多的窄索引能向优化器提供更多的选择。但是不要保留不必要的索引，因为它们将增加存储和维护的开支。对于复合索引、组合索引或多列索引，SQL Server优化器只保留最重要的列的分布统计信息，这样，索引的第1列应该有很大的选择性。(3) 表上的索引过多会影响UPDATE、INSERT和DELETE的性能，因为所有的索引都必须做相应的调整。另外，所有的分页操作都被记录在日志中，这也会增加I/O操作。(4) 对1个经常被更新的列建立索引，会严重影响性能。(5) 由于存储开支和I/O操作方面的原因，较小的自组索引比较大的索引性能更好一些。但它的缺点是要维护自组的列。(6) 尽量分析出每一个重要查询的使用频度，这样可以找出使用最多的索引，然后可以先对这些索引进行适当的优化。(7) 查询中的WHERE子句中的任何列都很可能是个索引列，因为优化器重点处理这个子句。(8) 对小于1个范围的小型表进行索引是不划算的，因为对于小表来说表扫描往往更快而且费用低。(9) 与“ORDER BY”或“GROUP BY”一起使用的列一般适于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引，那么就不会再生成1个工作表了，因为行已经排序了。“GROUP BY”命令则一定产生1个工作表。(10) 分簇索引不应该构造在经常变化的列上，因为这会引起整行的移动。在实现大型交易处理系统时，尤其要注意这一点，因为这些系统中数据往往是频繁变化的。3.2 避免或简化排序应当尽量简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序这个步骤。为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。3.3消除对大型表行数据的顺序存取在嵌套查询中，表的顺序存取对查询效率可能产生致命的影响。我们有时可以使用并集来避免顺序存取。尽管也许在所有的检查列上都有索引，但某些形式的where子句会强迫优化器使用顺序存取，这一点也应注意。3.4 避免相关子查询如果一个列同时在主查询和where子句中出现，很可能当主查询中的列值改变之后，子查询必须重新查询一次。而且查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。3.5 避免困难的正规表达式mathes和like关键字支持通配符匹配，但这种匹配特别耗时。例如：select * from customer where zipcode like “98_ _ _”，即使在zipcode字段上已建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为：select * from customer where zipcode “98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。3.6 使用临时表加速查询把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘i/o，所以查询工作量可以得到大幅减少。但要注意，临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。4 Sybase调优工具 在分析Sybase数据库的性能时，常要用到一些数据库系统本身提供的性能调优工具，以下是最常用的几个系统存储过程： 存储过程名称主要功能Sp_sysmon企业级系统性能报告工具Sp_lock查看锁的情况Sp_who查看线程的活动情况Sp_procqmode存储过程的查询处理模式Sp_configure配置SQL Serve 系统级参数Sp_estspace估计创建一个表需要的空间和时间Sp_spaccused估计表的总行数及表和索引占用的空间Sp_monitor监视CPU I/O的统计活动情况可以从18个方面了解在用系统性能状况，并在适当的时候利用环境参数进行性能调优：1、内核管理（kernal） 2、应用管理（appmgmt） 3、数据缓存管理（dcache）4、ESP管理（esp） 5、索引管理（indexmgmt） 6、锁管理（locks）7、内存管理（memory） 8、元数据