ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL 执行计划

SQL 执行计划

原创 Linux操作系统 作者:Devean 时间:2011-07-09 11:00:08 0 删除 编辑

======================================================================================

       SQL优化的一般原则

=================================

              1.SQL查询语句中,查询所有的列时尽量不要使用"*"号。

              -------------------------------------------------------------------------------------

因为如查使用了*号查询所的列,那么数据库须要额外的去查询数据字典,把*号转换为表的所有列,再将查询结果返回给用户。

                     ------------------------------------------------------------------------------------------------------------------------------

              2.编写SQL时使用相同的编码风格,尽可能的减少解析的次数。

              -------------------------------------------------------------------------------------

              3.如果是删除表中所的记录,可以使用TRUNCATE语句代替DELETE语句。提高执行的效率。

              -----------------------------------------------------------------------------------------------------------------------------

              4.在确保业务逻辑的前提下及时COMMIT提交事务,释放事务占用的资源。

              -----------------------------------------------------------------------------------------------------------------------------

              5.EXISTS/IN

              ------------------

                     1.EXISTS:

                            先执行主查询,再运行子查询直到找到第一个匹配项。

                            ---------------------------------------------------------------------------------------------------------

                     2.IN:

                            先对子查询中的表做全表扫描,获得查询结果,然后再执行主查询。

                            ---------------------------------------------------------------------------------------------------------

                     总结:EXISTS适合外表小而内表大的情况;IN适合外表大而内表小的情况。

                          另外,NOT IN操作符也是不建议使用的,因为它不能使用索引,建议使用

                          NOT EXISTS或外连接替代NOT IN

                          -------------------------------------------------------------------------------------------------------

              6.尽量用EXISTS代替DISTINCT

              --------------------------------------------

                            :

                               低效的SQL:

                               -----------------

                                   select distinct e.deptno,d.dname from emp e,dept d

                                                 where e.deptno=d.deptno;

                                   -------------------------------------------------------------

                               高效的SQL:

                               ----------------

                                   select deptno,dname from dept d

                                                 where exists(

                                                 select 1 from emp e where e.deptno=d.deptno);

                                   ---------------------------------------------------------------------

              7.在非必须时,使用UNION ALL代替UNION

              ------------------------------------------------------------

                     UNION操作符在运行时先取出两条SQL结果集,并对结果集进行排序运算,去掉重复记录,

                     再返回最终结果。而UNIOL ALL操作只是简单的将两个结果集合并后直接返回,没有排序

                     操作。所以,在非必须的情况下,可以使用UNION ALL省去这一部分资源消耗。

                     --------------------------------------------------------------------------------------------------------------------------

              8.在保证功能的前提下,减少对表的查询次数

              -----------------------------------------------------------

                     :

                        低效的SQL:

                        ----------------

                            select ename,job,sal,deptno from emp

                                   where

                                   job=(select job from emp where ename='SCOTT')

                                   and

                                   deptno=(select deptno from emp where ename='SCOTT');

                            -----------------------------------------------------------------------------

                        高效的SQL:

                        --------------

                            select ename,job,sal,deptno from emp

                                   where (job,deptno)=

                                   (select job,deptno from emp where ename='SCOTT');

                            -----------------------------------------------------------------------

              9.尽量使用表的别名,并在列前标注来自哪个表

              ------------------------------------------------------------------------------

              10.不要使用HAVING子句去实现WHERE子句的功能

              ------------------------------------------------------------------------------

                     在使用分组函数时,最好在分组前用WHERE子句过滤掉尽可能多的记录。   

                     ------------------------------------------------------------------------------------------------------

                            :

                               低效的SQL:

                               ----------------

                                   select job,avg(sal) from emp

                                          group by job

                                          having job <> 'PERSIDENT';

                                   ------------------------------------------

                               高效的SQL:

                               -----------------

                                   select job,avg(sal) from emp

                                          where job <> 'PERSIDENT';

                                          group by job;

                                   ------------------------------------------

       在表连接中的优化原则

