Oracle调优----调优索引

目标：

1.索引的类型

2.使用索引的有点

3.索引扫描类型

4.为什么不使用索引

5.怎样使用索引

6.创建索引的原则

一、索引的类型

1.B树索引  常用

2.位图索引   常用

3.HASH索引

4.索引组织表IOT

5.反转键（reverse keys）索引

6.基于函数的索引 常用

7.分区索引（本地索引、全局索引）

8.位图连接索引

----B树索引

B树索引在Oracle中是一个通用索引，创建索引的时候默认就是B树索引。

B树索引分为单列索引和组合索引（最多可以多达32个列）；

对于B树索引，我们需要关注他的二元高度（blevel）；

B树索引保存了索引列上有值的每个数据行的ROWID.

Oracle不会对索引列上包含NULL值得行进行索引，如果索引是一个组合索引，二其中有一列不为空，这一行会于索引列中；

-------位图索引

对于位图索引的列，列值的数量要求较少或者中等（索引列技术较小）

对于较低技术的位图索引来说，位图索引的尺寸远远的小于B树索引，因此可以大大的减少IO的数量。

对于非常大的表来说，在多个低基数列上建立位图索引是一个很好的选择。

对于位图索引来说，即使从表中读取很多行，也会使用位图索引。

例如在一个sex 列上建立索引，每次从表中读取板书的数据行，但是还是会使用位图索引。

---B树索引和位图索引的比较

在B树索引中，可以实现行级锁定，但是在位图索引中，因为对ROWID进行压缩存放（一个ROWID范围+位图），因此每次所内的都是ROWID范围，因此对表中的位图索引列进行更新的时候，并发性很差，很容易导致死锁，SELECT语句不会受到这种锁定问题的影响；

注意：不要在繁忙的OLTP系统中使用位图索引；

-----反转索引

当载入一些有序的数据时，索引肯定会碰到与IO相关的一些瓶颈。在载入期间，某部分索引和磁盘肯定会比掐部分使用拼房到的多。

为了解决这个为题，我们可以使用反转索引，这样对于有序数据的载入，Oracle会更新不同的索引块。

1234、1235、1236，如果数据以反转索引存储，那么这些数据的存放形式为4321,5321,6321.结果就是索引会为索引会每次新插入的行更新不同的索引块。

创建反转索引：

create unique index i1_t1 on t1(c1)
reverse pctfree 30
storage(initial 200k next 200k pctincrease o maxextents 50)tablesapce index;

create unique index i2_t1 on ()t1(c2)
alter index i2_t1 rebuild reverse;

------基于函数的索引

create index fbi_upper_lastname on customers(upper(cust_last_name))

select * from customers where upper (cust_last_name)='SMITH'

基于函数的索引需要考虑的问题

1.基于函数的索引只能针对一种函数，对于其余的函数不骑作用

2.控制索引的数量，因为对DML（数据操纵语言）会产生影响

基于函数的索引会带来极大的好处

-----分区索引

分区索引就是简单的吧一个索引分成多个片段，这样可以访问更小的片段，可以将片段分别放在不同的磁盘驱动器上。

B树索引和位图索引都可以被分区，而hash索引不能被分区。

几种分区方式：

1.表被分区二索引未被分区

2.表未被分区二索引被分区

3.表和索引都被分区

无论那种方式，都必须使用基于成本的优化器。

分区可以提高性能和可维护性。

二、索引的优点

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2.可以大大加快数据的检索速度，这也是创建索引的最主要原因

3.可以加速表和表之间的连接，特别是实现数据的参考完整性方面特别有意义

4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

三、索引扫描类型

根据索引的类型和where条件的限制不同，有五种索引扫描类型：

1.索引唯一扫描(INDEX UNIQUE SCAN)

2.索引范围扫描(INDEX RANGE SCAN)

3.索引全扫描(INDEX FULL SCAN)

4.索引快速扫描(INDEX FAST FULL SCAN)

