SQL编写规范和优化.docx

1、SQL编写规范和优化 SQL编写规范目 录1共享SQL语句 32减少访问数据库的次数 43使用DECODE函数 41 减少ORACLE事务操作 52 减少对表的查询，特别是重复的查询： 53 如何合理使用索引 73.1 要使用索引 73.2 分离表和索引 83.3 尽量建好和使用好索引 83.4 索引使用的注意事项 93.4.1 索引列值必须匹配 93.4.2 索引列不能加操作 103.4.3 索引列不能以通配符作词首 103.4.4 索引列不能使用空值 103.4.5 使用不等于操作（包括、！=和not =）不会使用索引 103.4.6 索引使用的顺序 103.4.7 强制索引失效 123.

2、4.8 无法预知位置的索引列作关联查询，不会用到索引 123.4.9 使用HINTS（提示） 133.5 删除无用的索引 144 使用Between而不是=和= 155 使用COUNT会造成效率的降低。 156 使用DISTINCT会造成效率的降低 157 尽量不用EXIST，IN等耗时的关键字，最好使用多表关联查询。 178 尽量少使用基于大表生成的视图 189 减少网络访问次数 1810 SQL中nowait 的使用 18附件1：如何在VB程序中构造使用变量的查询语句（作者：李生龙） 19附件2：建议：通过内部函数提高SQL效率 19附件3：Hint的使用 201 共享SQL语句ORACL

3、E将已执行的SQL语句的分析放入共享池中，执行SQL语句前，ORACLE先将其与共享池中的SQL做比较，若完全匹配则直接取结果，不再进行分析，可大大提高效率。但要注意：ORACLE只对简单的表提供高速缓冲ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等)（当前设定的Cursor_Sharing的匹配模式为EXACT）必须满足的三个条件当前被执行的语句和共享池中的语句必须完全相同两个语句所指的对象必须完全相同两个SQL语句中必须使用相同的名字的绑定变量因此在书写SQL语句的时候请注意：（1） 对完成相同功能的SQL语句尽量使用COPY的方式，以使得SQ

4、L语句保持“一模一样”（2） 在SQL中尽量使用相同的变量的方式，保持变量名字相同。（在SQL中使用变量的方式参见附件）2 减少访问数据库的次数当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。如：select id from tr_ddm where id=12344 select state from tr_ddm where id=12344应合并成一条。但要注意不是所有的情况都是放在一条语句中能够提高效率，有时一条太大的SQL语句若没

5、有组织好会严重影响效率。在后面的章节中会对此进行分析。3 使用DECODE函数DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如：select a.a_resource_type A端类型,a.a_device_id A端ID,a.row_num A端行号,a.col_num A端列号, a.a_device_start_serial A端起始端子号,a.a_device_end_serial A端终止端子号,b.a_resource_type B端类型,B.a_device_id B端ID,B.row_num B端行号,B.col_num B端列号,B.a_device_sta

6、rt_serial B端起始端子号, B.a_device_end_serial B端终止端子号, a.cable_id 缆,a.cable_first_num A端缆起止序号,a.cable_last_num A端缆终止序号, B.cable_first_num B端缆起止序号,B.cable_last_num B端缆终止序号 ,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_en

7、d_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_serial B端起始 ,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.cab

8、le_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) 缆终止from CABLE_SERIAL_CONNECT a,CABLE_SERIAL_CONNECT bwhere a.cable_id=B.CABLE_ID(+)and a.cable_first_num=B.cable_first_num(+)and a.idb.id(+)and (a.A_Device_ID=3577684 AND a.A_Resou

9、rce_Type=14 AND a.AB_LABEL=A)order by A端行号,A端列号,A端起始端子号,A端起始该SQL语句是查找电缆连接到的交接设备的起止端子号，利用decode函数巧妙解决了线序正序排列和反序排列两种情况，减少了访问表的次数。4 减少ORACLE事务操作用TRUNCATE替代DELETETRUNCATE不能做回滚，所以不需要存放任何可被恢复的信息。多记录时非常有效注意：如果使用时需要保存事务信息，不能使用Truncate替代Delete5 减少对表的查询，特别是重复的查询：很多SQL语句中大量重复使用了固定的查询，即查询语句本身比较复杂，有可能耗时较长。造成每查一次

