oracle复合索引介绍(多字段索引)

首先,在大多数情况下,复合索引比单字段索引好.以税务系统的SB_ZSXX(申报类_征收信息表)为例,该表为税务系统最大的交易表.如果分别按纳税人识别号,税务机关代码,月份3个字段查询,每个字段在该表中的可选性或约束性都不强,如一个纳税人识别号有很多纳税记录,一个税务机关代码和同一月份记录就更多了,所以3个字段合起来,"某个纳税人识别号+某个税务机关代码+某月"的记录就少多了.因此复合索引比单字段索引的效率高多了.很多系统就是靠新建一些合适的复合索引,使效率大幅度提高.

但是,复合索引比单字段索引的内容原理复杂,复合索引有两个重要原则需要把握:

前缀性和可选性.如果糊里糊涂的滥用复合索引,效果适得其反.

以例子来说明,例子如下:

假设在员工表(emp)的(ename,job,mgr)3个字段上建了一个索引,例如索引名叫idx_1.3个字段分别为员工姓名,工作和所属经理号.然后,写如下一个查询语句,并不断进行查询条件和次序的排列组合,例如:

Sql代码

1. select * from emp where ename = 'a' and job = 'b' and mgr = 3 ;
2. select * from emp where job = 'b' and ename = 'a' and mgr = 3 ;

3. select * from emp where mgr = 3 and ename =

   'a' and job = 'b' ;

4. select * from emp where mgr = 3 and job = 'b' and ename =

   'a'

   ;

5. select * from emp where job = 'b' and mgr = 3 and ename = 'a' ;
6. .....

回答问题:在各种条件组合情况下,刚才建的索引(idx_1)

是用还是不用?也就是说对emp表的访问是全表扫描还是按索引(idx_1)访问?

答案是 : 上述语句中只要有ename='a'条件,就能用上索引(ind_1),而不是全表扫描(这就是复合索引的前缀性).

复合索引的原理和设计建议

1.复合索引的第一个建议: 前缀性(Prefixing)

先从例子说起.假设省,市,县分别用3个字段存储数据,并建立了一个复合索引.请记住:

oracle索引,包括复合索引都是排序的.例如该复合索引在数据库索引树上是这样排序的,即先按省排序,再按市排序,最后按县排序:

省 市 县

北京 北京 东城

北京 北京 西城

北京 北京 海淀

... ...

黑龙江 哈尔滨 道里区

黑龙江 哈尔滨 道外区

黑龙江 哈尔滨 香坊区

黑龙江 齐齐哈尔 龙沙区

黑龙江 齐齐哈尔 铁锋区

黑龙江 齐齐哈尔 富拉尔基区

湖南 长沙 芙蓉区

湖南 长沙 岳路区

湖南 长沙 开福区

oracle不是智能的,它只会按图索骥,该索引结构是先按省排序的,所以只要给出省名,就能使用索引.如果没有省名,oracle就成了无头苍蝇,乱找一气,变成了全表扫描了.例如,如果你只给一个县条件,如"开福区",oracle肯定不会使用该索引了.

2.关于skip scan index

有时候复合索引第一个字段没有在语句中出现,oralce也会使用该索引.对,这叫oralce的skip scan

index功能,oracle 9i才提供的.

skip scan

index功能适合于什么情况呢?如果oracle发现第一个字段值很少的情况下,例如假设emp表有gender(性别)字段,并且建立了(gender,ename,job,mgr)复合索引.因为性别只有男和女,所以为了提高索引的利用率,oracle可将这个索引拆成('男',ename,job,mgr),('女',ename,job,mgr)两个复合索引.这样即便没有gender条件,oracle也会分别到男索引树和女索引树进行搜索.

但是,(gender,ename,job,mgr)索引本身设计是不合理的,它违背了复合索引的第二个原理,可选性(Selectivity),见下面描述.

3.复合索引的第二个原理:可选性(Selectivity)

您可能会问:复合索引中如何排序字段顺序?这时就要用到复合索引的第二个原理:可选性(Selectivity)规则.oracle建议按字段可选性高低进行排序,即字段值多的排在前面.例如,(ename,job,mgr,gender),(县,市,省).这是因为,字段值多,可选性越强,定位的记录越少,查询效率越高.例如,全国可能只有一个"开福区",而湖南省的记录则太多了.

4.复合索引设计建议

(1).分析SQL语句中的约束条件字段.

(2).如果约束条件字段比较固定,则优先考虑创建针对多字段的普通B*树复合索引.如果同时涉及到月份,纳税人识别号,税务机关代码3个字段的条件,则可以考虑建立一个复合索引.

(3).如果单字段是主键或唯一字段,或者可选性非常高的字段,尽管约束条件比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销.

(4).在复合索引设计中,需首先考虑复合索引的第一个设计原理:复合索引的前缀性.即在SQL语句中,只有将复合索引的第一个字段作为约束条件,该复合索引才会启用.

(5).在复合索引设计中,其实应考虑复合索引的可选性.即按可选性高低,进行复合索引字段的排序.例如上述索引的字段排序顺序为:纳税人识别号,税务机关代码,月份.

(6).如果条件涉及的字段不固定,组合比较灵活,则分别为月份,税务机关代码和纳税人识别号3个字段建立索引.

(7).如果是多表连接SQL语句,注意是否可以在被驱动表(drived

table)的连接字段与该表的其他约束条件字段上创建复合索引.

(8).通过多种SQL分析工具,分析执行计划以量化形式评估效果.