oracle-数据库的索引-index-详解_oracle index type

概述

Oracle-OLAP和OLTP解读

Oracle-index索引解读

Oracle-分区表解读

Oracle-锁解读

Oracle-等待事件解读

Oracle-procedure/cursor解读

---

---

---

• 索引是数据库对象之一，用于加快数据的检索

• 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式,索引直接指向包含所查询值的行的位置，减少磁盘I/O,，从而提高检索效率

• 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表,与所索引的表是相互独立的物理结构

• 索引一旦建立，Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引,不会对表产生影响.

• 索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

• oracle创建主键时会自动在该列上创建索引

---

为什么需要索引

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。

然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升。

---

什么是索引

索引是对记录按照多个字段进行排序的一种方式。对表中的某个字段建立索引会创建另一种数据结构，其中保存着字段的值，每个值又指向与它相关的记录。这种索引的数据结构是经过排序的，因而可以对其执行二分查找。

索引的缺点是占用额外的磁盘空间。所以如果为同一个表中的很多字段都建立索引，那这个文件可能会很快膨胀到文件系统规定的上限。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

---

索引的原理

首先，来看一个示例数据库表的模式：

注意：这里用char而不用varchar是为了精确地描述数据占用磁盘的大小。

这个示例数据库中包含500万行记录，而且没有建立索引。

接下来我们就分析针对这个表的两个查询：一个查询使用id（经过排序的键字段），另一个查询使用firstName（未经排序的非键字段）。

示例分析一

对于这个拥有r = 5 000 000条记录的示例数据库，在磁盘上要为每条记录分配 R = （4+50+50+100）204字节的固定存储空间（4个字段所占空间的总和）。

---

默认的数据库块大小为 B = 1024字节。于是，我们可计算出这个表的分块因数为 bfr = (B/R) = 1024/204 = 5，即磁盘上每个数据块保存5条记录。那么，保存整个表所需的数据块数就是 N = (r/bfr) = 5000000/5 = 1 000 000。

---

使用线性查找搜索id字段——这个字段是键字段（每个字段的值唯一），需要访问 N/2 ＝ 500 000个数据块才能找到目标值。不过，因为这个字段是经过排序的，所以可以使用二分查找法，而这样平均只需要访问log2(1000000 )= 19.93 = 20 个块。显然，这会给性能带来极大的提升。

---

再来看看firstName字段，这个字段是未经排序的，因此不可能使用二分查找，况且这个字段的值也不是唯一的，所以要从表的开头查找末尾，即要访问 N = 1 000 000个数据块。这种情况通过建立索引就能得到改善。

---

如果一条索引记录只包含索引字段和一个指向原始记录的指针，那么这条记录肯定要比它所指向的包含更多字段的记录更小。也就是说，索引本身占用的磁盘空间比原来的表更少，因此需要遍历的数据块数也比搜索原来的表更少。

以下是firstName字段索引的模式：

示例分析二

对于这个拥有r = 5 000 000条记录的示例数据库，每条索引记录要占用 R = 54字节磁盘空间，而且同样使用默认的数据块大小 B = 1024字节。那么索引的分块因数就是 bfr = (B/R) = 1024/54 = 18。最终这个表的索引需要占用 N = (r/bfr) = 5000000/18 = 277 778个数据块。

---

现在，再搜索firstName字段就可以使用索引来提高性能了。对索引使用二分查找，需要访问 log2 277778 = 18.09 = 19个数据块。再加上为找到实际记录的地址还要访问一个数据块，总共要访问 19 + 1 = 20个数据块，这与搜索未索引的表需要访问277 778个数据块相比，不啻于天壤之别。

---

什么时候用索引

创建索引要额外占用磁盘空间（比如，上面例子中要额外占用277 778个数据块），建立的索引太多可能导致磁盘空间不足。因此，在建立索引时，一定要慎重选择正确的字段。

由于索引只能提高搜索记录中某个匹配字段的速度，因此在执行插入和删除操作的情况下，仅为输出结果而为字段建立索引，就纯粹是浪费磁盘空间和处理时间了；这种情况下不用建立索引。

另外，由于二分查找的原因，数据的基数性（cardinality）或唯一性也非常重要。对基数性为2的字段建立索引，会将数据一分为二，而对基数性为1000的字段，则同样会返回大约1000条记录。在这么低的基数性下，索引的效率将减低至线性查找的水平，而查询优化器会在基数性小于记录数的30%时放弃索引，实际上等于索引纯粹只会浪费空间。

另外需要说明： 创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

---

