索引在数据库中的应用分析
索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能会提高一万倍的效率,而无效的索引可能会浪费数据库空,甚至会大大降低查询性能。
指数管理成本
1.存储索引的磁盘空
2.由数据修改操作(插入、更新、删除)引起的索引维护
3.数据处理需要额外的备用空房间。
实际数据修改测试:
一个表有A、B、C三个字段,同时进行插入10000行记录的测试。
没有索引的平均完成时间是2.9秒。
索引A字段后的平均完成时间为6.7秒。
索引字段A和字段B后的平均完成时间为10.3秒。
索引字段A、B和C后的平均完成时间为11.7秒。
从上面的测试结果中,我们可以清楚地看到index对数据修改的影响。
索引按存储方式分类。
B*树索引
B*树索引是最常用的索引,其存储结构类似于书籍。有两种类型的存储数据块:分支块和叶块。分支块相当于书籍的大目录,叶块相当于索引的具体页码。一般索引和约束索引都使用B*树索引。
位图索引
位图存储主要用于保存空,减少ORACLE对数据块的访问。它使用位图偏移量来对应表的行ID号。位图索引通常用于重复值过多的表字段。位图在实际的密集型OLTP(数据事务处理)中很少使用,因为OLTP会删除、修改、创建大量的表,ORACLE每次操作都会锁定要操作的数据块,所以很容易出现多人操作等待数据块锁定甚至死锁的情况。位图在OLAP(数据分析处理)中的应用是有优势的,因为OLAP大部分是数据库的查询操作,一般采用数据仓库技术,所以用位图索引大量数据时,节约空是很明显的。
按功能分类的索引
指数
Index有两个作用,一个是数据约束,一个是数据索引,其中数据约束主要用于保证数据的完整性,index生成的索引记录中的每条记录对应一个ROWID。
主键索引
主键索引生成的索引与索引相同,只是在数据库中建立主键时由系统自动建立。
总索引
索引一般不具有数据约束的功能,其作用是为字段建立索引表,以提高数据查询的速度。
索引是按索引对象分类的。
单列索引(表单中字段的索引)
多列索引(一个表的多个字段的索引)
函数索引(对字段执行函数操作的索引)建立函数索引的方法:
在GC_DFSS上创建收费日期索引(trunc(sk_rq))
在yhzl上创建完整的客户编号索引(qc_bh||kh_bh)
在函数被索引之后,如果当前会话想要引用它,当前会话的query_rewrite_enabled应该被设置为TRUE。
alter session set query _ rewrite _ enabled = true
注意:如果用户函数是索引的,那么用户函数要加上一个确定性的参数,也就是说即使输入值是固定的,函数的返回值也是固定的。示例:
创建或替换函数trunc_add(输入日期日期)返回日期确定性
如同
开始
return trunc(input _ date+1);
end trunc _ add
应用索引的扫描分类
唯一索引扫描(按索引值扫描)
select * from zl_yhjbqk其中hbs_bh=@#5420016000@#
按索引值范围扫描的索引范围)
select * from zl_yhjbqk其中hbs_bh>@#5420016000@#
select * from zl_yhjbqk其中qc_bh>@#7001@#
索引快速全扫描(按索引值快速全扫描)
从zl_yhjbqk order by hbs_bh中选择hbs_bh
select count(*) from zl_yhjbqk
通过qc_bh从zl_yhjbqk组中选择qc_bh
什么情况下应该建立指数?
表的主键
自动索引
比如zl_yhjbqk(用户基本信息)中的hbs_bh(账户识别号)
表的字段约束
ORACLE索引用于确保数据完整性。
比如lc_hj(流程链接)中的lc_bh+hj_sx(流程号+链接顺序)
直接即席查询的字段
SQL中用于约束的字段
如zl_yhjbqk(用户基本信息)中的qc_bh(区号)
select * from zl_yhjbqk其中qc_bh='7001 '
中与其他表相关联的查询字段
字段通常建立外键关系。
比如zl_ydcf(用电部件)中的jldb_bh(计量点电表数)
select * from zl_ydcf a,zl_yhdb b其中a.jldb_bh=b.jldb_bh和b.jldb_bh='540100214511 '
查询排序字段
如果通过索引访问排序后的字段,排序速度会大大提高。select * from ZL _ YhJBQK order by qc_bh(建立QC _ BH索引)
Select * from ZL _ yhjbqk其中qc_bh = @ # 7001 @ # order by cb_sx(建立qc_bh+cb_sx索引,注意:只是索引,包括qc_bh和cb_sx字段)
查询统计数据或分组统计数据的字段
从zl_yhjbqk中选择max(hbs_bh)
select qc_bh,count(*)from ZL _ yhjbqk group by QC _ BH
什么情况下应该不建索引或者少建索引?
记录太少了。
如果一个表只有五条记录,并且记录是通过索引访问的,那么需要先访问索引表,然后通过索引表访问数据表。通常,索引表和数据表不在同一个数据块中。在这种情况下,ORACLE必须至少读取数据块两次。没有索引,ORACLE会一次性读取所有数据,处理速度明显比有索引快。
比如表zl_sybm(运营部门)一般只有几条记录,索引除主键以外的任何字段都不会导致性能优化。事实上,如果对表进行统计分析,ORACLE不会使用你的索引,而是自动访问整个表。比如:
Select * from ZL _ sybm其中sydw_bh = @ # 5401 @ #(索引sydw_bh不会导致性能优化)
频繁插入、删除和修改的表
对于一些经常处理的业务表,在查询允许的情况下尽量减少索引,比如zl_yhbm、gc_dfss、gc_dfys、gc_fpdy等业务表。
具有重复数据和均匀分布数据的表字段
如果一个表有100000行记录,一个字段A只有T和F两个值,每个值的分布概率在50%左右,那么索引这个表A的字段一般不会提高数据库的查询速度。
经常与主字段一起查询的表字段,但主字段的索引值很大。
比如gc_dfss(实收电费)表,经常根据收费流水号、居民身份证号、抄表日期、电费发生年份、运行标志查询某笔款项。如果所有字段都建立在索引中,将增加数据修改、插入和删除的时间。实际上,如果根据收费序列号对一笔付款进行索引,记录将减少到只有几条。如果对以下字段进行索引,不会对性能产生太大影响。
如何仅通过索引返回结果?
索引通常包括一个或多个字段。如果可以不访问表直接应用索引并返回结果,将会大大提高数据库查询的性能。比较以下三个SQL,其中对表zl_yhjbqk的hbs_bh和qc_bh字段进行了索引:
1从zl_yhjbqk中选择hbs_bh、qc_bh、xh_bz,其中qc_bh='7001 '
执行路径:
SELECT语句,目标= CHOOSE 11 265 5565
通过索引访问表ROWID DLYX ZL_YHJBQK 11 265 5565
索引范围扫描DLYX卷索引1 265
平均执行时间(0.078秒)
2从zl_yhjbqk中选择hbs_bh,qc_bh,其中qc_bh='7001 '
执行路径:
选择语句,目标=选择11 265 3710
通过索引访问表ROWID DLYX ZL_YHJBQK 11 265 3710
索引范围扫描DLYX卷索引1 265
平均执行时间(0.078秒)
3从zl_yhjbqk中选择qc_bh,其中qc_bh='7001 '
执行路径:
SELECT语句,目标=选择1 265 1060
索引范围扫描DLYX卷索引1 265 1060
平均执行时间(0.062秒)
从执行结果可以看出第三条SQL的效率。从执行路径可以看出,第一条和第二条SQL都执行了索引ROWID访问表的步骤,因为返回的结果列包含了当前使用的索引(qc_bh)中未索引的列(hbs_bh,xh_bz),而第三条SQL直接通过QC_BH返回结果,这是通过索引直接返回结果的方法。
如何重建索引
变更指标电量结果表主键重建
如何快速创建大数据规模的指数
如果一个表中有超过100万条记录,索引其中一个字段可能需要很长时间,甚至会导致服务器数据库崩溃,因为在建立索引时,ORACLE要把索引字段的所有内容都拿出来,进行全面排序。如果数据量很大,可能是服务器排序内存不足,会使用磁盘交换空,严重影响服务器数据库的工作。解决方法是在数据库启动初始化时增加排序内存参数。如果要修改大量的索引,可以设置10M以上的排序内存(ORACLE默认的大小是64K)。建立索引后,应该修改参数,因为实际OLTP数据库应用程序中一般不会使用这么大的排序内存。
位律师回复
0条评论