Oracle-性能优化（二）

Oracle性能优化：1.优化索引  2.升级硬件 3.批量操作

1.索引

1）索引：索引就是为了快速查找表中记录所创建的一种特殊数据结构（类似书的目录），索引块中包含字段的值和指向对应行的地址。

2）特征：

        [1] 高度较低（可快速定位）,表查询差异小

        [2] 存储列值（可优化聚合）, 多列索引避免回表

        [3] 有序（可优化排序）

3）创建与删除

    [1] 创建：create index 索引名 on 表明（字段1，字段2，……）

 create index ind_tempcode on temp_1(tempcode);

    如下图所示，索引创建成功：

  

      [2] 删除：drop index 索引名;

drop index ind_tempcode;

     如下图所示，索引删除成功：

   
4）用途

     [1] 快速定位，避免排序

     [2] 存储列值，可进行列值的聚合，如sum，avg。

     [3] 覆盖索引可直接返回结果，无需回表扫描数据
     [4] 唯一索引可实现唯一约束

5）成本

      当表中插入、删除记录和更新索引字段的记录时，数据库就会自动维护到索引中

2.规范

1）查询条件尽量使用索引

     使用索引将极大地提升查询效率。

2）不要在索引列上使用函数或运算

     会对表中每条记录的对应字段进行计算，其计算量会是非常大的；会导致索引失效

3）避免隐式转换

     [1] where条件中等号两边数据类型要一致；

     [2] 表连接时，等号两边数据类型也要一致；

     否则会导致索引失效

4）慎用union/intersect/minus集合操作

     [1]这三类集合都会去除重复值，会进行排序操作；

     [2]对于union,优先考虑是否可以用union all代替；

5）将where条件放到SQL的最里层

     尽可能将where条件放到SQL最里层，以便在SQL执行之初，将不符合的数据过滤掉。

6）有意识的减少排序操作

     排序是SQL执行过程中资源开销最大的一项操作，严重影响语句执行效率，所以要坚决去掉没有必要的排序开销，或者借用索引来避免排序。

7）在查询中尽量避免使用 *，应使用确定的字段名代替星号

     通过指定字段，可以减少不必要字段的查询，减少不必要的资源开销，提高执行效率。尤其是表中包含LOB字段类型时，效率提升更明显。

8）谨慎使用标量子查询

     当查询结果集不大时，可以使用标量子查询；当查询结果集很大时，尽量使用外连接代替标量子查询。

9）将范围查询条件转为多个等于查询条件

     索引的等值查询比范围查询效率更高。

10）优先使用等于，合理使用like，尽量避免使用不等于

     [1] 使用等于时，是可以借助索引提升性能的；

     [2] 使用like时，尽量避免在左边使用%，这样还可以用到索引；

     [3] 使用不等于时，不走索引；

11）理解需求对SQL进行极限优化

        select count(*) from 表名;

     改造为

        select count(*) from 表名 where rownum=1;

     的效率会更高。

12）尽量使用批量操作

     每次更新一条记录，更新一万次；和一次更新一万条记录，效率相差非常明显。

3.执行计划

      选中SQL，按下F5，即可打开执行计划，如下图所示：

执行顺序规则：

        从上往下，遇到平级就停，上面先执行，下面后执行。同级的上面先执行，然后下面执行；

        不同级就一层一层往上执行。

        以上执行计划的执行顺序是 2 4 6 5 3 1 0
解析：

        执行0 要先执行1，执行1 要先执行2、3；

        2与3同级，先执行2，在执行3；

        执行3要执行4与5；
        4与5同级，4先执行，再执行5；
        执行5要执行6,6先执行，再执行5，4与5执行完，才能执行3,2与3执行完，才能执行1，最后才是0。

4.强制索引

         强制走索引，语法：/*+ INDEX(表别名 索引名称) */

5.常用SQL

     1）索引创建与删除

             创建：create index [索引名] ON [表名] (索引列名);
             删除：drop index [索引名] ;

     2）主键创建与删除

             创建：alter table [表名] add constraint [主键名] primary key (主键列名) ;
             删除：alter table [表名] drop constraint [主键名];

     3）字段新增、删除与修改

             新增：alter table [表名] add ([字段名] [数据类型] default [默认值] null/not null);
             删除：alter table [表名] drop ([字段名]);
             修改：alter table [表名] modify ([字段名] [数据类型] default [默认值] null/not null);

     4）表分析

             analyze table [表名] compute statistics;

     5）查询锁表与解锁

             查询： 

select object_name, machine, s.sid, s.serial#  from v$locked_object l, dba_objects o, v$session s where l.object_id　 = 　o.object_id   and l.session_id = s.sid;

             解锁： alter system kill session '670,25290'; (其中670，25290分别是上面查询出的sid,serial#)