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

Oracle数据库培训高效率SQL语句基础--张林一个高效率的数据库系统是从两个方面来评价的：响应时间和吞吐量。在应用系统开发阶段，由于开发库上的数据比较少，在SQL语句的编写上感觉不出各种写法的性能差异，在将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就会成为最需要解决的主要问题之一。缩短系统的响应时间，增加操作的并发度，可以提高系统的吞吐量。要缩短系统的响应时间，就需要可以高效率执行的SQL语句。引言一、优化器介绍二、高效率SQL基础三、oracle的执行计划四、书写高效率SQL语句建议目录Oracle数据库中优化器（Optimizer）是SQL分析和执行的优化工具，它负责指定SQL的执行计划，也就是它负责保证SQL执行的效率最高，比如优化器决定Oracle以什么样的方式来访问数据，是全表扫描（FullTableScan），索引范围扫描（IndexRangeScan）还是全索引快速扫描（INDEXFastFullScan：INDEX_FFS）;对于表关联查询，它负责确定表之间以一种什么方式来关联，比如HASH_JOHN还是NESTEDLOOPS或者MERGEJOIN。这些因素直接决定SQL的执行效率，所以优化器是SQL执行的核心，它做出的执行计划好坏，直接决定着SQL的执行效率。优化器介绍Oracle的优化器有两种：RBO(Rule-BasedOptimization):基于规则的优化器CBO(Cost-BasedOptimization):基于代价的优化器从Oracle10g开始，RBO已经被弃用，但是我们依然可以通过Hint方式来使用它。CBO的思路是让Oracle获取所有执行计划相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。优化器介绍使用CBO时，需要注意如下几个方面：1、编写SQL语句时，不必考虑FROM子句后面的表或视图的顺序和WHERE子句后面的条件顺序；2、使用CBO时，SQL语句FROM子句后面的表的个数不宜太多，因为CBO在选择表连接顺序时，会对FROM子句后面的表进行阶乘运算，选择最好的一个连接顺序。3、如果一个语句使用RBO的执行计划确实比CBO好，则可以通过hint机制，强制使用RBO。Hint机制可以参考这篇文章：优化器介绍理解索引大多数情况下，数据库使用索引来检索表，优化器根据用户定义的索引来提高执行性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器忽略索引而采用全表扫描，而这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时需要了解优化器根据何种原则来使用索引，这将有助于写出高性能的SQL语句。高效率SQL基础1、条件中包含NULL值(ISNULL与ISNOTNULL)以NULL值做条件时，将无法使用包含NULL值的列上的索引。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，在使用NULL值做条件时，即使对该列建索引也不会提高性能。理解索引-常见问题分析2、列的连接列被包含到表达式中导致不能使用索引。对于有连接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。例：假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME)和(LAST_NAME)，现在要查询一个叫BillCliton的职工。下面是一个采用联接查询的SQL语句:select*fromemployeewherefirst_name||’’||last_name=‘BillCliton’;改进方法：select*fromemployeewherefirst_name=‘Bill’andlast_name=‘Cliton’;理解索引-常见问题分析3、带通配符（%）的like语句select*fromemployeewherelast_namelike'%cliton%';由于通配符（%）在搜寻词首出现，所以数据库将不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有数，通配符如此使用会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select*fromemployeewherelast_namelike'c%';理解索引-常见问题分析4、orderby语句orderby语句决定了数据库如何将返回的查询结果排序。orderby语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在orderby语句的非索引项或者有计算表达式都将降低查询速度。需要仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在orderby子句中使用表达式。理解索引-常见问题分析5、NOTNOT可用来对任何逻辑运算符号取反，例：select*fromemployeewherenot(salary=3000);要使用NOT，则应在取反的短语前面加上括号。NOT运算符的以上形式比较少用，但是它会包含在另外一个逻辑运算符中，这就是不等于””运算符，如：select*fromemployeewheresalary3000;解决方法:不使用NOT，例：select*fromemployeewheresalary3000orsalary3000;这两种查询的结果一样，但是第二种查询会对salary列使用索引，会更快些，而第一种查询则不会使用索引。理解索引-常见问题分析6、IN和EXISTS(一)开发过程中经常会在where子句中使用子查询，主要有两种方式：...wherecolumnin(selectcolumnfrom...where...);...whereexists(select'X'from...where...);采用第二种格式要比第一种格式的效率高。第二种格式中，子查询以‘select'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。理解索引-常见问题分析6、IN和EXISTS(二)使用IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。所以使用EXISTS通常比使用IN查询速度快。应尽可能使用NOTEXISTS来代替NOTIN，尽管二者都使用了NOT(不能使用索引而降低速度)，但NOTEXISTS要比NOTIN查询效率高。理解索引-常见问题分析7、不可优化的where子句（一）以下这条语句在nsrsbh字段上建有恰当的索引，但执行却很慢：select*fromzk_kj_kpxxwheresubstr(nsrsbh,0,6)='000000';(24秒)类似的还有如下形式的语句（假设以下字段都建有恰当的索引）：select*fromrecordwhereamount/301000;即：where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。理解索引-常见问题分析7、不可优化的where子句（二）我们把上面的两条语句进行重写：select*fromzk_kj_kpxxwherensrsbhlike‘000000%’;(495毫秒)select*fromrecordwhereamount1000*30;即：没有对列进行任何操作，结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索。之前在网开系统的SQL语句优化时碰到过类似的情况，请大家多加注意，尽量避免此类问题的发生。理解索引-常见问题分析7、不可优化的where子句（三）表stuff有200000行，id_no上有非群集索引，如下：selectcount(*)fromstuffwhereid_noin('0','1');分析：where条件中的‘in’在逻辑上相当于‘or’，所以语法分析器会将in(‘0’,‘1’)转化为id_no=‘0’orid_no=‘1’来执行。虽然CBO会选择最优的执行计划，然而仍旧会出现没有利用id_no上索引的情况，导致查询较慢，如果数据量非常大且对SQL语句的效率有较高的要求不妨考虑使用简单的存储过程来实现相同的功能。理解索引-常见问题分析7、不可优化的where子句（四）小结：1、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。左右不重要，重要的是表达式中是否包含列。2、in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。3、要根据实际情况善于使用存储过程，它使SQL变得更加灵活和高效。理解索引-常见问题分析8、组合索引（一）在place，date，amount上建组合索引，前导列为placeselectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000（26秒）selectdate,sum(amount)fromrecordgroupbydate（27秒）selectcount(*)fromrecordwheredate'19990901'andplacein('BJ,'SH')（1秒）。这是一个不合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引。理解索引-常见问题分析8、组合索引（二）合理的索引设计，要使每个SQL都可以利用索引，那么我们就需要将date作为前导列，使三条语句性能达到最优。selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000(1秒)selectdate,sum(amount)fromrecordgroupbydate（11秒）selectcount(*)fromrecordwheredate'19990901'andplacein('BJ','SH')（1秒）理解索引-常见问题分析8、组合索引（三）小结：1、有大量重复值、且经常有范围查询，如：（between,,，=,=）和orderby、groupby发生的列，可考虑建立聚簇索引。2、经常同时存取多列，且每列都含有重复值可考虑建立组合索引；3、组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。理解索引-常见问题分析注意：索引并非越多越好，过多的索引会降低更新和插入操作的速度理解索引-常见问题分析1、如何查看执行计划1）在PL/SQL下按F5查看执行计划，可自定义查看的指标。oracle的执行计划1、如何查看执行计划2）toad等第三方工具oracle的执行计划1、如何查看执行计划3）在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下执行下面步骤SQLEXPLAINPLANFORSELECT*FROMSCOTT.EMP;--要解析的SQL脚本SQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);oracle的执行计划1、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（一）SQLSETTIMINGON--控制显示执行时间统计数据SQLSETAUTOTRACETRACEONLY--这样设置会有执行计划、统计信息，不会有脚本数据输出SQL执行需要查看执行计划的SQL语句SQLSETAUTOT