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

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

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

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

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

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

6、用联接查询的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;这两种查询的结果一样，但是第二种查询会对salary列使用索引，会更

9、快些，而第一种查询则不会使用索引。理解索引理解索引-常见问题分析常见问题分析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_kpxxwheresubstr(n

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

12、（26秒）selectdate,sum(amount)fromrecordgroupbydate（27秒）selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)（19991201anddate2000(19990901andplacein(BJ,SH)（,=,EXPLAINPLANFORSELECT*FROMSCOTT.EMP;-要解析的SQL脚本SQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);oracle的执行计划的执行计划1、如何查看执行计划、如何查看执行计划4）在SQL*PLUS下(有些命令在P

13、L/SQL下无效)执行如下命令（一）SQLSETTIMINGON-控制显示执行时间统计数据SQLSETAUTOTRACETRACEONLY-这样设置会有执行计划、统计信息，不会有脚本数据输出SQL执行需要查看执行计划的SQL语句SQLSETAUTOTRACEOFF-不生成AUTOTRACE报告，这是缺省模式这是SQL*PLUS下最常用的查看执行计划的方式，没有脚本数据的输出，节省时间，易查看。oracle的执行计划的执行计划1、如何查看执行计划、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（二）SQLSETAUTOTRACEON-这样设置包含执行计划、统

14、计信息、以及脚本数据输出SQL执行需要查看执行计划的SQL语句SQLSETAUTOTRACEOFForacle的执行计划的执行计划1、如何查看执行计划、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（三）SQLSETAUTOTRACEONEXPLAIN-包含执行计划、脚本数据输出，没有统计信息SQL执行需要查看执行计划的SQL语句SQLSETAUTOTRACEOFForacle的执行计划的执行计划1、如何查看执行计划、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（四）SQLSETAUTOTRACETRACEONLYS

15、TAT-这样设置只包含有统计信息SQL执行需要查看执行计划的SQL语句SQLSETAUTOTRACEOFForacle的执行计划的执行计划2、看懂执行计划、看懂执行计划oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划Planhashvalue这一行是这一条语句的的hash值，我们知道ORACLE对每一条ORACLE语句产生的执行计划放在SHAREPOOL里面，第一次要经过硬解析，产生hash值。下次再执行时比较hash值，如果相同就不会执行硬解析。oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划id:执行序列，但不是执行的先后顺序。执行的先后根据Operation缩

16、进来判断（采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行。一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。）operation：当前操作的内容。oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划Name：操作对象Rows：oracle估计估计当前操作的返回结果集行数。Bytes：表示执行该步骤后返回的字节数。Cost（%CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。Cost没有单位，是一个相对值，是SQL以CBO方式解析执行计划时，供ORACLE来评估CBO成本，选择执行计划用的。没有明确的含义

17、，但是在对比时非常有用Time：oracle估计估计当前操作的时间。oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划谓词说明：PredicateInformation(identifiedbyoperationid):-2-filter(B.MGRISNOTNULL)4-access(A.EMPNO=B.MGR)Access:表示这个谓词条件的值将会影响数据的访问路劲（全表扫描还是索引）。要注意access，考虑谓词的条件，使用的访问路径是否正确。Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划名

18、词解释：recursivecalls递归调用dbblockgets从buffercache中读取的block的数量，当前请求的块数目consistentgets从buffercache中读取的undo数据的block的数量，这里的概念是在你处理你这个操作的时侯需要在一致性读状态上处理多个块physicalreads物理读，就是从磁盘上读取数据块的数量。其产生的主要原因是：1：在数据库高速缓存中不存在这些块。2：全表扫描3：磁盘排序oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划名词解释：redosizeDML生成的redo的大小sorts(memory)在内存执行的排序量sort

19、s(disk)在磁盘执行的排序量1610bytessentviaSQL*Nettoclient从SQL*Net向客户端发送了1610字节的数据。519bytesreceivedviaSQL*Netfromclient客户端向SQL*Net发送了519字节的数据。oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划如果在执行计划中有如下提示：Note-dynamicsamplingusedforthestatement这提示用户CBO当前使用的技术，需要用户在分析计划时考虑这些因素。当出现这个提示，说明当前表使用了动态采样。我们从而推断这个表可能没有做过分析。oracle的执行计划的执

20、行计划2、看懂执行计划、看懂执行计划这里会出现两种情况：（1）如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。（2）如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。oracle的执行计划的执行计划2、看懂执行计划、看懂执行计划访问表方式总结：（1）FullTableScan(FTS)全表扫描（2）IndexLookup索引扫描，包含如下五种方式indexuniquescan-索引唯一扫描indexrangescan-索引局部扫描indexfullscan-索引全局扫描indexfastfullscan-索引快速全局扫描，不带orderby情况下常发生indexskipscan-索引跳跃扫描，where条件列是非索引的前提下常发生（3）rowid物理ID扫描，是最快的访问数据方式oracle的执行计划的执行计划1、在select/insert语句中尽量避免使用通配符（*）。对于一张表而言，通配符（*）意味着要进行全表遍历，对于数据量比较大的表就会给oracle增加很大的压力。2、尽量减少between的使用。3、能使用数字类型的字段尽量使用数字类型，这将提高查询和连