索引的语法

创建索引

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
      ON <schema>.<table_name>
           (<column_name> | <expression> ASC | DESC,
            <column_name> | <expression> ASC | DESC,...)
     TABLESPACE <tablespace_name>
     STORAGE <storage_settings>
     LOGGING | NOLOGGING
     COMPUTE STATISTICS
     NOCOMPRESS | COMPRESS<nn>
     NOSORT | REVERSE
     PARTITION | GLOBAL PARTITION<partition_setting>
 
 
1
2
3
4
5
6
7
8
9
10
11
 
 

  
  
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11

参数说明：

• 1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。

• 2）<column_name> | <expression> ASC |
DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”

• 3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)

• 4）STORAGE：可进一步设置表空间的存储参数

• 5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)

• 6）COMPUTE STATISTICS：创建新索引时收集统计信息

• 7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)

• 8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值

• 9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

修改索引

重命名索引

alter index index_sno rename to bitmap_index;
 
 
1
 
 

  
  
 
 
 
 
1

合并索引

表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低

alter index index_sno coalesce;
 
 
1
 
 

  
  
 
 
 
 
1

重建索引

方式一：删除原来的索引，重新建立索引

方式二：使用rebuild方式

alter index index_sno rebuild [online];
 
 
1
 
 

  
  
 
 
 
 
1

rebuild 和 rebuild online的区别

alter index rebuild online:实质上是扫描表而不是扫描现有的索引块来实现索引的重建

alter index rebuild:只扫描现有的索引块来实现索引的重建。

rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等待。也就是说在执行前仍会产生阻塞, 应该避免排他锁.

而rebuild index在执行期间会阻塞DML操作, 但速度较快.

两者重建索引时的扫描方式不同，
rebuild用的是“INDEX FAST FULL SCAN”，
rebuild online用的是“TABLE ACCESS FULL”;
即rebuild index是扫描索引块,而rebuild index online是扫描全表的数据块.

删除索引

drop index index_sno;
 
 
1
 
 

  
  
 
 
 
 
1

查看索引

查询all_indexes
select index_name, index_type, tablespace_name, uniqueness
  from all_indexes
 where table_name = 'tablename';
 
或者查询user_indexes 
select a.* from user_indexes a ;
 
 
1
2
3
4
5
6
7
 
 

  
  
 
 
 
 
1
2
3
4
5
6
7

B树索引的index_type为 NORMAL；而位图索引的index_type类型值为BITMAP

索引分类

• B-树索引（默认类型）
• 位图索引
• HASH索引
• 索引编排表
• 反转键索引
• 基于函数的索引
• 分区索引
• 本地和全局索引

B树索引

说明

B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。

B树索引最多可以包括32列。

特点

1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值

2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

3.能够适应精确查询、模糊查询和比较查询

创建

B树索引的单一索引

单一索引:

Create Index <Index-Name> On <Table_Name>(Column_Name)
 
 
1
 
 

  
  
 
 
 
 
1

B树索引的 复合索引

复合索引:

 Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。
 
 
1
 
 

  
  
 
 
 
 
1

　select * from emp where deptno=66 and job='sals' ->走索引。
 
　select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引
 
　select * from emp where deptno=66 ->走索引。
 
　select * from emp where job='sals' ->进行全表扫描、不走索引。
 
 
1
2
3
4
5
6
7
8
 
 

  
  
 
 
 
 
1
2
3
4
5
6
7
8

　　如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。
　　
　即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列

适合使用场景

• 适合与大量的增、删、改（OLTP）
• 不能用包含OR操作符的查询；
• 列基数（列不重复值的个数）大时适合使用B数索引

---

位图索引

说明

创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换.

位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。

它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。
尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

比如：某个表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

特点

• 适合与决策支持系统；
• 做UPDATE代价非常高；
• 非常适合OR操作符的查询；
• 基数比较少的时候才能建位图索引；

创建

例子：

Create bitmap Index <Index-Name> On <Table_Name>(Column_Name)
 
 
1
 
 

  
  
 
 
 
 
1

适合使用场景

• 对于基数小的列适合建立位图索引（例如性别等）

注意事项

• 建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。位图索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。

  位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。

  SELECT语句不会受到这种锁定问题的影响。

• 基于规则的优化器不会考虑位图索引。

• 当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。
• 位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。
• 位图索引不能被声明为唯一索引。
• 位图索引的最大长度为30。

---

函数索引

说明

• 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度

• 函数索引既可以使用B树索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引

• 函数索引中可以使用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等

• 基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

创建

