ql优化-oracle数据库.ppt

Sql优化 Oracle数据库 sql优化 Oracle数据库 Sql优化目的Sql优化原则绑定变量where条件索引排序和分页其他问题 优化目的 将每一次操作需要占用的数据量 IO 和资源 cpu 内存等 减少到最低 即在最短的时间达到最大的数据吞吐量更快的相应时间在有限资源内执行更多任务 优化原则 使用绑定变量 共享sql语句 消除不必要的大表全表搜索 不必要的全表搜索导致大量不必要的I O 从而拖慢整个数据库的性能 在全表搜索是一个最快的访问方法时 将小表的全表搜索放到缓存内存中 确保最优的索引使用 确保最优的JOIN操作 确保所有操作都在内存中完成 使用绑定变量 共享sql语句 Sql语句执行过程 1 语法检查 syntaxcheck 检查此sql的拼写是否语法 2 语义检查 semanticcheck 诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限 3 对sql语句进行解析 prase 利用内部算法对sql进行解析 生成解析树 parsetree 及执行计划 executionplan 4 执行sql 返回结果 executeandreturn 软解析 softprase 和硬解析 hardprase 为了不重复解析相同的SQL语句 在第一次解析之后 ORACLE将SQL语句存放在内存中 这块位于系统全局区域SGA systemglobalarea 的共享池 sharedbufferpool 中的内存可以被所有的数据库用户共享 因此 当你执行一个SQL语句 有时被称为一个游标 时 如果它和之前的执行过的语句完全相同 ORACLE就能很快获得已经被解析的语句以及最好的执行路径 Oracle利用内部的hash算法来取得sql的hash值 然后在共享池 sharedbufferpool 里查找是否存在该hash值 假设存在 则将此sql与cache中的进行比较 假设 相同 就将利用已有的解析树与执行计划 而省略了优化器的相关工作 这也就是软解析的过程 如果上面的2个假设中任有一个不成立 那么优化器都将进行创建解析树 生成执行计划的动作 这个过程就叫硬解析 Sql语句必须大小写完全一致才可以软解析 创建解析树 生成执行计划对于sql的执行来说是开销昂贵的动作 所以 应当极力避免硬解析 尽量使用软解析 绑定变量 共享SQL语句 Sql语句必须大小写完全一致才可以共享 使用绑定变量的sql可以共享 使用软解析 降低系统资源的耗用 a中的两组SQL语句是相同的 可以共享 而b中的两组语句是不同的 即使在运行时 赋于不同的绑定变量相同的值 a selectempnofromempwhereempname AAA selectempnofromempwhereempname AAA selectpin namefrompeoplewherepin blk1 pin selectpin namefrompeoplewherepin blk1 pin b selectempnofromempwhereempname AAA selectEmpNofromEmpwhereEmpName AAA selectpin namefrompeoplewherepin blk1 ot ind selectpin namefrompeoplewherepin blk1 ov ind where条件 在一个SQL语句中 如果一个where条件过滤的数据库记录越多 定位越准确 则该where条件越应该前移 避免对列的四则运算 特别是where条件的左边 严禁使用运算与函数对列进行处理 比如有些地方substring可以用like代替 in和exists In里数据项不能太多 200个以下 子查询返回数据相对外围查询多 用exists效率高 子查询返回数据相对外围查询少 用in效率高 在子查询中 NOTIN子句将执行一个内部的排序和合并 无论在哪种情况下 NOTIN都是最低效的 因为它对子查询中的表执行了一个全表遍历 为了避免使用NOTIN 我们可以把它改写成外连接 OuterJoins 或NOTEXISTS 高效 SELECT FROMEMP 基础表 WHEREEMPNO 0ANDEXISTS SELECT X FROMDEPTWHEREDEPT DEPTNO EMP DEPTNOANDLOC MELB 低效 SELECT FROMEMP 基础表 WHEREEMPNO 0ANDDEPTNOIN SELECTDEPTNOFROMDEPTWHERELOC MELB Or和unionall 用UNIONAll替换OR 适用于索引列 通常情况下 用UNIONAll替换WHERE子句中的OR将会起到较好的效果 对索引列使用OR将造成全表扫描 使用UnionAll不进行排序去重复 使用Union需要去重复 可以改在外围用distinct去重 selectdistinctt from selectt1 namefromt1unionallselectt2 namefromt2 t 及操作符 大于或小于操作符 不等于操作符是永远不会用到索引的 因此对它的处理只会产生全表扫描 推荐方案 用其它相同功能的操作运算代替 如a0改为a 0ora 改为a 尽量使用 2时ORACLE会先找出为2的记录索引再进行比较 而A 3时ORACLE则直接找到 3的记录索引 索引 数据库索引实际是什么 索引是一个单独的 物理的数据库结构 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单 索引高效原理通常 索引页面相对于数据页面来说小得多 当进行数据检索时 系统先搜索索引页面 从中找到所需数据的指针 再直接通过指针从数据页面中读取数据 索引的选择和建立 1 索引应该建立在查询条件中进行比较的字段上 而不是建立在我们要找出来并且显示的字段上2 返回结果70 Oracle会自动选择全表扫描 3 btree索引和bitmap索引 函数索引 Btree索引适合选择性低的 唯一性高的 比如主键 姓名等Bitmap索引适合选择性高 唯一性低的 比如性别 地区等函数索引 CREATEINDEXindex nameONtable name upper col name1 selectcol name1 col name2fromtable namewhereupper col name1 value 复合索引 1 SELECT的字段建立复合索引 这样查询时只进行索引扫描 不读取数据块 2 对于复合索引要注意 例如在建立复合索引时列的顺序是F1 F2 F3 则在where或orderby子句中这些字段出现的顺序要与建立索引时的字段顺序一致 且必须包含第一列 只能是F1或F1 F2或F1 F2 F3 否则不会用到该索引 索引无效 1 在索引列上使用ISNULL和ISNOTNULL 2 改变索引列的类型 假设EMPNO是一个数值类型的索引列 SELECT FROMEMPWHEREEMPNO 123 3 使用函数 假设EMPNAME是一个字符类型的索引列 SELECT FROMEMPWHEREupper EMPNAME ABC 4 在索引列上使用计算SELECT FROMDEPTWHERESAL 12 25000 5 Like 字符类型的索引列select from whereYY BHLIKE 5400 排序 在以下的情况下Oracle会进行排序的操作 使用Orderby的SQL语句使用Groupby的SQL语句在创建索引的时候进行tablejoin时 由于现有索引的不足而导致SQL优化器调用MERGESORTSelectdistinct 排序 当与Oracle建立起一个session时 在内存中就会为该session分配一个私有的排序区域 当内存中的空间不能够满足session排序需要时 就会出现磁盘排序 disksorts 磁盘排序要比内存排序大概慢14 000倍 减小排序 减小集合的数据量 分页 1 最好还是利用分析函数row number over partitionbycol1orderbycol2 比如想取出240 250条记录 按照tas id排序selectt1 from selectrow number over orderbyt tas id rn t fromc tastwheret tas id 40000000 t1wherernbetween240and250 2 直接使用rownum虚列selectt1 from selectrownumrn t fromc tastwheret tas id 44000000orderbyt tas id t1wheret1 rnbetween240and250 使用序列不能基于整个记录集合进行排序 如果指定了orderby子句 排序的的是选出来的记录集的排序 分页 其他问题 不要使用select from 一定给出字段名 删除重复记录DELETEFROMEMPEWHEREE ROWID SELECTMIN X ROWID FROMEMPXWHEREX EMP NO E EMP NO Truncate