索引的应用规律总结(二)

　　四、建议建立索引的情况

　　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返回了结果，这就是通过索引直接返回结果的方法。

　　一般来说：

　　①.有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by 、group by发生的列，可考虑建立群集索引；

　　②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；

　　③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

　　七、Oracle中的索引技术

　　<1> Oracle中如何重建索引

　　alter index 表电量结果表主键 rebuild

　　<2> Oracle中如何快速新建大数据量表的索引

　　如果一个表的记录达到100万以上的话，要对其中一个字段建索引可能要花很长的时间，甚至导致服务器数据库死机，因为在建索引的时候ORACLE要将索引字段所有的内容取出并进行全面排序，数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行，这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数，如果要进行大量的索引修改可以设置10M以上的排序内存（ORACLE缺省大小为64K），在索引建立完成后应将参数修改回来，因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

　　八、SQL Server中索引技术

　　<1> SQL Server中如何使用索引优化器

　　<2> SQL Server中在视图上建索引

　　<3> SQL Server中在计算列上建索引