============================

              1.识别驱动表,并将数据量最小的表作为驱动表                 

              -------------------------------------------------------------

                     驱动表是指在SQL语句中最先被访问的表,通常是以全表扫描的方式访问。Oracle会检查语句中

                     每个连接表的大小和索引状态,选择开销最低的方式去执行。          

                     -------------------------------------------------------------------------------------------------------------------------------

              2.如果是多个连接条件的查询,将过滤掉最多记录的条件放在最后面。

              -------------------------------------------------------------------------------------------------------------------------------------

       合理使用索引

============================

              1.全表扫描

              -------------------------------

                     可能引起全表扫描的操作:

                     ----------------------------------

                            1.所查询的表没有索引

                            -----------------------------------------------------------------------------

                            2.没有查询条件(WHERE子句)

                            -----------------------------------------------------------------------------

                            3.查询条件中的列没有索引

                            -----------------------------------------------------------------------------

                            4.查询条件中,对索引列使用了函数或者算数表达式

                                   :UPPER,NOT IN,IS NULL,%等。

                            -----------------------------------------------------------------------------

              2.尽量避免使用LIKE操作符

              ---------------------------------------

                     引起全表扫描的LIKE查询:LIKE '%SALES%'

                     -----------------------------------------------------------

                     引用了范围索引的LIKE查询:LIKE 'SALES%'

                     -----------------------------------------------------------

              3.WHERE子句中避免使用IS NULLIS NOT NULL<>,!=,否则引起全表扫描。

              --------------------------------------------------------------------------------------------------------------

              4.避免对在表查询中的列使用单行函数或算数表达式

              ---------------------------------------------------------------------

单行函数对于数据类型转换是常用和实用的功能,但是用在SQL语句中,如果索引不是基于函数的,当在WHERE子句中对索引列使用函数时,索引不再起作用,所以应尽量避免,或者将索引建在函数上。

                     -------------------------------------------------------------------------------------------------------------------------------

                     :

                            1.birthday列上上索引,则将:

                            --------------------------------------

                                   where trunc(birthday,'YEAR') = '1988';

                                   ------------------------------------------------

                                   优化处理为:

                                   ---------------

                                   where birthday >= '1-1 -1988'

                                   and

                                         birthday <= '31-12-1988';

                                   -----------------------------------------------

                            2. 避免在索引列上使用计算,否则也会导致索引失败而进行全表扫描。  

                            ------------------------------------------------------------------------------------------------

                                   where birthday + 30 =sysdate;

                                   --------------------------------------

                                   优化处理为:

                                   ----------------

                                   where birthday = sysdate - 30;

                                   --------------------------------------

                            3.对于连接多个列的操作符||,也会导致索引失效。

                            -------------------------------------------------------------------

                                   where studentID || classID = '123456';

                                   ---------------------------------------------

                                   优化处理为:

                                   -----------

                                   where studdentID = '123' and classID = '456';

                                   ---------------------------------------------

                            4.进行隐式数据类型转换时,也会使索引失效

                            --------------------------------------------------------------

                                   where ProductID = 123456;

                                   --------------------------------------

                                   优化处理为:

                                   -----------

                                   where ProductID = '123456';

                                   --------------------------------------

              5.对于ORDER BY语句中的列避免使用函数或算数表达式                

              ------------------------------------------------------------------------------

6.总结:任何对列的操作,如函数,算数表达式等,都会导致全表扫描,所以查询时尽量将操作移动到等号右边。       

              ------------------------------------------------------------------------------------------------------------------------------------

       Oracle优化器和执行计划

