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] 唯一索引可实现唯一约束
[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与5同级,4先执行,再执行5;
执行5要执行6,6先执行,再执行5,4与5执行完,才能执行3,2与3执行完,才能执行1,最后才是0。
4.强制索引
强制走索引,语法:/*+ INDEX(表别名 索引名称) */
5.常用SQL
1)索引创建与删除
创建:create index [索引名] ON [表名] (索引列名);
删除:drop index [索引名] ;
删除:drop index [索引名] ;
2)主键创建与删除
创建:alter table [表名] add constraint [主键名] primary key (主键列名) ;
删除:alter table [表名] drop constraint [主键名];
删除:alter table [表名] drop constraint [主键名];
3)字段新增、删除与修改
新增:alter table [表名] add ([字段名] [数据类型] default [默认值] null/not null);
删除:alter table [表名] drop ([字段名]);
修改:alter table [表名] modify ([字段名] [数据类型] 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#)