10、需重复执行一遍。例如：update an_onu_port set sequence_v5=sequence_id+decode(select max(a.sequence_v5) from an_onu_port a,an_v5id b,an_ne_device c,an_onu_subrack d where a.v5id=b.v5id and a.v5id is not null and a.subrack_id = d.id and (d.shelf_id = c.rack_id and c.subrack_id is null) or (d.id = c.subrack_id) an

11、d b.onu_id=c.ne_id and b.v5id=6 and b.olt_id=376193798),null,-1,(select max(a.sequence_v5) from an_onu_port ,an_v5id b,an_ne_device c,an_onu_subrack dwhere a.v5id=b.v5id and a.v5id is not null and a.subrack_id = d.id and (d.shelf_id = c.rack_id and c.subrack_id is null) or (d.id = c.subrack_id) and

12、b.onu_id=c.ne_id and b.v5id=6 and b.olt_id=376193798), v5id=6 where card_id=379370706;又比如：select d.accessnbr from des_service d,des_serviceorder_service e,des_serviceorder where d.serviceid=e.serviceid and e.serviceorderid=f.serviceorderid and (select unique i.jx jx from sw_number_res_bas i where st

13、art_number= (select unique h.accessnbr from des_service h where e.isoldservice=0 and h.serviceid=d.serviceid and d.serviceidseq= (select max(g.serviceidseq) from des_service g where g.serviceid=d.serviceid and rownum=1) and rownum=1) and length(start_number)=8 and rownum=1 ) (select unique i.jx jx f

14、rom sw_number_res_bas i where start_number= (select unique h.accessnbr from des_service h where e.isoldservice=1 and h.serviceid=d.serviceid and d.serviceidseq= (select max(g.serviceidseq)-1 from des_service g where g.serviceid=d.serviceid and rownum=1) and rownum=1) and length(start_number)=8 and r

15、ownum=1) and e.isoldservice=0 and d.serviceidseq= (select max(g.serviceidseq) from des_service g where g.serviceid=d.serviceid) and d.servicetype in (111010,111020,112010,113010,113020,113060,113070,113025,122010) and f.status in (10,11) and f.sotypeid in (402,414,504,513,527,316,317,229,230, 231,23

16、2,322,323,324,325,427,428,115) 在这个例子中，蓝体字部分重复执行了五次之多（MAX()-1实质是重复了MAX()查询），而SELECT MAX 本身就是耗时的操作！ 原句在EXPLAIN WINDOW中分析的结果消耗为14881，若将这些蓝体部分单查（消耗2），再查绿体部分（消耗767，但因为有rownum=1所以去掉UNIQUE后,消耗2），再将结果带入SQL，消耗大约为2000多！6 如何合理使用索引：6.1 要使用索引ORACLE访问表的方式（两种）a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database b

17、lock)的方式优化全表扫描.b. 通过ROWID访问表 采用基于ROWID的访问方式情况,提高访问表的效率。 ROWID包含了表中记录的物理位置信息. ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索

18、引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证. 除了那些LONG或LONG RAW数据类型,可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

19、 6.2 分离表和索引 总是将表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上. 为了保证脚本的通用性，当前共享各个组提交的脚本没有增加表空间指定，在辽宁项目部这部分工作由数据库管理员完成。6.3 尽量建好和使用好索引 建索引也是有讲究的，在建索引时，也不是索引越多越好，当一个表的索引达到4个以上时，对于表进行维护的时候性能可能会下降很多，因为OLTP系统每表超过5个索引即会降低性能，而且在一个sql 中， Oracle 从不能使用超过 5个索引； 要建立选择性高的索

20、引。索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。有文章表明，如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高。如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。如果索引列无选择性，基于开销的优化器可能决定使用全表扫描。 当我们用到GROUP BY和ORDER BY时,ORACLE就会自动对数据进行排序,而ORACLE在INIT.ORA中决定了sort_area_size区的大小,当排

