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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2021/2/8,#,Oracle,数据库培训,高效率,SQL,语句基础,-,张林,一,个高效率的数据库系统是从两个方面来评价的,：,响应时间,和吞吐量。,在,应用系统开发阶段，由于开发库上的数据比较少，在,SQL,语句的编写上感觉不出各种写法的性能差异，在将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就会成为最需要解决的主要问题之一。缩短系统的响应时间，增加操作的并发度，可以提高系统的吞吐量,。,要,缩短系统的响应时间，就需要可以高效率执行的,SQL,语句。,引言,一、优化器介绍,二、高效率,SQL,基础,三、,oracle,的执行计划,四、书写高效率,SQL,语句建议,目录,Oracle,数据库中优化器（,Optimizer,）是,SQL,分析和执行的优化工具，它负责指定,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 Optimization):,基于规则的优化器,CBO(Cost-Based Optimization):,基于代价的优化,器,从,Oracle 10g,开始，,RBO,已经被弃用，但是我们依然可以通过,Hint,方式来使用,它。,CBO,的,思路是让,Oracle,获取所有执行计划相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行,计划。,优化器介绍,使用,CBO,时,，需要注意如下几个方面：,1,、编写,SQL,语句时，不必考虑,FROM,子句后面的表或视图的顺序和,WHERE,子句后面的条件顺序,；,2,、,使用,CBO,时，,SQL,语句,FROM,子句后面的表的个数不宜太多，因为,CBO,在选择表连接顺序时，会对,FROM,子句后面的表进行,阶乘运算,，选择最好的一个连接顺序,。,3,、如果,一个语句使用,RBO,的执行计划确实比,CBO,好，则可以,通过,hint,机制，,强制使用,RBO,。,Hint,机制可以参考这篇文章：,http,:/ NULL,与,IS NOT NULL),以,NULL,值做条件时，将无法使用包含,NULL,值的列上的索引。即使索引有多列这样的情况下，只要这些列中有一列含有,null,，该列就会从索引中排除。也就是说如果某列存在空值，在使用,NULL,值做条件时，即使对该列建索引也不会提高性能。,理解索引,-,常见问题分析,2,、列的连接,列被包含到表达式中导致不能使用索引。,对于有,连接,的,列，即使最后的联接值为一个静态值，优化器是不会使用索引的,。,例：,假定有一个职工,表,(employee),，对于,一个职工的姓和名分成两列存放,(FIRST_NAME),和,(LAST_NAME),，,现在要查询一个,叫,Bill,Cliton,的,职工,。,下面,是一个采用联接查询,的,SQL,语句,:,select*from,employee,where,first_name,|last_name,=Bill,Cliton;,改进,方法,：,select*fromemployee,where,first_name=Billandlast_name=,Cliton;,理解索引,-,常见问题分析,3,、带通配符（,%,）的,like,语句,select*fromemployeewherelast_namelike%cliton%;,由于,通配符（,%,）在搜寻词首出现，所以数据库将不使用,last_name,的索引。在很多情况下可能无法避免这种情况，但是一定要心中有数，通配符如此使用会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：,select*fromemployeewherelast_namelikec%;,理解索引,-,常见问题分析,4,、,order by,语句,o,rder by,语句,决定了数据库如何将返回的查询结果排序,。,order,by,语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何,在,order,by,语句的非索引项或者有计算表达式都将降低查询速度。需要仔细检查,orderby,语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写,orderby,语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在,orderby,子句中使用表达式。,理解索引,-,常见问题分析,5,、,NOT,NOT,可用,来对任何逻辑运算符号取,反，例：,select*from employee where not(salary=3000,);,要使用,NOT,，,则应在取反的短语前面加上,括号。,NOT,运算符的以上形式比较少用，但是它会包含,在另外一个逻辑运算符中，这就是不,等于,”,运算符，如：,select*from employee where salary3000,;,解决,方法,:,不使用,NOT,，例：,select*from employee where salary3000;,这,两种查询的结果一样，但是第二种,查询会,对,salary,列使用索引,，会更快些，而,第一种查询则不会使用,索引。,理解索引,-,常见问题分析,6,、,IN,和,EXISTS,(,一,),开发过程,中经常会在,where,子句中使用,子,查询，主要有两种方式：,.w,here column in(select column from.,w,here.);,.whereexists,(,selectXfrom.where,.);,采用第二种格式要比第一种格式的效率高。,第二,种格式中，子查询以,selectX,开始。运用,EXISTS,子句不管子查询从表中抽取什么数据它只查看,where,子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在,where,语句中使用的列存在索引）。,理解索引,-,常见问题分析,6,、,IN,和,EXISTS(,二,),使用,IN,子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。所以使用,EXISTS,通常比使用,IN,查询速度,快。,应,尽可能使用,NOTEXISTS,来代替,NOTIN,，尽管二者都使用了,NOT(,不能,使用索引而降低,速度,),，,但,NOTEXISTS,要比,NOTIN,查询效率高。,理解索引,-,常见问题分析,7,、,不可优化的,where,子句（一）,以下这条语句在,nsrsbh,字段上建有恰当的索引，但执行却很慢：,select*from zk_kj_kpxx where substr(nsrsbh,0,6)=000000,;(24,秒,),类似的还有如下形式的语句（假设以下字段都建有恰当的索引）：,select,*fromrecordwhereamount/30,1000;,即：,where,子句中对列的任何操作结果都是在,SQL,运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的,索引。,理解索引,-,常见问题分析,7,、,不可优化的,where,子句（二）,我们把上面的两条语句进行重写：,select,*from zk_kj_kpxx where nsrsbh like,000000%,;,(495,毫秒,),select*fromrecordwhereamount19991201,anddate2000,（,26,秒）,select,date,sum(amount,)fromrecordgroupbydate,（,27,秒）,select,count(*)fromrecordwheredate19990901andplacein(BJ,SH),（,19991201anddate2000(19990901andplacein(BJ,SH),（,=,EXPLAIN,PLAN FOR,SELECT,*FROM SCOTT.EMP;-,要解析的,SQL,脚本,SQLSELECT,*FROM TABLE(DBMS_XPLAN.DISPLAY);,o,racle,的执行计划,1,、如何查看执行计划,4,）,在,SQL*PLUS,下,(,有些命令在,PL/SQL,下无效,),执行如下,命令（一）,SQLSET TIMING ON,-,控制显示执行时间统计数据,SQL,SET AUTOTRACE TRACEONLY,-,这样设置会有执行计划、统计信息，不会有脚本数据输出,SQL,执行需要查看执行计划的,SQL,语句,SQLSET AUTOTRACE,OFF -,不生成,AUTOTRACE,报告，这是缺省,模式,这是,SQL*PLUS,下最常用的查看执行计划的方式，没有脚本数据的输出，节省时间，易查看,。,o,racle,的执行计划,1,、如何查看执行计划,4,）,在,SQL*PLUS,下,(,有些命令在,PL/SQL,下无效,),执行如下,命令（二）,SQL SET AUTOTRACE ON,-,这样设置包含执行计划、统计信息、以及脚本数据输出,SQL,执行需要查看执行计划的,SQL,语句,SQLSET,AUTOTRACE OFF,o,racle,的执行计划,1,、如何查看执行计划,4,）,在,SQL*PLUS,下,(,有些命令在,PL/SQL,下无效,),执行如下,命令（三）,SQLSET,AUTOTRACE ON EXPLAIN -,包含执行计划、脚本数据输出，没有统计信息,SQL,执行需要查看执行计划的,SQL,语句,SQLSET AUTOTRACE OFF,o,racle,的执行计划,1,、如何查看执行计划,4,）,在,SQL*PLUS,下,(,有些命令在,PL/SQL,下无效,),执行如下,命令（四）,SQLSET AUTOTRACE TRACEONLY STAT,-,这样设置只包含有统计信息,SQL,执行需要查看执行计划的,SQL,语句,SQLSET AUTOTRACE OFF,o,racle,的执行计划,2,、看懂执行计划,o,racle,的执行计划,2,、看懂执行计划,Plan hash,value,这,一行是这一条语句的的,hash,值，我们知道,ORACLE,对每一条,ORACLE,语句产生的执行计划放在,SHARE POOL,里面，第一次要经过硬解析，产生,hash,值。下次再执行时比较,hash,值，如果相同就不会执行硬解析。,o,racle,的执行计划,2,、看懂执行计划,id,:,执行序列，但不是执行的先后顺序。执行的先后根据,Operation,缩进来判断（采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子,ID,就最先执行。一般按缩进长度来判断，缩进最大的最先执行，如果有,2,行缩进一样，那么就先执行上面的。,）,operation,：当前操作的内容。,o,racle,的执行计划,2,、看懂执行计划,Name,：操作对象,Rows,：,oracle,估计,当前操作的返回结果集行数。,Bytes,：表示执行该步骤后返回的字节数。,Cost,（,%CPU,）：表示执行到该步骤的一个