sybase数据库优化

1. 1   数据库安装的优化  

2. 1.1     应用数据库使用裸设备  

3. 若在UNIX（UNIXWARE）操作系统下安装数据库服务器，请将SYBASE应用数据库的设备(device)安装成裸设备。即在创建应用数据库设备（如：IVSP，DB160，NAP2000等）时用裸设备，把文件名指向 /dev/dsk/ 子目录下的相应文件。系统数据库设备（如:master等）仍然指向文件系统。  

4. 1.2     补丁程序  

5. 完成SYBASE数据库的安装，请注意原版的SYBASE软件都会带有最新的补丁，一定要把补丁打上，否则会出现一些莫名其妙的问题。FOR NT 版的补丁是一个ZIP文件，解压至C:\SYBASE子目录即可。  

6. 1.3     安装 SYBASE 在线帮助  

7. 在安装好 SYBASE 后，在安装目录下有一文件：../scripts/ins_syn_sql，在服务器上执行该脚本：  

8. Sybase for Unix版：     ./isql -Usa -P -i../scripts/ins_syn_sql  

9. Sybase for Winnt版：    isql -Usa -P -i\sybase\scripts\ins_syn_sql  

10. 执行完毕后，即可在任意的 SYBASE 客户端上连接上 SQL SERVER ，在线取得任意命令的帮助：  

11.     sp_syntax "关键字"  

12. 如：  sp_syntax "alter"   即可列出所有包含"alter"字符的命令  

13. 2   数据库配置的优化  

14. 2.1     优化master数据库  

15. 首先加大master设备空间，初始默认为30M，加大为150M。然后加大master数据库空间，默认数据段和日志段各为5M大小，建议改为数据段100M，日志段50M。  

16. alter database master on master=95  

17. 2.2     优化tempdb数据库  

18. 首先创建tempdb设备，分配给tempdb数据库，默认tempdb数据库数据段和日志段各为2M大小，并创建在master设备上，建议改为数据段200M，日志段50M，创建在tempdb设备上。  

19. alter database tempdb on tempdb=200  

20.   

21. SQL SEVRER所有用户都共享工作表和临时表的tempdb数据库，tempdb主要瓶颈是磁盘I/0。解决办法是把tempdb放在更快的设备上。在UNIX环境中，把tempdb放在文件系统中而不用原始的设备。由于tempdb在创建数据时，自动在master设备上创建为2M的数据库，为了减少冲突，最好的办法是把tempdb从master设备中移走。为了达到上述目的，可采用如下办法实现：  

22.   

23. 1：在单用户状态下启动SQL SERVER  

24. 启动单用户方法：dataserver -dmaster.dat -m  

25. 2：以sa登录  

26. 3：在文件系统中创建一个哑数据库。  

27. 4：删除sysusages和 sysdatabase表中对现有tempdb数据库的引用  

28. 5：获取哑数据库的数据库ID，相应修改sysusages和 ysdatabase表对tempdb的引用  

29. 6：重新启动数据库  

30. 以在newdevice中创建200M的tempdb数据库为例，执行过程如下：  

31. create database newtemp on newdevice=200  

32. go /* 创建新的数据库 */  

33. begin tran  

34. go /* 开始事务，防止操作错误时破坏整个SQL SERVER的运行*/  

35. delete sysusages where dbid = 2  

36. delete sysdatabases where dbid = 2  

37. go /*删除系统表对tempdb的引用，只能在单用户状态下执行*/  

38. select dbid from sysdatabases where name = ‘newtemp’  

39.   

40. go /*获取newtemp数据库ID，假定为10*/  

41.   

42. update sysusages set dbid = 2 where dbid=10  

43.   

44. update sysdatabases set name=’tempdb’,dbid=2 where name=’newtemp’  

45.   

46. go /*修改数据库的引用，对newtemp的引用改为对tempdb的引用*/  

47.   

