索引的使用

转自thomas zhang的杂货铺。

http://tomszrp.itpub.net/post/11835/68822

在使用Oracle的过程，我们就不能考虑性能和SQL优化，而正确的使用索引是优化SQL中的很关键的因素.

如果发现Oracle在有索引的情况下而没有使用索引,这并不是Oracle的优化器出错。在有些情况下Oracle确实会选择全表
扫描（Full Table Scan）,而非索引扫描（Index Scan）。比如：
1、表未做分析或者分析信息太老,导致 Oracle 判断失误.
2、根据表拥有的记录和数据块数,实际上全表扫描要比索引扫描更快.

对第1种情况最常见的例子,是以下这样的count语句：
select count(*) from table_name;
在未作分析之前,它使用全表扫描,需要读取1000[假设的]多个数据块（假如一个数据块是8k），做了分析之后，使用的是INDEX (FAST FULL SCAN)，可能只需要读取100个数据块。但是如何分析做得不好，也会导致Oracle不使用索引。

对第2种情况一般大家都认为通过索引访问比通过表访问要快,比较难理解的是在哪种情况下全表扫描要比索引扫描快。这就涉及到这么2个概念：
Oracle在评估使用索引的代价（cost）时有两个重要的数据：CF(Clustering factor) 和 FF(Filtering factor).
CF: 所谓 CF, 可以理解为每读入一个索引块要对应读入多少个数据块。
FF: 所谓 FF, 就是SQL语句所选择的结果集占总的数据量的百分比。

一般的估算公式是：FF * (CF + 索引块个数) [备注：toms 说"the formula used by the CBO to compute the cost is blevel + FF * leaf_blocks + FF * clustering_factor"]由此估计出一个查询如果使用某个索引会需要读入的数据块块数。需要读入的数据块越多，则 cost 越大Oracle 也就越可能不选择使用 index.
（全表扫描需要读入的数据块数等于该表的实际数据块数）

其核心就是，CF可能会比实际的数据块数量大。CF受到索引中数据的排列方式影响，通常在索引刚建立时，索引中的记录与表中的记录有良好的对应关系，CF 都很小;在表经过大量的插入/修改操作后，这种对应关系越来越乱，CF也越来越大。这个时候就需要DBA重建该索引。

如果某个SQL语句以前一直使用某个索引,突然有一天，你发现系统慢的不行了，检查发现该SQL语句的某个索引用不上了:其中一个很大的可能就是 CF 已经变得太大,需要重新整理该索引了.

FF 则是Oracle 根据分析所做的估计。比如某表有50多万行,其主键的最小值是1,最大值是500000，考虑以下sql 语句：
Select * from table_name where keyid>=1; 和
Select * from table_name where keyid>=500000
这两个表面看上去一样的sql语句，对Oracle而言却有巨大的差别。因为前者的FF是100%，而后者的FF可能只有 1%。如果
它的CF大于实际的数据块数，则Oracle可能会选择完全不同的优化方式。

索引有很多种[参考http://tomszrp.itpub.net/post/11835/59943],索引也有的"好坏"之分：
1、索引不是越多越好
特别是大量从来或者几乎不用的索引，对系统只有损害。OLTP系统每表超过5个索引即会降低性能

2、很多时候，单列索引不如复合索引有效率。

3、用于多表连结的字段，加上索引会很有作用。

那么，在什么情况下单列索引不如复合索引有效率呢？有一种情况是显而易见的，那就是，当SQL语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多。