SQL与效率[z]

From: http://user.qzone.qq.com/67622728/blog/1198426480

            ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如： A表2万条记录，B表1条记录
           选择B作为基础表 (最好的方法)
           select count(*) from a ,b执行时间0.96秒
           选择TAB2作为基础表 (不佳的方法)
           select count(*) from b,a 执行时间26.09秒

         SELECT子句中避免使用 ‘ * ‘
         当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

         <> 操作符（不等于）
         不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
         推荐方案：用其它相同功能的操作运算代替，如
         a<>0 改为 a>0 or a<0
         a<>’’ 改为 a>’’

         WHERE子句中的连接顺序．
         ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如:(低效,执行时间156.3秒)
        select * from emp e  where  sal > 50000  and  job = ‘manager’  
        and  25 < (select count(*) from emp  where mgr=e.empno);
        (高效,执行时间10.6秒)
        select * from emp e  where 25 < (select count(*) from emp where mgr=e.empno)
        and    sal > 50000  and job = ‘manager’;

        LIKE操作符
        LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

      尽量多使用COMMIT 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
                                 a. 回滚段上用于恢复数据的信息.
                                 b. 被程序语句获得的锁
                                 c. redo log buffer 中的空间
                                 d. ORACLE为管理上述3种资源中的内部花费

     减少对表的查询，在含有子查询的SQL语句中,要特别注意减少对表的查询.
      
     尽量多使用表的别名(Alias)，当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

     IN 操作符
    用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。
    但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
    ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
    推荐方案：在业务密集的SQL当中尽量不采用IN操作符。
    NOT IN操作符
    此操作是强列推荐不使用的，因为它不能应用表的索引。
    推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替。

评论

1 楼 mapengjie 2010-04-01  

谢谢，我看了之后学到了一些知识