第28章 SQL语句优化.pptVIP

SQL语句优化 我们说过一个设计良好的应用程序是不需要优化的，这里的“良好”很大程度上指良好的SQL语句设计，在Oracle数据库应用系统中几乎有80%的性能问题是有糟糕的SQL语句引起的。 那么如何写出“良好”的SQL语句呢，我们给出一个条款式的说明，并作简单地讨论。 对于对于规模较小的表，如果SQL语句的WHERE子句有GROUP BY 、DISTINCT或GROUP BY则对设计的列建立明确的索引。 如果SQL语句的WHERE子句在使用索引时比执行全表扫描还要耗时，则不使用索引。 在应用程序的SQL语句中不要使用相关子查询，因为随着子查询和主查询中表中行记录的增长，这种查询将极大的消耗CPU资源而尽量使用联机视图重新编写。 在SQL语句的where子句中用not exists来代替not in。 SQL语句优化 使用like运算符代替substr函数，因为如’scot%’这样的like运算符将使用索引，而substr函数则使索引无效。 如果SQL语句中频繁使用基于某种计算规则的查询并且知道所涉及的列，则创建基于函数的索引。 如果查询总是基于主从表关系的行，则对外键建立索引。 尽量减少建立索引的时间，可以将sort_area_size设置的足够大，使得建立索引的排序行为都在内存中发生，或者在系统不繁忙时创建大表的索引。 上述条款式说明的都基于减少I/O和更有效的内存使用，通过减少系统I/O和有效利用内存和CPU的使用减少系统压力，增加系统对用户的响应时间。 了解了这些基本规则后，我们介绍如何使用工具获得SQL的执行计划。 SQL语句优化 获得SQL语句的执行计划 。 SQL语句执行计划就是解释SQL语句的执行步骤，在Oracle中使用explain plan for指令来获得SQL语句的执行计划，也可以使用autotrace执行获得SQL语句的执行过程和相关的统计信息，如物理读的数据量、磁盘内排序的数据量等。 SQL语句优化 使用EXPLAIN PLAN FOR命令 在使用该指令时，必须先使用一个脚本文件utlxplan.sql来创建plan_table表来存储使用explain plan for 语句获得的SQL语句的分析结果 。 执行脚本文件utlxplan.sql。 SQL @F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql 查看表PLAN_TABLE的结构 SQLdesc PLAN_TABLE 通过explain plan for 指令分析SQL语句的执行计划。 SQL explain plan for 2 select count(*) from scott.emp; 2 TABLE ACCESS FULL EMP 1 SQL语句优化 查看表PLAN_TABLE中的SQL语句执行计划信息。 查看表PLAN_TABLE中的SQL语句执行计划信息。 SQL select id,operation,options,object_name,position 2 from plan_table; ID OPERATION OPTIONS OBJECT_NAME POSITION ------ ---------------------------------- ------------------------ ------------------------- ---------------- 0 SELECT STATEMENT 3 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL EMP 1 从上例的输出中可以看出SQL语句的执行过程，我们分析最后一行，ID说明步骤标识，OPERATION为TABLEACCESS说明该步骤的行为是访问表，OPTIONS为FULL说明，使用全表扫描访问表，OBJECT_NAME说明行为的对象为表EMP。 在分析一个SQL语句时，经常使用该指令判断是否使用适当的索引完成表的访问，从而适当的修改索引或创建索引来优化SQL语句的执行。 SQL语句优化 使用AUTOTRACE指令可以跟踪SQL语句并分析其执行 。 使用AUTOTRACE指令可以跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量，磁盘和