48. select name,dbid, from sysdatabases where name = ‘tempdb’  

49.   

50. select * from sysusages where dbid = 2  

51.   

52. go /*测试修改是否正确，正确则提交，否则可用rollback回退*/  

53.   

54. commit tran  

55.   

56. go /*修改成功，重新启动系统*/  

57.   

58. 这种方法只对tempdb有效，其他数据库不能采用这种方法。因为在SQL SERVER启动时，tempdb每次都重新初始化。  

59. 2.3     优化系统参数  

60. 以下参数为标准建议值，可根据实际情况修改。  

61. 优化系统参数的SQL脚本    注释  

62. sp_configure 'total memory', 100000 优化数据库的内存，应根据不同机器配置设置, 建议为一半的物理内存大小。以db_block为单位，即每个单位为2k，上例为200M，默认为24M.  

63. sp_configure "lock scheme" , 1,"datarows"   系统默认为表级锁，优化为行锁  

64. sp_configure "number of locks" , 10000  加大最大锁进程数, 默认为5000。  

65. （如果设置一个已经存在的表(tabel)的锁方式为行锁，则执行：  

66. alter table table_name lock datarows）  

67. sp_configure 'procedure cache percent' ,30  缺省值：20 建议值：procedure使用频率高时采用较大的值，不超过30  

68. sp_configure "number of user connections",100   最大用户连接数，默认为25，每个连接要占70k内存  

69. sp_configure 'number of devices',20 将最大设备文件数据改为15个  

70. sp_configure ‘number of Open databases’,20  最大打开数据库个数，对于需在一台数据库服务上打个多个数据库则需加大此参数，默认为15  

71. Sp_configure ‘max online engines CPU’,2 缺省值：1 建议值：采用实际机器的CPU个数  

72. Sp_configure ‘total data cache size’,60000  缺省值：0 建议值：使用SQL SERVER内存的30%  

73. 如果上述参数改动后SYBASE启动不正常，则可检查SYBASE的错误日志，把SYBASE.cfg中的相应选项修改为较小的值。  

74. 附：SYBASE SQL SERVER 内存的分配  

75.      1. SQL SERVER 可执行代码    3-4M  

76.      2. SQL SERVER 使用的静态内存  2.2-3.25M  

77.      3. 用户可配置的参数所占用内存，以下示例(11.9.2版)：  

78.                                         默认值     占用内存  

79.         用户连接数(user connections) 25      每个 约70k  

80.         打开的数据库数(open database)  12      每个 约60k  

81.         打开的对象数(open objects)        500     每个 约1k  

82.         打开的索引数(open indexs)     500     每个 约1k  

83.         锁数目(locks)                  5000    每个 约0.1k  

84.         数据库设备数(data device)     10      每个 约0.5k  

85.      4. 剩余部份分配给  

86.         过程缓存 ( 由 procedure cache percent 决定，默认值为 20% )  

87.         数据缓存 ( 默认值为减去1、2、3项的 80% )  

88. 2.4     优化数据库系统属性  

89. 在sybase center中选择数据库属性，将属性中options选项中的下列项目选中。  

90. allow select into/bulk copy  

91. truncate log on checkpoint  

92. checkpoint on recovery  

93. abort transction on full log  

94. free space accounting  

95. allow nulls by default  

96. auto identity column in non-unquie index   

97. 方法二：在SQLPLUS中执行下列SQL脚本  

98. 如：  

99. sp_dboption mydb,"abort tran on log full",true（设定当数据库的日志空间满时，就终止该进程，使用 sa 用户）  

100. sp_dboption mydb," select into/bulkcopy ",true  

101. sp_dboption mydb," trunc log on chkpt ",true  

102. sp_dboption mydb," no chkpt on recovery",true  

103. sp_dboption mydb," no free space acctg ",true  

104. sp_dboption mydb,"allow nulls by default",true  

105. sp_dboption mydb," auto identity dbo use only ",true  

