ORACLE+SQL性能优化系列

第 1 页 共 31 页 ORACLEORACLE SQLSQL 性能优化系列性能优化系列 1 1 访问访问 TableTable 的方式的方式 ORACLEORACLE 采用两种访问表中记录的方式采用两种访问表中记录的方式 a a 全表扫描全表扫描 全表扫描就是顺序地访问表中每条记录全表扫描就是顺序地访问表中每条记录 ORACLEORACLE 采用一次读入多个数据块采用一次读入多个数据块 database database block block 的方式优化全表扫描的方式优化全表扫描 b b 通过通过 ROWIDROWID 访问表访问表 你可以采用基于你可以采用基于 ROWIDROWID 的访问方式情况的访问方式情况 提高访问表的效率提高访问表的效率 ROWIDROWID 包含了表中记录的包含了表中记录的 物理位置信息物理位置信息 ORACLE ORACLE 采用索引采用索引 INDEX INDEX 实现了数据和存放数据的物理位置实现了数据和存放数据的物理位置 ROWID ROWID 之间之间 的联系的联系 通常索引提供了快速访问通常索引提供了快速访问 ROWIDROWID 的方法的方法 因此那些基于索引列的查询就可以得到因此那些基于索引列的查询就可以得到 性能上的提高性能上的提高 2 2 共享共享 SQLSQL 语句语句 为了不重复解析相同的为了不重复解析相同的 SQLSQL 语句语句 在第一次解析之后在第一次解析之后 ORACLEORACLE 将将 SQLSQL 语句存放在内存中语句存放在内存中 这块位于系统全局区域这块位于系统全局区域 SGA systemSGA system globalglobal area area 的共享池的共享池 shared shared bufferbuffer pool pool 中的内中的内 存可以被所有的数据库用户共享存可以被所有的数据库用户共享 因此因此 当你执行一个当你执行一个 SQLSQL 语句语句 有时被称为一个游标有时被称为一个游标 时时 如果它和之前的执行过的语句完全相同如果它和之前的执行过的语句完全相同 ORACLEORACLE 就能很快获得已经被解析的语句以及最就能很快获得已经被解析的语句以及最 好的执行路径好的执行路径 ORACLEORACLE 的这个功能大大地提高了的这个功能大大地提高了 SQLSQL 的执行性能并节省了内存的使用的执行性能并节省了内存的使用 可惜的是可惜的是 ORACLEORACLE 只对简单的表提供高速缓冲只对简单的表提供高速缓冲 cache cache buffering buffering 这个功能并不适用于多这个功能并不适用于多 表连接查询表连接查询 数据库管理员必须在数据库管理员必须在 init orainit ora 中为这个区域设置合适的参数中为这个区域设置合适的参数 当这个内存区域越大当这个内存区域越大 就可就可 以保留更多的语句以保留更多的语句 当然被共享的可能性也就越大了当然被共享的可能性也就越大了 当你向当你向 ORACLEORACLE 提交一个提交一个 SQLSQL 语句语句 ORACLE ORACLE 会首先在这块内存中查找相同的语句会首先在这块内存中查找相同的语句 这里需要注明的是这里需要注明的是 ORACLE ORACLE 对两者采取的是一种严格匹配对两者采取的是一种严格匹配 要达成共享要达成共享 SQL SQL 语句必须完全语句必须完全 相同相同 包括空格包括空格 换行等换行等 共享的语句必须满足三个条件共享的语句必须满足三个条件 A A 字符级的比较字符级的比较 当前被执行的语句和共享池中的语句必须完全相同当前被执行的语句和共享池中的语句必须完全相同 例如例如 SELECTSELECT FROMFROM EMP EMP 和下列每一个都不同和下列每一个都不同 SELECTSELECT fromfrom EMP EMP SelectSelect FromFrom Emp Emp SELECTSELECT FROMFROM EMP EMP 第 2 页 共 31 页 B B 两个语句所指的对象必须完全相同两个语句所指的对象必须完全相同 例如例如 用户用户 对象名对象名 如何访问如何访问 JackJack sal limitsal limit privateprivate synonymsynonym Work cityWork city publicpublic synonymsynonym Plant detailPlant detail publicpublic synonymsynonym JillJill sal limitsal limit privateprivate synonymsynonym Work cityWork city publicpublic synonymsynonym Plant detailPlant detail tabletable ownerowner 考虑一下下列考虑一下下列 SQLSQL 语句能否在这两个用户之间共享语句能否在这两个用户之间共享 SQLSQL 能否共享能否共享 原因原因 selectselect max sal cap max sal cap fromfrom sal limit sal limit 不能不能 每个用户都有一个每个用户都有一个 privateprivate synonymsynonym sal limitsal limit 它们是不同的对象它们是不同的对象 selectselect count 0count 0 fromfrom work citywork city wherewhere sdescsdesc likelike NEW NEW 能能 两个用户访问相同的对象两个用户访问相同的对象 publicpublic synonymsynonym work citywork city selectselect a sdesc b locationa sdesc b location fromfrom work citywork city a a plant detailplant detail b b wherewhere a city ida city id b city idb city id 不能不能 用户用户 jackjack 通过通过 privateprivate synonymsynonym 访问访问 plant detailplant detail 而而 jilljill 是表的所有者是表的所有者 对象不同对象不同 C C 两个两个 SQLSQL 语句中必须使用相同的名字的绑定变量语句中必须使用相同的名字的绑定变量 bind bind variables variables 例如 例如 第一组的两个第一组的两个 SQLSQL 语句是相同的语句是相同的 可以共享可以共享 而第二组中的两个语句是不同的而第二组中的两个语句是不同的 即使在运行即使在运行 时时 赋于不同的绑定变量相同的值赋于不同的绑定变量相同的值 a a selectselect pinpin namename fromfrom peoplepeople wherewhere pinpin blk1 pin blk1 pin selectselect pinpin namename fromfrom peoplepeople wherewhere pinpin blk1 pin blk1 pin b b selectselect pinpin namename fromfrom peoplepeople wherewhere pinpin blk1 ot ind blk1 ot ind selectselect pinpin namename fromfrom peoplepeople wherewhere pinpin blk1 ov ind blk1 ov ind 3 3 选择最有效率的表名顺序选择最有效率的表名顺序 只在基于规则的优化器中有效只在基于规则的优化器中有效 第 3 页 共 31 页 ORACLEORACLE 的解析器按照从右到左的顺序处理的解析器按照从右到左的顺序处理 FROMFROM 子句中的表名子句中的表名 因此因此 FROMFROM 子句中写在最后子句中写在最后 的表的表 基础表基础表 drivingdriving table table 将被最先处理将被最先处理 在在 FROMFROM 子句中包含多个表的情况下子句中包含多个表的情况下 你必须你必须 选择记录条数最少的表作为基础表选择记录条数最少的表作为基础表 当当 ORACLEORACLE 处理多个表时处理多个表时 会运用排序及合并的方式连会运用排序及合并的方式连 接它们接它们 首先首先 扫描第一个表扫描第一个表 FROM FROM 子句中最后的那个表子句中最后的那个表 并对记录进行排序并对记录进行排序 然后扫描第二然后扫描第二 个表个表 FROM FROM 子句中最后第二个表子句中最后第二个表 最后将所有从第二个表中检索出的记录与第一个表中合最后将所有从第二个表中检索出的记录与第一个表中合 适记录进行合并适记录进行合并 例如例如 表表 TAB1TAB1 16 38416 384 条记录条记录 表表 TAB2TAB2 1 1 条记录条记录 选择选择 TAB2TAB2 作为基础表作为基础表 最好的方法最好的方法 selectselect count count fromfrom tab1 tab2tab1 tab2 执行时间执行时间 0 960 96 秒秒 选择选择 TAB1TAB1 作为基础表作为基础表 不佳的方法不佳的方法 selectselect count count fromfrom tab2 tab1tab2 tab1 执行时间执行时间 26 0926 09 秒秒 如果有如果有 3 3 个以上的表连接查询个以上的表连接查询 那就需要选择交叉表那就需要选择交叉表 intersection intersection table table 作为基础表作为基础表 交叉表是指那个被其他表所引用的表交叉表是指那个被其他表所引用的表 例如例如 EMPEMP 表描述了表描述了 LOCATIONLOCATION 表和表和 CATEGORYCATEGORY 表的交集表的交集 SELECTSELECT FROMFROM LOCATIONLOCATION L L CATEGORYCATEGORY C C EMPEMP E E WHEREWHERE E EMP NOE EMP NO BETWEENBETWEEN 10001000 ANDAND 20002000 ANDAND E CAT NOE CAT NO C CAT NOC CAT NO ANDAND E LOCNE LOCN L LOCNL LOCN 将比下列将比下列 SQLSQL 更有效率更有效率 SELECTSELECT FROMFROM EMPEMP E E LOCATIONLOCATION L L CATEGORYCATEGORY C C WHEREWHERE E CAT NOE CAT NO C CAT NOC CAT NO ANDAND E LOCNE LOCN L LOCNL LOCN ANDAND E EMP NOE EMP NO BETWEENBETWEEN 10001000 ANDAND 20002000 4 4 WHERE WHERE 子句中的连接顺序子句中的连接顺序 ORACLEORACLE 采用自下而上的顺序解析采用自下而上的顺序解析 WHEREWHERE 子句子句 根据这个原理根据这个原理 表之间的连接必须写在其他表之间的连接必须写在其他 WHEREWHERE 条件之前条件之前 那些那些可以过滤掉最大数量记录的条件必须写在可以过滤掉最大数量记录的条件必须写在 WHEREWHERE 子句的末尾子句的末尾 例如例如 低效低效 执行时间执行时间 156 3156 3 秒秒 第 4 页 共 31 页 SELECTSELECT FROMFROM EMPEMP E E WHEREWHERE SALSAL 5000050000 ANDAND JOBJOB MANAGER MANAGER ANDAND 2525 SELECT SELECT COUNT COUNT FROMFROM EMPEMP WHEREWHERE MGR E EMPNO MGR E EMPNO 高效高效 执行时间执行时间 10 610 6 秒秒 SELECTSELECT FROMFROM EMPEMP E E WHEREWHERE 2525 5000050000 ANDAND JOBJOB MANAGER MANAGER 5 5 SELECT SELECT 子句中避免使用子句中避免使用 当你想在当你想在 SELECTSELECT 子句中列出所有的子句中列出所有的 COLUMNCOLUMN 时时 使用动态使用动态 SQLSQL 列引用列引用 是一个方便的是一个方便的 方法方法 不幸的是不幸的是 这是一个这是一个非常低效非常低效的方法的方法 实际上实际上 ORACLE ORACLE 在解析的过程中在解析的过程中 会将会将 依依 次转换成所有的列名次转换成所有的列名 这个工作是通过查询数据字典完成的这个工作是通过查询数据字典完成的 这意味着将耗费更多的时间这意味着将耗费更多的时间 6 6 减少访问数据库的次数减少访问数据库的次数 当执行每条当执行每条 SQLSQL 语句时语句时 ORACLEORACLE 在内部执行了许多工作在内部执行了许多工作 解析解析 SQLSQL 语句语句 估算索引的利估算索引的利 用率用率 绑定变量绑定变量 读数据块等等读数据块等等 由此可见由此可见 减少访问数据库的次数减少访问数据库的次数 就能实际上减少就能实际上减少 ORACLEORACLE 的工作量的工作量 例如例如 以下有三种方法可以检索出雇员号等于以下有三种方法可以检索出雇员号等于 03420342 或或 02910291 的职员的职员 方法方法 1 1 低效低效 SELECTSELECT EMP NAMEEMP NAME SALARYSALARY GRADEGRADE FROMFROM EMPEMP WHEREWHERE EMP NOEMP NO 342 342 SELECTSELECT EMP NAMEEMP NAME SALARYSALARY GRADEGRADE FROMFROM EMPEMP WHEREWHERE EMP NOEMP NO 291 291 方法方法 2 2 高效高效 SELECTSELECT A EMP NAMEA EMP NAME A SALARYA SALARY A GRADE A GRADE B EMP NAMEB EMP NAME B SALARYB SALARY B GRADEB GRADE FROMFROM EMPEMP A EMPA EMP B B WHEREWHERE A EMP NOA EMP NO 342342 ANDAND B EMP NOB EMP NO 291 291 OrOr 第 5 页 共 31 页 SELECTSELECT EMP NAMEEMP NAME SALARYSALARY GRADEGRADE FROMFROM EMPEMP WHEREWHERE EMP NOEMP NO 342342 oror EMP NOEMP NO 291 291 7 7 使用使用 DECODEDECODE 函数来减少处理时间函数来减少处理时间 使用使用 DECODEDECODE 函数可以避免重复扫描相同记录或重复连接相同的表函数可以避免重复扫描相同记录或重复连接相同的表 例如例如 SELECTSELECT COUNT COUNT SUM SAL SUM SAL FROMFROM EMPEMP WHEREWHERE DEPT NODEPT NO 00200020 ANDAND ENAMEENAME LIKELIKE SMITH SMITH SELECTSELECT COUNT COUNT SUM SAL SUM SAL FROMFROM EMPEMP WHEREWHERE DEPT NODEPT NO 00300030 ANDAND ENAMEENAME LIKELIKE SMITH SMITH 你可以用你可以用 DECODEDECODE 函数高效地得到相同结果函数高效地得到相同结果 SELECTSELECT COUNT DECODE DEPT NO 0020 X NULL COUNT DECODE DEPT NO 0020 X NULL D0020 COUNT D0020 COUNT COUNT DECODE DEPT NO 0030 X NULL COUNT DECODE DEPT NO 0030 X NULL D0030 COUNT D0030 COUNT SUM DECODE DEPT NO 0020 SAL NULL SUM DECODE DEPT NO 0020 SAL NULL D0020 SAL D0020 SAL SUM DECODE DEPT NO 0030 SAL NULL SUM DECODE DEPT NO 0030 SAL NULL D0030 SALD0030 SAL FROMFROM EMPEMP WHEREWHERE ENAMEENAME LIKELIKE SMITH SMITH 类似的类似的 DECODE DECODE 函数也可以运用于函数也可以运用于 GROUPGROUP BYBY 和和 ORDERORDER BYBY 子句中子句中 8 8 删除重复记录删除重复记录 最高效的删除重复记录方法最高效的删除重复记录方法 因为使用了因为使用了 ROWID ROWID DELETEDELETE FROMFROM EMPEMP E E WHEREWHERE E ROWIDE ROWID SELECT SELECT MIN X ROWID MIN X ROWID FROMFROM EMPEMP X X WHEREWHERE X EMP NOX EMP NO E EMP NO E EMP NO 9 9 用用 TRUNCATETRUNCATE 替代替代 DELETEDELETE 当删除表中的记录时当删除表中的记录时 在通常情况下在通常情况下 回滚段回滚段 rollback rollback segmentssegments 用来存放可以被恢复用来存放可以被恢复 的信息的信息 如果你没有如果你没有 COMMITCOMMIT 事务事务 ORACLE ORACLE 会将数据恢复到删除之前的状态会将数据恢复到删除之前的状态 准确地说是恢准确地说是恢 复到执行删除命令之前的状况复到执行删除命令之前的状况 而当运用而当运用 TRUNCATETRUNCATE 时时 回滚段不再存放任何可被恢复的信息回滚段不再存放任何可被恢复的信息 当命令运行后当命令运行后 数据不能被数据不能被 恢复恢复 因此很少的资源被调用因此很少的资源被调用 执行时间也会很短执行时间也会很短 第 6 页 共 31 页 译者按译者按 TRUNCATETRUNCATE 只在删除全表适用只在删除全表适用 TRUNCATE TRUNCATE 是是 DDLDDL 不是不是 DML DML 10 10 尽量多使用尽量多使用 COMMITCOMMIT 只要有可能只要有可能 在程序中尽量多使用在程序中尽量多使用 COMMIT COMMIT 这样程序的性能得到提高这样程序的性能得到提高 需求也会因为需求也会因为 COMMITCOMMIT 所释放的资源而减少所释放的资源而减少 COMMITCOMMIT 所释放的资源所释放的资源 a a 回滚段上用于恢复数据的信息回滚段上用于恢复数据的信息 b b 被程序语句获得的锁被程序语句获得的锁 c c redoredo loglog bufferbuffer 中的空间中的空间 d d ORACLEORACLE 为管理上述为管理上述 3 3 种资源中的内部花费种资源中的内部花费 译者按译者按 在使用在使用 COMMITCOMMIT 时必须要注意到事务的完整性时必须要注意到事务的完整性 现实中效率和事务完整性往往是现实中效率和事务完整性往往是 鱼和熊掌不可得兼鱼和熊掌不可得兼 11 11 计算记录条数计算记录条数 和一般的观点相反和一般的观点相反 count count 比比 count 1 count 1 稍快稍快 当然如果可以通过索引检索当然如果可以通过索引检索 对索引列对索引列 的计数仍旧是最快的的计数仍旧是最快的 例如例如 COUNT EMPNO COUNT EMPNO 译者按译者按 在在 CSDNCSDN 论坛中论坛中 曾经对此有过相当热烈的讨论曾经对此有过相当热烈的讨论 作者的观点并不十分准确作者的观点并不十分准确 通过通过 实际的测试实际的测试 上述三种方法并没有显著的性能差别上述三种方法并没有显著的性能差别 12 12 用用 WhereWhere 子句替换子句替换 HAVINGHAVING 子句子句 避免使用避免使用 HAVINGHAVING 子句子句 HAVINGHAVING 只会在检索出所有记录之后才对结果集进行过滤只会在检索出所有记录之后才对结果集进行过滤 这个处这个处 理需要排序理需要排序 总计等操作总计等操作 如果能通过如果能通过 WHEREWHERE 子句限制记录的数目子句限制记录的数目 那就能减少这方面的开那就能减少这方面的开 销销 例如例如 低效低效 SELECTSELECT REGIONREGION AVG LOG SIZE AVG LOG SIZE FROMFROM LOCATIONLOCATION GROUPGROUP BYBY REGIONREGION HAVINGHAVING REGIONREGION REGIONREGION SYDNEY SYDNEY ANDAND REGIONREGION PERTH PERTH 高效高效 SELECTSELECT REGIONREGION AVG LOG SIZE AVG LOG SIZE FROMFROM LOCATIONLOCATION 第 7 页 共 31 页 WHEREWHERE REGIONREGION REGIONREGION SYDNEY SYDNEY ANDAND REGIONREGION PERTH PERTH GROUPGROUP BYBY REGIONREGION 译者按译者按 HAVINGHAVING 中的条件一般用于对一些集合函数的比较中的条件一般用于对一些集合函数的比较 如如 COUNT COUNT 等等等等 除此而外除此而外 一一 般的条件应该写在般的条件应该写在 WHEREWHERE 子句中子句中 13 13 减少对表的查询减少对表的查询 在含有子查询的在含有子查询的 SQLSQL 语句中语句中 要特别注意减少对表的查询要特别注意减少对表的查询 例如例如 低效低效 SELECTSELECT TAB NAMETAB NAME FROMFROM TABLESTABLES WHEREWHERE TAB NAMETAB NAME SELECTSELECT TAB NAMETAB NAME FROMFROM TAB COLUMNSTAB COLUMNS WHEREWHERE VERSIONVERSION 604 604 ANDAND DB VER DB VER SELECTSELECT DB VERDB VER FROMFROM TAB COLUMNSTAB COLUMNS WHEREWHERE VERSIONVERSION 604 604 高效高效 SELECTSELECT TAB NAMETAB NAME FROMFROM TABLESTABLES WHEREWHERE TAB NAME DB VER TAB NAME DB VER SELECTSELECT TAB NAME DB VER TAB NAME DB VER FROMFROM TAB COLUMNSTAB COLUMNS WHEREWHERE VERSIONVERSION 604 604 UpdateUpdate 多个多个 ColumnColumn 例子例子 低效低效 UPDATEUPDATE EMPEMP SETSET EMP CATEMP CAT SELECT SELECT MAX CATEGORY MAX CATEGORY FROMFROM EMP CATEGORIES EMP CATEGORIES SAL RANGESAL RANGE SELECT SELECT MAX SAL RANGE MAX SAL RANGE FROMFROM EMP CATEGORIES EMP CATEGORIES WHEREWHERE EMP DEPTEMP DEPT 0020 0020 高效高效 UPDATEUPDATE EMPEMP SETSET EMP CAT EMP CAT SAL RANGE SAL RANGE SELECT SELECT MAX CATEGORY MAX CATEGORY MAX SAL RANGE MAX SAL RANGE FROMFROM EMP CATEGORIES EMP CATEGORIES WHEREWHERE EMP DEPTEMP DEPT 0020 0020 14 14 通过内部函数提高通过内部函数提高 SQLSQL 效率效率 第 8 页 共 31 页 SELECTSELECT H EMPNO E ENAME H HIST TYPE T TYPE DESC COUNT H EMPNO E ENAME H HIST TYPE T TYPE DESC COUNT FROMFROM HISTORY TYPEHISTORY TYPE T EMPT EMP E EMP HISTORYE EMP HISTORY H H WHEREWHERE H EMPNOH EMPNO E EMPNOE EMPNO ANDAND H HIST TYPEH HIST TYPE T HIST TYPET HIST TYPE GROUPGROUP BYBY H EMPNO E ENAME H HIST TYPE T TYPE DESC H EMPNO E ENAME H HIST TYPE T TYPE DESC 通过调用下面的函数可以提高效率通过调用下面的函数可以提高效率 FUNCTIONFUNCTION LOOKUP HIST TYPE TYPLOOKUP HIST TYPE TYP ININ NUMBER NUMBER RETURNRETURN VARCHAR2VARCHAR2 ASAS TDESCTDESC VARCHAR2 30 VARCHAR2 30 CURSORCURSOR C1C1 ISIS SELECTSELECT TYPE DESCTYPE DESC FROMFROM HISTORY TYPEHISTORY TYPE WHEREWHERE HIST TYPEHIST TYPE TYP TYP BEGINBEGIN OPENOPEN C1 C1 FETCHFETCH C1C1 INTOINTO TDESC TDESC CLOSECLOSE C1 C1 RETURNRETURN NVL TDESC NVL TDESC END END FUNCTIONFUNCTION LOOKUP EMP EMPLOOKUP EMP EMP ININ NUMBER NUMBER RETURNRETURN VARCHAR2VARCHAR2 ASAS ENAMEENAME VARCHAR2 30 VARCHAR2 30 CURSORCURSOR C1C1 ISIS SELECTSELECT ENAMEENAME FROMFROM EMPEMP WHEREWHERE EMPNO EMP EMPNO EMP BEGINBEGIN OPENOPEN C1 C1 FETCHFETCH C1C1 INTOINTO ENAME ENAME CLOSECLOSE C1 C1 RETURNRETURN NVL ENAME NVL ENAME END END SELECTSELECT H EMPNO LOOKUP EMP H EMPNO H EMPNO LOOKUP EMP H EMPNO H HIST TYPE LOOKUP HIST TYPE H HIST TYPE COUNT H HIST TYPE LOOKUP HIST TYPE H HIST TYPE COUNT FROMFROM EMP HISTORYEMP HISTORY H H GROUPGROUP BYBY H EMPNOH EMPNO H HIST TYPE H HIST TYPE 译者按译者按 经常在论坛中看到如经常在论坛中看到如 能不能用一个能不能用一个 SQLSQL 写出写出 的贴子的贴子 殊不知复杂的殊不知复杂的 SQLSQL 往往牺牲了执行效率往往牺牲了执行效率 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意 义的义的 15 15 使用表的别名使用表的别名 Alias Alias 第 9 页 共 31 页 当在当在 SQLSQL 语句中连接多个表时语句中连接多个表时 请使用表的别名并把别名前缀于每个请使用表的别名并把别名前缀于每个 ColumnColumn 上上 这样一来这样一来 就就 可以减少解析的时间并减少那些由可以减少解析的时间并减少那些由 ColumnColumn 歧义引起的语法错误歧义引起的语法错误 译者注译者注 ColumnColumn 歧义指的是由于歧义指的是由于 SQLSQL 中不同的表具有相同的中不同的表具有相同的 ColumnColumn 名名 当当 SQLSQL 语句中出语句中出 现这个现这个 ColumnColumn 时时 SQL SQL 解析器无法判断这个解析器无法判断这个 ColumnColumn 的归属的归属 16 16 用用 EXISTSEXISTS 替代替代 ININ 在许多基于基础表的查询中在许多基于基础表的查询中 为了满足一个条件为了满足一个条件 往往需要对另一个表进行联接往往需要对另一个表进行联接 在这种情在这种情 况下况下 使用使用 EXISTS EXISTS 或或 NOTNOT EXISTS EXISTS 通常将提高查询的效率通常将提高查询的效率 低效低效 SELECTSELECT FROMFROM EMPEMP 基础表基础表 WHEREWHERE EMPNOEMPNO 0 0 ANDAND DEPTNODEPTNO ININ SELECT SELECT DEPTNODEPTNO FROMFROM DEPTDEPT WHEREWHERE LOCLOC MELB MELB 高效高效 SELECTSELECT FROMFROM EMPEMP 基础表基础表 WHEREWHERE EMPNOEMPNO 0 0 ANDAND EXISTSEXISTS SELECT SELECT X X FROMFROM DEPTDEPT WHEREWHERE DEPT DEPTNODEPT DEPTNO EMP DEPTNOEMP DEPTNO ANDAND LOCLOC MELB MELB 我相信绝大多数人会使用第一种格式 因为它比较容易编写 而实际上第二种格式要远比我相信绝大多数人会使用第一种格式 因为它比较容易编写 而实际上第二种格式要远比 第一种格式的效率高 在第一种格式的效率高 在 OracleOracle 中可以几乎将所有的中可以几乎将所有的 ININ 操作符子查询改写为使用操作符子查询改写为使用 EXISTSEXISTS 的子查询 的子查询 第二种格式中 子查询以第二种格式中 子查询以 select select X X 开始 开始 运用运用 EXISTSEXISTS 子句不管子查询从表中抽取什子句不管子查询从表中抽取什 么数据它只查看么数据它只查看 wherewhere 子句子句 这样优化器就不必遍历整个表而仅根据索引就可完成工作 这样优化器就不必遍历整个表而仅根据索引就可完成工作 这里假定在 这里假定在 wherewhere 语句中使用的列存在索引 语句中使用的列存在索引 相对于 相对于 ININ 子句来说 子句来说 EXISTSEXISTS 使用相连使用相连 子查询 构造起来要比子查询 构造起来要比 ININ 子查询困难一些 子查询困难一些 通过使用通过使用 EXISTEXIST OracleOracle 系统会首先检查主查询 然后运行子查询直到它找到第一个匹配系统会首先检查主查询 然后运行子查询直到它找到第一个匹配 项 这就节省了时间 项 这就节省了时间 OracleOracle 系统在执行系统在执行 ININ 子查询时 首先执行子查询 并将获得的结子查询时 首先执行子查询 并将获得的结 果列表存放在在一个加了索引的临时表中 在执行子查询之前 系统先将主查询挂起 待果列表存放在在一个加了索引的临时表中 在执行子查询之前 系统先将主查询挂起 待 子查询执行完毕 存放在临时表中以后再执行主查询 这也就是使用子查询执行完毕 存放在临时表中以后再执行主查询 这也就是使用 EXISTSEXISTS 比使用比使用 ININ 通通 常查询速度快的原因 常查询速度快的原因 17 17 用用 NOTNOT EXISTSEXISTS 替代替代 NOTNOT ININ 第 10 页 共 31 页 在子查询中在子查询中 NOT NOT ININ 子句将执行一个内部的排序和合并子句将执行一个内部的排序和合并 无论在哪种情况下无论在哪种情况下 NOT NOT ININ 都是都是 最低效的最低效的 因为它对子查询中的表执行了一个全表遍历因为它对子查询中的表执行了一个全表遍历 为了避免使用为了避免使用 NOTNOT ININ 我们可我们可 以把它改写成外连接以把它改写成外连接 Outer Outer Joins Joins 或或 NOTNOT EXISTS EXISTS 例如例如 SELECTSELECT FROMFROM EMPEMP WHEREWHERE DEPT NODEPT NO NOTNOT ININ SELECT SELECT DEPT NODEPT NO FROMFROM DEPTDEPT WHEREWHERE DEPT CAT A DEPT CAT A 为了提高效率为了提高效率 改写为改写为 外连接外连接 高效高效 SELECTSELECT FROMFROM EMPEMP A DEPTA DEPT B B WHEREWHERE A DEPT NOA DEPT NO B DEPT B DEPT ANDAND B DEPT NOB DEPT NO ISIS NULLNULL ANDAND B DEPT CAT B DEPT CAT A A NOT NOT EXISTS EXISTS 最高效最高效 SELECTSELECT FROMFROM EMPEMP E E WHEREWHERE NOTNOT EXISTSEXISTS SELECT SELECT X X FROMFROM DEPTDEPT D D WHEREWHERE D DEPT NOD DEPT NO E DEPT NOE DEPT NO ANDAND DEPT CATDEPT CAT A A 18 18 用表连接替换用表连接替换 EXISTSEXISTS 通常来说通常来说 采用表连接的方式比采用表连接的方式比 EXISTSEXISTS 更有效率更有效率 SELECTSELECT ENAMEENAME FROMFROM EMPEMP E E WHEREWHERE EXISTSEXISTS SELECT SELECT X X FROMFROM DEPTDEPT WHEREWHERE DEPT NODEPT NO E DEPT NOE DEPT NO ANDAND DEPT CATDEPT CAT A A 更高效更高效 SELECTSELECT ENAMEENAME FROMFROM DEPTDEPT D EMPD EMP E E WHEREWHERE E DEPT NOE DEPT NO D DEPT NOD DEPT NO ANDAND DEPT CATDEPT CAT A A 第 11 页 共 31 页 19 19 用用 EXISTSEXISTS 替换替换 DISTINCTDISTINCT 当提交一个包含一对多表信息当提交一个包含一对多表信息 比如部门表和雇员表比如部门表和雇员表 的查询时的查询时 避免在避免在 SELECTSELECT 子句中使用子句中使用 DISTINCT DISTINCT 一般可以考虑用一般可以考虑用 EXISTEXIST 替换替换 例如例如 低效低效 SELECTSELECT DISTINCTDISTINCT D DEPT NO D DEPT NAMED DEPT NO D DEPT NAME FROMFROM DEPTDEPT D EMPD EMP E E WHEREWHERE D DEPT NOD DEPT NO E DEPT NOE DEPT NO 高效高效 SELECTSELECT DEPT NO DEPT NAMEDEPT NO DEPT NAME FROMFROM DEPTDEPT D D WHEREWHERE EXISTSEXISTS SELECTSELECT X X FROMFROM EMPEMP E E WHEREWHERE E DEPT NOE DEPT NO D DEPT NO D DEPT NO EXISTSEXISTS 使查询更为迅速使查询更为迅速 因为因为 RDBMSRDBMS 核心模块将在子查询的条件一旦满足后核心模块将在子查询的条件一旦满足后 立刻返回结立刻返回结 果果 20 20 联接列联接列 对于有联接的列 即使最后的联接值为一个静态值 优化器是不会使用索引的 我们一起对于有联接的列 即使最后的联接值为一个静态值 优化器是不会使用索引的 我们一起 来看一个例子 假定有一个职工表 来看一个例子 假定有一个职工表 employeeemployee 对于一个职工的姓和名分成两列存放 对于一个职工的姓和名分成两列存放 FIRST NAMEFIRST NAME 和和 LAST NAMELAST NAME 现在要查询一个叫比尔 现在要查询一个叫比尔 克林顿 克林顿 BillBill ClitonCliton 的职工 的职工 下面是一个采用联接查询的下面是一个采用联接查询的 SQLSQL 语句 语句 selectselect fromfrom employssemployss wherewhere first name last namefirst name last name Beill Beill Cliton Cliton 上面这条语句完全可以查询出是否有上面这条语句完全可以查询出是否有 BillBill ClitonCliton 这个员工 但是这里需要注意 这个员工 但是这里需要注意 系统优系统优 化器对基于化器对基于 last namelast name 创建的索引没有使用 创建的索引没有使用 当采用下面这种当采用下面这种 SQLSQL 语句的编写 语句的编写 OracleOracle 系统就可以采用基于系统就可以采用基于 last namelast name 创建的索引 创建的索引 SelectSelect fromfrom employeeemployee wherewhere first namefirst name Beill Beill andand last namelast name Cliton Cliton 第 12 页 共 31 页 遇到下面这种情况又如何处理呢 如果一个变量 遇到下面这种情况又如何处理呢 如果一个变量 namename 中存放着 中存放着 BillBill ClitonCliton 这个员工这个员工 的姓名 对于这种情况我们又如何避免全程遍历 使用索引呢 可以使用一个函数 将变的姓名 对于这种情况我们又如何避免全程遍历 使用索引呢 可以使用一个函数 将变 量量 namename 中的姓和名分开就可以了 但是有一点需要注意 中的姓和名分开就可以了 但是有一点需要注意 这个函数是不能作用在索引列这个函数是不能作用在索引列 上 上 下面是下面是 SQLSQL 查询脚本 查询脚本 selectselect fromfrom employeeemployee wherewhere first namefirst name SUBSTR cliton 这里由于这里由于通配符 通配符 在搜寻词首出现 所以 在搜寻词首出现 所以 OracleOracle 系统不使用系统不使用 last namelast name 的索引 的索引 在很在很 多情况下可能无法避免这种情况 但是一定要心中有底 通配符如此使用会降低查询速度 多情况下可能无法避免这种情况 但是一定要心中有底 通配符如此使用会降低查询速度 然而然而当通配符出现在字符串其他位置时 优化器就能利用索引当通配符出现在字符串其他位置时 优化器就能利用索引 在下面的查询中索引得到 在下面的查询中索引得到 了使用 了使用 selectselect fromfrom employeeemployee wherewhere last namelast name likelike c c 22 22 使用使用 TKPROFTKPROF 工具来查询工具来查询 SQLSQL 性能状态性能状态 SQLSQL tracetrace 工具收集正在执行的工具收集正在执行的 SQLSQL 的性能状态数据并记录到一个跟踪文件中的性能状态数据并记录到一个跟踪文件中 这个跟踪这个跟踪 文件提供了许多有用的信息文件提供了许多有用的信息 例如解析次数例如解析次数 执行次数执行次数 CPU CPU 使用时间等使用时间等 这些数据将可以用这些数据将可以用 来优化你的系统来优化你的系统 设置设置 SQLSQL TRACETRACE 在会话级别在会话级别 有效有效 ALTERALTER SESSIONSESSION SETSET SQL TRACESQL TRACE TRUETRUE 设置设置 SQLSQL TRACETRACE 在整个数据库有效仿在整个数据库有效仿 你必须将你必须将 SQL TRACESQL TRACE 参数在参数在 init orainit ora 中设为中设为 TRUE TRUE USER DUMP DESTUSER DUMP DEST 参数说明了生成跟踪文件的目录参数说明了生成跟踪文件的目录 译者按译者按 这一节中这一节中 作者并没有提到作者并没有提到