SQL性能优化系列（二）

20、表连接替换EXISTS

表连接比EXISTS效率更高

SELECT ENAME

FROM EMP E

WHERE EXISTS (SELECT ‘X'

FROM DEPT

WHERE DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A');


(更高效)

SELECT ENAME

FROM DEPT D,EMP E

WHERE E.DEPT_NO = D.DEPT_NO

AND DEPT_CAT = ‘A' ;

 

21、用EXISTS替换DISTINCT

提交一对多表信息的查询时，避免使用DISTINCT，使用EXISTS替换

SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X'

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

 

22、识别低效执行的SQL语句

下列SQL脚本找出低效SQL：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY 4 DESC;

 

 

23、是用TKPROF工具查询SQL性能状态

收集正在执行的SQL性能状态数据并记录到一个跟踪文件中，该文件提供相关信息：解析次数、执行次数、CPU使用时间等

设置sql trace会话级生效：

alter session set sql_trace true

 

24、用explain plan来分析SQL语句

一种分析SQL语句的工具，甚至可以在不执行SQL情况下分析语句，分析结果可得知oracle如何连接表、如何扫描表、使用到的索引名

按照从里到外、从上到下次序解读分析的结果，explain plan分析的结果是用缩进格式排列的，最内部的操作将被首先解读，如果两个操作处于同一层中，带有最小操作号的将被首先执行 

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/

SQL> /

14 rows selected.

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)


Statistics

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

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed
 

通过以上步骤，得出实际的执行步骤是：

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

25、索引提高效率

oracle使用了一个复杂的自平衡B-tree结构，索引查询比全表扫描要快，当oracle找出执行查询和update语句的最佳路径时，oracle优化器将使用索引，同样在联接多个表时用索引也可提高效率，另一个使用索引的好处，其提供了主键的唯一性验证。

除了那些LONG 或者LONG RAW数据类型，可以索引几乎所有的列，通常大型表中使用索引特别有效。当然，在扫描小表时，使用索引同样提高效率

索引虽然能带来查询效率，但其也有代价，那就是索引表空间

存储也需要定期维护，记录的增删改也会导致索引本身的修改，这意味着每条记录的insert、delete、update将为此多出4、5次的磁盘I/O，因为索引需要额外存储和处理，所以不必要的索引反而会降低查询效率

有必要定期的重建索引 alter index <indexname> rebuild <tablespacename>

 

26 索引的操作    索引的两种访问模式：

索引唯一扫描（index unique scan）

大多数情况下，优化器通过where子句访问index

select * from lodging

where lodging='rose hill';        //表lodging在lodging列上有唯一索引lodging_pk和manager列上非唯一性索引lodging_manager

上述SQL分成2步执行，首先，lodging_pk索引将通过索引唯一扫描的方式被访问，获得相应ROWID，再通过ROWID访问表的方式执行下一步检索

如果被检索返回的列包含在INDEX列中，ORACLE将不执行第二步处理（通过ROWID访问表），因为检索数据保存在索引中，仅访问索引就可完全满足查询结果

select lodging from lodging

where lodging='rose hill'      //该SQL只需要index uniquescan操作，检索数据就包含在索引列中

 

索引范围查询（index range scan）

使用2种情况：

1、基于一个范围的检索

2、基于非唯一性索引的检索

select lodging

from lodging

where lodging like 'M%';    //范围的检索，返回一组值，效率比唯一索引低

 

select lodging

from lodging

where manager='BILL GATES'    //分两步执行，第一步，根据lodging_manager非唯一性索引查询，得到所有符合条件记录ROWID，第二步通过ROWID访问表得到lodging列的值，由于其是非唯一性索引，数据库不能对其执行索引唯一扫描

由于SQL返回lodging列，而它补存在于lodging_manager索引中，所以索引范围查询后会执行一个通过ROWID访问表的操作

 

select lodging

from lodging

where manager like '%HANMAN';   //where子句中，如果索引列对应的值第一个字符由通配符开始，索引将不被采用，这里将采用全表扫描

 

27、基础表的选择

基础表即最先被访问的表（通常以全表扫描的方式被访问），根据优化器的不同，基础表的选择也不同。

若是CBO，优化器则检查SQL语句中每个表的物理大小、索引状态，然后花费最低的执行路径。

若是RBO，且所有的连接条件都有索引对应，则基础表就是FROM子句最后面那个表。

select a.name,b.manager

from worker a, lodging b

where a.lodging=b.lodging    //由于loding表loding列上有一个索引，而worker表中没有相比较的索引，lodging表将被作为查询中的基础表

 

