数据库培训高效率语句基础

1、数据库培训高效率语句基础一个高效率的数据库系统是从两个方面来评价的：响应时间和吞吐量。在应用系统开发阶段，由于开发库上的数据比较少，在SQL语句的编写上感觉不出各种写法的性能差异，在将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就会成为最需要解决的主要问题之一。缩短系统的响应时间，增加操作的并发度，可以提高系统的吞吐量。要缩短系统的响应时间，就需要可以高效率执行的SQL语句。引言引言一、优化器介绍一、优化器介绍二、高效率二、高效率SQL根底根底三、三、oracle的执行方案的执行方案四、书写高效率四、书写高效率SQL语句建议语句建议目录目录Oracle数据库中优化器Optim

2、izer是SQL分析和执行的优化工具，它负责指定SQL的执行方案，也就是它负责保证SQL执行的效率最高，比方优化器决定Oracle以什么样的方式来访问数据，是全表扫描FullTableScan，索引范围扫描IndexRangeScan还是全索引快速扫描INDEXFastFullScan：INDEX_FFS;对于表关联查询，它负责确定表之间以一种什么方式来关联，比方HASH_JOHN还是NESTEDLOOPS或者MERGEJOIN。这些因素直接决定SQL的执行效率，所以优化器是SQL执行的核心，它做出的执行方案好坏，直接决定着SQL的执行效率。优化器介绍优化器介绍Oracle的优化器有两种：RB

3、O(Rule-BasedOptimization):基于规那么的优化器CBO(Cost-BasedOptimization):基于代价的优化器从Oracle10g开场，RBO已经被弃用，但是我们依然可以通过Hint方式来使用它。CBO的思路是让Oracle获取所有执行方案相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行方案作为最终的执行方案。优化器介绍优化器介绍使用CBO时，需要注意如下几个方面：1、编写SQL语句时，不必考虑FROM子句后面的表或视图的顺序和WHERE子句后面的条件顺序；2、使用CBO时，SQL语句FROM子句后面的表的个数不宜太多，因为CBO在选择表连接顺序

4、时，会对FROM子句后面的表进展阶乘运算，选择最好的一个连接顺序。3、如果一个语句使用RBO的执行方案确实比CBO好，那么可以通过hint机制，强制使用RBO。Hint机制可以参考这篇文章：:/czmmiao.iteye/blog/1478465优化器介绍优化器介绍理解索引大多数情况下，数据库使用索引来检索表，优化器根据用户定义的索引来提高执行性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器忽略索引而采用全表扫描，而这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时需要了解优化器根据何种原那么来使用索引，这将有助于写出高性能的SQL语句。高效率高效率S

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

6、illCliton的职工。下面是一个采用联接查询的SQL语句: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、orderby语句orderby语句决定了数据库如何将返回的查询结果排序。orderby语句对要排序的列没有什么特别的限制，也可以将函数参加列中象联接或者附加等。任何在orderby语句的非索引项或者有计算表达式都将降低查询速度。需要仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的方法就是重写orderby语句以使用索引，也可以为所使用

8、的列建立另外一个索引，同时应绝对防止在orderby子句中使用表达式。理解索引理解索引-常见问题分析常见问题分析5、NOTNOT可用来对任何逻辑运算符号取反，例：select*fromemployeewherenot(salary=3000);要使用NOT，那么应在取反的短语前面加上括号。NOT运算符的以上形式比较少用，但是它会包含在另外一个逻辑运算符中，这就是不等于运算符，如：select*fromemployeewheresalary3000;解决方法:不使用NOT，例：select*fromemployeewheresalary3000;这两种查询的结果一样，但是第二种查询会对salar

9、y列使用索引，会更快些，而第一种查询那么不会使用索引。理解索引理解索引-常见问题分析常见问题分析6、IN和EXISTS(一)开发过程中经常会在where子句中使用子查询，主要有两种方式：.wherecolumnin(selectcolumnfrom.where.);.whereexists(selectXfrom.where.);采用第二种格式要比第一种格式的效率高。第二种格式中，子查询以selectX开场。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作这里假定在where语句中使用的列存在索引。理解索引理解索引-常见