5.索引跳跃扫描(INDEX SKIP SCAN)

--索引唯一扫描

通过唯一键、主键，Oracle通常放回一个数据行，因此采用的是索引唯一扫描

---索引范围扫描

1.在唯一键上使用range操作符（>,<,<>,>=,<=,between）

2.在组合索引上，只使用部分列进行查询、导致查询多行

3.对非唯一索引列上进行的查询

---索引全扫描

差存储的数据必须从全部所以那种得到

---快速全索引扫描

在索引的快速全局扫描期间，Oracle读取B书索引上的所有树叶块，这个索引可以按照顺序读取，这样可以一次性读取多个块，初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT可以控制同时被读取的块数。

相对全表扫描，快速全局索引扫描通常需要较少的物理IO。

如果表查询的所有列都被包含在索引中，而索引的前置两列并不在where条件中，那么就可以使用快速全局索引扫描。

select empno,ename,deptno form emp where deptno=30; empno,ename,deptno上有一个组合索引

作为一个选择，针对上面的情况，Oracle可能采取跳跃式索引扫描。

---跳跃式索引扫描

组合索引、where条件中没有出现前置列（通常是组合索引的第二列）

相对全索引扫描，跳跃式索引扫描读取的数据块更少、速度更快。

四、索引限制

我们来研究下使用where但是阻止Oracle使用索引的集中情况

1.使用不等于运算符（<>,!=）

explain plan for select * from abc where xinbie<>1;
select * from table(dbms_xplan.display);

2.使用is null或使用is not null

在where子句中使用is null或者is not null同样会限制索引的使用。

如果被索引的列在某些行中存在null值，在索引列中就不会有相应的条目。

（例外：位图索引中对于null列也会进行记录，因为此位图索引对于null搜索通常较为快速）

3.使用函数

如果不使用基于函数的索引，那么在SQL语句中where自己中对存在索引的列使用函数时，会使优化器忽略掉这些索引

一些常见的函数：trunc,substr,to_date,to_char,instr等，都可能会使索引失效

解决方案：

①使用基于函数的索引 

②灵活书写SQL、避免在索引列上使用SQL函数

4.比较不匹配的数据类型

这个是比较难于发现的问题。Oracle不会对不匹配的数据类型报错，Oracle会隐式的吧varchar2列的数据类型转换成要被比较的数值类型（举例，还存在其他的数据类型转换）

select bank_name,address,city,state,zip from banks where account_number=990354
Oracle可以自动把where子句变成to_number(account_number)=990354

五、索引的选择性

Oracle通过多种方法来判断使用索引的价值，第一个方法是判断索引中的胃一直和不同建的数量

唯一键的数量/表的行数，判断索引的选择性。

六、集群因子（cluster factor）

集群因子是索引与他所基于的表想比较得出的有序性度量，它用于检查在索引访问之后执行的表查找的成本（将集群因子与选择性相乘即可的到该操作的成本）。

Custering Factor的含义：如果通过一个索引扫描一张表，需要访问的表的数据块的数量。

Custering Factor计算的方法如下：

1.扫描一个索引

2.比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1

3.整个索引扫描完毕后，就得到了该索引的cluster factor。如果Clustering Factor接近于表存储的块数，说明这张表是按照索引字段你的顺序存储的，如果Clustering Factor接近于行的数量，那说明这张表不是按照索引字段顺序存储的。

在计算索引访问成本的时候，这个值十分有用。Clustering Factor乘以选择性参数（selectivity）就是访问索引的开销。

如果这个统计数据不能真实反映出索引的真实情况，那么可能曹成优化器错误的选择执行计划。

另外如果某张表上的大多数访问时按照某个索引做索引扫描，那么僵该表的数据按照索引字段的顺序从新组织，可以提高该表的访问性能。

select index_name,table_name,clustering_factor from dba_indexes where table_name='abc';
select segment_name,blcoks from dba_segments where segment_name='abc';
select AUG_ROW_LEN from dba_tables where table_name='abc';