================================

              SQL语句被Oracel Server收到后,从分析到执行的步骤如下:

              -------------------------------------------------------------------------------

                     1.检查SQL语句的语法是否正确

                     -----------------------------------------------------------------------------

                     2.核实SQL中的表名和列名是否正确

                     -----------------------------------------------------------------------------

                     3.检查概要(Profile),确定SQL语句是否已经存在执行计划。

                     -----------------------------------------------------------------------------

                     4.如果没有,则生成执行计划,使用基于成本的优化规则(CBO,Cost-Based Optimizer),

                       根据数据字典中的统计数据来决定最终的执行计划。

                     ---------------------------------------------------------------------------------------------------------------

                     5.基于执行计划,为SQL语句生成二进制执行代码

                     -----------------------------------------------------------------------------

                     6.执行二进制代码

                     -----------------------------------------------------------------------------

       AutoTrace之查看执行计划

===============================

              1.设置跟踪SQL

              -------------------------------

                     set autotrace on/off/traceonly [explain|statistics]

                     ----------------------------------------------------------

              2.创建PLAN_TABLE和角色PLUSTRACE

              -------------------------------

                     1.创建PLAN_TABLE

                     ---------------------------------

                            SQL>@ ?/rdbms/admin/utlxplan.sql

                            ---------------------------------------------

                     2.创建PLUSTRACE角色

                     ----------------------------------

                            SQL>create role plustrace;

                            ---------------------------------------------------------------

                            SQL>grant select on v_$sesstat  to plustrace;

                            SQL>grant select on v_$statname to plustrace;

                            SQL>grant select on v_$mystat   to plustrace;

                            --------------------------------------------------------------

                            SQL>grant plustrace to dba with admin option;

                            --------------------------------------------------------------

                     3.将角色PLUSTRACE赋给用户

                     -------------------------------------------

                            SQL>grant plustrace to scott;

                            SQL>alter user scott default role connect,resource,plustrace;

                            -------------------------------------------------------------------------

              3.查看执行计划

              ----------------------------

                            SQL>conn scott/lisheng

                            SQL>set autotrace on;

                            ---------------------------------------------

       单表访问路径

================================

              1.从物理层面看,一次读取的最小单位是Oracle数据块,一次读取得最大值由操作系统的I/O最大值

                multiblock参数共同决定。

              ------------------------------------------------------------------------------------------------------------------------------------

              2.从逻辑上来看,Oracle通过以下几种方式访问数据表:

              ------------------------------------------------------------------------

                     1.Full Table Scan

                     -----------------------------------------

                            全表扫描模式下会读取表中的所有数据行,直到表的高水位线。

                            (High Water Mark,HWM.即表示曾经写入过的最后那个数据块)

如果曾经使用DELETE语句删除表中的全部数据,HWM不会清除,全表扫描仍然会扫描到HWM的位置,如果使用TRUNCATE删除数据,HWM会回退到表的初始位置。

                            -------------------------------------------------------------------------------------------------------------------------

                     2.ROWID Lookup

                     -----------------------------------------

                     3.Index Lookup

                     -----------------------------------------

                            1.索引扫描由两步完成,分别为:

                            ---------------------------------------------------

                                   1.扫描索引得到对应的ROWID

                                   ---------------------------------------------------------------------------

                                   2.通过找到的ROWID从表中读出具体的数据

                                   ---------------------------------------------------------------------------

                                          注意:这两个步骤中的每一个都是单独的一次I/O

                                          ---------------------------------------------------------------------

                            2.索引扫描的五种方式:

                            ---------------------------------

                                   1.INDEX UNIQUE SCAN(索引唯一扫描)

                                   -----------------------------------------------------

                                          唯一索引意味着返回单个ROWID。如果唯一索引是组合索引,则至少要有该组合

                                          索引的引导列参与到查询中。但是否是索引唯一扫描,取决于是否返回单条记录

                                          -------------------------------------------------------------------------------------------------------------

                                   2.INDEX RANGE  SCAN(索引范围扫描)             

                                   ------------------------------------------------------

                                          当使用索引存取多行数据时      

                                          :

                                              SQL>select * from dept where deptno>10;  

                                          ----------------------------------------------------------------------

                                   3.INDEX FULL   SCAN(索引全局扫描)

                                   --------------------------------------------------------

