SQL编写规范和优化.docx
《SQL编写规范和优化.docx》由会员分享，可在线阅读，更多相关《SQL编写规范和优化.docx（31页珍藏版）》请在冰豆网上搜索。
SQL编写规范和优化
SQL编写规范
目录
1共享SQL语句3
2减少访问数据库的次数4
3使用DECODE函数4
1减少ORACLE事务操作5
2减少对表的查询，特别是重复的查询：
5
3如何合理使用索引7
3.1要使用索引7
3.2分离表和索引8
3.3尽量建好和使用好索引8
3.4索引使用的注意事项9
3.4.1索引列值必须匹配9
3.4.2索引列不能加操作10
3.4.3索引列不能以通配符作词首10
3.4.4索引列不能使用空值10
3.4.5使用不等于操作（包括<>、！
=和not=）不会使用索引10
3.4.6索引使用的顺序10
3.4.7强制索引失效12
3.4.8无法预知位置的索引列作关联查询，不会用到索引12
3.4.9使用HINTS（提示）13
3.5删除无用的索引14
4使用Between而不是>=和<=15
5使用COUNT会造成效率的降低。
15
6使用DISTINCT会造成效率的降低15
7尽量不用EXIST，IN等耗时的关键字，最好使用多表关联查询。
17
8尽量少使用基于大表生成的视图18
9减少网络访问次数18
10SQL中nowait的使用18
附件1：
如何在VB程序中构造使用变量的查询语句（作者：
李生龙）19
附件2：
建议：
通过内部函数提高SQL效率19
附件3：
Hint的使用20
1共享SQL语句
ORACLE将已执行的SQL语句的分析放入共享池中，执行SQL语句前，ORACLE先将其与共享池中的SQL做比较，若完全匹配则直接取结果，不再进行分析，可大大提高效率。
但要注意：
•ORACLE只对简单的表提供高速缓冲
•ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同（包括空格,换行等）（当前设定的Cursor_Sharing的匹配模式为EXACT）
•必须满足的三个条件
Ø当前被执行的语句和共享池中的语句必须完全相同
Ø两个语句所指的对象必须完全相同
Ø两个SQL语句中必须使用相同的名字的绑定变量
因此在书写SQL语句的时候请注意：
（1）对完成相同功能的SQL语句尽量使用COPY的方式，以使得SQL语句保持“一模一样”
（2）在SQL中尽量使用相同的变量的方式，保持变量名字相同。
（在SQL中使用变量的方式参见附件）
2减少访问数据库的次数
当执行每条SQL语句时,ORACLE在内部执行了许多工作:
解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量。
如：
selectidfromtr_ddmwhereid=’12344’
selectstatefromtr_ddmwhereid=’12344’
应合并成一条。
但要注意不是所有的情况都是放在一条语句中能够提高效率，有时一条太大的SQL语句若没有组织好会严重影响效率。
在后面的章节中会对此进行分析。
3使用DECODE函数
DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
例如：
selecta.a_resource_typeA端类型,a.a_device_idA端ID,a.row_numA端行号,a.col_numA端列号,a.a_device_start_serialA端起始端子号,a.a_device_end_serialA端终止端子号,
b.a_resource_typeB端类型,B.a_device_idB端ID,B.row_numB端行号,B.col_numB端列号,B.a_device_start_serialB端起始端子号,B.a_device_end_serialB端终止端子号,
a.cable_id缆,a.cable_first_numA端缆起止序号,a.cable_last_numA端缆终止序号,
B.cable_first_numB端缆起止序号,B.cable_last_numB端缆终止序号,
a.a_device_start_serial+decode（sign（B.cable_first_num-a.cable_first_num）,-1,0,B.cable_first_num-a.cable_first_num）A端起始,
a.a_device_end_serial-decode（sign（a.cable_last_num-B.cable_last_num）,1,a.cable_last_num-B.cable_last_num,0）A端终止,
B.a_device_start_serialB端起始,
B.a_device_end_serial-decode（sign（B.cable_last_num-a.cable_last_num）,1,B.cable_last_num-a.cable_last_num,0）B端终止,
decode（sign（a.cable_first_num-B.cable_first_num）,1,a.cable_first_num,B.cable_first_num）缆起始,
decode（sign（a.cable_last_num-B.cable_last_num）,1,B.cable_last_num,a.cable_last_num）缆终止
fromCABLE_SERIAL_CONNECTa,CABLE_SERIAL_CONNECTb
wherea.cable_id=B.CABLE_ID（+）
anda.cable_first_num<=B.cable_last_num（+）anda.cable_last_num>=B.cable_first_num（+）
anda.id<>b.id（+）
and（a.A_Device_ID='3577684'ANDa.A_Resource_Type=14ANDa.AB_LABEL='A'）
orderbyA端行号,A端列号,A端起始端子号,A端起始
该SQL语句是查找电缆连接到的交接设备的起止端子号，利用decode函数巧妙解决了线序正序排列和反序排列两种情况，减少了访问表的次数。
4减少ORACLE事务操作
•用TRUNCATE替代DELETE
•TRUNCATE不能做回滚，所以不需要存放任何可被恢复的信息。
•多记录时非常有效
注意：
如果使用时需要保存事务信息，不能使用Truncate替代Delete
5减少对表的查询，特别是重复的查询：
很多SQL语句中大量重复使用了固定的查询，即查询语句本身比较复杂，有可能耗时较长。
造成每查一次需重复执行一遍。
例如：
updatean_onu_portset
sequence_v5=sequence_id+decode（
（selectmax（a.sequence_v5）froman_onu_porta,an_v5idb,an_ne_devicec,an_onu_subrackdwherea.v5id=b.v5idanda.v5idisnotnullanda.subrack_id=d.idand（（d.shelf_id=c.rack_idandc.subrack_idisnull）or（d.id=c.subrack_id））andb.onu_id=c.ne_idandb.v5id='6'andb.olt_id='376193798'）,
null,-1,
（selectmax（a.sequence_v5）froman_onu_port,an_v5idb,an_ne_devicec,an_onu_subrackdwherea.v5id=b.v5idanda.v5idisnotnullanda.subrack_id=d.idand（（d.shelf_id=c.rack_idandc.subrack_idisnull）or（d.id=c.subrack_id））andb.onu_id=c.ne_idandb.v5id='6'andb.olt_id='376193798'）
）,
v5id='6'
wherecard_id='379370706';
又比如：
selectd.accessnbr
fromdes_serviced,des_serviceorder_servicee,des_serviceorder
whered.serviceid=e.serviceidande.serviceorderid=f.serviceorderid
and（（selectuniquei.jxjx
fromsw_number_res_basi
wherestart_number<=
（selectuniqueh.accessnbr
fromdes_serviceh
wheree.isoldservice='0'andh.serviceid=d.serviceidandd.serviceidseq=
（selectmax（g.serviceidseq）
fromdes_serviceg
whereg.serviceid=d.serviceidandrownum=1）
andrownum=1）
andend_number>=
（selectuniqueh.accessnbr
fromdes_serviceh
wheree.isoldservice='0'andh.serviceid=d.serviceidandd.serviceidseq=
（selectmax（g.serviceidseq）
fromdes_serviceg
whereg.serviceid=d.serviceidandrownum=1）
andrownum=1）
andlength（start_number）=8andrownum=1）
<>
（selectuniquei.jxjx
fromsw_number_res_basi
wherestart_number<=
（selectuniqueh.accessnbr
fromdes_serviceh
wheree.isoldservice='1'andh.serviceid=d.serviceidandd.serviceidseq=
（selectmax（g.serviceidseq）-1
fromdes_serviceg
whereg.serviceid=d.serviceidandrownum=1）
andrownum=1）
andend_number>=
（selectuniqueh.accessnbr
fromdes_serviceh
wheree.isoldservice='1'andh.serviceid=d.serviceidandd.serviceidseq=
（selectmax（g.serviceidseq）-1
fromdes_serviceg
whereg.serviceid=d.serviceidandrownum=1）
andrownum=1）
andlength（start_number）=8andrownum=1））
ande.isoldservice='0'andd.serviceidseq=
（selectmax（g.serviceidseq）
fromdes_serviceg
whereg.serviceid=d.serviceid）
andd.servicetypein（111010,111020,112010,113010,113020,113060,113070,113025,122010）
andf.statusin（10,11）
andf.sotypeidin（'402','414','504','513','527','316','317','229','230','231','232','322','323','324','325','427','428','115'）
在这个例子中，蓝体字部分重复执行了五次之多（MAX（…）-1实质是重复了MAX（…）查询），而SELECTMAX本身就是耗时的操作！
！
原句在EXPLAINWINDOW中分析的结果消耗为14881，若将这些蓝体部分单查（消耗2），再查绿体部分（消耗767，但因为有rownum=1所以去掉UNIQUE后,消耗2），再将结果带入SQL，消耗大约为2000多！
6如何合理使用索引：
6.1要使用索引
ORACLE访问表的方式（两种）
•a.全表扫描
全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块（databaseblock）的方式优化全表扫描.
•b.通过ROWID访问表
采用基于ROWID的访问方式情况,提高访问表的效率。
ROWID包含了表中记录的物理位置信息.
ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高
索引是表的一个概念部分,用来提高检索数据的效率.实际上,ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键（primarykey）的唯一性验证.
除了那些LONG或LONGRAW数据类型,可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,扫描小表时,使用索引同样能提高效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
6.2分离表和索引
总是将表和索引建立在不同的表空间内（TABLESPACES）.决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里.同时,确保数据表空间和索引表空间置于不同的硬盘上.
为了保证脚本的通用性，当前共享各个组提交的脚本没有增加表空间指定，在辽宁项目部这部分工作由数据库管理员完成。
6.3尽量建好和使用好索引
●建索引也是有讲究的，在建索引时，也不是索引越多越好，当一个表的索引达到4个以上时，对于表进行维护的时候性能可能会下降很多，因为OLTP系统每表超过5个索引即会降低性能，而且在一个sql中，Oracle从不能使用超过5个索引；
●要建立选择性高的索引。
索引的选择性是指索引列里不同值的数目与表中记录数的比。
如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。
最好的可能性选择是1.0。
依据非空值列的唯一索引，通常其选择性为1.0。
有文章表明，如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高。
如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。
如果索引列无选择性，基于开销的优化器可能决定使用全表扫描。
●当我们用到GROUPBY和ORDERBY时,ORACLE就会自动对数据进行排序,而ORACLE在INIT.ORA中决定了sort_area_size区的大小,当排序不能在我们给定的排序区完成时,ORACLE就会在磁盘中进行排序,也就是我们讲的临时表空间中排序,过多的磁盘排序将会令freebufferwaits的值变高,而这个区间并不只是用于排序的,由此会导致性能下降；
当与Oracle建立起一个session时，在内存中就会为该session分配一个私有的排序区域。
如果该连接是一个专用的连接（dedicatedconnection），那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个ProgramGlobalArea（PGA）。
如果连接是通过多线程服务器建立的，那么排序的空间就在large_pool中分配。
不幸的是，对于所有的session，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。
因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disksorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。
当然，当排序的空间需求超出了sort_area_size的大小时，这时将会在TEMP表空间中分页进行磁盘排序。
磁盘排序要比内存排序大概慢14,000倍。
在开发中应该注意:
（1）、select,update,delete语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.
（2）索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片.
（3）、在使用索引时一定要按索引对应字段的顺序进行引用。
（4）、用（+）比用NOTIN更有效率。
（5）、不要对查询中用到的每个列都建立索引，只建立选择性高的索引。
例如：
表CABLE_CONNECTOR_ROUTE对列USE_STATUS建有索引IDX_USE_STATUS，但其选择性约为几比几百万；列SPECLINENO建有索引IDX_SPECLINENO，选择性要高得多。
Select*FromCABLE_CONNECTOR_ROUTEWhere
Use_STATUS='2'AndSPECLINENOLike'30946%'
分析：
使用索引IDX_USE_STATUS，成本很低（为2），但执行需耗费76秒多。
Select*FromCABLE_CONNECTOR_ROUTEWhere
Use_STATUS||''='2'AndSPECLINENOLike'30946%'
分析：
使用USE_STATUS||’’=’2’，强制IDX_USE_STATUS索引无效后，使用了索引IDX_SPECLINENO，成为比上一句略增（为10），但实际执行时仅耗费0.5秒不到！
6.4索引使用的注意事项
6.4.1索引列值必须匹配
SQL语句中所使用的查询索引列值的类型必须与列的类型匹配。
例如：
selecta.CODE,a.SEQUENCE,a.COOR,a."ROWID"fromsw_slota
wherea.PANEL_ID=406657623orderbyto_number（substr（（substr（a.
coor,1,（instr（a.coor,';'）-1）））,1,（instr（（substr（a.coor,1,（instr（
a.coor,';'）-1）））,','）-1）））,to_number（substr（（substr（a.coor,1,（
instr（a.coor,';'）-1）））,（instr（（substr（a.coor,1,（instr（a.coor,';'）-1）））,','）+1）））;
因为PANEL_ID是字符型，所以该SQL语句不能使用PANEL_ID索引。
6.4.2索引列不能加操作
要使用某列索引，不能对该列作任何操作，包括函数、运算符等。
例：
SELECTSERVICEORDERID,REQUESTID,MESSAGETIME,A1
FROMDES_INTERFACEMESSAGE
WHEREINTERFACETYPE='C1306'ANDTO_CHAR（MESSAGETIME,'YYYYMMDD'）='20050403'ORDERBYMESSAGETIME
中MESSAGETIME列虽做了索引却不会用到，应改为：
…ANDMESSAGETIME=TO_DATE（'20050403','YYYYMMDD'）…
6.4.3索引列不能以通配符作词首
对索引主列作条件查询，但条件使用Like操作且值以‘％’开始时，无法使用索引。
例如：
TR_DDM表对列DDF_ID建有索引，但
SELECT*FROMTR_DDMWHEREDDF_IDLIKE‘%01%’
由于使用了LIKE‘%01%’不会用到索引，而是做全表扫描。
6.4.4索引列不能使用空值
在索引列上使用空值，将无法使用索引，因为索引中不含空值。
最常用的如ISNOTNULL。
例如：
select*fromtr_ddm_termtWhereLOGIC_IDISNOTNULL
全表扫描
select*fromtr_ddm_termtWhereLOGIC_ID>'0'
使用IDX_LOGIC_ID
6.4.5使用不等于操作（包括<>、！
=和not=）不会使用索引
6.4.6索引使用的顺序
✓当SQL语句的执行路径可以使用分布在多个表上的多个索引时,ORACLE会同时使用多个索引并在运行时对它们的记录进行合并,检索出仅对全部索引有效的记录.
✓在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引.然而这个规则只有当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较.这种子句在优化器中的等级是非常低的.
✓如果不同表中两个相同等级的索引将被引用,FROM子句中表的顺序将决定哪个会被率先使用.
✓FROM子句中最后的表的索引将有最高的优先级.
✓如果相同表中两个相同等级的索引将被引用,WHERE子句中最先被引用的索引将有最高的优先级.
注意：
索引使用顺序可能与优化器选择策略有关，当前Oracle9i缺省参数是choose，在做过表分析以后的使用顺序可能会发生变化，使用时请在测试环境进行测试。
例子:
TR_DDM_TERM表对ID建有主键PK_ID，对DDM_ID建有非唯一性索引IDX_DDM_ID，对LOGIC_ID建有非唯一性索引IDX_LOGIC_ID。
下述SQL语句对索引的使用情况：
SELECT*FROMTR_DDM_TERM
升级会员 