Oracle -- 索引

索引概述

索引的设计是应用和开发的一个重要方面，好的索引能够提升查询性能。

– 如果有太多的索引，DML的性能就会受到影响
– 如果索引太少，又会影响查询（包括插入、更新和删除）的性能。
– 要找到一个合适的平衡点，对于一个用的性能至关重要。

常见索引

1. B*树索引
2. 位图索引
3. 基于函数索引

— B*树索引传统索引，所有数据库最常用的索引。类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需很少的读操作就能找到正确的行。
— 位图索引：在一颗B*树中，通常索引条目和行之间存在一种一对一的关系，而对于位图索引，一个索引条目则使用一个位图同时指向多行，位图索引适用于高度重复而且通常只读的数据。
— 基于函数的索引：就是B*树索引或位图索引，将一个函数计算得到的结果存储在行的列中，而不是存储列数据本身。可以把基于函数的索引看作一个虚拟列上的索引。

B*数索引

• 叶子节点：B*树最底层的块成为叶子节点或叶子块，其中分别包含各个索引键以及一个rowid（指向所索引的行）；
• 分支块：叶子节点之上的内部块称为分支块，这些节点用于在结构中实现导航。
• 注意：索引的叶子节点实际上构成了一个双向链表。一旦发现要从叶子节点中的哪里开始，执行值的有序扫描（即索引的区间扫描）就会很容易。
• B*树的特点之一是：所有叶子块都应该在树的同一层上。这一层也称为索引的高度。说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。
  比如：

select indexed_col from t where indexed_col = :x

要达到叶子块来获取第一行，不论使用的:x值是什么，都会执行同样数目的I/O。
也就是说，索引是高度平衡的。大多数B*树索引的高度都是2或者3，即使索引中有百万行记录也是如此。
应用环境
B*树是一个绝佳的通用索引机制，无论是大表还是小表都适用，随着底层表大小的增长，获取数据的性能只会稍有恶化。
有两种做法：

• 仅当要通过索引访问表中很少的一部分行（只占一个很小的百分比）时，才使用B*树在列上建立索引。
• 如果要处理表中的多行，而且可以使用索引而不用表，就可以使用一个B*树索引。
• 索引并不意味着更快的访问。这实际上是两个因素的函数：
  – 通过索引访问表中多少数据
  – 数据如何布局

索引指向的块则随机地存储在堆中，因此通过索引访问表时，会执行大量分散、随机的I/O。这种I/O可能很慢。
索引的此种存储机制决定了索引的两种使用情况。记住，有索引并不代表更快的相应速度。

位图索引

– 位图索引是为数据仓库/即席查询环境设计的，在此所有查询要求的数据在系统实现时根本不知道。
– 位图索引特别不适用于OLTP系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。
– 位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针；与B*树结构不同。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。

应用环境

• 位图索引对于相异基数低的数据最为适合（也就是说，与整个数据集的基数相比，这个数据只有很少几个不同的值）。
• 这些列就可以创建位图索引。它们不适合创建B*数索引，因为每个值对应了表中的大量数据。
• 如果有大量即席查询，特别是查询以一种即席方式引用了多列或者生成诸如count之类的聚合，此时位图索引就特别有用。
  比如：
  有一个很大的表，其中有3列：gender、location和age_group。
  – Gender取值为M和F
  – Location取值为1～50
  – Age_group取值为18及以下、19-25、26-30、31-40和41及以上。

查询为以下的即席查询：

Select count(*) from t where gender=‘M’ 
And location in (1,10,20) and age_group =’19-25’;
Select count(*) from t where location in (11, 21, 43);

位图索引在读密集的环境中能很好地工作，但在写密集的环境则不行。原因是：

• 一个位图索引键条目指向多行。
• 如果一个会话修改了所索引的数据，这个索引条目指向的所有行都会被锁定。
• Oracle无法锁定一个位图索引条目中的单独一位，而是会锁定整个位图索引条目。
• 倘若其他修改也需要更新同样的位图索引条目，就会被”关在门外“，大大影响并发性。

基于函数索引

利用基于函数的索引，能够对计算得出的列建立索引，并且在查询中使用这些索引。
利用这种能力，应用可以做：

• 执行大小写无关的搜索或排序；
• 根据复杂的公式进行搜索
• 实现应用自己的函数和运算符，然后在此基础上执行搜索从而高效地扩展SQL语言

使用基于函数的索引的主要原因：

1. 容易实现，并能立即提交一个值；
2. 可以加快现有应用的速度，而不用修改任何逻辑或查询。

基于函数的索引-只对部分行建立索引

• 基于函数的索引除了对使用内置函数的查询有帮助之外，还可以用来有选择地只是对表中的某些行建立索引。
• B*树索引对于完全为NULL的键没有相应的索引条目。此时如果只对表中的某些行建立索引，就可以使用基于函数的索引。

使用基于函数的索引，可以用于保证某种复杂的约束。