提高INFORMIX数据库运行效率的策略与措施

Direct source: www.btonline.com.cn

正文： 转自http://www.powerba.com/develop/database/informix/article/20001122016.htm

提高INFORMIX数据库运行效率的策略与措施
范俊军
摘　要　本文从INFORMIX数据库系统参数性能调整、库表创建方式、ESQL/C程序设计、索引等实际应用方面提出了提高INFORMIX数据库运行效率 的若干策略与措施，旨在为INFORMIX数据库维护人员和程序设计人员提供有益的参考，文中穿插了一些简洁实例以便于读者理解掌握。
关键词　INFORMIX　数据库　运行效率　优化策略

[@more@]INFORMIX数据库是一被广泛应用的关系型数据库，如何提高其应用性能是大家关心的话题，特别是随着库中数据量与应用处理交易量的不断增多，其运行效率问题尤显突出。除了充分应用INFORMIX新品质，从INFORMIX－SE到INFORMIX－ONLINE高版本外， INFORMIX数据库的运行效率与其系统参数(online)的配置、性能的调整、库表创建的方式、索引的策略、ESQL/C程序的质量优劣直接相关。本文结合工作实践介绍以下提高 INFORMIX数据库运行效率的若干优化策略与措施。
一、 INFORMIX系统性能参数优化
系统性能与磁盘、CPU、共享内存和网络相关。对磁盘调整的原则是降低读盘次数，极大化每次读盘数据量，数据分布均匀，防止瓶颈的发 生。Online的磁盘空间应采用裸设备方式(raw
device)，而不采用文件系统方式(cooked file)，前者比后者处理速度要快得多，且可靠性 高。物理日志缓存空间应在30～50兆间即可，不必太大。设置cpu
vp个数为cpu个数减1(若cpu个数为1，则cpu vp也为1)。共享内存一般是系 统内存的1/3～1/4，一个cpu vp配4个LRU队列，n个
LRU队列配n个页刷新进程page_cleaner，调谐使其高速缓冲区读命中率大于95％，写命中率大于85％，设定多张网卡可改进性能，网络碰撞率应在8％以内。系统核心参数按informix各版本提出的配置要求调整即可，若调整不对，在构造online时即可能不会成功。Informix－online数据服务器性能的调试往往在一定的经验值基础上动态反复调整、测试才能获得最终满意的结果。INFORMIX数据库系统参数性能调整的优劣直接影响INFORMIX数据库运行效率，对其性能调整的具体方法与步骤，informix资料有较详 尽说明，这里不作重点讨论。
二、 创建库表方式优化
1.数据库建库程序对每个数据表空间分配的优化。
在建表前将数据库每张表数据量大小作一估算，以便将表的第一个“extent”(物理上连续的页)空间分配尽量和估算值大小一致，下一 个“extent”空间分配则根据表数据的增加量估计值来分配，这样可减少数据分配碎片和空间浪费，提高数据库系统的效率。
2.引入表分割fragmentation，使数据在物理逻辑上分布均匀，有助于并行处理性能的提高。
3.建表时对表的记录锁方式根据应用处理的不同区别对待。批量处理的表采用页锁(page)方式，实时交易的表采用行锁(row)方式。锁方式可 以在建表时确定，也可以用alter
tabname lock mode(row)和alter tabname lock mode(page)命令改变。值得注意的是通过 dbimport、dbexport转移生成的表其默认锁方式是页级锁，对于实时交易且操作频繁的表应改为行级锁方式，使用oncheck
－pt命令可查得表 的锁方式状态。
4.建库的日志方式：
(1)No logging:不能进行事务处理。
(2)buffered log：共享缓存满即刷新写入磁盘。
(3)unbuffered log：当一个交易完成时即刷新写入磁盘。
(4)ansi mode：只有日期格式差异，月日年形式，其他与unbuffered相同。
一般我们对实时处理系统日志方式采用unbuffered log，在进行大批量数据集中装卸时采用no logging。如：
create database workdb in dbspacel with log；
create table satmx(
zh char (20),
rq date,
fse money(16),
ye money(16)
)
in dbspacel
EXTENT size 1024
NEXT size 64
LOCK mode (row)；
Greate index satmx_idx on satmx(zh,rq);
Alter table satmx modify next size 128 lock mode (page);
Alter index satmx_idx to cluster;
三、 应用程序的优化
1.Select语句优化要点
(1)对于大数据量的求和应避免使用单一的sum命令处理，可采用group by方式与其结合，有时其效率可提高几倍甚至百倍。例如，银行常要进 行帐户的总帐与明细帐一致性核对(总分核
对)，数据量大，原采用单一的sum命令与while语句结合来完成，现改用以下group by方式后效率 大相径庭。
/＊将定期表中所有数据按机构，储种统计户数，余额置临时表中并建索引＊/
select zh[1,9] jg,zh[19,20]cz,count(＊)hs,sum(ye)sumye
from satdq
where bz=″0″
group by zh[1,9],zh [19,20]
into temp satdq_sum;
create index satdq_suml
on satdq_sum(jg,cz);
(帐号zh的前9位为机构编码，第19至20位为储种)
(2)最具有限制性的条件放在前面，大值在前，小值在后。
如：where col<=1000 and col>=1　效率高
where col>=1 and col<=1000 效率低
(3)避免子查询与相关查询。
如：where zh in (select zh from table where xm matches ″＊11＊″)
可将其编为declare cursor 的一while循环来处理。
(4)避免会引起磁盘读写的rowid操作。在where子句中或select语句中，用rowid要产生磁盘读写，是一个物理过程，会影响性能。
如原为：
declare ps2 cursor
for
select ＊,rowid
into b,id
from satmxhz
where zh[1,9]=vvjgbm
and bz=″0″
order by zh;
open ps2;
fetch ps2;
while (sqlca.sqlcode==0){
……
update satmxhz
set
sbrq=b.sbrq,
ye=b.ye,
lxjs=b.lxjs,
wdbs=wdbs＋1,
dac=dac
where rowid=id;
……
fetch ps2;
}
改为：
declare ps2 cursor
for
select ＊ into b
from satmxhz
where zh [1,9]=vvjgbm
and bz=″0″
for update of sbrq,ye,lxjs,wdbs,dac;
open ps2;
fetch ps2;
while (sqlca.sqlcode==0){
……
update satmxhz
set
sbrq=b.sbrq,
ye =b.ye,
lxjs=b.lxjs,
wdbs=b.wdbs,
dac=dac
where current of ps2;
……
fetch ps2;
}
(5)where子句中变量顺序应与索引字键顺序相同。
如：create index putlsz_idx on putlsz(zh ,rq,lsh)
索引字键顺序：首先是帐号zh，其次是日期rq,最后是流水号lsh,
所以where子句变量顺序应是where zh=″11111″and rq=″06/06/1999″and lsh<1000,不应是where
lsh<1000 and rq=″06/06/1999″ and zh =″11111″等非索引字键顺序。
(6)用=替代matches的操作。
如：where zh matches ″330678860＊″应用where zh[1,9]=″330678860″替代。
(7)通过聚族索引cluster index提高效率。
(避免使用order by,group by,该操作需生成临时表而影响效率，可用视图来处理，视图的引入能控制用户的存取，提高效率。
2.insert语句优化要点
(1)采用insert cursor或put替代insert；
如：wr_satmx ()
begin work;
prepare insert_mx from
″insert into satmx
values(?,?,?,?,?,?,?,?,?,?,?,?)″；
declare mx_cur cursor
for insert_mx;
open mx_cur;
declare cur_mxh cursor
for
select ＊ into bmxh
from satmxh
for update;
open cur_mxh;
fetch cur_mxh;
while (sqlca.sqlcode==0){
put mx_cur from
bmxh.zh ,bmxh,rq,bmxh,l sh,bmxh,jym,
bmx,pzhm,bmxh.bz,bmxh,fse,
bmxh.ye,bmxh.bdlsh,bmxh.bd rq,bmxh.czy,bmxh.dybz;
delete from satmxh
where current of cur_mxh;
fetch cur_mxh;
}
close mx_cur;
close cur_mxh;
commit work;
以上一段程序是将satmxh表中记录转移到satmx表中，虽然可用
begin work;
insert into satmx select
＊ from satmxh;
dele te from satmxh;
commit work;
四行程序即可实现，但若表中记录多的话，其运行效率远远不如前者的处理方式，因为insert cursor是先在共享内存缓存中处理，刷新时写 入磁盘的，所以上载数据速度最快，但其缺点是必须编程实现。
(2)避免加长锁、长事务操作，这在处理大数据量时其优劣尤为突出，在能保证数据一致性的前提下应将长事务分解为小事务来处理。
如将前面例题数据分不同网点机构进行转移，避免长事务，可大大提高运行效率。
wr_satmx():
database workdb;
declare cur_jgl cursor
with hold for
select jgbm,jgmc
into vvjgbm,vvjgmc
from putjgbm
order by jgbm
open cur_jgl;
fetch cur_jgl;
while(sqlca.sqlcode==0){
begin work;
prepare insert_mx from
″insert into satmx
values(?,?，?,?,?,?,?,?,?,?,?,?)″;
declare mx_cur cursor
for insert_mx
open mx_cur
declare cur_mxh cursor
for
select ＊ into bmxh
from satmxh
where zh [1,9]=vvjgbm
for update;
open cur_mxh;
fetch cur_mxh;
while (sqlca.sqlcode==0){
put mx_cur from
bmxh.zh,bmxh.rq,bmxh.lsh,bmxh,jym,
bmx.pzhm,bmxh.bz,bmxh.fse,
bmxh.ye,bmxh.bdlsh,bmxh.bd
rq,bmxh.czy,bmxh.dybz;
delete from satmxh
where current of cur_mxh;
fetch cur_mxh;
}
close mx_cur;
close cur_mxh;
commit work;
fetch cor_jgl;
}
close
cur_jgl;
close database;
(3)宿主变量应在执行insert操作前转换为表结构描述的数据类型，避免insert语句操作时不同数据类型自动转换而影响其效率。
(4)对表的insert操作很频繁时，可以将index fill factor降低一些，采用row lock 代替page lock。
3.update语句优化要点
(1)用子串代替matches,避免使用不从第一个开始的子串。
如where a matches ″ab＊″采用where a [1,2]=″ab″代替；避免使用如b[5,6]的子串。
(2)避免加长锁修改，避免长事务处理，例子参见insert的语句优化(2)方式。
4.delete语句优化要点
(1)用drop table,create table和create index代替delete from table，能快速清理并释放表空间。
(2)避免长事务处理，例子参见insert的语句优化(2)方式。
(3)使用关联(父子)删除cascading delete。
(4)编写程序使用delete cursor删，而不采用delete from table where…的方式。例子参见insert的语句优化(1)方式。
四、 index索引策略的优化与原则
(1)两表中有关联，则关联字段必须建索引。如果select语句的where子句具有在一个表的一个单字段和另一个表的一个单字段之间的连接条件，则对记录数目更多的那个表的那个字段建立索引；如果一个表的几个字段和另一个表的几个字段的连接条件，则对记录数目更多的那个表的起作用的字段上建立复合索引。
(2)避免高重复率字段建索引。
(3)对同一表不要过多地建索引。
(4)建索引的字段的size尽量少，复合索引尽量少用。
(5)建聚族索引，减少索引文件碎片，以加快检索速度。
(6)where子句经常用到的字段做索引。
(7)先load data,后create index。
(数据量小的表是否建索引影响不大，一般不要对记录数小于200的小表建立索引，因为从使用索引得到的速度不能抵消在表上打开和检索索 引文件所需的时间。
(9)建primary key
五、 其他因素与措施
(1)INFORMIX数据库中一个事务处理不能跨多个进程。例：
……
begin work;
……
if(fork()==0){/＊子进程处理＊/
……
conmit work;(或rollback work;)
……
}
……
引起数据库运行效率很低或处理异常。
(2)利用“set explain on” INFORMIX语句来分析数据库查找策略。
当发现某一部分INFORMIX语句运行特别慢又找不到原因时，可在程序中加入“set explain on”语句，当程序运行时，在程序运行的当前目录下产生一个“sqexplain.out”文件，记录了INFORMIX数据库服务器采用何种优化策略来查找数据库。在该文件中可以发现查找中有无使用索引条件，估计的查找代价等信息。
(3)数据库在做″dbimport″后应运行update statistics语句。
当数据库系统用“dbimport”实用程序完成数据库装载后，应运行update statistics数据库语句保证系统表中统计信息准确，否则将影响数 据库优化器的策略和系统运行性能。例：
select ＊ from satmxhz where zh matches″3306100001111111＊″
satmxhz表在zh字段上建立了索引，但在数据库运行update statitic前，数据库优化查找策略是按顺序查找而不是按索引查找，严重影响了查 找速度。
(4)经常插入和删除的大表应定期运行update statistics high语句。
对经常插入和删除的大表应定期运行update statistics语句保证系统表中统计信息准确，保证数据库优化器作出正确的优化策略提高系统运 行性能。
(5)对大文件采用load命令装载入库前可先去掉原有的日志方式和去掉索引，等load装载完成后再重建索引和日志，能大大提高装载效率，避 免出错产生。
a.去掉原有的日志方式：ontape－S －N dbname
b.去索引：delete index indexname
c.用load 装入数据：load from filename insert into tabname
d.重建索引：create index to indexname on tabname(fld1,fld2……)
e.重建日志：ontape －C －B
(6)在应用上引入多进程并发处理，充分利用系统资源，能大大提高处理效率。如以下是对多机构多表同时并发处理的例子：
＃include<stdio.h>
＃include<signal.h>
＃include<sys/types.h>
＃include<sys/wait.h>
……
main()
{
pid_t pids[256]；
int pid,i,ii,stat_loc,exit_flag=0；
signal(SIGCLD,SIG_DFL);
for(i=0;i< 3;i＋＋){
pid=fork();
if (pid< 0)
exit(0)；
else{
if(pid==0)
in_son(i);
else
pids=pid；
}
}
while(1){
pid=wait(＆stat_loc);
exit_flag=1;
for(i=0;i< 3;i＋＋){
if(pid==pids)
pids=－1;
if(pids!=－1)
exit_flag=0;
}
if(exit_flag) break;
}
return(0);
}
in_son(i)
int i;
{
sqldetach();
switch(i){
case 0:
wr_satmx();/＊由[satmxh]转入[satmx]＊/
break;
case 1:
wr_satzhmx();/＊由[satzhmxh]转入[satzhmx]＊/
break;
case 2:
wr_putrzz();/＊由[putrzzh]转入[putrzz]＊/
break;
default;
break;
}
exit(0);
}
wr_satmx()为前面举例的一函数，wr_satzhmx(),wr_putrzz()可为操作表不同的函数。□
范俊军(中国建设银行浙江省金华市分行科技处　(浙江金华321000))