复合索引

复合索引是把双刃剑，用好了，极大提高系统性能。用不好，放在数据库中就是一堆垃圾。复合索引比单字段索引的内部原理更复杂。先说说复合索引的2个原则：前缀性和可选性。如果稀里糊涂的滥用符合索引，效果就会适得其反。

1、例子

假设员工信息表（emp）的（ename，job，mgr）3个字段建立了一个ind_1的索引。

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

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

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

select * from emp where job='b' and mgr=3;

select * from emp where ename='a' and mgr=3;

select * from emp where ename='a';

select * from emp where job='b';

select * from emp where mgr=3;

上面的语句中只要有ename=‘a'的条件就是使用ind_1的索引，而不是全表扫描。

2、复合索引的第一个原理：前缀性

举例子：假设省、市、县分别用3个字段存储数据，并建立一个复合索引。请记住oracle索引包括复合索引都是排序的。例如该复合索引在数据库索引数上是按照省、市、县这种顺序排序的。

省             市               县

山东省      济南市      长清县

山东省      济南市      平阴县

山东省      济南市      济阳县

... ...

北京市      北京市      东城区

北京市      北京市      西城区

北京市      北京市      海淀区

oracle不是智能的，它只会按图索骥，该索引结构式先按照省排序的，所以只要有省名就会使用索引。如果没有省名oracle就成了无头苍蝇，乱找一气，变成了全表扫描。如果你只给一个县（区）条件如“西城区”，oracle肯定不会使用该索引。

3、关于skip scan index

oracle9i 提供了skip scan index功能，那么skip scan index功能适合于什么情况呢？如果oracle发现第一个字段值很少的情况下，例如假设emp表有“性别”字段，并且建立了“姓名”、“工作”、“年龄”字段。因为性别只有’男‘、’女‘，所以为了提高索引的利用率，oracle可将这个索引拆成（“男”、“姓名”、“工作”、“年龄”）、（“女”、“姓名”、“工作”、“年龄”）两个复合索引。索引即便没有性别这个字段，oracle也会分别到男索引和女索引数中进行搜索。

4、复合索引的第二个原理

复合索引中如何排列字段顺序，这时就要用到复合索引第二个原理：可选性规则。oracle建议按字段可选性高低进行排列，即字段值多的排在前面。例如：（县、市、省）。这是因为字段值多，可选性越强，定位记录越少，查询效率越高。例如全国可能只有一个”西城区“，而”北京市“的记录则多了。

5、复合索引设计建议

（1）、分析SQL语句中的约束条件字段。

（2）、如果约束条件字段比较固定，则优先考虑创建针对多字段的普通B*数复合索引。

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

（4）、在复合索引设计中，需要首先考虑复合索引的第一个设计原理：复合索引的前缀性。即在SQL语句中，只有将复合索引的第一个字段作为约束条件，该复合索引才会启用。

（5）、在复合索引设计中，其次应该考虑复合索引的可选性。即按字段可选性高低，进行复合索引字段的排序。

（6）、如果条件设计的字段不固定，组合比较灵活，则分别为县、市、省3个字段建立索引。

（7）、如果是多表连接SQL语句，注意是否可以在被驱动表的连接字段与该表的其他约束条件字段上创建复合索引。

（8）、通过多种SQL分析工具，分析执行计划并以量化形式评估效果。

参考《品悟oracle性能优化》--罗敏 著