sql优化-6（索引）

<一>索引开销
1.访问开销
访问集中导致热块竞争
索引查询要尽可能的避免回表，如果不可避免，需要关注聚合因子是否过大。
举个例子：
select a from test_db where b=5
A、假设b上没有索引
那么该条SQL将进行表扫描，扫描所有该表的数据块
从数据块中找到记录，并且进行过滤
可想而知，没有索引将会导致扫描该表所有数据块，性能低下
B、 假设b上有索引
那么该条SQL将进行索引扫描，在索引中找到b=5的位置，一般只需要扫描3个块左右就找到了
获得所有b=5的行的rowid
根据rowid再查询数据(这就是回表)，如果数据量少，那么回表次数就少，如果需要的数据全部在索引中，那么就不会再回表了,例如a也在索引中，如果a不在索引中，那么仍然要回表一次查出a。
2.更新开销
在无索引的情况下，表的记录越大，插入的速度只会受到很小的影响，基本不会越慢。
在无索引的情况下，分区表的插入要比普通表更慢，因为插入的数据需要做判断落在哪个分区。有这方面的开销。
在有索引的情况下，表记录越大，索引越多，插入速度越慢。
在有索引的情况下，如果表非常大，索引也很多，在条件允许下，可以将索引先失效再生效，速度可以更快。
在有索引的情况下，分区表如果只是有局部索引，一般来说，分区表的插入速度比普通表更快，因为每个分区的分区索引都比较小，更新的开销自然也小。
3.建立开销
建索引过程产生全表锁
普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞
ONLINE建索引的方式，这种建索引的方式不会阻止针对该表的更新操作，与建普通索引相反的是，ONLINE建索引的动作是反过来被更新操作阻塞。
建索引的过程会产生排序，排序的开销一般比较大，所以要尽量避免在生产缓慢的时候建索引。

<二>索引失效
逻辑失效：
1.类型转换导致索引失效（是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型）
2.列上使用函数运算导致索引失效，比如substr(列），trunc(列)
3.单独引用符合索引非第一位置的索引列
4.not in,not exist
5.表未进行分析
6.索引遇到like '%LJB' 或者是'%LJB%'的查询，是用不到索引，不过like 'LJB%'是可以用到索引
7.索引能够消除排序，但是如果排序是部分升序部分降序，就必须建对应部分升降序的索引，否则无法用这个来消除排序。
比如order by col1 desc col2 asc，我们可以建(col1 desc,col2 asc)的索引。
物理失效：
1.LONG修改为CLOB，居然会导致其他列的索引失效
2.用ALTER TABLE MOVE的方式来降低高水平，这个操作会导致索引失效
3.alter table t shrink的方式降低表的高水平位，也不会导致索引失效，却无法消除索引的大量空块。最终导致虽然索引不失效，查询依然不用索引
4.分区表操作导致全局索引失效
truncate分区会导致全局索引失效，不会导致局部索引失效。如果truncate 增加update global indexes，全局索引不会失效。
drop分区会导致全局索引失效，局部索引因为drop分区，所以也不存在该分区的局部索引了。如果drop分区增加update global indexes，全局索引不会失效。
split分区会导致全局索引失效，也会导致局部索引失效。如果split分区增加update global indexes，全局索引不会失效。
exchange会导致全局索引失效，不会导致局部索引失效。如果exchange分区增加update global indexes，全局索引不会失效。

<三>索引需注意问题：
1.一个表索引个数不宜过多（低于5）；
2.外键应创建索引；
3.组合索引列不宜过多（小于4）；
4.大表应该建索引（大于2G）;
5.索引聚合因子不应该很大（聚簇因子是指，按照索引列值进行了排序的索引行序和对应表中数据行序的相似程度）；
如果越有序，即相邻的键值存储在相同的block，那么这时候ClusteringFactor 的值就越低。
如果不是很有序，即键值是随即的存储在block上，这样在读取键值时，可能就需要一次又一次的去访问相同的block，从而增加了I/O.
Clustering Factor 的计算方式如下：
扫描一个索引（large index range scan）
比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1
整个索引扫描完毕后，就得到了该索引的cluster factor。
如果ClusteringFactor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。
如果ClusteringFactor接近于行的数量，那说明这张表不是按索引字段顺序存储的。
6.索引是否失效（普通表、分区表）；
7.组合索引与单列索引存在交叉情况；
8.索引高度不宜过高（低于5）；
9.索引是否设置并行属性（影响查询性能）；
10.索引统计信息太旧