大型数据库的优化方法及实例

大型数据库的优化方法及实例尹德明杨富玉杨莹时鹏泉中国金融电子化公司E_mail: dm_mis@1.引言随着银行业数据集中，作为整个系统核心的数据库，其存放、管理的数据越来越庞大，已经超越GB而到达TB数据量层次，数据库的性能成为整个系统性能的关键。

国库会计核算系统是国库部门用以进行国库业务的会计核算，并通过支付系统、国库内部往来、同城票据交换系统进行资金清算的计算机网络系统。

国家金库会计核算系统每天处理的税票数据多达10万笔，税收高峰可能会到100万笔，这样一年累计下来其中历史登记簿中的数据达到2000万条以上，给检索和数据处理带来非常大的困难。

如何对于一个已经上线运行的重要业务系统，通过对数据库的优化和简单的系统流程调整，实现系统性能的大幅提升具有现实、迫切、重要的意义。

2.优化策略根据Sybase的数据存储机制，在进行一段时期的数据删除、插入和更新等操作后，数据库往往会产生大量的碎片。

大量碎片的存在，会严重影响数据库的I/O性能，如果在使用数据库一段时间后，整理碎片，可以提高数据库的性能。

由于国家金库会计核算系统在预处理、日间报解、日初始化等步骤，会大批量进行数据删除、插入和更新等操作，因此会产生大量的数据碎片。

碎片整理对于国家金库会计核算系统性能优化将会有重要效果。

Sybase Adaptive Server对于按顺序存储和访问的页，在单个I/O中最多读取八个数据页。

由于大部分I/O时间都花在磁盘上的物理定位和搜寻上，因此大I/O可极大地减少磁盘访问时间。

在大多数情况下，希望在缺省数据高速缓存中配置一个16K缓冲池。

为事务日志创建4K缓冲池可极大地减少数据库系统日志写操作的数量。

好的性能同优良的数据库设计及优秀的程序写法关系极大，可以这样说，如果一个数据库没有好的设计及对程序未进行优化的话即使对参数进行调整也不可能有好的性能。

3.数据库碎片整理由于Sybase是通过OAM页、分配单元和扩展页来管理数据的，所以对OLTP应用的Database Server会十分频繁地进行数据删除、插入和更新等操作，时间一长就会出现以下几种情况：(1)页碎片即本来可以存放在一个页上的数据却分散地存储在多个页上。

如果这些页存储在不同的扩展单元上，Database Server就要访问多个扩展单元，因此降低了系统性能。

(2)扩展单元碎片在堆表中，当删除数据链中间的记录行时，会出现空页。

随着空页的累积，扩展单元的利用率也会下降，从而出现扩展单元碎片。

带cluster index的table也有可能出现扩展单元碎片。

当有扩展单元碎片存在，会出现以下问题：对表进行处理时，常常出现死锁；利用较大的I/O操作或增加I/O缓冲区的大小也无法改变较慢的I/O速度；行操作的争用。

（3）扩展单元遍历带有cluster index的table会由于插入记录而导致页分裂，但当删除记录后，页会获得释放，从而形成跨几个扩展单元和分配单元的数据，而要访问该数据就必须遍历几个扩展单元和分配单元。

这将导致访问/查询记录的时间大大延长，开始时数据库的性能虽然较高，但使用一段时间后性能就会下降等问题。

实际上，数据在存储空间上排列得越紧密有序，Database Server访问的速度就越快，消除碎片有助于提高系统的性能和更有效地利用数据存储空间。

(4) 碎片优化方法处理碎片有多种方法，如重新定义table的填充因子，根据table的定义删除并重新创建索引、重建表等。

可以采用重新创建聚簇索来整理数据库碎片，这样的好处是碎片整理和索引优化可以同时进行。

如对table_name进行重建索引。