select count(*) from emr.abc;

聚集因子的数量和表的块数一直，因此表的数据排序和索引一致，因此在进行索引范围扫描ode时候，对表的访问成本还是比较低的。

狙击因子主要影响的是索引范围扫描。

如果聚集因子很高，那么建议对表进行重新整理，减低聚集。

七、二元高度（binary height）

索引查找分为两个过程：

1.根据树进行定位、找出rowid（索引查找）

2.根据rowid找出表中的数据行（表数据查找）

索引：进行索引查找的时候，首先从树根开始读数据，通过中间表节点，最后定位到叶节点，整个过程只能进行行但数据块的读取；

如下：二元高度是3，读取一行数据经过了三次数据块的读取，其中三次是索引，一次是表数据。

select blevel,index_name from user_indexes where index_name='EMP_DEPATMENT_IX';
查找每个索引的二元高度，这个索引值占用了1个数据块，因此二元高度是0;

二元高度随着表中索引列的非NULL值以及索引列中值的宽度而变化。

如果索引列上大量的行被删除，那么他的二元高度不会降低，重建索引会降低二元高度，如果一个索引中被删除的行接近20%~25%，重建索引会降低二元高度。

二元高度对索引的性能影响不是很大，但是在可能的情况下，降低二元高度还是有必要的。

八、使用直方图

在分析表和索引时，直方图用户记录数据的分布。

通过获取该信息，基于成本的优化器就可以决定使用量返回少量行的索引，而避免使用基于限制条件返回许多行的索引。

直方图的使用不受索引的限制，我们可以在表的任何列上构建直方图（一般是在表的索引列上构建直方图）

构建直方图最主要的原因就是：帮助优化器在表中数据严重倾斜时做出更好的规划。

如果一个表中的列上（通常是索引列）数据放生严重的倾斜，那么在这个列上建立直方图将非常的有意义。这样优化器就知道什么时候该使用索引、什么时候不该使用索引。

exec dbms_stats.gather_table_stats('hr','emrloyees',method_opt=>'for columns size 10 job_id');
在hr用户下面的employees表的job_id列上建立了一个直方图，这个直方图有10个存储桶；

对整个列的100行数据进行均分成10个存储桶，每个同种存储10个数据行。然后写出每个桶中的数据范围。

我们发现1430这个数值对应的行号数非常多，大约73行，找到了73%，因此当我们使用 where id=1403的时候，优化器就不会走索引，默认情况下，Oracle的直方图会长胜75的存储桶，可以把存储桶的size限制在1~254之间

九、其他

创建查询：指导原则

1.平衡查询和DML需求

2.存储在独立的表空间中

3.使用同一的片大小：5个块的整数倍或者表空间的MINIMUN EXTENT

4.对于巨型索引，考虑NOLOGGING

5.INITRANS应该设得比相应的表要高一些

索引的联机重构

在重构索引时，可以使用最少的表锁

alter index orders_id_idx rebuild online;

鉴别不使用的索引：

1.开始检测一个索引的使用

alter index summit.orders_id_idx monitoring usage;

2.停止对一个索引的检测

alter index summit.orders_id_idx nomonitoring usage;

创建索引的原则：

1.在OLTP系统中，索引尽量少创建；

2.在OLTP系统中对拼房更新的表不要创建位图索引；

3.在经常存取的所以一个列上不要建立符合索引，但要注意符合索引的建立顺序要按照使用的频度来确定；

4.如果where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应的函数索引；

5.用于连接的列（主外键）上建立索引；

6.在SQL语句中经常进行group by、order by的字段上建立索引；

获取索引信息：

通过查询数据字典获取有关索引的信息。

①DBA_INDEXES：提供索引的信息

②DBA_IND_COLUMNS：提供索引字段的信息

③DBA_IND_EXPRESSIONS：提供基于函数的索引信息

④V$OBJECT_USAGE：提供了索引的使用信息