索引的使用场景：
1.对应的索引字段经常被查询，且返回的数据结果占总数据量的一小部分
2.有外键约束的单个或多个字段上，需要建立索引。避免在更新主表的主键值，插入数据或删除数据时，对整张表的表锁出现。
3.唯一键约束，有相对应的索引存在
索引的属性：
索引与表中数据独立，增加或删除索引对表数据库无影响，只不过对应数据查询速度变慢，索引会降低dml操作性能(需要额外更新索引数据)
1.可用性
默认属性为可用，一个unuseable索引，dml操作不在维护对应索引且被优化器忽略，相对于删除一个索引继而创建它，可使用使索引不可用然后rebuild。无效索引或分区索引不占用空间，当把索引置成无效时，对应的存储空间被删掉,（对应分配的区段给删除掉）.
2.可见性
默认可见。一个invisible索引，dml操作进行维护，默认优化器不使用此索引。把一个索引置成不可见，是删除它或者更改为无效状态两者中的一种。把索引置成不可见非常有用，在删除以前移除索引或临时使用索引对应所有应用都没有影响。
相同字段不同顺序可以创建不同索引。
例如：create index ind_1 on halee(empno,ename);
create index ind_2 on halee(ename,empno);
index scan：I/O次数跟使用的索引的高度一致，在一次查询数据中。
full index scan:当谓语(where clause)中包含索引字段的过滤条件，都会走索引全扫描,前提是必须前置字段在前，如果排序字段与创建索引顺序不同，则不会使用索引全扫描
例如：全索引扫描场景
索引创建顺序为 empno,ename,sal；
1.查询字段全部在索引里，直接从索引中获取不在访问对应表
explain plan for
select empno,ename,sal from marlie where sal >= 3000 order by empno,ename;
-----
Plan hash value: 3148600482
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 99 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | IND_MARLIE_COMPLI | 3 | 99 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
2.查询除了索引字段以前的其他字段，需要通过索引rowid来访问表来回去对应数据值
explain plan for
select empno,ename,sal,HIREDATE from marlie where sal >= 3000 order by empno,ename;
--------------
Plan hash value: 3449361140
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 126 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MARLIE | 3 | 126 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | IND_MARLIE_COMPLI | 1 | | 1 (0)| 00:00:01 |
3.查询字段只包括索引字段，排序非创建顺序，需要有额外排序操作
explain plan for
select empno,ename,sal from marlie where sal >= 3000 order by ename,empno;
--------------------
Plan hash value: 584562644
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 99 | 2 (50)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 99 | 2 (50)| 00:00:01 |
|* 2 | INDEX FULL SCAN| IND_MARLIE_COMPLI | 3 | 99 | 1 (0)| 00:00:01 |
4.查询字段除索引字段外还有其他字段，排序非创建顺序，直接不会走索引全所描
explain plan for
select empno,ename,sal,HIREDATE from marlie where sal >= 3000 order by ename,empno;
-------------
Plan hash value: 1152818333
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 126 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 126 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| MARLIE | 3 | 126 | 3 (0)| 00:00:01 |
fast full index scan:属于full index scan一种，当查询的字段全部在索引里，且没有特殊的排序要求。
以下两种情况会替代全表扫描：
1.索引包含所有的查询字段
2.一行数据全部为null的值不会显示在结果集中
2.1.索引字段有一个字段有not null约束
2.2.where条件里有过滤null值的条件，保证全部为null的不展示出来
index range scan:
where条件中索引字段对应有过滤条件，且索引不是唯一索引，一个key对应多个rowid，就会使用索引范围扫描，在条件中有范围查询 between and，会走index range scan。
explain plan for
select empno,ename,sal,deptno from marlie where empno=7521;-- 索引字段出现在where中
----
Plan hash value: 3540606923
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MARLIE | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MARLIE_COMPLI | 1 | | 1 (0)| 00:00:01 |
index unique scan:
一个key对应0或1条数据，where条件为等值查询，索引字段为唯一键值，当查询到一个符合条件的记录即可会停止对应查询进程。与index range scan对应。
在empno字段创建了唯一索引
explain plan for
select empno,ename,sal,deptno from marlie where empno=7521;
------
Plan hash value: 260264523
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MARLIE | 1 | 17 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UIND_MARLIE_EMPNO | 1 | | 0 (0)| 00:00:01 |
index skip scan:
使用符合索引的逻辑子索引进行扫描获取数据。在复合索引前导列数据只有很少的几个值，非前导列有很多非重复的值，在where条件中使用非前导列查询时，会走index skip scan.
逻辑上按照前导列的非重复值的个数，把复合索引分成若干个子索引。
举例如下：
在表halee中创建 sex，id复合索引，分析表后：
explain plan for
select * from halee where id = 2050;
----
Plan hash value: 1329500493
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IND_HALEE_COM | 2 | 14 | 4 (0)| 00:00:01 |
实际执行类似：
select * from halee where sex = 1 and id = 2050
union all
select * from halee where sex = 0 and id = 2050;
index clustering factor:
用于衡量索引数据排序是否工整，顺序排列对应的相邻叶子块 对应的行存储在同一个数据块中(读取数据都是按照块读取的)，这样可降低IO次数，索引聚集因子就很小，接近于表数据对应的块个数。
只有尽可能的降低表的集群因子。一个索引怎么查询都不走索引或强制执行花费更大，有可能是集群因子过大导致。
reserve key indexes:把字段值对应的十六进制 按照倒叙的顺序存储
优点：避免热块操作，特别是在rac中，不同的实例操作相同的数据块。增加了插入性能。
缺点：在范围查询中，不能使用反转索引进行查询。
ascending and descending indexes:
ascending :以升序的方式存储数据，默认情况下，字符串类型按照值对应的字节数存储，数字类型按照从小大小数据，时间类型按照从古至今的方式存储。
descending：以倒叙的方式存储数据。
优点：在索引字段对应排序时，不需要额外的排序操作。
key compression:可以压缩主键对应的b-tree索引，或者是索引组织表；键值压缩，可以大大缩小索引存储所需空间。
每个索引都有公共部分(group piece)及唯一部分(unique piece),压缩后更改为 前缀部分(prefix)和后缀部分(suffix)，prefix对应group piece,suffix对应unqiue piece，prefix对应索引对应的字段值，suffix对应rowid
| 压缩前，存储类似： | 压缩后 |
20 1234 AAASMSAAEAAAACzAAA 20 1234 AAASMSAAEAAAACzAAB 30 2154 AAASMSAAEAAAACzAAC 40 1111 AAASMSAAEAAAACzAAD 40 1112 AAASMSAAEAAAACzAAE | 20 1234 AAASMSAAEAAAACzAAA AAASMSAAEAAAACzAAB 30 2145 AAASMSAAEAAAACzAAC 40 1111 AAASMSAAEAAAACzAAD 40 1112 AAASMSAAEAAAACzAAE |
位图索引(bitmap index)
适用环境：
字段中数据基数少，即唯一值不多。
表中更新少或几乎没有.
表中更新数据时，直接把索引键对应的值所有的行都进行锁定，不是锁定某一更改的行。
针对与某个bitmap索引，每一行都有一个对应的值来标识该行数据是否满足索引要求
存储如下：
| col_value | row1 | row2 | row3 | row4 | row5 | row6 | row7 |
| M | 1 | 0 | 0 | 0 | 1 | 1 | 1 |
| F | 0 | 1 | 1 | 1 | 0 | 0 | 0 |
针对bitmap类型的值来进行计算数量等类似的查询，可以创建爱你bitmap join index
例如在dept.dname字段上创建bitmap索引,基表为emp
create bitmap index bind_emp_dep_dname on emp(dept.dname)
from emp,dept where emp.deptno=dept.deptno;
类似如下的查询，会直接返回结果：
select count(*) from emp,dept where emp.deptno=dept.deptno and dname='ACCOUNTING';
bitmap index存储：
使用b-tree的方式存储每个索引键值，每个位图都存储在叶子节点上。
每个位图索引以b-tree方式存储有下面三部分组成：
每个索引值以索引键值开头
每个范围以rowid最小值和最大值标志
每个范围内的rowid的位图值
转载于:https://blog.51cto.com/8026776/1688833
393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