10、问题分析常见问题分析6、IN和EXISTS(二)使用IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。所以使用EXISTS通常比使用IN查询速度快。应尽可能使用NOTEXISTS来代替NOTIN，尽管二者都使用了NOT(不能使用索引而降低速度)，但NOTEXISTS要比NOTIN查询效率高。理解索引理解索引-常见问题分析常见问题分析7、不可优化的where子句一以下这条语句在nsrsbh字段上建有恰当的索引，但执行却很慢：select*fromzk_kj_kpxxwheresu

11、bstr(nsrsbh,0,6)=000000;(24秒)类似的还有如下形式的语句假设以下字段都建有恰当的索引：select*fromrecordwhereamount/301000;即：where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进展表搜索，而没有使用该列上面的索引。理解索引理解索引-常见问题分析常见问题分析7、不可优化的where子句二我们把上面的两条语句进展重写：select*fromzk_kj_kpxxwherensrsbhlike000000%;(495毫秒)select*fromrecordwhereamount19991201anddate20

12、0026秒selectdate,sum(amount)fromrecordgroupbydate27秒selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)19991201anddate2000(19990901andplacein(BJ,SH),=,EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP; -要解析的要解析的SQL脚本脚本 SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); oracle的执行方案的执行方案1、如何查看执行方案、如何查看执行方案4在在SQ

13、L*PLUS下下(有些命令在有些命令在PL/SQL下无效下无效)执行如下命令一执行如下命令一SQLSET TIMING ON -控制显示执行时间统计数据控制显示执行时间统计数据 SQL SET AUTOTRACE TRACEONLY -这样设置会有执行方案、统计这样设置会有执行方案、统计信息，不会有脚本数据输出信息，不会有脚本数据输出 SQL执行需要查看执行方案的执行需要查看执行方案的SQL语句语句 SQLSET AUTOTRACE OFF -不生成不生成AUTOTRACE报告，这是报告，这是缺省模式缺省模式这是这是SQL*PLUS下最常用的查看执行方案的方式，没有脚本数据的下最常用的查看执行

