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

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 fromemployeewheresalary3000 这两种查询的结果一样 但是第二种查询会对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 30 1000 即 where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的 因此它不得不进行表搜索 而没有使用该列上面的索引 理解索引 常见问题分析 7 不可优化的where子句 二 我们把上面的两条语句进行重写 select fromzk kj kpxxwherensrsbhlike 000000 495毫秒 select fromrecordwhereamount 1000 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 anddate2000 26秒 selectdate sum amount fromrecordgroupbydate 27秒 selectcount fromrecordwheredate 19990901 andplacein BJ SH 1秒 这是一个不合理的组合索引 因为它的前导列是place 第一和第二条SQL没有引用place 因此也没有利用上索引 理解索引 常见问题分析 8 组合索引 二 合理的索引设计 要使每个SQL都可以利用索引 那么我们就需要将date作为前导列 使三条语句性能达到最优 selectcount fromrecordwheredate 19991201 anddate2000 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窗口均可 下执行下面步骤SQL EXPLAINPLANFORSELECT FROMSCOTT EMP 要解析的SQL脚本SQL SELECT FROMTABLE DBMS XPLAN DISPLAY oracle的执行计划 1 如何查看执行计划4 在SQL PLUS下 有些命令在PL SQL下无效 执行如下命令 一 SQL SETTIMINGON 控制显示执行时间统计数据SQL SETAUTOTRACETRACEONLY 这样设置会有执行计划 统计信息 不会有脚本数据输出SQL 执行需要查看执行计划的SQL语句SQL SETAUTOTRACEOFF 不生成AUTOTRACE报告 这是缺省模式这是SQL PLUS下最常用的查看执行计划的方式 没有脚本数据的输出 节省时间 易查看 oracle的执行计划 1 如何查看执行计划4 在SQL PLUS下 有些命令在PL SQL下无效 执行如下命令 二 SQL SETAUTOTRACEON 这样设置包含执行计划 统计信息 以及脚本数据输出SQL 执行需要查看执行计划的SQL语句SQL SETAUTOTRACEOFF oracle的执行计划 1 如何查看执行计划4 在SQL PLUS下 有些命令在PL SQL下无效 执行如下命令 三 SQL SETAUTOTRACEONEXPLAIN 包含执行计划 脚本数据输出 没有统计信息SQL 执行需要查看执行计划的SQL语句SQL SETAUTOTRACEOFF oracle的执行计划 1 如何查看执行计划4 在SQL PLUS下 有些命令在PL SQL下无效 执行如下命令 四 SQL SETAUTOTRACETRACEONLYSTAT 这样设置只包含有统计信息SQL 执行需要查看执行计划的SQL语句SQL SETAUTOTRACEOFF oracle的执行计划 2 看懂执行计划 oracle的执行计划 2 看懂执行计划Planhashvalue这一行是这一条语句的的hash值 我们知道ORACLE对每一条ORACLE语句产生的执行计划放在SHAREPOOL里面 第一次要经过硬解析 产生hash值 下次再执行时比较hash值 如果相同就不会执行硬解析 oracle的执行计划 2 看懂执行计划id 执行序列 但不是执行的先后顺序 执行的先后根据Operation缩进来判断 采用最右最上最先执行的原则看层次关系 在同一级如果某个动作没有子ID就最先执行 一般按缩进长度来判断 缩进最大的最先执行 如果有2行缩进一样 那么就先执行上面的 operation 当前操作的内容 oracle的执行计划 2 看懂执行计划Name 操作对象Rows oracle估计当前操作的返回结果集行数 Bytes 表示执行该步骤后返回的字节数 Cost CPU 表示执行到该步骤的一个执行成本 用于说明SQL执行的代价 Cost没有单位 是一个相对值 是SQL以CBO方式解析执行计划时 供ORACLE来评估CBO成本 选择执行计划用的 没有明确的含义 但是在对比时非常有用Time oracle估计当前操作的时间 oracle的执行计划 2 看懂执行计划谓词说明 PredicateInformation identifiedbyoperationid 2 filter B MGR ISNOTNULL 4 access A EMPNO B MGR Access 表示这个谓词条件的值将会影响数据的访问路劲 全表扫描还是索引 要注意access 考虑谓词的条件 使用的访问路径是否正确 Filter 表示谓词条件的值不会影响数据的访问路劲 只起过滤的作用 oracle的执行计划 2 看懂执行计划名词解释 recursivecalls递归调用dbblockgets从buffercache中读取的block的数量 当前请求的块数目consistentgets从buffercache中读取的undo数据的block的数量 这里的概念是在你处理你这个操作的时侯需要在一致性读状态上处理多个块physicalreads物理读 就是从磁盘上读取数据块的数量 其产生的主要原因是 1 在数据库高速缓存中不存在这些块 2 全表扫描3 磁盘排序 oracle的执行计划 2 看懂执行计划名词解释 redosizeDML生成的redo的大小sorts memory 在内存执行的排序量sorts disk 在磁盘执行的排序量1610bytessentviaSQL Nettoclient从SQL Net向客户端发送了1610字节的数据 519bytesreceivedviaSQL Netfromclient客户端向SQL Net发送了519字节的数据 oracle的执行计划 2 看懂执行计划如果在执行计划中有如下提示 Note dynamicsamplingusedforthestatement这提示用户CBO当前使用的技术 需要用户在分析计划时考虑这些因素 当出现这个提示 说明当前表使用了动态采样 我们从而推断这个表可能没有做过分析 oracle的执行计划 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语句中尽