106. 2.5     创建阈值存储过程  

107. 可根据不同的应用修改以下脚本或创建多个阈值存储过程，并在配置阈值时指定相应的存储过程。  

108.    create procedure sp_thresholdaction   

109.    @dbname varchar(30),@segmentname varchar(30), @free_space int,@status int    

110.             as dump transaction @dbname with no_log   

111. print "LOG DUMP: '%1!' for '%2!' dumped",@segmentname,@dbname  

112. go  

113. 2.6     配置多个阈值  

114. 方法一：   

115.     打开 Sybase Central，双击相应数据库(database)的段 Segments -> logsegment，在   Thresholds 页面中可设置自动清除日志的阀值。其中有 Last Chance 的一行是系统默认的最后机会阀值，即系统日志空闲空间小于该值时为最后一次自动清除日志的机会。设置时阀值的大小可设为日志总空间大小的20%左右。  

116. 另外再增加多个阈值。  

117. 方法二：  

118. 1、使用如下指令查出数据库中日志的容量（用页表示）  

119. select sum(size) from master..sysusages where dbid=db_id("database_name") and (segmap&4)=4  

120. 2、使用sp_addthreshold增加新的阈值，大小为日志容量的50%,如上面语句显示值为2048  

121. sp_addthreshold database_name,logsegment,1024，proc_dump_display  

122. 注意：因一个大事务时可能会越过当前的threshold，所以必须加多个threshold，  

123. 使用命令select @@thresh_hysteresis查看数据库的滞后值，如结果为64页，则下一个阈值设为"最近的阈值-（2*64）"，请在所设阈值再按这种原则各增加两个更小的阈值。  

124. 3   索引的优化  

125. 在良好的数据库设计基础上，需高效地使用索引，并经常的维护索引，下文介绍关于索引的相关内容。  

126. 3.1     创建索引  

127. 索引分为三类：聚簇索引(clustered indexes)、非聚簇索引(nonclustered indexes)、覆盖索引(covering indexes)  

128. 鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在单独的物理设备上来改善操作性能。  

129. create [unique][clustered|nonclustered] index index_name on table_name(column_name...)  

130. 3.2     重建索引  

131. 　　随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：  

132. (1)、数据和使用模式大幅度变化。  

133. (2)、排序的顺序发生改变。  

134. (3)、要进行大量插入操作或已经完成。  

135. (4)、使用大块I/O的查询的磁盘读次数比预料的要多。  

136. (5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。  

137. (6)、dbcc检查出索引有问题。  

138. 　　当重建聚簇索引时,这张表的所有非聚簇索引将被重建.  

139. 3.3     索引统计信息的更新  

140. 　　当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：  

141. (1)、数据行的插入和删除修改了数据的分布。  

142. (2)、对用truncate table删除数据的表上增加数据行。  

143. (3)、修改索引列的值。  

144. 4   查询优化  

145. 4.1     NOT IN子句  

146. 不知大家是否喜欢使用‘NOT IN’这样的操作，如果是，那尽量使用(NOT) EXISTS 替代。  

147. 例子：  

148. 语句1  

149. SELECT dname, deptno FROM dept  WHERE deptno NOT IN (SELECT deptno FROM emp);  

150. 语句2  

151. SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);  

152. 明显的，2要比1的执行性能好很多，因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是range scan。  

153. 4.2     海量查询  

154. 在海量查询时尽量少用格式转换。  

155. 如用  

156. WHERE a.order_no = b.order_no  

157. 而不用  

158. WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)= TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)  

159. 3）查询海量数据是可以使用optimizer hints，例如/*+ORDERED */   

160. 如  

161. SELECT /*+ FULL(EMP) */ E.ENAME FROM EMP E WHERE E.JOB = 'CLERK';  

162. 而不是  

163. SELECT E.ENAME FROM EMP E WHERE E.JOB || '' = 'CLERK';