数据库培训高效率sql语句基础课件.pptx

Oracle数据库培训,高效率SQL语句基础-张林,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数据库中优化器（Optimizer）是SQL分,Oracle的优化器有两种：RBO(Rule-Based Optimization):基于规则的优化器CBO(Cost-Based Optimization):基于代价的优化器从Oracle 10g开始，RBO已经被弃用，但是我们依然可以通过Hint方式来使用它。CBO的思路是让Oracle获取所有执行计划相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。,优化器介绍,Oracle的优化器有两种：优化器介绍,使用CBO 时，需要注意如下几个方面：1、编写SQL语句时，不必考虑FROM 子句后面的表或视图的顺序和WHERE 子句后面的条件顺序；2、使用CBO 时，SQL语句 FROM 子句后面的表的个数不宜太多，因为CBO在选择表连接顺序时，会对FROM 子句后面的表进行阶乘运算，选择最好的一个连接顺序。3、如果一个语句使用 RBO的执行计划确实比CBO 好，则可以通过hint机制，强制使用RBO。Hint机制可以参考这篇文章：http:/,优化器介绍,使用CBO 时，需要注意如下几个方面：优化器介绍,理解索引大多数情况下，数据库使用索引来检索表，优化器根据用户定义的索引来提高执行性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器忽略索引而采用全表扫描，而这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时需要了解优化器根据何种原则来使用索引，这将有助于写出高性能的SQL语句。,高效率SQL基础,理解索引高效率SQL基础,1、条件中包含NULL值(IS NULL 与 IS NOT NULL)以NULL值做条件时，将无法使用包含NULL值的列上的索引。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，在使用NULL值做条件时，即使对该列建索引也不会提高性能。,理解索引-常见问题分析,1、条件中包含NULL值(IS NULL 与 IS NOT,2、列的连接列被包含到表达式中导致不能使用索引。对于有连接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。例：假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME)和(LAST_NAME)，现在要查询一个叫BillCliton的职工。下面是一个采用联接查询的SQL语句:select*fromemployeewherefirst_name|last_name=BillCliton;改进方法：select*fromemployeewherefirst_name=Billandlast_name=Cliton;,理解索引-常见问题分析,2、列的连接理解索引-常见问题分析,3、带通配符（%）的like语句select*fromemployeewherelast_namelike%cliton%;由于通配符（%）在搜寻词首出现，所以数据库将不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有数，通配符如此使用会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select*fromemployeewherelast_namelikec%;,理解索引-常见问题分析,3、带通配符（%）的like语句理解索引-常见问题分析,4、order by语句order by语句决定了数据库如何将返回的查询结果排序。orderby语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在orderby语句的非索引项或者有计算表达式都将降低查询速度。需要仔细检查orderby语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写orderby语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在orderby子句中使用表达式。,理解索引-常见问题分析,4、order by语句理解索引-常见问题分析,5、NOTNOT可用来对任何逻辑运算符号取反，例：select*from employee where not(salary=3000);要使用NOT，则应在取反的短语前面加上括号。NOT运算符的以上形式比较少用，但是它会包含在另外一个逻辑运算符中，这就是不等于”运算符，如：select*from employee where salary3000;解决方法:不使用NOT，例：select*from employee where salary3000;这两种查询的结果一样，但是第二种查询会对salary列使用索引，会更快些，而第一种查询则不会使用索引。,理解索引-常见问题分析,5、NOT理解索引-常见问题分析,6、IN和EXISTS(一)开发过程中经常会在where子句中使用子查询，主要有两种方式：.where column in(select column from.where.);.whereexists(selectXfrom.where.);采用第二种格式要比第一种格式的效率高。第二种格式中，子查询以selectX开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。,理解索引-常见问题分析,6、IN和EXISTS(一)理解索引-常见问题分析,6、IN和EXISTS(二)使用IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。所以使用EXISTS通常比使用IN查询速度快。应尽可能使用NOTEXISTS来代替NOTIN，尽管二者都使用了NOT(不能使用索引而降低速度)，但NOTEXISTS要比NOTIN查询效率高。,理解索引-常见问题分析,6、IN和EXISTS(二)理解索引-常见问题分析,7、不可优化的where子句（一）以下这条语句在nsrsbh字段上建有恰当的索引，但执行却很慢：select*from zk_kj_kpxx where substr(nsrsbh,0,6)=000000;(24秒)类似的还有如下形式的语句（假设以下字段都建有恰当的索引）：select*fromrecordwhereamount/301000;即：where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。,理解索引-常见问题分析,7、不可优化的where子句（一）理解索引-常见问题分析,7、不可优化的where子句（二）我们把上面的两条语句进行重写：select*from zk_kj_kpxx where nsrsbh like 000000%;(495毫秒)select*fromrecordwhereamount1000*30;即：没有对列进行任何操作，结果在查询编译时就能得到，那么就可以被 SQL优化器优化，使用索引，避免表搜索。之前在网开系统的SQL语句优化时碰到过类似的情况，请大家多加注意，尽量避免此类问题的发生。,理解索引-常见问题分析,7、不可优化的where子句（二）理解索引-常见问题分析,7、不可优化的where子句（三）表stuff有200000行，id_no上有非群集索引，如下：selectcount(*)fromstuffwhereid_noin(0,1);分析：where条件中的in在逻辑上相当于or，所以语法分析器会将in(0,1)转化为id_no=0orid_no=1来执行。虽然CBO会选择最优的执行计划，然而仍旧会出现没有利用id_no上索引的情况，导致查询较慢，如果数据量非常大且对SQL语句的效率有较高的要求不妨考虑使用简单的存储过程来实现相同的功能。,理解索引-常见问题分析,7、不可优化的where子句（三）理解索引-常见问题分析,7、不可优化的where子句（四）小结：1、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。左右不重要，重要的是表达式中是否包含列。2、in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。3、要根据实际情况善于使用存储过程，它使SQL变得更加灵活和高效。,理解索引-常见问题分析,7、不可优化的where子句（四）理解索引-常见问题分析,8、组合索引（一）在place，date，amount上建组合索引，前导列为placeselectcount(*)fromrecordwheredate19991201anddate2000（26秒）selectdate,sum(amount)fromrecordgroupbydate（27秒）selectcount(*)fromrecordwheredate19990901andplacein(BJ,SH)（1秒）。这是一个不合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引。,理解索引-常见问题分析,8、组合索引（一）理解索引-常见问题分析,8、组合索引（二）合理的索引设计，要使每个SQL都可以利用索引，那么我们就需要将date作为前导列，使三条语句性能达到最优。selectcount(*)fromrecordwheredate19991201anddate2000(19990901andplacein(BJ,SH)（1秒）,理解索引-常见问题分析,8、组合索引（二）理解索引-常见问题分析,8、组合索引（三）小结：1、有大量重复值、且经常有范围查询，如：（between,=,=）和orderby、groupby发生的列，可考虑建立聚簇索引。2、经常同时存取多列，且每列都含有重复值可考虑建立组合索引；3、组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。,理解索引-常见问题分析,8、组合索引（三）理解索引-常见问题分析,注意：索引并非越多越好，过多的索引会降低更新和插入操作的速度,理解索引-常见问题分析,注意：理解索引-常见问题分析,1、如何查看执行计划1）在PL/SQL下按F5查看执行计划，可自定义查看的指标。,oracle的执行计划,1、如何查看执行计划oracle的执行计划,1、如何查看执行计划2）toad等第三方工具,oracle的执行计划,1、如何查看执行计划oracle的执行计划,1、如何查看执行计划3）在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下执行下面步骤SQLEXPLAIN PLAN FORSELECT*FROM SCOTT.EMP;-要解析的SQL脚本SQLSELECT*FROM TABLE(DBMS_XPLAN.DISPLAY);,oracle的执行计划,1、如何查看执行计划oracle的执行计划,1、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（一）SQLSET TIMING ON-控制显示执行时间统计数据SQL SET AUTOTRACE TRACEONLY-这样设置会有执行计划、统计信息，不会有脚本数据输出SQL执行需要查看执行计划的SQL语句SQLSET AUTOTRACE OFF-不生成AUTOTRACE报告，这是缺省模式这是SQL*PLUS下最常用的查看执行计划的方式，没有脚本数据的输出，节省时间，易查看。,oracle的执行计划,1、如何查看执行计划oracle的执行计划,1、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（二）SQL SET AUTOTRACE ON-这样设置包含执行计划、统计信息、以及脚本数据输出SQL执行需要查看执行计划的SQL语句SQLSET AUTOTRACE OFF,oracle的执行计划,1、如何查看执行计划oracle的执行计划,1、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（三）SQLSET AUTOTRACE ON EXPLAIN-包含执行计划、脚本数据输出，没有统计信息SQL执行需要查看执行计划的SQL语句SQLSET AUTOTRACE OFF,oracle的执行计划,1、如何查看执行计划oracle的执行计划,1、如何查看执行计划4）在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令（四）SQLSET AUTOTRACE TRACEONLY STAT-这样设置只包含有统计信息SQL执行需要查看执行计划的SQL语句SQLSET AUTOTRACE OFF,oracle的执行计划,1、如何查看执行计划oracle的执行计划,2、看懂执行计划,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划Plan hash value这一行是这一条语句的的hash值，我们知道ORACLE对每一条ORACLE语句产生的执行计划放在SHARE POOL里面，第一次要经过硬解析，产生hash值。下次再执行时比较hash值，如果相同就不会执行硬解析。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划id:执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判断（采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行。一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。）operation：当前操作的内容。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划Name：操作对象Rows：oracle估计当前操作的返回结果集行数。Bytes：表示执行该步骤后返回的字节数。Cost（%CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。Cost没有单位，是一个相对值，是SQL以CBO方式解析执行计划时，供ORACLE来评估CBO成本，选择执行计划用的。没有明确的含义，但是在对比时非常有用Time：oracle 估计当前操作的时间。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划谓词说明：Predicate Information(identified by operation id):-2-filter(B.MGR IS NOT NULL)4-access(A.EMPNO=B.MGR)Access:表示这个谓词条件的值将会影响数据的访问路劲（全表扫描还是索引）。要注意access，考虑谓词的条件，使用的访问路径是否正确。Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划名词解释：recursive calls 递归调用db block gets 从buffer cache中读取的block的数量，当前请求的块数目consistent gets 从buffer cache中读取的undo数据的block的数量，这里的概念是在你处理你这个操作的时侯需要在一致性读状态上处理多个块physical reads 物理读，就是从磁盘上读取数据块的数量。其产生的主要原因是：1：在数据库高速缓存中不存在这些块。2：全表扫描3：磁盘排序,oracle的执行计划,2、看懂执行计划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发送了519字节的数据。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划如果在执行计划中有如下提示：Note-dynamic sampling used for the statement这提示用户CBO当前使用的技术，需要用户在分析计划时考虑这些因素。当出现这个提示，说明当前表使用了动态采样。我们从而推断这个表可能没有做过分析。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划这里会出现两种情况：（1）如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。（2）如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。,oracle的执行计划,2、看懂执行计划oracle的执行计划,2、看懂执行计划访问表方式总结：（1）Full Table Scan(FTS)全表扫描（2）Index Lookup 索引扫描，包含如下五种方式 index unique scan-索引唯一扫描 index range scan-索引局部扫描 index full scan-索引全局扫描 index fast full scan-索引快速全局扫描，不带order by情况下常发生 index skip scan-索引跳跃扫描，where条件列是非索引的前提下常发生（3）rowid 物理ID扫描，是最快的访问数据方式,oracle的执行计划,2、看懂执行计划oracle的执行计划,1、在select/insert语句中尽量避免使用通配符（*）。对于一张表而言，通配符（*）意味着要进行全表遍历，对于数据量比较大的表就会给oracle增加很大的压力。2、尽量减少between的使用。3、能使用数字类型的字段尽量使用数字类型，这将提高查询和连接的性能，并减少存储开销。引擎在处理查询和连接时会逐个比较字符串中的每个字符，对于数字类型只需要比较一次。4、尽量减少is null 和 is not null的使用，在任何where子句中使用is null 和 is not null的语句oracle内部优化器是不允许使用索引的。,书写高效率SQL语句建议,1、在select/insert语句中尽量避免使用通配符（*,5、优化where字句中的连接顺序。oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，可以过滤掉最大数据记录的条件必须写在where字句的末尾。6、减少访问数据库的次数（使用绑定变量）。7、合理使用like关键字，如非必要尽量减少like关键字的使用。8、oracle对于相同的语句，如果大小写或空格不同，oracle也会认为是不同的，因此编写sql语句的时候注意语句的大小写保持一致，可以减少访问数据库的次数，实际上减少oracle的工作量，这对于多用户多次执行相同的语句非常有意义。,书写高效率SQL语句建议,5、优化where字句中的连接顺序。oracle采用自下而上,9、书写sql语句的时候不要在索引字段上进行任何操作，包括函数操作，例如：select*from emp where sal*1210000;应该写成select*from emp where sal10000/12;10、尽量使用union替换掉or的操作，如果可能使用union all，但要注意，此操作是假设字段上已经合理创建索引，可通过字段索引快速查询到所需要的数据。11、尽量不要在含有索引的字段里面添加null，oracle对含有null的字段不使用索引。,书写高效率SQL语句建议,9、书写sql语句的时候不要在索引字段上进行任何操作，包括函,感谢！,感谢！,踏实，奋斗，坚持，专业，努力成就未来。23.1.323.1.3Tuesday,January 3,2023弄虚作假要不得，踏实肯干第一名。07:24:3307:24:3307:241/3/2023 7:24:33 AM安全象只弓，不拉它就松，要想保安全，常把弓弦绷。23.1.307:24:3307:24Jan-233-Jan-23重于泰山，轻于鸿毛。07:24:3307:24:3307:24Tuesday,January 3,2023不可麻痹大意，要防微杜渐。23.1.323.1.307:24:3307:24:33January 3,2023加强自身建设，增强个人的休养。2023年1月3日上午7时24分23.1.323.1.3追求卓越，让自己更好，向上而生。2023年1月3日星期二上午7时24分33秒07:24:3323.1.3严格把控质量关，让生产更加有保障。2023年1月上午7时24分23.1.307:24January 3,2023重规矩，严要求，少危险。2023年1月3日星期二7时24分33秒07:24:333 January 2023好的事情马上就会到来，一切都是最好的安排。上午7时24分33秒上午7时24分07:24:3323.1.3每天都是美好的一天，新的一天开启。23.1.323.1.307:2407:24:3307:24:33Jan-23务实，奋斗，成就，成功。2023年1月3日星期二7时24分33秒Tuesday,January 3,2023抓住每一次机会不能轻易流失，这样我们才能真正强大。23.1.32023年1月3日星期二7时24分33秒23.1.3,谢谢大家！,踏实，奋斗，坚持，专业，努力成就未来。9月-229月-22S,