ORACLESQL语句优化资料共享.ppt

ORACLESQL语句优化 资料共享 此内容参考 ORACLESQL性能优化系列 doc 访问Table的方式 ORACLE采用两种访问表中记录的方式 a 全表扫描全表扫描就是顺序地访问表中每条记录 ORACLE采用一次读入多个数据块 databaseblock 的方式优化全表扫描 b 通过ROWID访问表你可以采用基于ROWID的访问方式情况 提高访问表的效率 ROWID包含了表中记录的物理位置信息 ORACLE采用索引 INDEX 实现了数据和存放数据的物理位置 ROWID 之间的联系 通常索引提供了快速访问ROWID的方法 因此那些基于索引列的查询就可以得到性能上的提高 删除重复记录 最高效的删除重复记录方法 因为使用了ROWID DELETEFROMEMPEWHEREE ROWID SELECTMIN X ROWID FROMEMPXWHEREX EMP NO E EMP NO 基于索引的SQL语句优化 1 ISNULL与ISNOTNULL2 联接列3 带通配符 的like语句4 Orderby语句5 NOT6 IN和EXISTS7 用表连接替换EXISTS8 用EXISTS替换DISTINCT9 用WHERE替代ORDERBY10 用UNION替换OR 适用于索引列 11 用IN来替换OR 1 ISNULL与ISNOTNULL 不能用null作索引 任何包含null值的列都将不会被包含在索引中 即使索引有多列的情况下 只要这些列中有一列含有null 该列就会从索引中排除 也就是说如果某列存在空值 即使对该列建索引也不会提高性能 任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的 2 联接列 对于有联接的列 即使最后的联接值为一个静态值 优化器不会使用索引的 select fromemployeewherefirst name last name GeorgeBush 使用索引的语句Select Fromemployeewherefirst name George andlast name Bush select fromemployeewherefirst name SUBSTR 3 带通配符 的like语句 通配符 在搜寻词首出现 所以Oracle系统不使用的索引不使用索引的SQLselect fromemployeewherelast namelike Bush 使用索引的SQLselect fromemployeewherelast namelike c 4 Orderby语句 Orderby语句决定了Oracle如何将返回的查询结果排序 Orderby语句对要排序的列没有什么特别的限制 也可以将函数加入列中 象联接或者附加等 任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度 仔细检查orderby语句以找出非索引项或者表达式 它们会降低性能 解决这个问题的办法就是重写orderby语句以使用索引 也可以为所使用的列建立另外一个索引 同时应绝对避免在orderby子句中使用表达式 5 逻辑表达式NOT 不使用索引wherenot status VALID wherestatus VALID wherestatus VALID select fromemployeewheresalary3000 使用索引select fromemployeewheresalary3000 6 IN和EXISTS wherecolumnin select from where whereexists select X from where 通过使用EXISTS Oracle系统会首先检查主查询 然后运行子查询直到找到第一个匹配项 这就节省了时间 Oracle系统在执行IN子查询时 首先执行子查询 并将获得的结果列表存放在一个加了索引的临时表中 在执行子查询之前 系统先将主查询挂起 待子查询执行完毕 存放在临时表中以后再执行主查询 这也就是使用EXISTS比使用IN通常查询速度快的原因同时应尽可能使用NOTEXISTS来代替NOTIN 尽管二者都使用了NOT 不能使用索引而降低速度 但NOTEXISTS要比NOTIN查询效率更高 7 用表连接替换EXISTS 通常来说 采用表连接的方式比EXISTS更有效率SELECTENAMEFROMEMPEWHEREEXISTS SELECT X FROMDEPTWHEREDEPT NO E DEPT NOANDDEPT CAT A 更高效 SELECTENAMEFROMDEPTD EMPEWHEREE DEPT NO D DEPT NOANDDEPT CAT A 8 用EXISTS替换DISTINCT 多表信息的查询时 避免在SELECT子句中使用DISTINCT 一般可以考虑用EXIST替换 例如 低效 SELECTDISTINCTDEPT NO DEPT NAMEFROMDEPTD EMPEWHERED DEPT NO E DEPT NO高效 SELECTDEPT NO DEPT NAMEFROMDEPTDWHEREEXISTS SELECT X FROMEMPEWHEREE DEPT NO D DEPT NO EXISTS使查询更为迅速 因为RDBMS核心模块将在子查询的条件一旦满足后 立刻返回结果 9 用WHERE替代ORDERBY ORDERBY子句只在两种严格的条件下使用索引 ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序 ORDERBY中所有的列必须定义为非空 WHERE子句使用的索引和ORDERBY子句中所使用的索引不能并列 例如 表DEPT包含以下列 DEPT CODEPKNOTNULLDEPT DESCNOTNULLDEPT TYPENULL非唯一性的索引 DEPT TYPE 9 用WHERE替代ORDERBY 低效 索引不被使用 SELECTDEPT CODEFROMDEPTORDERBYDEPT TYPEEXPLAINPLAN SORTORDERBYTABLEACCESSFULL高效 使用索引 SELECTDEPT CODEFROMDEPTWHEREDEPT TYPE 0EXPLAINPLAN TABLEACCESSBYROWIDONEMPINDEXRANGESCANONDEPT IDX 10 用UNION替换OR 适用于索引列 通常情况下 用UNION替换WHERE子句中的OR将会起到较好的效果 对索引列使用OR将造成全表扫描 注意 以上规则只针对多个索引列有效 如果有column没有被索引 查询效率可能会因为你没有选择OR而降低 在下面的例子中 LOC ID和REGION上都建有索引 高效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10UNIONSELECTLOC ID LOC DESC REGIONFROMLOCATIONWHEREREGION MELBOURNE 低效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10ORREGION MELBOURNE 11 用IN来替换OR 下面的查询可以被更有效率的语句替换 低效 SELECT FROMLOCATIONWHERELOC ID 10ORLOC ID 20ORLOC ID 30高效SELECT FROMLOCATIONWHERELOC ININ 10 20 30 最有效率的表名顺序 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名 因此FROM子句中写在最后的表 基础表drivingtable 将被最先处理表TAB116 384条记录表TAB21条记录选择TAB2作为基础表 最好的方法 selectcount fromtab1 tab2执行时间0 96秒选择TAB2作为基础表 不佳的方法 selectcount fromtab2 tab1执行时间26 09秒 多表连接 交叉表为基础表 SELECT FROMLOCATIONL CATEGORYC EMPEWHEREE EMP NOBETWEEN1000AND2000ANDE CAT NO C CAT NOANDE LOCN L LOCN将比下列SQL更有效率SELECT FROMEMPE LOCATIONL CATEGORYCWHEREE CAT NO C CAT NOANDE LOCN L LOCNANDE EMP NOBETWEEN1000AND2000 WHERE子句中的连接顺序 原理 自下而上的顺序解析WHERE子句表之间的连接必须写在其他WHERE条件之前 可过滤掉最大数量记录的条件必须写在WHERE子句的末尾 WHERE子句中的连接顺序 例如 低效 执行时间156 3秒 SELECT FROMEMPEWHERESAL 50000ANDJOB MANAGER AND2550000ANDJOB MANAGER SELECT子句中避免使用 ORACLE在解析的过程中 会将 依次转换成所有的列名 这个工作是通过查询数据字典完成的 这意味着将耗费更多的时间子句中写出指定的列名 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表 例如 SELECTCOUNT SUM SAL FROMEMPWHEREDEPT NO 0020ANDENAMELIKE SMITH SELECTCOUNT SUM SAL FROMEMPWHEREDEPT NO 0030ANDENAMELIKE SMITH 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表 例如 你可以用DECODE函数高效地得到相同结果SELECTCOUNT DECODE DEPT NO 0020 X NULL D0020 COUNT COUNT DECODE DEPT NO 0030 X NULL D0030 COUNT SUM DECODE DEPT NO 0020 SAL NULL D0020 SAL SUM DECODE DEPT NO 0030 SAL NULL D0030 SALFROMEMPWHEREENAMELIKE SMITH 类似的 DECODE函数也可以运用于GROUPBY和ORDERBY子句中 尽量多使用COMMIT 只要有可能 在程序中尽量多使用COMMIT 这样程序的性能得到提高 需求也会因为COMMIT所释放的资源而减少 COMMIT所释放的资源 a 回滚段上用于恢复数据的信息 b 被程序语句获得的锁c redologbuffer中的空间d ORACLE为管理上述3种资源中的内部花费 用Where子句替换HAVING子句 避免使用HAVING子句 HAVING只会在检索出所有记录之后才对结果集进行过滤 这个处理需要排序 总计等操作 如果能通过WHERE子句限制记录的数目 那就能减少这方面的开销 用Where子句替换HAVING子句 例如 低效 SELECTREGION AVG LOG SIZE FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION SYDNEY ANDREGION PERTH 高效SELECTREGION AVG LOG SIZE FROMLOCATIONWHEREREGIONREGION SYDNEY ANDREGION PERTH GROUPBYREGION 用 替代 如果DEPTNO上有一个索引 高效 SELECT FROMEMPWHEREDEPTNO 4低效 SELECT FROMEMPWHEREDEPTNO 3两者的区别在于 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO 3的记录并且向前扫描到第一个DEPT大于3的记录 使用表的别名 Alias 当在SQL语句中连接多个表时 请使用表的别名并把别名前缀于每个Column上 这样一来 就可以减少解析的时间并减少那些由Column歧义引起的语法错误 译者注 Column歧义指的是由于SQL中不同的表具有相同的Column名 当SQL语句中出现这个Column时 SQL解析器无法判断这个Column的归属 减少访问数据库的次数 当执行每条SQL语句时 ORACLE在内部执行了许多工作 解析SQL语句 估算索引的利用率 绑定变量 读数据块等等 由此可见 减少访问数据库的次数 就能实际上减少ORACLE的工作量 减少访问数据库的次数 例如 以下有三种方法可以检索出雇员号等于0342或0291的职员 方法1 最低效 SELECTEMP NAME SALARY GRADEFROMEMPWHEREEMP NO 342 SELECTEMP NAME SALARY GRADEFROMEMPWHEREEMP NO 291 减少访问数据库的次数 例如 以下有三种方法可以检索出雇员号等于0342或0291的职员 方法2 次低效 DECLARECURSORC1 E NONUMBER ISSELECTEMP NAME SALARY GRADEFROMEMPWHEREEMP NO E NO BEGINOPENC1 342 FETCHC1INTO OPENC1 291 FETCHC1INTO CLOSEC1 END 减少访问数据库的次数 例如 以下有三种方法可以检索出雇员号等于0342或0291的职员 方法3 高效 SELECTA EMP NAME A SALARY A GRADE B EMP NAME B SALARY B GRADEFROMEMPA EMPBWHEREA EMP NO 342ANDB EMP NO 291 减少对表的查询 在含有子查询的SQL语句中 要特别注意减少对表的查询 减少对表的查询 例如 低效SELECTTAB NAMEFROMTABLESWHERETAB NAME SELECTTAB NAMEFROMTAB COLUMNSWHEREVERSION 604 ANDDB VER SELECTDB VERFROMTAB COLUMNSWHEREVERSION 604 高效SELECTTAB NAMEFROMTABLESWHERE TAB NAME DB VER SELECTTAB NAME DB VERFROMTAB COLUMNSWHEREVERSION 604 减少对表的查询 Update多个Column例子 低效 UPDATEEMPSETEMP CAT SELECTMAX CATEGORY FROMEMP CATEGORIES SAL RANGE SELECTMAX SAL RANGE FROMEMP CATEGORIES WHEREEMP DEPT 0020 高效 UPDATEEMPSET EMP CAT SAL RANGE SELECTMAX CATEGORY MAX SAL RANGE FROMEMP CATEGORIES WHEREEMP DEPT 0020 使用提示 Hints 对于表的访问 可以使用两种Hints FULL和ROWID索引FULLhint告诉ORACLE使用全表扫描的方式访问指定表 索引hint告诉ORACLE使用基于索引的扫描方式 你不必说明具体的索引名称在不使用hint的情况下 以上的查询应该也会使用索引 然而 如果该索引的重复值过多而你的优化器是CBO 优化器就可能忽略索引 在这种情况下 你可以用INDEXhint强制ORACLE使用该索引 ORACLEhints还包括ALL ROWS FIRST ROWS RULE USE NL USE MERGE USE HASH等等参考文件sql语句优化 doc 识别 低效执行 的SQL语句 用下列SQL工具找出低效SQL 用EXPLAINPLAN分析SQL语句 EXPLAINPLAN是一个很好的分析SQL语句的工具 它甚至可以在不执行SQL的情况下分析语句 通过分析 我们就可以知道ORACLE是怎么样连接表 使用什么方式扫描表 索引扫描或全表扫描 以及使用到的索引名称 你需要按照从里到外 从上到下的次序解读分析的结果 EXPLAINPLAN分析的结果是用缩进的格式排列的 最内部的操作将被最先解读 如果两个操作处于同一层中 带有最小操作号的将被首先执行 NESTEDLOOP是少数不按照上述规则处理的操作 正确的执行路径是检查对NESTEDLOOP提供数据的操作 其中操作号最小的将被最先处理 用EXPLAINPLAN分析SQL语句 通过实践 感到还是用SQLPLUS中的SETTRACE功能比较方便 举例 SQL list1SELECT 2FROMSCdept emp3 WHEREemp deptno dept deptnoSQL setautotracetraceonly traceonly可以不显示执行结果 SQL 14rowsselected 用EXPLAINPLAN分析SQL语句 ExecutionPlan 0SELECTSTATEMENTOptimizer CHOOSE10NESTEDLOOPS21TABLEACCESS FULL OF EMP 31TABLEACCESS BYINDEXROWID OF DEPT 43INDEX UNIQUESCAN OF PK DEPT UNIQUE 用EXPLAINPLAN分析SQL语句 Statistics 0recursivecalls2dbblockgets30consistentgets0physicalreads0redosize2598bytessentviaSQL Nettoclient503bytesreceivedviaSQL Netfromclient2SQL Netroundtripsto fromclient0sorts memory 0sorts disk 14rowsprocessed 用EXPLAINPLAN分析SQL语句 通过以上分析 可以得出实际的执行步骤是 1 TABLEACCESS FULL OF EMP 2 INDEX UNIQUESCAN OF PK DEPT UNIQUE 3 TABLEACCESS BYINDEXROWID OF DEPT 4 NESTEDLOOPS JOINING1AND3 注 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQLAnalyze都提供了极其方便的EXPLAINPLAN工具 也许喜欢图形化界面的朋友们可以选用它们