数据库培训-高效率sql语句基础ppt课件

1、Oracle数据库培训高效率SQL语句根底 -张林一个高效率的数据库系统是从两个方面来评价的：呼应时间和吞吐量。在运用系统开发阶段，由于开发库上的数据比较少，在SQL语句的编写上觉得不出各种写法的性能差别，在将运用系统提交实践运用后，随着数据库中数据的添加，系统的呼应速度就会成为最需求处理的主要问题之一。缩短系统的呼应时间，添加操作的并发度，可以提高系统的吞吐量。要缩短系统的呼应时间，就需求可以高效率执行的SQL语句。引言一、优化器引见二、高效率SQL根底三、oracle的执行方案四、书写高效率SQL语句建议目录Oracle数据库中优化器Optimizer是SQL分析和执行的优化工具，它担任指

2、定SQL的执行方案，也就是它担任保证SQL执行的效率最高，比如优化器决议Oracle以什么样的方式来访问数据，是全表扫描Full Table Scan，索引范围扫描Index Range Scan还是全索引快速扫描INDEX Fast Full Scan：INDEX_FFS;对于表关联查询，它担任确定表之间以一种什么方式来关联，比如HASH_JOHN还是NESTED LOOPS或者MERGE JOIN。这些要素直接决议SQL的执行效率，所以优化器是SQL执行的中心，它做出的执行方案好坏，直接决议着SQL的执行效率。优化器引见Oracle的优化器有两种：RBO(Rule-Based Optimi

3、zation):基于规那么的优化器CBO(Cost-Based Optimization):基于代价的优化器从Oracle 10g开场，RBO曾经被弃用，但是我们依然可以经过Hint方式来运用它。CBO的思绪是让Oracle获取一切执行方案相关的信息，经过对这些信息做计算分析，最后得出一个代价最小的执行方案作为最终的执行方案。优化器引见运用CBO 时，需求留意如下几个方面：1、编写SQL语句时，不用思索FROM 子句后面的表或视图的顺序和WHERE 子句后面的条件顺序；2、运用CBO 时，SQL语句 FROM 子句后面的表的个数不宜太多，由于CBO在选择表衔接顺序时，会对FROM 子句后面的表

4、进展阶乘运算，选择最好的一个衔接顺序。3、假设一个语句运用 RBO的执行方案确实比CBO 好，那么可以经过hint机制，强迫运用RBO。Hint机制可以参考这篇文章：czmmiao.iteye/blog/1478465优化器引见了解索引大多数情况下，数据库运用索引来检索表，优化器根据用户定义的索引来提高执行性能。但是，假设在SQL语句的where子句中写的SQL代码不合理，就会呵斥优化器忽略索引而采用全表扫描，而这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时需求了解优化器根据何种原那么来运用索引，这将有助于写出高性能的SQL语句。高效率SQL根底1、条件中包含NULL值(IS NU

5、LL 与 IS NOT NULL)以NULL值做条件时，将无法运用包含NULL值的列上的索引。即使索引有多列这样的情况下，只需这些列中有一列含有null，该列就会从索引中排除。也就是说假设某列存在空值，在运用NULL值做条件时，即使对该列建索引也不会提高性能。了解索引-常见问题分析2、列的衔接列被包含到表达式中导致不能运用索引。对于有衔接的列，即使最后的联接值为一个静态值，优化器是不会运用索引的。例：假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME)和(LAST_NAME)，如今要查询一个叫BillCliton的职工。下面是一个采用联接查询的SQL语

6、句:select*fromemployeewherefirst_name| |last_name=BillCliton;改良方法：select*fromemployeewherefirst_name=Billandlast_name=Cliton;了解索引-常见问题分析3、带通配符%的like语句select*fromemployeewherelast_namelike%cliton%;由于通配符%在搜索词首出现，所以数据库将不运用last_name的索引。在很多情况下能够无法防止这种情况，但是一定要心中有数，通配符如此运用会降低查询速度。当通配符出如今字符串其他位置时，优化器就能利用索引。在

