数据库索引原理分析

 

在使用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 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多。