FTS类似,当获取数据量占整个表的数据量比较大时,而且在WHERE条件中使用索引查询,CBO判断全索引扫描比全表扫描获得数据更有效,将进行全索引扫描,并且要保证此时查询出的数据必须从索引中可以获取到。因为索引是排序的,所

                                          以查询的结果集也是排序的。

                                          -------------------------------------------------------------------------------------------------------------

                                          :

                                             SQL>select empno from emp;       

                                          ----------------------------------------------------------------------

                                   4.INDEX FAST FULL SCAN(索引快速全局扫描)

                                   --------------------------------------------------------------

                                          扫描索引中的所有数据块,与INDEX FULL SCAN类似,但不同点是它对查询结果

                                          不排序,目的是利用多块或者并行读入,获得最大吞吐量,从而缩短执行时间。   

                                          当对有索引的大表作COUNT(*)操作时,经常发生。   

                                          -------------------------------------------------------------------------------------------------------------

                                   5.INDEX SKIP   SCAN(索引跳跃扫描)

                                   -------------------------------------------------------------

       JOIN AND ROW SOURCE

==============================

              Oracle的官方文档中,用来JOIN的表被称作ROW SOURCE

              --------------------------------------------------------------------------------------

              以下讨论以等值连接为主展开:

              ------------------------------------------

                     两个ROW SOURCE之间有三种典型的JOIN方式:

                     --------------------------------------------------------------------

                            1.Nested loop Joins(嵌套循环连接,NL)

                            --------------------------------------------------------------

                                   嵌套循环连接的方式是确定一个驱动表为Outer Table,另一个表作为Inner Table,

                                   驱动表中的每一行与Inner表中的相应记录做连接。

                                   -------------------------------------------------------------------------------------------------------------

                                   从嵌套循环的内部连接过程来看,需要用ROW SOURCE1中的每一行,去探查(匹配)

ROW SOURCE2中的所有行,所以要求ROW SOURCE1尽可能的小与高效访问ROW SOURCE2,它们是影响连接效率的关键,这样做的目的是使连接操作产生最少的物理I/O次数。

                                   ------------------------------------------------------------------------------------------------------------------

                            2.Hash Joins(哈希连接,HJ)

                            ---------------------------------

                                   哈希连接一般用于连接大数据表。

                                   ----------------------------------------------------------------------------

                                   这种连接是在oracle 7.3以后引入的,从理论上来说比NLSMJ更高效,而且只用在CBO

                                   优化器中。较小的row source被用来构建hash tablebitmap,第2row source被用

来被hansed,并与第一个row source生成的hash table进行匹配,以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法,来检查在hash table中是否有匹配的行。特别的,当hash table比较大而不能全部容纳在内存中时,这种查找方法更为有用。这种

                                   连接方法也有NL连接中所谓的驱动表的概念,被构建为hash tablebitmap的表为驱动

                                   表,当被构建的hash tablebitmap能被容纳在内存中时,这种连接方式的效率极高。

                                   -------------------------------------------------------------------------------------------------------------------

                            3.Sort Merge Joins(排序合并连接,SMJ)

                            ------------------------------------------------------

SORT MERGE JOIN用来连接两个ROW SOURCE,大多数情况下HASH JOIN的性能要优于SORTMERGE JOIN,但如果是两个ROW SOURCE已排好序,或者不需要排序的情况下,SORT MERGE JOIN的性能要好于HASH JOIN。一旦SORT MERGE JOIN中包含了比较慢的访问方式,如全表扫描,这种连接方式的效率会非常低。

                                   ------------------------------------------------------------------------------------------------------------------

              其它连接方式的原理类似于等值连接。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25963200/viewspace-701744/,如需转载,请注明出处,否则将追究法律责任。

上一篇: SQL 优化原理
下一篇: 转载ERP维护经验
请登录后发表评论 登录
全部评论

注册时间:2011-06-20

  • 博文量
    21
  • 访问量
    16164