基于实际开发的Oracle SQL优化建议

柔情只为你懂 2022-05-31 07:21 202阅读 0赞

1)避免Select \*  
   2)编写SQL尽量使用相同的编码风格。语句解析、语句执行、返回执行结果等几个步骤。共享的SGA区，如有省去解析sql  
   3)使用truncate替换delete。delete属于DML语言。  
   4)在确保业务逻辑前提下及时COMMIT事务  
   5)in：子查询->主查询  exists：主查询->子查询。子查询的记录较少，主查询是记录较大且有索引使用in，反之亦然。  
     not exists 的效率什么时候都高于not  in  
   6)使用exists替代distinct  
     低效SQL:  
     select distinct e.deptno,d.dname from dept d ,emp e where d.deptno = e.deptno  
     高效SQL:  
     select d.deptno,d.dname from dept d where exists(select \* from emp e where e.deptno = d.deptno)  
   7)使用union替换union all  
     union->去重，排序;union all->简单的结果连接  
   8)在保证功能的前提下减少对表的查询  
     低效SQL:  
     SELECT ename,JOB,sal,deptno FROM emp WHERE JOB = (SELECT JOB FROM emp WHERE ename = 'SCOTT')  
                                          AND deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');  
     高效SQL:  
     SELECT ename,JOB,sal,deptno FROM emp WHERE (JOB,deptno) = (SELECT JOB,deptno FROM emp WHERE ename = 'SCOTT');  
   9)尽量使用表的别名(Alias),并在列前标注来自哪个表  
     低效SQL:  
     select ename,job,sal,EMP.deptno from emp,dept where EMP.deptno = dept.deptno;  
     高效SQL:  
     select e.ename,e.job,e.sal,e.deptno from emp e,dept d where e.deptno = d.deptno;  
   10)不要使用having子句实现where子句的功能  
     带分组的SQL，尽可能地把筛选条件放到where子句中进行筛选，having用来对分组过的结果进行过滤  
   11)在表连接中的优化原则  
     \*识别驱动表，将数据量最小的表作为驱动表  
     \*如果是多个连接条件查询，将过滤掉最多纪录的条件放到最前面(也就是where后面有多个条件)  
   12)合理使用索引  
     \*避免全表扫描：没有索引，没有where条件，查询条件的列没有索引，查询条件中对索引列使用了函数或算数表达式  
      ex： where upper(job) = 'SALEMAN'     ->使用函数  
           where not in('CLARK','SALEMAN')  ->条件中not in  
   where job is not null            ->条件中有is null，is not null,<>,!=  
   where job like '%SALEMAN%'  
     \*尽量避免使用like操作符  
     \*避免对大表查询中的列使用单行函数或算数表达式  
     -> where trunc(birthday,'YEAR') = '1998'     
        优化为：   
where birthday >= '1998-01-01'  
and birthday <= '1998-12-31'  
     -> where birthday  + 30 = sysdate   避免在索引列上进行计算  
        优化为：；  
where birthday = sysdate + 30;  
     -> where productId = 12345678;     进行隐式数据类型转换也会使索引失效  
        优化为：  
        where productId = '12345678'  
     \*对于order by 语句中的列上如果没有索引会降低性能，建议在经常排序的列上添加索引，并且避免在这些列上使用函数表示式