数据库优化之我见

a. 数据库配置
   包括sga配置：数据缓存，共享池等。
      数据缓存可以根据需要来设定缓存策略，比如keep，recyle，defaultcelve。
      共享池的库缓存与sql的缓存相关。
b. 表结构的设计
    主键外键，索引。
    纵向拓展：表分区，垂直分库
    横向拓展：表分片

c. sql优化
    访问Table的方式
    变量绑定
3.       共享SQL语句
4. 选择最有效率的表名顺序(只在基于规则的优化器中有效)
5.       WHERE子句中的连接顺序．
6.     SELECT子句中避免使用 ‘ * ‘
7.     减少访问数据库的次数
    当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算
    索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的
    次数 , 就能实际上减少ORACLE的工作量.
10.       删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

                   FROM EMP X

                   WHERE X.EMP_NO = E.EMP_NO);

11.       用TRUNCATE替代DELETE
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息.
    如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)

    而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.
    因此很少的资源被调用,执行时间也会很短.

(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
14.       用Where子句替换HAVING子句

     避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理
     需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:

     低效:

     SELECT REGION，AVG(LOG_SIZE)

     FROM LOCATION

     GROUP BY REGION

     HAVING REGION REGION != ‘SYDNEY’

     AND REGION != ‘PERTH’

     高效

     SELECT REGION，AVG(LOG_SIZE)

     FROM LOCATION

     WHERE REGION REGION != ‘SYDNEY’

     AND REGION != ‘PERTH’

     GROUP BY REGION

(译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

15.       减少对表的查询

在含有子查询的SQL语句中,要特别注意减少对表的查询.  
17.       使用表的别名(Alias)

   当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.
   这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
25.       用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个
复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找
出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多
个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的
唯一性验证.

除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型
表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提
高效率.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来

存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修
改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O .
因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

译者按:

定期的重构索引是有必要的.

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

26.       索引的操作

ORACLE对索引有两种访问模式.

索引唯一扫描 ( INDEX UNIQUE SCAN)

大多数情况下, 优化器通过WHERE子句访问INDEX.

例如:

表LODGING有两个索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER. 

SELECT *

FROM LODGING

WHERE LODGING = ‘ROSE HILL’;

   在内部 , 上述SQL将被分成两步执行, 首先 , LODGING_PK 索引将通过索
   引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方
   式 执行下一步检索.

   如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通
   过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完
   全满足查询结果.

   下面SQL只需要INDEX UNIQUE SCAN 操作.       

        SELECT LODGING

        FROM  LODGING

WHERE LODGING = ‘ROSE HILL’;

  索引范围查询(INDEX RANGE SCAN)

      适用于两种情况:

1.       基于一个范围的检索

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

例1:

      SELECT LODGING

      FROM  LODGING

WHERE LODGING LIKE ‘M%’;

WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式
查询LODGING_PK . 由于索引范围查询将返回一组值, 它的效率就
要比索引唯一扫描低一些. 

例2:

      SELECT LODGING

      FROM  LODGING

WHERE MANAGER = ‘BILL GATES’;

  这个SQL的执行分两步, LODGING$MANAGER的索引范围查询(得到所有符合
  条件记录的ROWID) 和下一步同过ROWID访问表得到LODGING列的值. 由于
  LODGING$MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描. 

  由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在
  索引范围查询后会执行一个通过ROWID访问表的操作. 

  WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT LODGING

      FROM  LODGING

WHERE MANAGER LIKE ‘％HANMAN’;

在这种情况下，ORACLE将使用全表扫描.

27.       基础表的选择

基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问).
根据优化器的不同, SQL语句中基础表的选择是不一样的.
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
32.       避免在索引列上使用计算．

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

举例:

低效：

SELECT …

FROM DEPT

WHERE SAL * 12 > 25000;

高效:

SELECT …

FROM DEPT

WHERE SAL  > 25000/12;

译者按:

这是一个非常实用的规则，请务必牢记
34.       避免在索引列上使用NOT

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

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

如果DEPTNO上有一个索引, 

高效:

   SELECT *

   FROM EMP

   WHERE DEPTNO >=4  

   低效:

   SELECT *

   FROM EMP

   WHERE DEPTNO >3

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