21、序不能在我们给定的排序区完成时,ORACLE就会在磁盘中进行排序,也就是我们讲的临时表空间中排序, 过多的磁盘排序将会令 free buffer waits 的值变高,而这个区间并不只是用于排序的,由此会导致性能下降；当与Oracle建立起一个session时，在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接(dedicated connection)，那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个Program Global Area (PGA) 。如果连接是通过多线程服务器建立的，那么排序的空间就在large_pool中

22、分配。不幸的是，对于所有的session，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disk sorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，当排序的空间需求超出了sort_area_size的大小时，这时将会在TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14,000倍。在开发中应该注意:（1）、select,update,delete 语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20

23、%,它将不能通过使用索引获得性能上的提高. （2）索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片. （3）、在使用索引时一定要按索引对应字段的顺序进行引用。（4）、用（+）比用NOT IN更有效率。（5）、不要对查询中用到的每个列都建立索引，只建立选择性高的索引。例如：表CABLE_CONNECTOR_ROUTE对列USE_STATUS建

24、有索引IDX_USE_STATUS，但其选择性约为几比几百万；列SPECLINENO建有索引IDX_SPECLINENO，选择性要高得多。Select * From CABLE_CONNECTOR_ROUTE Where Use_STATUS=2 And SPECLINENO Like 30946%分析：使用索引IDX_USE_STATUS，成本很低（为2），但执行需耗费76秒多。Select * From CABLE_CONNECTOR_ROUTE Where Use_STATUS|=2 And SPECLINENO Like 30946%分析：使用USE_STATUS|=2，强制IDX_U

25、SE_STATUS索引无效后，使用了索引IDX_SPECLINENO，成为比上一句略增（为10），但实际执行时仅耗费0.5秒不到！6.4 索引使用的注意事项6.4.1 索引列值必须匹配SQL语句中所使用的查询索引列值的类型必须与列的类型匹配。例如：select a.CODE , a.SEQUENCE , a.COOR , a.ROWID from sw_slot awhere a.PANEL_ID=406657623 order by to_number(substr(substr(a.coor,1,(instr(a.coor,;)-1),1,(instr(substr(a.coor,1,(i

26、nstr(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 索引列不能加操作要使用某列索引，不能对该列作任何操作，包括函数、运算符等。例：SELECT SERVICEORDERID,REQUESTID,MESSAGETIME,A1 FROM DES_INTERFACEMESSAGE WHERE INTERFACETYPE

27、=C1306 AND TO_CHAR(MESSAGETIME,YYYYMMDD) =20050403 ORDER BY MESSAGETIME中MESSAGETIME列虽做了索引却不会用到，应改为：AND MESSAGETIME=TO_DATE(20050403,YYYYMMDD) 6.4.3 索引列不能以通配符作词首 对索引主列作条件查询，但条件使用Like操作且值以开始时，无法使用索引。 例如：TR_DDM表对列DDF_ID建有索引，但SELECT * FROM TR_DDM WHERE DDF_ID LIKE %01%由于使用了LIKE %01%不会用到索引，而是做全表扫描。6.4.4

28、索引列不能使用空值在索引列上使用空值，将无法使用索引，因为索引中不含空值。最常用的如IS NOT NULL。例如：select * from tr_ddm_term t Where LOGIC_ID IS NOT NULL全表扫描select * from tr_ddm_term t Where LOGIC_ID0使用IDX_LOGIC_ID6.4.5 使用不等于操作（包括、！=和not =）不会使用索引6.4.6 索引使用的顺序 当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录. 在ORA

29、CLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有 当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的. 如果不同表中两个相同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用. FROM子句中最后的表的索引将有最高的优先级. 如果相同表中两个相同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级. 注意：索引使用顺序可能与优化器选择策略有关，当前Oracle9i缺省参数是choose，在做过表分析以后的使用顺序可能会发生变化，使用时请在测试环境进行测试。例子: TR_DDM_TERM表对ID建有主键PK_ID，对DDM_ID建有非唯一性索引IDX_DDM_ID，对LOGIC_ID建有非唯一性索引IDX_LOGIC_ID。下述SQL语句对索引的使用情况：SELECT * FROM TR_DDM_TERM