黑马程序员技术交流社区

标题: sql优化方法总结 [打印本页]

作者: 小江哥 时间: 2018-1-11 16:20
标题: sql优化方法总结
本帖最后由小江哥于 2018-1-11 16:20 编辑

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

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2