28、多个平等的索引

当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录.

在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有

当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的.

如果不同表中两个想同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用. FROM子句中最后的表的索引将有最高的优先级.

如果相同表中两个想同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME,

FROM EMP

WHERE DEPT_NO = 20

AND EMP_CAT = ‘A';

这里,DEPTNO索引将被最先检索,然后同EMP_CAT索引检索出的记录进行合并. 执行路径如下:

TABLE ACCESS BY ROWID ON EMP

AND-EQUAL

INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

 

29. 等式比较和范围比较

当WHERE子句中有索引列, ORACLE不能合并它们,ORACLE将用范围比较.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME

FROM EMP

WHERE DEPTNO > 20

AND EMP_CAT = ‘A';


这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较. 执行路径如下:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON CAT_IDX



30. 不明确的索引等级

当ORACLE无法判断索引的等级高低差别,优化器将只使用一个索引,它就是在WHERE子句中被列在最前面的.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME

FROM EMP

WHERE DEPTNO > 20

AND EMP_CAT > ‘A';

这里, ORACLE只用到了DEPT_NO索引. 执行路径如下:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

译者按:

我们来试一下以下这种情况:

SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';

INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE

EMPTYPE NONUNIQUE

SQL> select * from emp where empno >= 2 and emp_type = 'A' ;

no rows selected

Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)

虽然EMPNO是唯一性索引,但是由于它所做的是范围比较, 等级要比非唯一性索引的等式比较低! 

 

 

31. 强制索引失效

如果两个或以上索引具有相同的等级,你可以强制命令ORACLE优化器使用其中的一个(通过它,检索出的记录数量少) .

举例:

SELECT ENAME

FROM EMP

WHERE EMPNO = 7935

AND DEPTNO + 0 = 10 /*DEPTNO上的索引将失效*/

AND EMP_TYPE || ‘' = ‘A' /*EMP_TYPE上的索引将失效*/

这是一种相当直接的提高查询效率的办法. 但是你必须谨慎考虑这种策略,一般来说,只有在你希望单独优化几个SQL时才能采用它.

这里有一个例子关于何时采用这种策略,

假设在EMP表的EMP_TYPE列上有一个非唯一性的索引而EMP_CLASS上没有索引.

SELECT ENAME

FROM EMP

WHERE EMP_TYPE = ‘A'

AND EMP_CLASS = ‘X';

优化器会注意到EMP_TYPE上的索引并使用它. 这是目前唯一的选择. 如果,一段时间以后, 另一个非唯一性建立在EMP_CLASS上,优化器必须对两个索引进行选择,在通常情况下,优化器将使用两个索引并在他们的结果集合上执行排序及合并. 然而,如果其中一个索引（EMP_TYPE）接近于唯一性而另一个索引（EMP_CLASS）上有几千个重复的值. 排序及合并就会成为一种不必要的负担. 在这种情况下,你希望使优化器屏蔽掉EMP_CLASS索引.

用下面的方案就可以解决问题.

SELECT ENAME

FROM EMP

WHERE EMP_TYPE = ‘A'

AND EMP_CLASS||'' = ‘X';



32. 避免在索引列上使用计算．

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

举例:

低效：

SELECT …

FROM DEPT

WHERE SAL * 12 > 25000;

高效:

SELECT …

FROM DEPT

WHERE SAL > 25000/12;

译者按:

这是一个非常实用的规则，请务必牢记

33. 自动选择索引

如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．

在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．

举例:

SELECT ENAME

FROM EMP

WHERE EMPNO = 2326

AND DEPTNO = 20 ;

这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．

TABLE ACCESS BY ROWID ON EMP

INDEX UNIQUE SCAN ON EMP_NO_IDX



34. 避免在索引列上使用NOT

通常，　我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的

影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

举例:

低效: (这里,不使用索引)

SELECT …

FROM DEPT

WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引)

SELECT …

FROM DEPT

WHERE DEPT_CODE > 0;

需要注意的是,在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符.

NOT > to <=

NOT >= to <

NOT < to >=

NOT <= to >

译者按:

在这个例子中,作者犯了一些错误. 例子中的低效率SQL是不能被执行的.

我做了一些测试:

SQL> select * from emp where NOT empno > 1;

no rows selected

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)

SQL> select * from emp where empno <= 1;

no rows selected

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)

两者的效率完全一样，也许这符合作者关于” 在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符” 的观点．



35. 用>=替代>

如果DEPTNO上有一个索引,

高效:

SELECT *

FROM EMP

WHERE DEPTNO >=4

低效:

SELECT *

FROM EMP

WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.