7、下面的查询中索引得到了运用：select*fromemployeewherelast_namelikec%;了解索引-常见问题分析4、 order by语句order by语句决议了数据库如何将前往的查询结果排序。orderby语句对要排序的列没有什么特别的限制，也可以将函数参与列中象联接或者附加等。任何在orderby语句的非索引项或者有计算表达式都将降低查询速度。需求仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。处理这个问题的方法就是重写orderby语句以运用索引，也可以为所运用的列建立另外一个索引，同时应绝对防止在orderby子句中运用表达式。了解索引-常见问题

8、分析5、 NOTNOT可用来对任何逻辑运算符号取反，例：select * from employee where not (salary=3000);要运用NOT，那么应在取反的短语前面加上括号。NOT运算符的以上方式比较少用，但是它会包含在另外一个逻辑运算符中，这就是不等于运算符，如：select * from employee where salary3000;处理方法:不运用NOT，例：select * from employee where salary3000; 这两种查询的结果一样，但是第二种查询会对salary列运用索引，会更快些，而第一种查询那么不会运用索引。了解索引-常见问题

9、分析6、 IN和EXISTS(一)开发过程中经常会在where子句中运用子查询，主要有两种方式：. where column in(select column from . where .);.whereexists (selectXfrom.where.);采用第二种格式要比第一种格式的效率高。第二种格式中，子查询以selectX开场。运用EXISTS子句不论子查询从表中抽取什么数据它只查看where子句。这样优化器就不用遍历整个表而仅根据索引就可完成任务这里假定在where语句中运用的列存在索引。了解索引-常见问题分析6、 IN和EXISTS(二)运用IN子查询时，首先执行子查询，并将获得

10、的结果列表存放在在一个加了索引的暂时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行终了，存放在暂时表中以后再执行主查询。所以运用EXISTS通常比运用IN查询速度快。应尽能够运用NOTEXISTS来替代NOTIN，虽然二者都运用了NOT(不能运用索引而降低速度)，但NOTEXISTS要比NOTIN查询效率高。 了解索引-常见问题分析7、 不可优化的where子句一以下这条语句在nsrsbh字段上建有恰当的索引，但执行却很慢：select * from zk_kj_kpxx where substr(nsrsbh,0,6)=000000;(24秒)类似的还有如下方式的语句假设以下字段都

11、建有恰当的索引：select*fromrecordwhereamount/301000; 即： where子句中对列的任何操作结果都是在SQL运转时逐列计算得到的，因此它不得不进展表搜索，而没有运用该列上面的索引。了解索引-常见问题分析7、 不可优化的where子句二我们把上面的两条语句进展重写：select * from zk_kj_kpxx where nsrsbh like 000000%;(495毫秒)select*fromrecordwhereamount19991201anddate200026秒selectdate, sum(amount)fromrecordgroupbydat

12、e27秒selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)19991201anddate2000(19990901andplacein(BJ,SH),=,EXPLAIN PLAN FORSELECT * FROM SCOTT.EMP; -要解析的SQL脚本SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);oracle的执行方案1、如何查看执行方案4在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令一SQLSET TIMING ON -控制显示执行时间统计数据SQL SET AUT

13、OTRACE TRACEONLY -这样设置会有执行方案、统计信息，不会有脚本数据输出SQL执行需求查看执行方案的SQL语句SQLSET AUTOTRACE OFF -不生成AUTOTRACE报告，这是缺省方式这是SQL*PLUS下最常用的查看执行方案的方式，没有脚本数据的输出，节省时间，易查看。oracle的执行方案1、如何查看执行方案4在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令二SQL SET AUTOTRACE ON -这样设置包含执行方案、统计信息、以及脚本数据输出SQL执行需求查看执行方案的SQL语句SQLSET AUTOTRACE OFForacle的执行方案

14、1、如何查看执行方案4在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令三SQLSET AUTOTRACE ON EXPLAIN -包含执行方案、脚本数据输出，没有统计信息SQL执行需求查看执行方案的SQL语句SQLSET AUTOTRACE OFForacle的执行方案1、如何查看执行方案4在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令四SQLSET AUTOTRACE TRACEONLY STAT -这样设置只包含有统计信息SQL执行需求查看执行方案的SQL语句SQLSET AUTOTRACE OFForacle的执行方案2、看懂执行方案oracle的执行方

