开发常用sql优化总结

在索引列上使用NOT或者“<>”;

对索引列使用函数或者计算；

NOT IN操作；

通配符位于查询字符串的第一个字符；

IS NULL或者IS NOT NULL；

多列索引，但它的第一个列并没有被Where子句引用；

 

1、检查SQL语句关联到的表有多少个

一条SQL语句如果关联到的表个数太多，就会由于排列组合的基数增大，导致CBO在产生执行计划的时候，可选执行计划太多，这不仅会增加CBO获得最优执行计划的时间，还可能使SQL语句的执行计划变得很不稳定,让同一条SQL语句在某个情况下出现执行效率较低的情况.一般而言，参与SQL语句的表个数不要超过5个，尤其是参与连接的表不是主键关联的基表时，表的连接个数更不要太多，如果的确要关联那么多表，就要考虑是否可以将SQL语句拆分，或者是否是表结构的设计不太合理，需要考虑方法加以改善。

2、SQL语句关联主表中的WHERE条件是否有合适的索引

我们都很惧怕SQL语句在执行时出现全表扫描，尤其是大表的全表扫描，这在多数情况下不无道理，因为我们的数据大多是在线实时处理的，此时SQL语句选择的数据量不能太多，响应时间却要求极高，这说明数据的选择性一般较好，通过全表扫描可能不太合理，往往需要通过区分度高的索引来直接筛选数据。如果SQL中关联主表的WHERE条件上有索引，查看执行计划时也确实用到了这个索引，问题就不会太大，如果没有索引，就要考虑其他的已知条件是否可以有效筛选数据，还是不行的话，建议对该表新建一个索引进行评估。

3、SQL语句中是否使用了不恰当的函数

在SQL语句中不恰当地使用函数，尤其是在索引字段上使用函数，会降低SQL语句的执行效率，这几乎成了我们的共识，不过，我们在SQL开发时，仍然会不经意的、在不一定要使用函数的地方使用函数，这一点在日期函数的使用上表现得比较突出，比较典型的写法为：trunc(表的日期字段)>=或<=trunc(传入的日期变量),其实这种写法可以转变为仅在变量这部分使用函数，而不必要在数据库的字段上使用函数。

 

4、是否可以用其他方法代替not in ,not like，!= 等WHERE条件

在SQL语句中使用Not in,not like,和!=的写法，和在字段上使用函数一样，也会致使该字段上的索引无法使用，尤其是not in的写法，无论在哪种情况下,它都是最低效的，因为它要对子查询中的表执行一个全表遍历。据说用EXISTS替代IN、用NOT EXISTS替代NOT IN可以提高SQL效率，我们在开发时，不妨尝试比较一下它们的执行效率。

 

5、SQL语句的写法是否尽量简洁，不必要的字段是否出现在选择列中，是否尽可能的避免了select * from的写法

有两个原因：  1、当我们仅将需要查询的字段列出时，如果这些字段已经在组合索引中，则Oracle仅访问索引就可以获得所需要的数据，而不必访问表，进而提升效率。2、ORACLE在解析的过程中， 会将'*' 依次转换成所有的列名， 这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

 

6、索引字段的分布是否均匀，是否会因为大量相同的默认值带来执行计划的改变，以致SQL语句的执行效率突然恶化

由于最初设计的原因，数据库表的某个字段可能大量默认同一个值，或者虚拟为某个固定的值；还有一种情况是：表中数据会随着某个状态的变化，最后达到最终状态而不再更改，所以该表中的大量数据都属于这种状态，其它状态的数据很少。这时，数据的分布就很不均匀，会出现两种弊端：一是索引的访问效率降低，二是绑定变量后，可能因为一次查询了不均匀的数据，产生了较差的执行计划，而后的每一次访问都效率较差。建议对这种情况预先进行分析，写SQL时将不均匀的默认值排除在外，甚至可以建立基于函数的索引，提高索引的效率。

 

7、是否在应该使用绑定变量的情况下，没有使用绑定变量

虽然绑定变量可能在数据不均匀时带来性能缺陷，使用绑定变量带来的好处仍然毋庸置疑，既可以减少对数据库内存区共享池的占用，又可以避免对SQL语句的多次硬解析而降低效率。所以在分析了数据的分布之后，强烈建议在适宜使用绑定变量的SQL中，使用邦定变量，例如在java变量中使用双“#”号，在PL/SQL存储过程中避免动态SQL等。

 

8、是否通过查看SQL语句的执行计划，预估SQL语句的执行效率

整个SQL语句分析完毕，为了评估其可能的执行效率，还可以看看SQL语句的执行计划，如果发现和最初设想的访问路径或者连接方式不同，可以进一步分析原因所在，考虑是否能够改善。