SQL优化手册_v1.0 - search read.pudn.com.doc

ORACLE-SQL优化手册一、SQL语句的易读性2二、ORACLE的执行优化器和执行路径21执行优化器22优化器模式23查看执行路径的方法34理解执行路径3三、索引的使用61索引基础知识62建立正确有效的索引的经验73索引无法被使用的情况74ORACLE9I索引新特性85与索引相关的数据库参数9四、使用关系运算符101ISNULL与ISNOTNULL102IN和EXISTS113NOT IN和 NOT EXISTS114 （不等于）115 及 （大于或小于操作符）126LIKE操作符12五、SQL子查询131理解子查询132使用子查询的原则133使用子查询常见问题134子查询的类型14六、常用优化方法151使用批量绑定（BULK BINDING）152Order by语句153消除对大型表行数据的顺序存取154优化包含视图的SQL语句16一、 SQL语句的易读性编写SQL语句的有很多需要注意的地方，在这里只谈SQL的易读性问题，好的SQL语句总是具有好的易读性。 SQL语句是数据库程序最主要的组成部分，SQL语句的易读性也就成为数据库程序中最重要的环节之一； 良好的语句结构带来的好的易读性，也会提高SQL的解析速度 能体现出开发人员对SQL语句性能的理解程度。 能体现出开发人员对数据库设计的理解程度，不理解数据库的设计，也写不出好的SQL语句。因此，注意SQL书写的易读性，也是编写好的SQL语句的基础，为此，建议大家养成良好的书写习惯。二、ORACLE的执行优化器和执行路径1执行优化器Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.ORACLE10版本只支持CBO方式。2优化器模式1、 optimizer_mode=first_rows相对全表扫描访问，这个优化模式更注重索引访问。当你想要一个查询以最快的速度返回结果行时，即 使它的逻辑输入输出总量比全表扫描高，也要使用这个模式在线访问系统一般都使用这个模式，因为终端用户想要尽快地看到第一页查询结果。2、optimizer_mode=all_rows这个优化模式更注重全表扫描（特别是并发全表扫描），因为在这种情况下服务器资源的开销最小。这个模式一般被用于批处理进程和数据仓库中，它们的目标都是使服务器消耗的资源最小化。3、 optimizer_mode=first_rows_n从Oracle 9i开始，又有一种新的优化模式针对某些返回小结果集的查询进行优化。其取值范围是first_rows_1, first_rows_10 和 first_rows_100，使用这些参数值可以确保Oracle能够优化这类SQL。3查看执行路径的方法1.sql_trace执行情况的性能统计、实际的查询方案 启动sql_trace a)alter session set sql_trace=true|false b)sys.dbms_system.set_sql_trace_in_session 允许在数据库中为任何已有会话设置sql_trace开和关 c)察看trace文件 tkprof tracefile outputfile explain= table= print= insert= sys= sort=2.explain plan set statement_id = test for sql语句 不执行sql语句，只显示执行计划，查看plan_table表即可3.ALTER session set optimizer_goal=RULE;4理解执行路径下面就执行路径中显示的操作做具体的说明：1.表操作1)Table Access Full（全表扫描）2)Table Access by RowID(基于RowID的访问) -RowID记录了数据行的物理存储位置；-Oracle使用索引将数据值与RowID相关联，从而与数据物理位置相关联。2Index操作:1)Index unique scana)首先通过insex unique scan操作访问列索引；b)从索引返回与相匹配的RowID值，然后利用此RowID值通过Table Access by RowID操作来查询表。2)Index range scan如果基于一个值的范围查询或者利用一个非唯一索引进行查询，则可以使用index rangescan操作对索引进行查询。由于index range scan操作需要从索引中读取多个值，所以它的效率要比index unique scan低。3)And-Equal操作，处理类型：行操作 功能：合并由多个索引返回的值的排序列表，索引的执行顺序是无关紧要的。 例如： select name,city,state from company where city=Roanoke and state =VA city ,state 都是单个索引 TABLE ACCESS BY ROWID COMPANY AND-EQUAL INDEX RANGE SCAN COMPANY CITY INDEX RANGE SCAN COMPANY STATE 在这里，两个索引的执行顺序是无关紧要的3两表连接行集操作一旦数据从表或索引返回，就可以对其进行处理了，可以对记录分组，分类，计数，锁定或者将查询的结果与其它查询的结果进行合并(union、minus、intersect)。 大多数处理记录集的操作在整个操作完成前不会向用户返回数据； 索引扫描操作和表访问操作在找到记录后立刻将它返回给用户； 在集合操作中，用户必须等待操作处理完成所有的行，才将结果集返回给用户；1）HASH JOIN操作类型： 行和集操作的组合功能：先将一个表读入内存，再利用哈希（散列）功能在第二个表里定位 连接行，通过哈希连接将表连接起来例如：HASH JOIN TABLE ACCESS FULL SALES TABLE ACCESS FULL COMPANY性能：要考虑驱动表问题，尽量选择小表做驱动表，先读入内存。在内存中比较两个表，在散列连接中，扫描第一个表，并且数据库对其数据应用散列函数，为连接表做准备，然后，读取第二个表的值（一般为TABLE ACCESS FULL），散列函数将第二个表与第一个表进行比较，匹配的行将返回给用户。2）MERGE JOIN操作类型：集功能：先排序每个表，再将排序后结果连接例如：MERGE JOIN SORT JOIN TABLE ACCESS FULL SALES TABLE ACCESS FULL COMPANY性能：连接的两个输入将分别处理、分类和连接。如果两个表 都很小或者都很大则应该使用merge join，注意所有记录处理完后再返回给用户。3）NESTED LOOPS操作类型：行功能：当某个连接列被索引过 有驱动表问题性能：循环连接两张表，即从一张表中检索记录，并对每个检索出记录，执行对第二个表的访问，对第二个表的访问是通过一个基于索引的访问执行的。用于连接尺寸不同的表，特别是一张大，一张小。小表可以做为驱动表使用。 一旦得到行就可以返回用户，适合联机查询。三、索引的使用1索引基础知识索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据。正确的索引可以加快查询速度、减少I/O操作、消除磁盘排序。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。Oracle存储索引的数据结构是B*树，位图索引也是如此，只不过是叶子节点不同B*数索引。索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。在Oracle中，索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引，interMedia全文索引等。1）b*tree index：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。2）反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。反向索引的一个缺点就是不能在所有使用常规索引的地方使用。3）降序索引：8i中新出现的索引类型，针对逆向排序的查询。4）位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。还要注意位图索引不是为OLTP数据库设计的，不应该在OLTP数据库中大量的使用它，尤其是对那些有更新操作的表 ，有可能会造成位图索引空间的不可控制。 如果要使用位图索引，初始化参数STAR_TRANSFORMATION_ENABLED应该设置为TRUE. 优化模式应该是CBO。对于数据仓库的环境中，总是应该考虑使用CBO（COST-BASEDOPTIMIZER）。 位图索引应该建立在每一个事实表的外键列上。(这只是一个一般的规则.)5)_函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function（column）=value这种类型的语句中起作用。基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件 必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。 必须使用基于成本的优化器，基于规则的优化器将被忽略。 必须设置以下两个系统参数：QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED6）全文索引用于模糊检索技术。2建立正确有效的索引的经验 经常一起使用多个字段检索记录，组合索引比单索引更有效； 把最常用的列放在最前面； 对于频繁插入和更新表，索引越多系统CPU，I/O负担就越重，建议每张表不超过5个索引； 创建索引时应明确指定表空间； 对大表可以采用并行创建索引，在并行创建索引时，存储参数被每个查询服务器进程分别使用，例如：initial为1M，并行度为8，则创建索引期间至少要消耗8M空间； 大表建立索引，考虑用nologging创建索引，节省重做日志文件的空间，缩短创建索引的时间; 在用SQL*Loader或import工具插入或装载数据后，建立索引比较有效； 重建现存的索引的当前时刻不会影响查询，重建索引可以删除额外的数据块； 分区表尽量避免采用全局索引，要使用分区索引来提高性能。 可以考虑定期重建索引。 3索引无法被使用的情况1）使用不等于操作符（）注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。如： 字段名 aa 可以改成 字段名 aa OR 字段名 alter index test_pk monitoring usage;b)查询v$object_usage(可以看到正监视中)SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;5与索引相关的数据库参数1、optimizer_index_cost_adj这个参数可用来调整“基于代价的优化”相对于全表扫描访问而言，更加倾向于索引访问的程度。这个值越小，“基于代价的优化”就越有可能使用一个可用的索引。2、 optimizer_index_caching这个参数告诉Oracle你的索引在内存的数据缓冲区中的可能性有多大。对这个参数的设置将会影响到“基于代价的优化” 做出的对一个表连接（嵌套循环）使用索引还是使用全表扫描选择。3、 db_file_multiblock_read_count当把这个值设置得比较大时（使用更大的服务器），“基于代价的优化”识别出分散的（多块）读操作的代价或许比识别顺序读操作的代价更小一些。这就使得“基于代价的优化”更加倾向于全表扫描。但是从Oracle 9.2版本开始，情况不再是这样了。当计算系统统计表时，它包含了“多块读操作记数”（MBRC），这个数字决定了全表扫描的成本。Oracle 10g则更进一步，加入了一些“系统默认值”，这些默认值是非常不合适的。对于Oracle 9.2版本而言，请注意Metalink上的149560.1。4、 parallel_automatic_tuning当该参数设置为“开启”时，对于含有许多CPU的Oracle服务器，全表扫描并发执行。因为并发全表扫描的速度可以非常快，所以“基于代价的优化”对于索引访问开销很大，因此更加倾向于使用全表扫描。5、 hash_area_size（假如不使用pga_aggregate_target的话）这个参数设置“基于代价的优化”相对于使用嵌套循环和排序合并表连接来说，更倾向于使用哈希连接的程度。6、sort_area_size（只当不使用参数pga_aggregate_target时）这个参数影响了“基于代价的优化”做出的执行索引 访问还是执行对结果集的排序的决定。这个参数值越高，则在内存中执行排序（比使用临时表空间快上千倍）的可能性就越大，同时“基于代价的优化”相对于使用 预先排序好的索引检索，更倾向于使用直接排序。四、使用关系运算符1ISNULL与ISNOTNULL判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。 推荐方案： 用其它相同功能的操作运算代替，如 a is not null 改为 a0 或a等。 2IN和EXISTSEXISTS要远比IN的效率高。里面关系到fulltablescan和rangescan。几乎将所有的IN操作符子查询应该改写为使用EXISTS的子查询。用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：ORACLE 试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用 IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。3NOT IN和 NOT EXISTS此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替4 （不等于）不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。推荐方案：用其它相同功能的操作运算代替，如a0 改为 a0 or a0 a 改为 a5 及 2与A=3的效果就有很大的区别了，因为 A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。6LIKE操作符LIKE 操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE %5400% 这种查询不会引用索引，而LIKE X5400%则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE %5400% 这个条件会产生全表扫描，如果改成YY_BH LIKE