• 售前

  • 售后

热门帖子
入门百科

索引在Oracle中的应用深入分析

[复制链接]
天蝎孤星等 显示全部楼层 发表于 2021-10-26 14:07:21 |阅读模式 打印 上一主题 下一主题
索引是提高数据查询最有效的方法,也是最难全面把握的技能,因为正确的索引大概使效率提高10000倍,而无效的索引大概是浪费了数据库空间,乃至大大低落查询性能。
一.索引的管理成本
1、 存储索引的磁盘空间
2、 实验数据修改操纵(INSERT、UPDATE、DELETE)产生的索引维护
3、 在数据处理时所需额外的回退空间。
二.现实数据修改测试:
一个表有字段A、B、C,同时进行插入10000行记录测试
在没有建索引时平均完成时间是2.9秒
在对A字段建索引后平均完成时间是6.7秒
在对A字段和B字段建索引后平均完成时间是10.3秒
在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒
从以上测试效果可以显着看出索引对数据修改产生的影响
三.索引按存储方法分类
1.B*树索引
B*树索引是最常用的索引,其存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目次,叶块相当于索引到的具体的书页。一样平常索引及唯一束缚索引都使用B*树索引。
2.位图索引
位图索引储存主要用来节省空间,淘汰ORACLE对数据块的访问,它采取位图偏移方式来与表的行ID号对应,采取位图索引一样平常是重复值太多的表字段。位图索引在现实麋集型OLTP(数据变乱处理)中用得比力少,因为OLTP会对表进行大量的删除、修改、新建操纵,ORACLE每次进行操纵都会对要操纵的数据块加锁,以是多人操纵很容易产生数据块锁等待乃至死锁征象。在OLAP(数据分析处理)中应用位图有上风,因为OLAP中大部分是对数据库的查询操纵,而且一样平常采取数据堆栈技能,以是大量数据采取位图索引节省空间比力显着。
四.索引按功能分类
1.唯一索引
唯一索引有两个作用,一个是数据束缚,一个是数据索引,其中数据束缚主要用来包管数据的完整性,唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。
2.主关键字索引
主关键字索引产生的索引同唯一索引,只不外它是在数据库建立主关键字时系统自动建立的。
3.一样平常索引
一样平常索引不产生数据束缚作用,其功能主要是对字段建立索引表,以提高数据查询速度。
五.索引按索引对象分类
1.单列索引(表单个字段的索引)
2.多列索引(表多个字段的索引)
3.函数索引(对字段进行函数运算的索引)
建立函数索引的方法:
create index 收费日期索引 on GC_DFSS(trunc(sk_rq))
create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)
在对函数进行了索引后,如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。
alter session set query_rewrite_enabled=true
注:如果对用户函数进行索引的话,那用户函数应加上 deterministic参数,意思是函数在输入值固定的环境下返回值也固定。例:
create or replace function trunc_add(input_date date)return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;
六.应用索引的扫描分类
1.INDEX UNIQUE SCAN(按索引唯一值扫描)
select * from zl_yhjbqk where hbs_bh='5420016000'
2.INDEX RANGE SCAN(按索引值范围扫描)
select * from zl_yhjbqk where hbs_bh>'5420016000'
select * from zl_yhjbqk where qc_bh>'7001'
3.INDEX FAST FULL SCAN(按索引值快速全部扫描)
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh
七.什么环境下应该建立索引
1.表的主关键字,自动建立唯一索引
如zl_yhjbqk(用户基本环境)中的hbs_bh(户标识编号)
2.表的字段唯一束缚
ORACLE使用索引来包管数据的完整性
如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)
3.直接条件查询的字段
在SQL中用于条件束缚的字段
如zl_yhjbqk(用户基本环境)中的qc_bh(区册编号)
select * from zl_yhjbqk where qc_bh='7001'
4.查询中与其它表关联的字段,经常建立了外键关系
如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh='540100214511'
5.查询中排序的字段
排序的字段如果通过索引去访问那将大大提高排序速度
select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)
select * from zl_yhjbqk where qc_bh='7001' order by cb_sx(建立qc_bh+cb_sx索引,注:只是一个索引,其中包括qc_bh和cb_sx字段)
6.查询中统计或分组统计的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
八.什么环境下应不建或少建索引
1.表记录太少
如果一个表只有5条记录,采取索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一样平常索引表与数据表不在同一个数据块,这种情 况下ORACLE至少要来回读取数据块两次。而不用索引的环境下ORACLE会将全部的数据一次读出,处理速度显然会比用索引快。

如表zl_sybm(使用部门)一样平常只有几条记录,除了主关键字外对任何一个字段建索引都不会产生性能优化,现实上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动实验全表访问。如:
select * from zl_sybm where sydw_bh='5401'(对sydw_bh建立索引不会产生性能优化)
2.经常插入、删除、修改的表
对一些经常处理的业务表应在查询答应的环境下只管淘汰索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。
3.数据重复且分布平均的表字段
假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率约莫为50%,那么对这种表A字段建索引一样平常不会提高数据库的查询速度。
4.经常和主字段一块查询但主字段索引值比力多的表字段
如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年代、操纵标志来具体查询某一笔收款的环境,如果将全部的字段 都建在一个索引里那将会增加数据的修改、插入、删除时间,从现实上分析一笔收款如果按收费序号索引就已经将记录淘汰到只有几条,如果再按反面的几个字段索 引查询将对性能不产生太大的影响。
九.怎样只通过索引返回效果
一个索引一样平常包括单个或多个字段,如果能不访问表直接应用索引就返回效果那将大大提高数据库查询的性能。对比以下三个SQL,其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引:
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh='7001'
实验路径:
SELECT STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565
INDEX RANGE SCAN DLYX 区册索引 1 265
平均实验时间(0.078秒)
2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh='7001'
实验路径:
SELECT STATEMENT, GOAL = CHOOSE 11 265 3710
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710
INDEX RANGE SCAN DLYX 区册索引 1 265
平均实验时间(0.078秒)
3 select qc_bh from zl_yhjbqk where qc_bh='7001'
实验路径:
SELECT STATEMENT, GOAL = CHOOSE 1 265 1060
INDEX RANGE SCAN DLYX 区册索引 1 265 1060
平均实验时间(0.062秒)
从实验效果可以看出第三条SQL的效率最高。实验路径可以看出第1、2条SQL都多实验了TABLE ACCESS BY INDEX ROWID(通过ROWID访问表) 这个步骤,因为返回的效果列中包括当前使用索引(qc_bh)中未索引的列(hbs_bh,xh_bz),而第3条SQL直接通过QC_BH返回了效果, 这就是通过索引直接返回效果的方法。
十.怎样快速新建大数据量表的索引
如果一个表的记录到达100万以上的话,要对其中一个字段建索引大概要花很长的时间,乃至导致服务器数据库死机,因为在建索引的时候 ORACLE要将索引字段全部的内容取出并进行全面排序,数据量大的话大概导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工 作。办理方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置10M以上的排序内存(ORACLE缺省大小为64K),在索引 建立完成后应将参数修改返来,因为在现实OLTP数据库应用中一样平常不会用到这么大的排序内存。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作