Sybase数据库性能优化Word文档格式.docx
《Sybase数据库性能优化Word文档格式.docx》由会员分享，可在线阅读，更多相关《Sybase数据库性能优化Word文档格式.docx（9页珍藏版）》请在冰豆网上搜索。
视图
Rules：
规则
Defaults：
缺省
Cursors：
游标
存储过程不可重入，意即每个并发用户调用都会在内存中产生一个拷贝。
Procedure，triggers，andviews当它们被装载到存储过程缓冲中时，被查询优化器优化，建立查询计划。
如果存储过程在缓冲中，被调用时就不需要重新编译。
如果存储过程缓冲太小，存储过程就会经常被其他调入内存的存储过程冲洗掉，当再次被调用时，存储过程又被调入内存，再重新编译，用户请求因此不得不等待。
最严重的情况，如果存储过程缓冲不够，存储过程甚至都不能运行。
所以在内存足够的情况下，存储过程缓冲参数比例尽可能大一些。
1.1.2数据缓冲（DataCache）
数据缓冲用来缓存数据页和索引页，是除去存储过程缓冲，系统其他占用的缓冲外的剩余内存空间。
通过给服务器增加物理内存扩大数据缓冲，是最有效的方法。
当然，如果不能加内存，就只能通过减少存储过程缓冲的比例等方法来扩大数据缓冲了。
配置足够大的数据缓冲可防止其它服务器活动争用高速缓存空间，并加速使用这些表的查询，因为所需页始终都可在高速缓存中找到。
同时，可以考虑将“热”表如：
用户应用程序对其需求较大的表绑定到一个高速缓存上，而表上的索引绑定到其它高速缓存，以提高并发性。
具体做法如下：
创建命名缓存
sp_cacheconfigcache_name，”size[P|K|M|G]”
例如创建一个10MB的命名缓存pubs_cache：
sp_cacheconfigpubs_cache，”10M”
把表绑定到指定的命名缓存：
sp_bindcachecache_name，dbname[，[owner.]table_name[，indexname|”textonly”]]例如把titles表绑定到上面刚建的命名缓存中：
sp_bindcachepubs_cache，pubs2..titles
注意：
每开辟一个缓冲占用16K的系统内存，应根据服务器的内存大小来定义所要开的数据缓冲的个数。
1.1.3tempdb数据库的优化
缺省情况下，tempdb数据库是放置在master设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注，缺省情况下，用于tempdb的system、default和logsegment段在主设备上分配了2MB空间。
将第二个设备分配给tempdb后，即可在default和logsegment段中将主设备删除。
使用这种方式，可以确保tempdb中的工作表和其它临时表不会和主设备上的其它使用相互争用。
优化tempdb数据库有以下步骤：
第一步：
调整临时库的位置
tempdb数据库缺省放在master设备上，将临时数据库发在分离的设备上是更可取的。
1）初始化一个用来存放临时数据库的设备（在SQLAdvantage中）
diskinit
name="
tempdb_dev"
physname="
c:
\sybase\\tempdb.dat"
vdevno=3,
size=10240
（注意：
如果将tempdb数据库放在多个设备上，需初始化多个数据库设备）
2）将临时数据库扩展到该一个设备上
alterdatabasetempdbontempdb_dev=3
3）打开tempdb数据库，从段上删除master设备
sp_dropsegment"
default"
tempdb,master
sp_dropsegmentlogsegment,tempdb,master
4）发出如下命令，检查default段中是否不再包含master设备
selectdbid,name,segmapfromsysusages,sysdevices
wheresysdevices.low<
=syusages.size+vstart
andsysdevices.high>
=sysusages.size+vstart-1
anddbid=2
and（status=2orstatus=3）说明：
若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。
第二步：
将临时数据库与高速缓冲进行绑定。
由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：
1、创建命名高速缓存
sp_cacheconfig“tempdb_cache”,”10m”,”mixed”
2、重新启动server
3、捆绑临时数据库到tempdb_cache高速缓存
sp_bindcache“tempdb_cachet”em,pdb
4、若有大的I/O，配置内存池
第三步：
优化临时表大多数临时表的使用是简单的，很少需要优化。
但需要对临时表进行复杂的访问则应通过使用多个过程
或批处理来把表的创建和索引分开。
以下两种技术可以改善临时表的优化（系统中有auths和article表）
1在临时表上创建索引
1）临时表必须存在
2）统计页必须存在（即不能在空表上创建索引）
2把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息下面的这个过程需要进行优化：
createprocbase_proc
as
select*into#huge_resultfromauths
select*fromarticle,#huge_resultwherearticle.author_code=
#huge_result.author_codeandsex=”0”使用两个过程可以得到更好的性能
1）createprocbase_proc
select*
into#huge_result
fromauths
execselect_proc
2）createprocselect_proc
select*fromarticle,#huge_result
wherearticle.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操作。
把一个表放在某个物理设备上，再通过sqlserver的段把它的部分簇索引放在一个不同的物理设备上，这样能提高性能。
尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做的好处更加明显。
用sqlserver段把一个频繁使用的大表分割开，并放在多个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。
因为有多个磁头在查找，所以数据分离也能提高性能。
用sqlserver段把文本或图像列的数据存放在一个单独的物理设备上可以提高性能。
一个专用的智能
型的控制器能进一步提高性能。
3应用程序级调优
3.1合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是提高查询效率。
SQLServer采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。
因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。
所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。
通常要想建立合理的索引需遵循下面所介绍的一些原则。
对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。
只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。
因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。
想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。
如果1个子句有可用的索引，那么优化器就会为它确定选择性。
在设计过程中，要根据查询设计准则仔细检查所有的查询，以查询的优化特点为基础设计索引。
索引的设计通常有以下原则：
（1）比较窄的索引具有比较高的效率。
对于比较窄的索引来说，每页上能存放较多的索引行，而且索引的级别也较少。
所以，缓存中能放置更多的索引页，这样也减少了I/O操作。
（2）SQLServer优化器能分析大量的索引和合并可能性。
所以与较少的宽索引相比，较多的窄索引能向优化器提供更多的选择。
但是不要保留不必要的索引，因为它们将增加存储和维护的开支。
对于复合索引、组合索引或多列索引，SQLServer优化器只保留最重要的列的分布统计信息，这样，索引的第1列应该有很大的选择性。
（3）表上的索引过多会影响UPDATE、INSERT和DELETE的性能，因为所有的索引都必须做相应的调整。
另外，所有的分页操作都被记录在日志中，这也会增加I/O操作。
（4）对1个经常被更新的列建立索引，会严重影响性能。
（5）由于存储开支和I/O操作方面的原因，较小的自组索引比较大的索引性能更好一些。
但它的缺点是要维护自组的列。
（6）尽量分析出每一个重要查询的使用频度，这样可以找出使用最多的索引，然后可以先对这些索引进行适当的优化。
（7）查询中的WHERE子句中的任何列都很可能是个索引列，因为优化器重点处理这个子句。
（8）对小于1个范围的小型表进行索引是不划算的，因为对于小表来说表扫描往往更快而且费用低。
（9）与“ORDERBY或“GROUPBY—起使用的列一般适于做分族索引。
如果“ORDERB”命令中用到的
列上有分簇索引，那么就不会再生成1个工作表了，因为行已经排序了。
“GROUPBY”命令则一定产生1
个工作表。
（10）分簇索引不应该构造在经常变化的列上，因为这会引起整行的移动。
在实现大型交易处理系统时，尤其要注意这一点，因为这些系统中数据往往是频繁变化的。
3.2避免或简化排序应当尽量简化或避免对大型表进行重复的排序。
当能够利用索引自动以适当的次序产生输出时，优化
器就避免了排序这个步骤。
为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。
如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。
3.3消除对大型表行数据的顺序存取在嵌套查询中，表的顺序存取对查询效率可能产生致命的影响。
我们有时可以使用并集来避免顺序存
取。
尽管也许在所有的检查列上都有索引，但某些形式的where子句会强迫优化器使用顺序存取，这一点也应注意。
3.4避免相关子查询
如果一个列同时在主查询和where子句中出现，很可能当主查询中的列值改变之后，子查询必须重新查询一次。
而且查询嵌套层次越多，效率越低，因此应当尽量避免子查询。
如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
3.5避免困难的正规表达式
mathes和like关键字支持通配符匹配，但这种匹配特别耗时。
例如：
select*fromcustomerwherezipcodelike“98__，_即”使在zipcode字段上已建立了索引，在这种情况下也还是采用顺序
扫描的方式。
如果把语句改为：
select*fromcustomerwherezipcode>
“98000”，在执行查询时就
会利用索引来查询，显然会大大提高速度。
3.6使用临时表加速查询把表的一个子集进行排序并创建临时表，有时能加速查询。
它有助于避免多重排序操作，而且在其他
方面还能简化优化器的工作。
临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘i/o，所以查询工作量可以得到大幅减少。
但要注意，临时表创建后不会反映主表的修改。
在主表中数据频繁修改的情况下，注意不要丢失数据。
4Sybase调优工具
在分析Sybase数据库的性能时，常要用到一些数据库系统本身提供的性能调优工具，以下是最常用的几个系统存储过程：
存储过程名称
主要功能
Spsysmon
企业级系统性能报告工具
Splock
查看锁的情况
Sp_who
查看线程的活动情况
Spprocqmode
存储过程的查询处理模式
Spconfigure
配置SQLServe系统级参数
Spestspace
估计创建一个表需要的空间和时间
Spspaccused
估计表的总行数及表和索引占用的空间
Spmonitor
监视CPUI/O的统计活动情况
可以从18个方面了解在用系统性能状况，并在适当的时候利用环境参数进行性能调优：
1、内核管理（kernal）2、应用管理（appmgmt）3、数据缓存管理（dcache）
4、ESP管理（esp）5、索引管理（indexmgmt）6、锁管理（locks）
7、内存管理（memory）8、元数据高速缓存管理（mdcache）9、任务管理（taskmgmt）
10、监视器访问SQL的执行（monaccess）11、网络I/O管理（netio）
12、并行查询管理（parallel）13、过程缓存管理（pcache）14、恢复管理（recovery）
15、事务管理（xactmgmt）16、事务概要（xactsum）17、磁盘I/O管理（diskio）
18、工作进程管理（wpm）
括号后英文短词是该模块参数。
在此简要分析一下sp_sysmon对AdaptiveServer系统运行情况的影响，有利于更好地熟悉系统性能，更为有效地进行系统管理，合理地利用和配置系统资源，达到系统性能调优的目的。
这里要特别提一下sp_sysmon存储过程，通过它可以得到数据库系统的性能基准报告，但只有在比较稳定的状态下产生时，方可作为参考和对照的依据。
执行步骤：
1在isql中输入sp_sysmon'
egin_sampel'
命令，点go采样开始
2过一段时间后，输入sp_sysmon'
nd_sampel'
'
memory”就显示出内存使用的相关信息
"
IfipLlfiwifldlDW
sp_sysmDn"
end_sampilEffm曰rncir/'
siaii&
tic^Clearedal:
as.300T10144:
27
StallstiesSampledatSap05.200710:
44:
42SamipleInlevvaI00:
00:
15
执行结果集的每列信息提示:
persec:
采样期间每秒的平均值
perxact:
采样期间每提交一个事务的平均值
count:
采样期间每秒的总计值
%oftotal:
占总数的百分比，根据不同情况各有不同
根据相关的信息，结合服务器、数据库的一些参数，对相关的性能情况进行分析，并做岀调整等。
注：
Sybase数据库的一些查询优化方法与SQLServer2000相似，在此就不在多述，如有需要请参考
前面的介绍
升级会员 