sql优化方法

来源：互联网发布：一键root软件编辑：程序博客网时间：2024/06/06 18:39

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

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

0 0