if exists (select 1 from sysindexes where id = object_id('table_name')and name = 'PK_HTD_BYSSRHZ' and indid > 0 and indid < 255) alter table 'table_name drop constraint PK_HTD_BYSSRHZalter table 'table_name’add constraint PK_HTD_BYSSRHZ PRIMARY KEY CLUSTERED (zwrq,skgkdm,ssgkdm,mdgkdm,ysjc,yszl, jgdm,bjcs,sjbz,kmdm,ssyf,fczbz)4．内存优化(1)配置16K缓冲池为总数据高速缓存的25%sp_poolconfig 'default data cache',35M','16K'(2)配置4K缓冲池为总数据高速缓存的15%sp_poolconfig 'default data cache',21M','4K'5.索引调优好的性能离不开优良的数据库设计，数据库性能优化的目标就是尽量减少I/O，提高吞吐量，从而缩短系统的响应时间，而实现这一目标的重要手段之一——就是创建合理的索引：索引可以起到以下作用：(1) 避免表扫描；(2) 点查询中定位包含特定数据的特定数据页；(3) 范围(域)查询确定上下限；(4) 索引覆盖，完全避免存取数据页；(5) 连接时避免排序。

建立索引的注意事项：(1) Unique和primary key可以创建唯一索引，缺省情况下unique创建nonclustered，primary key创建clustered索引；(2) Allpages表一般都需要创建clustered索引或分区以减少最后一页的争夺；(3) 如果需要大量插入，不要将clustered索引建立在单调上升的字段上，如identity；对于dol表，此问题并不严重，但allpages却往往是锁争夺的根源；(4) 对allpages表，如有可能不要将clustered索引建立在频繁更新的字段上；(5) 使用索引覆盖来进行关键查询和不太频繁的查询；(6) 如果索引字段元唯一建立唯一索引，优化程序知道只有一行纪录匹配；(7) 索引键尽可能小，如果可能使用最小的数据类型。

确保连接字段元数据类型相同，如果连接查询需要转换数据类型就不能使用索引；(8) 使用索引尽可能使用前导列能够提供良好的性能（即前导列要具备一定的选择性）。

6. 代码调优代码优化包括两个方面：(1)、SQL语句的优化；(2)、代码流程设计的优化。

性能低下的SQL语句往往来自于不恰当的索引设计、不充分的连接条件和不可优化的where 子句；所以对于性能要求较高的功能模块中，我们通常需要考虑上述几个方面，从而写出性能优良的SQL语句；但是，在某些情况下，优良的SQL语句也不能完全解决性能问题，比如说：某个SQL语句对一个有200万笔数据的表进行检索，用时0.1秒，我想这样的性能应该说已经不错了，然而，如果在代码流程设计上需要对该语句循环1000次，那么对于该功能模块的整体性能来说，可能还是无法满足性能要求，所以说对于性能要求高的功能流程设计需要遵循一个原则：尽量减少检索表的次数。

(1)SQL语句优化不能用null作索引，任何包含null值的列都将不会被包含在索引中。

即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。

也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

在where子句中对列使用任何操作（比如：函数）都是在SQL运行时逐列计算得到的，因此查询优化器即使知道使用索引，也不得不进行部分或全部表扫描，以至于降低性能；然而对SQL语句换一种写法，可能有些结果在查询编译时就能得到，从而可以被SQL优化器优化，使用索引，避免表搜索。

如将代码：select max(zwrq) from table_name where substring(zwrq,1,6) = '200305'修改成select max(zwrq) from table_name where zwrq >= '20030501' and zwrq <= '20030531'和select max(zwrq) from table_name where zwrq like '200305%'建立充分的连接条件。

多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。

连接条件要充分考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数×内层表中每一次查找的次数来确定，乘积最小为最佳方案。

(2) 代码流程设计的优化在大型数据库中，如何提高数据操作效率值得关注。

可以根据不同系统的实际情况对代码流程进行优化。

如：根据业务规则减少访问表的个数；通过预取多个SQL语句共用的查询条件，替代Where 子句的自查询，从而简化了Sql语句的复杂度；减少对表检索的次数；由汇总某旬的所有发生额改为汇总该旬最大账务日期的最大报解次数的旬累计，从而减少数据库的吞吐量；对账部分的实现由游标循环对账改为通过内外两次表连接（即检索两次表）对账，从而大大减少了检索表的次数。

7.结论经过数据库碎片整理，索引调优，内存优化，代码优化后，我们对某分库进行了导出数据接口和对账测试，测试表明优化取得了很大效果，优化是成功的。