plsql知识梳理-sql优化策略

1  In or的效率说明
       IN 的作用相当与OR，如语句: Select * from table1 where tid in (2,3) 和 Select * from table1 where tid=2 or tid=3 是一样的,都会引起全表扫描,如果 tid 上有索引,其索引也会失效。

     Sqlserver数据库中 in=or的执行效率一样。

     针对其它数据库 in的执行效率比or高。(多家之言)

     in or 都可以自动过滤重复数据。 即一条记录只显示一次。

2 In exists

    常量数值范围 用 in ，子查询范围  用 in 或 exists。

     针对子查询的构建，存在效率问题。

     如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标。

     exists()后面的子查询被称做相关子查询,他是不返回列表的值的.只是返回一个ture或false的结果,其运行方式是先运行主查询一次,再去子查询里查询与其对应的结果。

     如果是ture则输出,反之则不输出。

    in()后面的子查询是返回一个结果集的。

    如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：应该灵活使用in,而不能直接就把IN关键字给完全抛弃不用。要根据操作的数据情况来看。

     例如：表A（小表），表B（大表）
                           select * from A where cc in (select cc from B)
                              效率低
                           select * from A where exists(select 1 from B where B.cc=A.cc)
                              效率高
                             而 
                           select * from A where cc in (‘1’，‘2’),就没有太大的必要去做sql优化。

     补充:
    exists()后面的子查询被称做相关子查询 ，他是不返回列表的值的，只是返回一个ture或false的结果。

     (这也是为什么子查询里是 "select   1 "的原因   当然也可以select任何东西,如可以写成 select 2 )

      总结为：

              当构建过滤条件的子查询时候，如果子查询结果集很少，就用 in

              当构建过滤条件的子查询时候，如果子查询结果集很大，就用 exists

              通常在过滤条件子查询时，选用exists

3  not in 和not exists

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

4 count（*） count（字段）的执行效率

     在不加WHERE限制条件的情况下，COUNT(*)与COUNT(COL)基本可以认为是等价的；

     但是在有WHERE限制条件的情况下，COUNT(*)会比COUNT(COL)快非常多；

     用 count(*)和用 count(主键)的速度是相当的,而 count(*)却比其 他任何除主键以外的字段汇总速度要快；

    count(唯一键索引的效率)比count(非唯一键)效率高，但count(*)的综合效率会更高些。

5  带通配符（%）的like语句

     一个例子来看这种情况

    select * from employee where last_name like '%lee%';

    这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
    select * from employee where last_name like 'lee%';
   所以 在做表结构设计的时候 一定要主要一些字段内容的存储格式。

6 GROUP BY和where一起使用

     Where 是在分组前执行的。
     Group by 是在分组后执行的。
     提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。提高sql执行效率。

     举例

     select name1,count(*) from productor  group by name1 having name1 in ('leean','weiguo');   用时 0.172s

     select name1,count(*) from productor  where name1 in ('leean','weiguo') group by name1;   用时 0.062s

7 使用表别名访问能提高效率

    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8 WHERE子句中的连接顺序

    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的

     末尾。

9  SELECT子句中避免使用*

      当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用'*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
      总结，当查询灵活性的时候，用* ,只针对几个列的信息查询时，可以直接列出列的名称。在这个地方，执行效率和代码灵活性不能兼得。

10  删除数据方面，可以适当用TRUNCATE替代DELETE
           1， TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。
           2， TRUNCATE将重新设置高水平线和所有的索引。
           3， TRUNCATE不能触发任何DELETE触发器。
             语法:
           delete from table_name1   dml语言
           truncate table table_name1  ddl语言 

11 用>=替代>

     当>=完全能替代>时候，可以用>=替代>
    高效: 
        SELECT * FROM EMP WHERE DEPTNO >=4 
    低效
       SELECT * FROM EMP WHERE DEPTNO >3 
     两者的区别在于, 前者DBMS将直接跳到第一个DEPTNO等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

12  用UNION替换OR (适用于索引列)

      很多种情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果。

      如果union 涉及到的column没有被索引, 查询效率可能会因为你没有选择OR而降低。

      在下面的例子中, LOC_ID 和REGION上都建有索引。

                 高效: 
               SELECT LOC_ID , LOC_DESC , REGION 
               FROM LOCATION    WHERE LOC_ID = 10 
                     UNION 
              SELECT LOC_ID , LOC_DESC , REGION 
              FROM LOCATION   WHERE REGION = ‘MELBOURNE’

                低效: 
               SELECT LOC_ID , LOC_DESC , REGION 
               FROM LOCATION    WHERE LOC_ID = 10 OR REGION =‘MELBOURNE’

             总结如下：

                   当条件过滤列是索引列的时候，用union 来替代or 是提高效率。

                  没有创建索引的情况下，使用UNION改写后效率没有提高，反而下降了， 一定要注意查看使用的字段是否已经创建了索引。

13 用UNION-ALL 替换UNION ( 如果可能完全替换的话)

            当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此操作过程中还是要从业务需求分析使用UNION ALL的可行性.  
 UNION  将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的。

14 避免在索引列上使用计算       

          WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描。

          低效: 
                SELECT …   FROM DEPT  WHERE SAL * 12 > 25000;
          高效:
                SELECT …   FROM DEPT   WHERE SAL  > 25000/12;