1、Select語(yǔ)句優(yōu)化要點(diǎn)

(1) 對(duì)于大數(shù)據(jù)量的求和應(yīng)避免使用單一的sum命令處理，可采用group by方式與其結(jié)合，有時(shí)其效率可提高幾倍甚至百倍。例如，銀行常要進(jìn)行帳戶的總帳與明細(xì)帳一致性核對(duì)(總分核 對(duì))，數(shù)據(jù)量大，原采用單一的sum命令與while語(yǔ)句結(jié)合來(lái)完成，現(xiàn)改用以下group by 方式后效率大相徑庭。

/＊將定期表中所有數(shù)據(jù)按機(jī)構(gòu)，儲(chǔ)種統(tǒng)計(jì)戶數(shù)，余額置臨時(shí)表中并建索引＊/    
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);    
(帳號(hào)zh的前9位為機(jī)構(gòu)編碼，第19至20位為儲(chǔ)種) 

(2) ***有限制性的條件放在前面，大值在前，小值在后。

如：where col<=1000 and col>=1 效率高 where col>=1 and col<=1000 效率低

(3)避免子查詢與相關(guān)查詢。

如：where zh in (select zh from table where xm matches ″*11*″) 可將其編為declare cursor 的一while循環(huán)來(lái)處理。

(4)避免會(huì)引起磁盤讀寫的rowid操作。

在where子句中或select語(yǔ)句中，用rowid要產(chǎn)生磁盤讀寫，是一個(gè)物理過(guò)程，會(huì)影響性能。

如原為：

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,    wdbswdbs=wdbs＋1, dacdac=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, dacdac=dac where current of ps2;   
  ……  
  fetch ps2;    
} 

(5)where子句中變量順序應(yīng)與索引字鍵順序相同。

如：create index putlsz_idx on putlsz(zh ,rq,lsh)

索引字鍵順序：首先是帳號(hào)zh，其次是日期rq,***是流水號(hào)lsh,

所以where子句變量順序應(yīng)是where zh=″11111″and rq=″06/06/1999″and lsh<1000,不應(yīng)是where lsh<1000 and rq=″06/06/1999″ and zh =″11111″等非索引字鍵順序。

(6)用=替代matches的操作。

如：where zh matches ″330678860*″應(yīng)用where zh[1,9]=″330678860″替代。

(7)通過(guò)聚族索引cluster index提高效率。

(8)避免使用order by,group by,該操作需生成臨時(shí)表而影響效率，可用視圖來(lái)處理，視圖的引入能控制用戶的存取，提高效率。

[[178401]]

2、insert語(yǔ)句優(yōu)化要點(diǎn)

(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表中記錄轉(zhuǎn)移到satmx表中，雖然可用 begin work;

insert into satmx select * from satmxh; dele te from satmxh; commit work;

四行程序即可實(shí)現(xiàn)，但若表中記錄多的話，其運(yùn)行效率遠(yuǎn)遠(yuǎn)不如前者的處理方式，因?yàn)閕nsert cursor是先在共享內(nèi)存緩存中處理，刷新時(shí)寫入磁盤的，所以上載數(shù)據(jù)速度最快，但其缺點(diǎn)是必須編程實(shí)現(xiàn)。

(2)避免加長(zhǎng)鎖、長(zhǎng)事務(wù)操作，這在處理大數(shù)據(jù)量時(shí)其優(yōu)劣尤為突出，在能保證數(shù)據(jù)一致性的前提下應(yīng)將長(zhǎng)事務(wù)分解為小事務(wù)來(lái)處理。

如將前面例題數(shù)據(jù)分不同網(wǎng)點(diǎn)機(jī)構(gòu)進(jìn)行轉(zhuǎn)移，避免長(zhǎng)事務(wù)，可大大提高運(yùn)行效率。

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)宿主變量應(yīng)在執(zhí)行insert操作前轉(zhuǎn)換為表結(jié)構(gòu)描述的數(shù)據(jù)類型，避免insert語(yǔ)句操作時(shí)不同數(shù)據(jù)類型自動(dòng)轉(zhuǎn)換而影響其效率。

(4)對(duì)表的insert操作很頻繁時(shí)，可以將index fill factor降低一些，采用row lock 代替page lock。

3、update語(yǔ)句優(yōu)化要點(diǎn)

(1)用子串代替matches,避免使用不從***個(gè)開始的子串。

如where a matches ″ab*″采用where a [1,2]=″ab″代替;避免使用如b[5,6]的子串。

(2)避免加長(zhǎng)鎖修改，避免長(zhǎng)事務(wù)處理，例子參見insert的語(yǔ)句優(yōu)化(2)方式。

4.delete語(yǔ)句優(yōu)化要點(diǎn)

(1)用drop table,create table和create index代替delete from table，能快速清理并釋放表空間。

(2)避免長(zhǎng)事務(wù)處理，例子參見insert的語(yǔ)句優(yōu)化(2)方式。

(3)使用關(guān)聯(lián)(父子)刪除cascading delete。

(4)編寫程序使用delete cursor刪，而不采用delete from table where…的方式。例子參見insert的語(yǔ)句優(yōu)化(1)方式。

【本文為51CTO專欄作者“王森豐”的原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)注明出處】