索引在数据库中的应用分析

索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能会提高一万倍的效率，而无效的索引可能会浪费数据库空，甚至会大大降低查询性能。

指数管理成本

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数据库应用程序中一般不会使用这么大的排序内存。