14、方案的方式，没有脚本数据的输出，节省时间，易查看。输出，节省时间，易查看。oracle的执行方案的执行方案1、如何查看执行方案、如何查看执行方案4在在SQL*PLUS下下(有些命令在有些命令在PL/SQL下无效下无效)执行如下命令二执行如下命令二SQL SET AUTOTRACE ON -这样设置包含执行方案、统计信这样设置包含执行方案、统计信息、以及脚本数据输出息、以及脚本数据输出 SQL执行需要查看执行方案的执行需要查看执行方案的SQL语句语句 SQLSET AUTOTRACE OFF oracle的执行方案的执行方案1、如何查看执行方案、如何查看执行方案4在在SQL*PLUS下下(有些命

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

16、SQL执行需要查看执行方案的执行需要查看执行方案的SQL语句语句 SQLSET AUTOTRACE OFForacle的执行方案的执行方案2、看懂执行方案、看懂执行方案oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案Plan hash value 这一行是这一条语句的的这一行是这一条语句的的hash值，我们知道值，我们知道ORACLE对每一条对每一条ORACLE语句产生的执行方案放在语句产生的执行方案放在SHARE POOL里面，第一次要经过硬解析，产生里面，第一次要经过硬解析，产生hash值。下次再执行时比较值。下次再执行时比较hash值，如果一样就不会执行硬解析。值，如果一样

17、就不会执行硬解析。oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案id: 执行序列，但不是执行的先后顺序。执行的先后根据执行序列，但不是执行的先后顺序。执行的先后根据Operation缩缩进来判断采用最右最上最先执行的原那么看层次关系，在同一级如果进来判断采用最右最上最先执行的原那么看层次关系，在同一级如果某个动作没有子某个动作没有子ID就最先执行。一般按缩进长度来判断，缩进最大的最就最先执行。一般按缩进长度来判断，缩进最大的最先执行，如果有先执行，如果有2行缩进一样，那么就先执行上面的。行缩进一样，那么就先执行上面的。operation：当前操作的内容。：当前操作的内容。 or

18、acle的执行方案的执行方案2、看懂执行方案、看懂执行方案Name：操作对象：操作对象 Rows：oracle估计当前操作的返回结果集行数。估计当前操作的返回结果集行数。 Bytes：表示执行该步骤后返回的字节数。：表示执行该步骤后返回的字节数。 Cost%CPU：表示执行到该步骤的一个执行本钱，用于说明：表示执行到该步骤的一个执行本钱，用于说明SQL执行的代价。执行的代价。 Cost没有单位，是一个相对值，是没有单位，是一个相对值，是SQL以以CBO方式解析执方式解析执行方案时，供行方案时，供ORACLE来评估来评估CBO本钱，选择执行方案用的。没有明确的本钱，选择执行方案用的。没有明确的含

19、义，但是在比照时非常有用含义，但是在比照时非常有用Time：oracle 估计当前操作的时间。估计当前操作的时间。 oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案谓词说明：谓词说明： Predicate Information (identified by operation id): - 2 - filter(B.MGR IS NOT NULL) 4 - access(A.EMPNO = B.MGR) Access: 表示这个谓词条件的值将会影响数据的访问路劲全表扫描还是表示这个谓词条件的值将会影响数据的访问路劲全表扫描还是索引。要注意索引。要注意access，考虑谓词的条件

20、，使用的访问路径是否正确。，考虑谓词的条件，使用的访问路径是否正确。Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。 oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案名词解释：名词解释：recursive calls 递归调用递归调用 db block gets 从从buffer cache中读取的中读取的block的数量，当前请求的块数目的数量，当前请求的块数目 consistent gets 从从buffer cache中读取的中读取的undo数据的数据的block的数量，这里的的数量，这里的概念

21、是在你处理你这个操作的时侯需要在一致性读状态上处理多个块概念是在你处理你这个操作的时侯需要在一致性读状态上处理多个块physical reads 物理读，就是从磁盘上读取数据块的数量。物理读，就是从磁盘上读取数据块的数量。其产生的主要原因是：其产生的主要原因是： 1：在数据库高速缓存中不存在这些块。：在数据库高速缓存中不存在这些块。 2：全表扫描：全表扫描 3：磁盘排序：磁盘排序 oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案名词解释：名词解释：redo size DML生成的生成的redo的大小的大小 sorts (memory) 在内存执行的排序量在内存执行的排序量 sor

22、ts (disk) 在磁盘执行的排序量在磁盘执行的排序量 1610 bytes sent via SQL*Net to client 从从SQL*Net向客户端发送了向客户端发送了1610字节的数据。字节的数据。 519 bytes received via SQL*Net from client 客户端向客户端向SQL*Net发送了发送了519字节的数据。字节的数据。oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案如果在执行方案中有如下提示：如果在执行方案中有如下提示： Note - -dynamic sampling used for the statement 这提示用户这

23、提示用户CBO当前使用的技术，需要用户在分析方案时考虑这些因素。当前使用的技术，需要用户在分析方案时考虑这些因素。 当出现这个提示，说明当前表使用了动态采样。我们从而推断这个表当出现这个提示，说明当前表使用了动态采样。我们从而推断这个表可能没有做过分析。可能没有做过分析。 oracle的执行方案的执行方案2、看懂执行方案、看懂执行方案这里会出现两种情况：这里会出现两种情况： 1 如果表没有做过分析，那么如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析可以通过动态采样的方式来获取分析数据，也可以或者正确的执行方案。数据，也可以或者正确的执行方案。 2 如果表分析过，但是分析信息过旧

24、，这时如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行方案。而是使用这些旧的分析数据，从而可能导致错误的执行方案。oracle的执行方案的执行方案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/in