15、案2、看懂执行方案Plan hash value这一行是这一条语句的的hash值，我们知道ORACLE对每一条ORACLE语句产生的执行方案放在SHARE POOL里面，第一次要经过硬解析，产生hash值。下次再执行时比较hash值，假设一样就不会执行硬解析。oracle的执行方案2、看懂执行方案id: 执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判别采用最右最上最先执行的原那么看层次关系，在同一级假设某个动作没有子ID就最先执行。普通按缩进长度来判别，缩进最大的最先执行，假设有2行缩进一样，那么就先执行上面的。operation：当前操作的内容。oracle的执行方

16、案2、看懂执行方案Name：操作对象Rows：oracle估计当前操作的前往结果集行数。Bytes：表示执行该步骤后前往的字节数。Cost%CPU：表示执行到该步骤的一个执行本钱，用于阐明SQL执行的代价。 Cost没有单位，是一个相对值，是SQL以CBO方式解析执行方案时，供ORACLE来评价CBO本钱，选择执行方案用的。没有明确的含义，但是在对比时非常有用Time：oracle 估计当前操作的时间。oracle的执行方案2、看懂执行方案谓词阐明：Predicate Information (identified by operation id):-2 - filter(B.MGR IS N

17、OT NULL)4 - access(A.EMPNO = B.MGR)Access: 表示这个谓词条件的值将会影响数据的访问路劲全表扫描还是索引。要留意access，思索谓词的条件，运用的访问途径能否正确。Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。oracle的执行方案2、看懂执行方案名词解释：recursive calls 递归调用db block gets 从buffer cache中读取的block的数量，当前恳求的块数目consistent gets 从buffer cache中读取的undo数据的block的数量，这里的概念是在他处置他这个操作的时侯需求在

18、一致性读形状上处置多个块physical reads 物理读，就是从磁盘上读取数据块的数量。其产生的主要缘由是：1：在数据库高速缓存中不存在这些块。2：全表扫描3：磁盘排序oracle的执行方案2、看懂执行方案名词解释：redo size DML生成的redo的大小sorts (memory) 在内存执行的排序量sorts (disk) 在磁盘执行的排序量1610 bytes sent via SQL*Net to client 从SQL*Net向客户端发送了1610字节的数据。519 bytes received via SQL*Net from client 客户端向SQL*Net发送了5

19、19字节的数据。oracle的执行方案2、看懂执行方案假设在执行方案中有如下提示：Note-dynamic sampling used for the statement这提示用户CBO当前运用的技术，需求用户在分析方案时思索这些要素。 当出现这个提示，阐明当前表运用了动态采样。我们从而推断这个表能够没有做过分析。oracle的执行方案2、看懂执行方案这里会出现两种情况：1 假设表没有做过分析，那么CBO可以经过动态采样的方式来获取分析数据，也可以或者正确的执行方案。2 假设表分析过，但是分析信息过旧，这时CBO就不会在运用动态采样，而是运用这些旧的分析数据，从而能够导致错误的执行方案。ora

20、cle的执行方案2、看懂执行方案访问表方式总结：1Full Table Scan (FTS) 全表扫描2Index Lookup 索引扫描，包含如下五种方式 index unique scan -索引独一扫描 index range scan -索引部分扫描 index full scan -索引全局扫描 index fast full scan -索引快速全局扫描，不带order by情况下常发生 index skip scan -索引腾跃扫描，where条件列是非索引的前提下常发生3rowid 物理ID扫描，是最快的访问数据方式oracle的执行方案1、在select/insert语句中尽量防止运用通配符*。对于一张表而言，通配符*意味着要进展全表遍历，对于数据量比较大的表就会给oracle添加很大的压力。2、尽量减少between的运用。3、能运用数字类型的字段尽量运用数字类型，这将提高查询和衔接的性能，并减少存储