例子：

CREATE INDEX index ON table (FUNCTION(column));
 
举例：
select * from student where upper(name) ='XGJ';
 
 
1
2
3
4
 
 

  
  
 
 
 
 
1
2
3
4

---

分区索引

分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。

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

可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。

不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性

有两种类型的分区索引：本地分区索引和全局分区索引。

每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。

把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理这条语句。

---

创建索引的一些规则

1. 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。
这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

2. 把索引与对应的表放在不同的表空间。
当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

3. 最好使用一样大小的块。
Oracle默认五块，读一次I/O，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。

4. 如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。

5. 建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。

索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。

6. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

7. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

8. 小表不要建立索引

9. 对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引

10. 列中有很多空值，但经常查询该列上非空记录时应该建立索引

11. 经常进行连接查询的列应该创建索引

12. 使用create index时要将最常查询的列放在最前面

13. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

14. 限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

15 .对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也应该建立索引。

---

索引失效的情况

1.使用不等于操作符（not 、<>、!=）

oracle碰到not或者 <> !=会停止使用索引，而采用全表扫描

select * from student where not (score=100);
 
select * from student where score <> 100;
 
--替换为
 
select * from student where score>100 or score <100
 
 
1
2
3
4
5
6
7
 
 

  
  
 
 
 
 
1
2
3
4
5
6
7

通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

2. 使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同样会限制索引的使用。
因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL。
如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

select * from student where score is not null;
 
 
1
 
 

  
  
 
 
 
 
1

索引上使用空值比较将停止使用索引.

3.使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）

select empno,ename,deptno from emp  where  trunc(hiredate)='01-MAY-81';
 
--把上面的语句改成下面的语句，这样就可以通过索引进行查找。
select empno,ename,deptno from emp where  hiredate<(to_date('01-MAY-81')+0.9999);
 
 
1
2
3
4
 
 

  
  
 
 
 
 
1
2
3
4

4.索引列上进行计算

索引列上不能进行计算

SELECT Col FROM tbl WHERE col / 10 > 10
 
 
1
 
 

  
  
 
 
 
 
1

则会使索引失效，应该改成

SELECT Col FROM tbl WHERE col > 10 * 10
 
 
1
 
 

  
  
 
 
 
 
1

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

假设account_number是一个VARCHAR2类型,在account_number字段上有索引。
下面的语句将执行全表扫描：

 select bank_name,address,city,state,zip from banks where account_number = 990354;
 
 
1
 
 

  
  
 
 
 
 
1

Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用。

改成下面的查询就可以使用索引：

 select bank_name,address,city,state,zip from banks where account_number ='990354';
 
 
1
 
 

  
  
 
 
 
 
1

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

6.通配符在搜索词首出现时，oracle不能使用索引

--我们在name上创建索引；
 
create index index_name on student('name');
 
--下面的方式oracle不适用name索引
 
select * from student where name like '%xgj%';
 
--如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
 
select * from student where name like 'xgj%';
 
 
1
2
3
4
5
6
7
8
9
10
11
 
 

  
  
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11

7.用UNION替换OR(适用于索引列)

union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。

由于是追加操作，需要两个结果集的列数应该是相关的，并且相应列的数据类型也应该相当的。

union 返回两个结果集，同时将两个结果集重复的项进行消除。

如果不进行消除，用UNOIN ALL.

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效.

如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

在下面的例子中, LOC_ID 和REGION上都建有索引.

　　高效:

　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10
　　UNION
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE REGION = “MELBOURNE”
 
 
1
2
3
4
5
6
7
 
 

  
  
 
 
 
 
1
2
3
4
5
6
7

　　低效:
　

　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
 
 
1
2
3
 
 

  
  
 
 
 
 
1
2
3

　　如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

8. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

在子查询中, NOT IN子句将执行一个内部的排序和合并.

无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).

为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例子：

高效:

SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
 
 
1
 
 

  
  
 
 
 
 
1

低效:

SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
 
 
1
 
 

  
  
 
 
 
 
1

Sql优化的一点建议

Sql 优化:

当Oracle数据库拿到SQL语句时，其会根据查询优化器分析该语句，并根据分析结果生成查询执行计划。
也就是说，数据库是执行的查询计划，而不是Sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。

其中基于规则的查询优化器在10g版本中消失。

对于规则查询，其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

• 1、先执行From ->Where ->Group By->Order By

• 2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。

• 3、对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾，而对于多表之间的连接，则写在之前。
  因为这样进行连接时，可以去掉大多不重复的项。

• 4、SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间