`

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

阅读更多

 

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

 

一、排序介绍:

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%的问题).

 

 

 

  • 大小: 12.9 KB
  • 大小: 11.4 KB
  • 大小: 15.3 KB
  • 大小: 11.6 KB
  • 大小: 13.3 KB
  • 大小: 16.5 KB
  • 大小: 13.2 KB
  • 大小: 19.3 KB
  • 大小: 9.2 KB
  • 大小: 12 KB
  • 大小: 19.1 KB
  • 大小: 14.1 KB
  • 大小: 20.3 KB
  • 大小: 21.2 KB
0
1
分享到:
评论

相关推荐

    论文研究-一种基于可排序视图的RDF模式匹配算法.pdf

    随着语义网络中数据量的激增,在RDF数据集中高效查询数据已成为一个亟待解决的问题。传统的基于物化视图的RDF模式匹配...理论分析及实验证明,基于可排序视图的RDF模式匹配算法能有效地兼顾算法效率及算法可扩展性。

    一个基于 C 语言的学生管理系统,使用了并归排序,不需要连接数据库.zip

    这些系统通常被设计用来提高效率、减少错误、加强安全性,同时提供数据和信息支持。以下是一些常见类型的管理系统: 学校管理系统: 用于学校或教育机构的学生信息、教职员工信息、课程管理、成绩记录、考勤管理等...

    海量数据库解决方案_韩国_李华植

    第2部分中主要介绍提高数据读取效率的具体战略方案,在这部分中介绍与数据读取效率相关的局部范围扫描的原理和具体应用方法,以及对被认为是提高数据库使用效率基础的表连接的所有类型予以详细说明。  《海量...

    海量数据库解决方案_韩国_李华植_Part02

    第2部分中主要介绍提高数据读取效率的具体战略方案,在这部分中介绍与数据读取效率相关的局部范围扫描的原理和具体应用方法,以及对被认为是提高数据库使用效率基础的表连接的所有类型予以详细说明。  《海量...

    mysql数据库my.cnf配置文件

    建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    其二、技术层次深:如果期望进入IT服务或者产品公司(类似毕博、DELL、IBM等),Oracle技术能够帮助提高就业的深度。 其三、职业方向多:Oracle数据库管理方向、Oracle开发及系统架构方向、Oracle数据建模数据仓库等...

    飞哈远程桌面连接 v3.0.zip

    飞哈远程桌面连接软件也叫 远程桌面连接器,同时也是一个小型浏览器,是纯绿色软件!单文件!可以替代Windows系统默认的远程桌面连接,可以批量管理WINDOWS 3389 服务器,适合拥有很多Windows服务器和VPS的朋友使用,当然也...

    EXCEL数据高级查询

    EXCEL 对批量数据的多次重复或相似的统计和交叉性集合运算操作比较困难或效率不高 而使用 Visual Basic 开发平台利用ADO 数据库连接和操作技术可以很好的解决这样的问题 提高 EXCEL 文件数据的处理效率 这个方法前提...

    寻呼台用户管理系统

    连接数据库,提高了数据访问效率。 此系统用户档案、经销商档案及经销商购机档案统一在一个界面上 ,通过点击相应TAB标签即可对相应的档案进行管理。 可按指定即直接单击相应的字段名动态排序, 选择指定字段...

    SQL培训第一期

    如果表之间有匹配行,则整个结果集行包含基表的数据值。 1.6.4.2 语法 select A.*, B.* from student_A A full outer join student_B B on A.Uuid = B.Uuid; 1.6.4.3 结果 1.6.4.4 全外连接不支持(+)写法 1.6.5 ...

    Infix PDF Editor 6.33中文破解版.rar

    插入超链接工具,创建页到页、网页、表单和图像导出链接,添加文本连接,依次点击几个文本列用以连接回流它们,提高办公效率。使用文本工具可以编辑现有文本,点击并拖拽创建新文本。裁切工具拖出选框用以删除不需要...

    基于GPU的煤炭院校档案数据库应用研究

    提出了利用图形处理器较强的运算能力,将GPU用于煤炭院校档案数据库领域的相关研究,包括谓词、聚集、排序、连接等。实践证明大大提高了对煤炭院校档案数据库信息的处理速度,显著地减少查询时间。从而使用户能方便、...

    数据库设计准则及方法论.docx

    但是如果系统在运行一段时间后,这三张表的数据变的非常多,那么连接查询的性能将会变得很差,我们通常的做法是再设计一张横向表,将客户信息和产品信息全都放到销售流水表里去,这样应避免了关联查询。 表通常分为...

    MySQL优化之缓存优化(续)

    MySQL 内部处处皆缓存,等什么时候看了MySQL的源码,再来详细的...一段时间内,如果是同样的SQL,则直接从缓存中读取结果,提高查找数据的效率。但当缓存中的数据与硬盘中的数据不一致时,缓存就会失效。 mysql> show v

    Oracle数据库中基本的查询优化与子查询优化讲解

    之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率。 例如: SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, ...

    论文研究-PostgreSQL查询优化中的等价类研究与改进.pdf

    研究了PostgreSQL查询引擎中等价类在查询优化过程中的应用原理,详细阐述了其如何帮助优化器产生潜在的等值连接,等式约束和记录排序信息。同时也发现PostgreSQL查询引擎并没有充分利用等价类的属性约简特性,以及...

    学生成绩管理系统数据库设计.doc

    学生成绩管理系统数据库设计 [提要] 一个好的数据库,不但可以提高数据查询效率,而且还可以保证数据的完整性和一致性 。所以,数据库在信息管理系统中有着非常重要的作用。本文介绍如何使用SQL Server 2005完成学生...

    SQL查询安全性及性能优化

    经验:使用子查询嵌套不要过多,尽量使用表连接查询代替子查询,因为表连接查询效率稍高一点。 SQL优化工具 使用报表服务 通过Reporting Service查找低效SQL 选择 性能 - 批处理执行统计信息和性能相关的几个...

    数据库系统概论chp3-2.pptx

    可以加速表与表之间的连接,这一点在实现数据的参照完整性方面有特别的意义。 可以显著减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化隐藏器,提高系统性能。 数据库系统概论chp3-2全文共66...

    DaisyDisk for mac

    DaisyDisk for mac介绍 DaisyDisk给你一个完美的概述所有磁盘连接到你的Mac,Macintosh硬盘,闪存卡,迅雷...改进的磁盘排序顺序:首先通过物理设备,然后通过字母表。 优化的上下文菜单和调整的UI。 修复了很多bug。

Global site tag (gtag.js) - Google Analytics