oracle-sql性能优化

1.连接查询的表顺序

select a.empno，a.ename，c.deptno，c.dname，a.log_action from emp_log a,emp b,dept c

在执行时，oracle会先查询dept表，根据dept表查询的行作为数据源串行连接emp表继续执行，因此dept表又称为基础表或驱动表。由于连接的顺序对于查询的效率有非常大的影响。因此在处理多表连接时，必须选择记录条数较少的表作为基础表，oracle会使用排序与合并的方式进行连接。比如先扫描dept表，然后对dept表进行排序，再扫描emp表，最后将所有检索出来的记录与第一个表中的记录进行合并

如果有3个以上的表连接查询，就需要选择交叉表作为基础表。交叉表是指那个被其他表所引用的表，由于emp_log是dept与emp表中的交叉表，既包含dept的内容又包含emp的内容。

select a.empno，a.ename，c.deptno，c.dname，a.log_action from emp b,dept c,emp_log a;

2.指定where条件顺序

在查询表时，where子句中条件的顺序往往影响了执行的性能。默认情况下，oracle采用自下而上的顺序解析where子句，因此在处理多表查询时，表之间的连接必须写在其他的where条件之前，但是过滤数据记录的条件则必须写在where子句的尾部，以便在过滤了数据之后再进行连接处理，这样可以提升sql语句的性能
好的写法：

SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action, b.sal
  FROM emp b, dept c, emp_log a
 WHERE a.deptno = b.deptno AND a.empno=b.empno AND c.deptno IN (20, 30)

坏的写法：

SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action, a.mgr
  FROM emp b, dept c, emp_log a
 WHERE c.deptno IN (20, 30) AND  a.deptno = b.deptno

3.避免使用*符号

Oracle在遇到*符号时，会去查询数据字典表中获取所有的列信息，然后依次转换成所有的列名，这将耗费较长的执行时间，因此尽量避免使用*符号获取所有的列信息

坏的写法：

SELECT * FROM emp

4.使用where而非having

where子句和having子句都可以过滤数据，但是where子句不能使用聚集函数，如count max min avg sum等函数。因此通常将Having子句与Group By子句一起使用
注意：当利用Group By进行分组时，可以没有Having子句。但Having出现时，一定会有Group By
需要了解的是，WHERE语句是在GROUP BY语句之前筛选出记录，而HAVING是在各种记录都筛选之后再进行过滤。也就是说HAVING子句是在从数据库中提取数据之后进行筛选的，因此在编写SQL语句时，尽量在筛选之前将数据使用WHERE子句进行过滤，因此执行的顺序应该总是这样。
①.使用WHERE子句查询符合条件的数据
②.使用GROUP BY子句对数据进行分组。
③.在GROUP BY分组的基础上运行聚合函数计算每一组的值
④.用HAVING子句去掉不符合条件的组。
例子：查询部门20和30的员工薪资总数大于1000的员工信息

坏的写法：

select empno,deptno，sum(sal) 
from emp group by empno，deptno
having sum(sal) > 1000 and deptno in (20，30);

好的写法：

在having子句中，过滤出部门编号为20或30的记录，实际上这将导致查询取出所有部门的员工记录，在进行分组计算，最后才根据分组的结果过滤出部门 20和30的记录。这非常低效，好的算法是先使用where子句取出部门编号为20和30的记录，再进行过滤。

select empno，deptno，sum(sal) 
from emp where deptno in （20,30）
group by empno，deptno having sum (sal) > 1000;

5.使用UNION而非OR

如果要进行OR运算的两个列都是索引列，可以考虑使用union来提升性能。
例子：比如emp表中，empno和ename都创建了索引列，当需要在empno和ename之间进行OR操作查询时，可以考虑将这两个查询更改为union来提升性能。

坏的写法：

select empno，ename，job，sal from emp where empno > 7500 OR ename LIKE 'S%';

好的写法：

select empno，ename，job，sal from emp where empno > 7500 
UNION
select empno，ename，job，sal from emp where ename LIKE 'S%';

但这种方式要确保两个列都是索引列。否则还不如OR语句。
如果坚持使用OR语句，①.需要记住尽量将返回记录最少的索引列写在最前面，这样能获得较好的性能，例如empno > 7500 返回的记录要少于对ename的查询，因此在OR语句中将其放到前面能获得较好的性能。②.另外一个建议是在要对单个字段值进行OR计算的时候，可以考虑使用IN来代替

以下写法最好用in

select empno，ename，job，sal from emp where deptno=20 OR deptno=30;

6.使用exists而非IN

坏的写法：

SELECT *
  FROM emp
 WHERE deptno IN (SELECT deptno
                    FROM dept
                   WHERE loc = 'CHICAGO');

好的写法：

SELECT a.*
  FROM emp a
 WHERE NOT EXISTS (SELECT 1
                     FROM dept b
                    WHERE a.deptno = b.deptno AND loc = 'CHICAGO');

同样的替换页发生在not in 和not exists之间，not in 子句将执行一个内部的排序和合并，实际上它对子查询中的表执行了一次全表扫描，因此效率低，在需要使用NOT IN的场合，英爱总是考虑把它更改成外连接或NOT EXISTS

坏的写法：

SELECT *
  FROM emp
 WHERE deptno NOT IN (SELECT deptno
                    FROM dept
                   WHERE loc = 'CHICAGO');

好的写法1：

SELECT a.*
  FROM emp a, dept b
 WHERE a.deptno = b.deptno AND b.loc <> 'CHICAGO';

好的写法2:

select a.* from emp a 
          where NOT EXISTS (
            select 1 from dept b where a.deptno =b.deptno and loc='CHICAGO');

7.避免隐式类型转换

posted @ 2019-11-19 12:03 林被熊烟岛阅读(140) 评论(0) 编辑收藏举报

刷新页面返回顶部