如果提高排序及表连接的效率

 

文章截自《品悟性能优化》。

 

一、排序介绍：

Oracle里面有哪些操作需要排序,或者有哪些操作是隐含进行排序的?

1.order by短语是当然要进行排序的.

2.其实还有distinct,Union等操作会隐藏进行排序.

      a.distinct是需要先排序相关字段,然后去掉重复记录.

      b.union和union all的区别是,前者的结果集也需要去掉两个查询语句的重复记录,所以需要排序.后者的结果集是所有记录,包括重复记录,所以不需要排序.如果两个结果集之间根本没有交集,当然使用union all而不是union.

 

二、Oracle表连接技术和应用.

1.数据库精髓之一：表连接.

2.最经典,最常用的表连接技术_嵌套循环.

      以举例方式,来形象、通俗地描述Oracle各种表连接技术。例如，如下语句，欲查询所有员工所在部门的所有情况：

select e.*,d.*
from emp e, dept d
where e.deptno = d.deptno;

      Oracle经典的嵌套循环（Nested_Loop）连接执行计划如下： 

       即先循环查询dept，再按dept每条记录去查询emp，找到dept对应部门的所有员工。 

       如果以图表示如下：

 

        也就是说Oracle是以两层循环方式实现两个表的连接和检索，其中dept表是外循环，emp表是内循环。

        那么我们把外循环表（dept）叫作外表或驱动表，内循环表（emp）叫作内表或被驱动表。

3.继续举例，如果要查询员工号为7499的员工信息和所在部门信息，语句如下：

select e.*,d.*
from emp e, dept d
where e.deptno = d.deptno
and e.empno=7499 ;

      为提高查询效率，Oracle应结合索引技术来实现上述操作。正确的查询方式应该是：先按照建立在empno字段上的索引去emp表查询empno为7499的员工信息，再根据7499所

在的部门号（deptno）去dept表查询该部门的详细信息，而且dept表的deptno字段上应该有索引。因此，这就是该语句的执行计划。

如果以图表示，如下：  

 

       所以单字段索引设计建议：如果是多表连接SQL语句，注意被驱动表（drived table）的连接字段是否需要创建索引。

       在上例中，被驱动表是dept，dept表连接字段是deptno。而emp的deptno字段是可以不需要建索引的。

       

 

继续举例，如果要查询员工号为7499的员工信息，并且部门在DALLAS的部门信息，语句如下：

select e.*,d.*
from emp e, dept d
where e.deptno = d.deptno
and e.empno = 7499 
and d.loc = 'DALLAS';

        正确的查询方式应该还是：先按照建立在empno字段上的索引去emp表查询empno为7499的员工信息，再根据7499所在部门号（deptno）去dept表查询该部门详细信息。此时

dept表还有一个条件：loc='DALLAS'，因此可考虑按（deptno,loc）复合方式去查询dept表，效率更高，即可建立（deptno,loc）字段上的复合索引（idx_dept_2）。因此，这就

是该语句的执行计划： 

如果以图表示，如下： 

 

 

        复合索引设计建议：如果是多表连接SQL语句，注意是否可以在被驱动表（drived table）的连接字段与该表的其他约束条件字段上创建复合索引。

        在上例中，被驱动表是dept，dept表连接字段是deptno，而loc是其他约束条件，所以可以创建（deptno,loc）字段上的复合索引。而emp表的deptno字段是不需要建索引

的。

        需要进一步说明的是，为阐述在表连接中建立复合索引的重要性，作者故意将建立在deptno字段上的dept表的主键pk_dept先删除掉。因为有如下建议：如果单个字段是主

键或唯一字段，或者可选性非常高的字段，尽管约束条件字段比较固定，也不一定要建成复合索引，可建成单字段索引，降低复合索引开销。

        即在本例中，本来是不需要建立上述复合索引（idx_dept_2）的，只需要为dept表的loc字段建立单字段索引。

 

在Oracle中,适合于大批量数据处理的连接技术只有如下两类. 

1.排序合并连接(Sort/Merge)技术 

       该技术也非常易于理解,即两个表先按连接字段进行排序,再将两个表的排序结果进行顺序匹配,将合并结果返回给客户.以下是其示意图:

 例如:使用如下语句,查询所有员工所在部门的所有情况: 

select e.*, d.* 
from emp e, dept d 
where e.deptno = d.deptno;

 oracle采用排序合并连接技术的执行计划如下:

 即将DEPT表和EMP表先按deptno字段进行排序,再将两个表的排序结果进行顺序匹配,最后将合并结果返回给客户.

 

2.哈希连接(HASH)技术

       总体而言,哈希连接(HASH)技术和排序合并连接(Sort/Merge)技术一样,适合于大表与大表,更准备地讲是大数据量和大数据量的连接应用场景.而且通常情况下,哈希连接(HASH)技术性能优于排序合并连接(Sort/Merge)技术,更优于嵌套循环(Nested_Loop)连接技术.尤其是当哈希连接(HASH)与Oracle并行处理技术相结合的情况下,将极大地提高系统的整体吞吐量.以下是哈希连接(HASH)示意图:

  

 例如,如下语句,欲查询所有员工所在部门的所有情况:

select e.*, d.*
from emp e, dept d
where e.deptno = d.deptno;

 Oracle采用哈希连接(HASH)技术的执行计划如下:

 

 

 多表连接优化的基本思路

       国内很多IT系统的SQL语句经常让人望而生畏,不仅逻辑复杂,更是冗长地好几屏幕.但是,如前所述,关系数据库的精髓就是多表连接,再复杂的应用,其实也只是多表连接的实就Oracle每次都只进行两个表的连接.因此,只要按如下基本思路去优化多表连接,多复杂的应用都可以应付自如了.

 

总体思路

       首先应判断该语句是OLTP(联机事务处理)应用还是OLAP(联机分析处理)应用(见OLTP和OLAP文章). 

       如果是OLTP应用,则优化思路是由小到大,即从限制性最强,返回记录最少的连接开始,基本采用嵌套循环连接技术,依次完成其他表的连接,并在访问每张表时,合理使用索引,特别是复合索引技术.

       如果是OLAP应用,则优化思路基本是HASH加并行处理,表连接顺序不是最主要的.

 

OLTP应用的表连接优化

OLTP应用的表连接优化的基本思路如下:

        1.尽量将限制性最强的表作为驱动表.当然,驱动表上的限制性条件字段上应该有索引,包括主键 唯一索引或其他索引,复合索引等.

        2.考虑如下原则:在每次连接操作之后尽量保证返回记录数最少,传递给下一个连接操作.
        3.每次连接操作基本采用嵌套循环连接技术.
        4.尽量通过在被驱动表的连接字段上的索引,访问被驱动表.
        5.如果被驱动表上还有其他限制性条件,可以遵循复合索引创建原则,创建合适的复合索引.
        6.全表扫描也许是合理的.例如若干小表 代码表的访问.
        7.依次类推,顺序完成所有表的连接操作.

 

 

如何使用子查询(能不写子查询,尽量不写子查询,而是直接编写多表连接操作).

到底是使用in还是exists

        的确,并不是所有多表之间的访问都可以通过表连接方式完成.如果必须书写子查询代码,到底是使用in还是exists?

1.in和exists的原理.
       in操作的原理是先进行子查询操作,再进行主查询操作.例如,欲查询SALES部门的所有员工信息,以in方式编写的语句如下:

select e.*
  from emp e
 where e.deptno in (select d.deptno from dept d where d.dname = 'SALES')

其执行计划如下:

       即执行过程是:先按建立在dname字段上的索引IDX_DEPT_DNAME访问DEPT表,再按建立在EMP表的deptno字段上的索引IDX_EMP_DEPTNO访问EMP表.此时,EMP表成了被驱动表,因此应在EMP表的deptno字段上建立索引IDX_EMP_DEPTNO.
       上述查询,如果以exists方式编写,则语句如下:

select e.*
  from emp e
 where exists (select 1
          from dept d
         where e.deptno = d.deptno
           and d.dname = 'SALES')

其执行计划如下: 

       即通常情况下,exists操作的原理是先进行主查询操作,再到子查询中进行过滤.本例中:先进行EMP表的全表扫描,再根据每个员工的部门号deptno去DEPT表中查询是否是SALES部门,进行过滤.显然在这种情况下exists查询效率低于in操作.

 

2.in和exists的使用建议. 

       先回到前面讲的一个原理:Oracle里面只要该技术依然存在,就说明它一定有应用场景,否则就该直接被淘汰了.in和exists依然并存,说明它们一定有不同的应用场景.如果理解上述in和exists技术原理,就不难理解如下的关于in和exists的使用的一般性建议了.
       a.如果限制性强的条件在子查询,则使用in操作.
       b.如果限制性强的条件在主查询,则使用exists操作.
       上述例子适合使用in操作,而欲查询号为7499,并且部门位于DALLAS的详细员工信息,则适合使用如下的exists方式了:

select e.*
  from emp e
 where empno = 7499
   and exists (select 1
          from dept d
         where e.deptno = d.deptno
           and d.loc = 'DALLAS')

       在这里假设DALLAS包括多个部门,DEPT表的loc字段的可选性显然低于EMP表基于empno的主键.该语句的执行计划如下:  

       即先通过EMP表的empno字段上的主键PK_EMP,快速定位出empno=7499的记录,再通过该记录的deptno值,基于主键PK_DEPT去访问DEPT表,并过滤掉loc不为DALLAS的值.
而如果以in操作书写,则语句如下:

select e.*
  from emp e
 where empno = 7499
   and deptno in (select deptno from dept d where d.loc = 'DALLAS')

 执行计划如下:

       即先通过DEPT表的loc字段上的索引IDX_DEPT_LOC,查询出loc为DALLAS的部门信息,再通过deptno值,基于IDX_EMP_DEPTNO索引,去访问EMP表,并过滤掉empno不为7499的值.显然,在这种情况下,in操作效率低于exists操作. 

 

技术方面的总结:
1.尽量将限制性最强的表作为驱动表(外表).
2.尽量别写子查询.
3.再次诠释20/80规则(20%的简单技术可以解决80%的问题).