sql优化方法

• 1、Select语句优化要点 
• 2、insert语句优化要点 
• 3、update语句优化要点 
• 4.delete语句优化要点 

1、Select语句优化要点 

(1)  对于大数据量的求和应避免使用单一的sum命令处理，可采用group by方式与其结合，有时其效率可提高几倍甚至百倍。例如，银行常要进行帐户的总帐与明细帐一致性核对(总分核 对)，数据量大，原采用单一的sum命令与while语句结合来完成，现改用以下group by    方式后效率大相径庭。 

?

1 2 3 4

/＊将定期表中所有数据按机构，储种统计户数，余额置临时表中并建索引＊/   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要产生磁盘读写，是一个物理过程，会影响性能。  

如原为： 

?

1 2 3 4 5 6 7 8

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;   }

 改为： 

?

1 2 3 4 5 6 7 8 9

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提高效率。

(8)避免使用order by,group by,该操作需生成临时表而影响效率，可用视图来处理，视图的引入能控制用户的存取，提高效率。   

2、insert语句优化要点 

(1)采用insert cursor或put替代insert；   如：wr_satmx ()   begin work; 

  

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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)避免加长锁、长事务操作，这在处理大数据量时其优劣尤为突出，在能保证数据一致性的前提下应将长事务分解为小事务来处理。 

如将前面例题数据分不同网点机构进行转移，避免长事务，可大大提高运行效率。   

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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)方式。