oracle索引 一复合索引

oracle复合索引

 

    索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。复合索引的第一列称为前导列(leading column)。

 

    B树索引不存储索引列全为空的记录。对于复合索引，如果某一个索引列不为空，那么索引就会包括这条记录，即使其他所有的所有列都是NULL值。对于经常查询字段IS NULL又希望使用索引的情况，则需要结合查询条件选择合适的非空字段创建复合索引。

 

原则一：前缀性(Prefixing)

 

1、 当使用基于规则的优化器（RBO）时，只有 当复合索引的前导列 出现在查询条件中时，才会 使用到该索引；

2、 Oracle9i之前，使用基于成本的优化器（CBO）时， 只有当复合索引的前导列出现在查询条件中时，才可能 会使用到该索引。根据优化器估算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径；

      例：

SQL> explain plan for select * from  j1 where j1.status='125' and j1.no='NNNN';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 581817488
--------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |   469 |   246K|   113   (1)| 00:
|*  1 |  TABLE ACCESS FULL| J1                |   469 |   246K|   113   (1)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("J1"."NO"='NNNN' AND "J1"."STATUS"='125')

13 rows selected

   status列是索引的前导列，但是仍使用了全表扫描的方式，因为status字段上的值为125的行太多了。

3、 从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（index skip scan）。索引跳跃扫描基于成本估算，只在使用CBO时可用。这样，当查询条件中没有复合索引的前导列时，优化器估算索引跳跃扫描的成本低于其他扫描方式的成本时，就会使用索引；当前导列在的时候正常还是使用该复合索引的。

     查询条件中不包含前导列，但包含其它的索引列，这种情况下使用索引，通常是查询条件具有较高的选择性。

 

   原则二：可选性（Selectivity）

 

Oracle建议复合索引应按字段可选性（即值的多少）的高低进行排列，这是因为，字段值越多，可选性越强，定位的记录就越少，查询效率就越高。

 

Sql代码

CREATE INDEX name ON employee (emp_lname, emp_fname);  

如果第一列 不能单独提供较高的选择性 ，复合索引将会非常有用。例如，当许多雇员具有相同的姓氏时，emp_lname 和 emp_fname 上的复合索引非常有用。因为每个雇员都有唯一的 ID，所以 emp_id 和 emp_lname 上的复合索引可能没有用处，因此列 emp_lname 不会提供任何附加选择性 。

利用索引中的附加列 ，您可以缩小搜索的范围 ，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，它首先按姓氏对雇员 进行排序，然后按名字对所有姓氏相同的雇员进行排序。如果您知道姓氏，电话簿将非常有用，如果您知道名字和姓氏，电话簿则更为有用，但如果您只知道名字而 不知道姓氏，电话簿将没有用处。

 

索引列顺序

 

1、经常搜索的列排在前面，如仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。

2、如果对复合索引中的列多次执行单独的搜索，则应该在该列上创建另一个单独的索引。

3、选择低选择性列 作为索引列的前导列时，如果查询条件中包含所有 索引列或者低选择性列 （前导列），Oracle选择的执行计划中进行“INDEX RANGE SCAN ”操作，可获得较好的搜索性能；如果查询条件中没有出现索引前导列，而是出现了高选择性列。 Oracle 选择利用索引进行了“ INDEX SKIP SCAN”操作。

4、选择高选择性列为前导列时，如果查询条件中包含所有 索引列，执行INDEX RANGE SCAN操作；如果只包含低选择列，则会执行全表扫描（FTS）；如果包含高选择性列，则使用INDEX RANGE SCAN操作。

 

总结：

1、索引相当于书的目录，目录越多效率越低，如果目录很大，那还不如不要目录，直接全表扫描，这就是为什么有时有索引，但sql优化器不去用而是全表扫描的原因


2、复合索引，就是目录太多，又有了目录的目录


3、复合索引，在where里用到复合索引的第一个字段才会使用此索引


4、如果复合索引有多个字段如a,b,c， 在where里用到了a和c的情况下，是使用的跳跃式索引，即c也用进行参与


5、经测试，一个sql语句的where里有多个索引，只会使用其中一个最优的


6、为了解决where下可以使用多个字段的索引，所以才会有复合索引，即索引了内容又索引了目录，多次索引，在设计好的情况下效果会比单一索引好


7、where条件的书写顺序不影响