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 NULL，IS 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以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO

优化器中。较小的row source被用来构建hash table与bitmap，第2个row source被用

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

连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动

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

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

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

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

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