sql優化方法
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, 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子句中變量順序應與索引字鍵順序相同。
如：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提高效率。
(8)避免使用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)方式。
【本文為51CTO專欄作者“王森豐”的原創稿件，轉載請注明出處】
