高效SQL及一些小技巧

2019独角兽企业重金招聘Python工程师标准>>>

转载 http://blog.sina.com.cn/s/blog_3f1b8dcd0100062g.html

良好的SQL语句风格易于发现问题、易于阅读，移植性好。80％的性能问题是由不良SQL语句引发的。

    尽可能对查询条件的列建立索引；

    尽量不要在where条件中对查询列使用函数，除非建立了相应的函数索引，如可用带前导字符的like代替substr；

    任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的；

    对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的；

    通配符（%）在搜寻词首出现，Oracle系统不使用索引；

    任何在Order by语句的非索引项或者有计算表达式都将降低查询速度；

    如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符，用NOT就不能用索引，尽量用“< or >”代替“<>”；

    IN和EXISTS，绝大多数情况下EXISTS比IN性能高，加上NOT也一样；

    Where子句中要将值和列的类型匹配，如数字和字符间，否则将导致全表扫描；

    不要在应用程序中使用关联子查询，可使用联机视图来代替；

    对于带有很多OR的非常复杂查询，可以考虑用union all来重写；

    可批量完成的操作不应该由循环方式来进行；

    用格式一致的SQL语句，包括字母的大小写、标点符号、换行的位置等都要一致，以减少SQL分析编译开销；

    游标、动态SQL语句尽量用绑定变量方式调用，以减少SQL分析编译开销；

    在可能产生异常的地方用异常处理；

    把一些常用功能模块用函数或存储过程封装；

 

小技巧

 

1.选择最有效率的表名顺序
FROM子句按照从右到左的顺序处理,记录条数最少的,交叉表,放右

WHERE子句采用自下而上的顺序解析,可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
SELECT *                                 使用"*"会降低效率
FROM EMP E
WHERE  SAL > 50000
AND    JOB = 'MANAGER'
AND    25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);

SELECT *
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
             WHERE MGR=E.EMPNO)
AND    SAL > 50000
AND    JOB = 'MANAGER';

2.减少访问数据库的次数
    SELECT A.ENAME , A.SALARY ,
            B.ENAME , B.SALARY
    FROM EMP A,EMP B
    WHERE A.EMPNO = 342
    AND   B.EMPNO = 291;

3.使用DECODE函数来减少处理时间
SELECT COUNT(DECODE(DEPTNO,0020,'X',NULL)) D0020_COUNT,
        COUNT(DECODE(DEPTNO,0030,'X',NULL)) D0030_COUNT,
        SUM(DECODE(DEPTNO,0020,SAL,NULL)) D0020_SAL,
        SUM(DECODE(DEPTNO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE 'SMITH%';

4.整合简单,无关联的数据库访问

定期的重构索引是有必要的
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

set autotrace traceonly /*traceonly 可以不显示执行结果*/
set autotrace off

转载于:https://my.oschina.net/jeromePeng/blog/204578