工作中必须会的Sql性能优化及常见问题

TableOfContentSQL性能优化1IN和EXISTS1NOTIN和NOTEXISTS1选择最有效的表名顺序1WHERE字句中的连接顺序2通配符的使用错误!未定义书签用IN代替OR错误!未定义书签避免进行全表扫描2常见问题3存储过程执行快，但程序调用则执行慢31Sql性能优化

1.1in和existsin是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists子查询表小的用in；例如表A（小表），表B（大表）select*fromAwhereccinselectccfromB效率低用到了A表上cc列的索引;select*fromAwhereexistsselectccfromBwherecc=A.cc—＞效率高，用到了B表上cc列的索引相反的:select*fromBwhereccinselectccfromA效率高用到了B表上cc列的索引select*fromBwhereexistsselectccfromAwherecc=B.cc一一＞效率低，用到了A表上cc列的索弓I

1.2notin和notexists如果查询语句使用了notin那么对内外表都进行全表扫描，没有用到索引；而notexists的子查询依然能用到表上的索引所以无论哪个表大用notexists都比notin要快

1.3选择最有效的表名顺序例如TAB11000条记录，TAB21条记录选择记录最少的作为基表Selectcount（*）fromtabltab2;如果有3个或者3个以上的表则选择交叉表作为基表where字句中的连接顺序Oracle采用自下而上的顺序解析WHERE子句根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾避免进行全表扫描1）应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如selectidfromtwherenumisnull可以在num上设置默认值0确保表中num列没有null值，然后这样查询selectidfromtwherenum=02）应尽量避免在where子句中使用!二或操作符，否则将引擎放弃使用索引而进行全表扫描3）应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描如selectidfromtwherenum=10ornum=20可以这样查询selectidfromtwherenum=10unionallselectidfromtwherenum=204）in和notin也要慎用，否则会导致全表扫描，如selectidfromtwherenumin（l23）对于连续的数值，能用between就不要用in了selectidfromtwherenumbetween1and35）下面的查询也将导致全表扫描selectidfromtwherenamelike%abc%f若要提高效率，可以考虑全文检索6）应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描如selectidfromtwherenum/2=100应改为selectidfromtwherenum=100*27）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描如selectidfromtwheresubstring（name』3）=abc//oracle总有的是substr函数selectidfromtwheredatediff（daycreatedate2OO5-11-301）=0〃查过了确实没有datediff函数应改为selectidfromtwherenamelikeabc%selectidfromtwherecreatedate=2005-ll・30andcreatedate2005-12-T//oracle中时间应该把char转换成date如:createdate=to_date（2005-ll-30yyyy-nim-dd）8）不要在where子句中的“=”左边进行函数、算术运算或其疝表达式运算，否则系统将可能无法正确使用索引使用表的别名（Alias）当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上.这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时SQL解析器无法判断这个Column的归属索引建设原则:

1、索引应该经常建在Where子句经常用到的列上如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%则应该考虑

2、对于两表连接的字段，应该建立索引如果经常在某表的一个字段进行OrderBy则也经过进行索引

3、不应该在小表上建设索引优缺点

1、索引主要进行提高数据的查询速度当进行DML时，会更新索引因此索引越多，则DML越慢，其需要维护索引因此在创建索引及DML需要权衡创建索引单一索引:CreateIndexIndex-NameOnTable_NameColumn_Name;复合索弓I CreateIndexi_deptnojobonempdeptnojob;——在emp表的deptno、job歹U建立索弓Iselect*fromempwheredeptno=66andjob=sals-走索弓Iselect*fromempwheredeptno=66ORjob=sals・将进行全表扫描不走索弓Iselect*fromempwheredeptno=66・走索弓Iselect*fromempwherejob=sals-＞进行全表扫描、不走索引如果在where子句中有OR操作符或单独引用Job歹U（索引列的后面列）则将不会走索弓I，将会进行全表扫描2常见问题

2.1存储过程执行快但程序调用则执行慢由于存储过程是预编译的，在第一次执行的时候，会生成执行计划，以后执行的时候，会使用这个执行计划（除非存储过程侯或者显示指定重新编译），而不是每次执行时都去生成执行计划当存储过程涉及的对象结构调整，或者相关的数据产生了很大变化这可能导致原来的计划不适合当前的现状（执行计划过期），这种情况下应该重新编译存储过程解决存储过程优化了，但还是慢，不是程序的问题，有可能是执行计划过期，重新编译该存储过程即可。