Oracle之SQL优化-索引的基本原理(一)

Oracle之SQL优化-索引的基本原理(一)。

1、索引的基本概念:

(1)创建索引的目的:

以索引小的IO换取表的大IO。

何时创建索引:

当访问的数据块少于表中20%的数据时,建议使用索引。

(2)索引的副作用

会使insert、delete速度变慢(索引个数多的话速度就会慢)。

对于update语句,需要先判断是否要修改索引列。

(3)单字段索引和组合索引

在Oracle9i之前,只有使用到索引的前导引用时才可以使用组合索引。

现在可以通过索引的跳跃式扫描来使用非前导引用的组合索引。

(4)Oracle的rowid

rowid是直接指向单行的线路图,不同的版本rowid的结构不同,不能对rowid进行硬编码。

具体可看上面的原理图。

2、使用索引时需要注意

(1)条件中使用不等于操作(<>,!=),将不会走索引,而是走全表扫描。

(2)条件中使用IS NULL 或者IS NOT NULL,也不会走索引,走全表扫描。字段可以使用缺省值。

(3)条件中对字段使用函数,也不会走索引,除非是函数索引。

(4)比较不匹配的数据类型时,oracle可以帮我们自动做数据类型的转换,但是oracle还是建议我们最好

使用转换函数(to_char()、to_date()、to_number()等)做显示的转换。

3、索引的选择性

(1)选择性越高,索引返回的数据就越少。

(2)索引的群集因子越高,表中的数据按照这个索引字段越有序,表中返回的数据块就越少。

(3)索引的二元高度的每个级别需要单独的io,如果索引中被删除的行接近20~30%,需要重建索引。

降低二元高度;数据库快的尺寸越大,索引的二元高度就越小。

(4)索引的空间

索引的空间被重复利用的概率很小,有时索引的空间比表大。

4、索引的扫描方式

(1)全局快速扫描索引(fast full scan):

使用hint提示中的INDEX_FFS。索引相对于表的总体大小来说很小。如果在连接中只查询索引的

连接键列时,通常都会执行快速全索引扫描。

(2)跳跃式扫描索引(skip scan):

允许优化器使用组合索引,即使索引的前导列没有出现在where子句里面。比全索引扫描要快得多。

(3)索引的全局扫描(full scan);

(4)索引的范围扫描(range scan)。

5、索引的类型及特点

(1)B——索引

如果索引的值都在索引中,就可以不访问表,从而减少IO量。

(2)位图索引

适用于DSS系统,他使用较少基数(唯一键数目)列访问非常大的表。

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

B-树索引的索引值中包含rowid,这样oracle就可以在行级别上锁定索引。

位图索引被存储为压缩的索引值,其中包含一个范围的rowid,因此oracle必须针对一个给定值锁

定所有范围内的rowid。这种锁定可能在某些dml语句中造成死锁。

位图索引的限制:

a、基于代价的优化

b、当执行alter table 语句,并修改包含位图索引的列时,位图索引会失效。

c、不能用于任何类型的完整性检查。

d、不能被声明为唯一索引。

注意:不要在繁重的oltp环境中使用位图索引。

创建位图索引

 create bitmap index emp_sex on employees(sex)
 tablespace users;     

(3)Hash 索引

使用hash 索引必须使用hash群集。

(4)索引编排表

又称为索引组织表,只有索引段,没有数据段。

(5)反转键索引

数据1234就被存储为4321。不能对位图索引和索引编排表进行反转键处理。

此类型的索引可能比b-索引要慢2.5~3倍。

一般适用于磁盘数很少而插入很多的情况。

创建反序索引

 create unique index order_reinx on orders(order_num,order_date)
 tablespace users reverse;

(6)基于函数的索引

数据库的参数:QUERY_REWRITE_ENABLED = TRUE

创建函数索引(函数索引即可以是普通的B树索引,也可以是位图索引)

 create index emp_substr_empno on employees(substr(empno,1,2)) 
 tablespace users;

(7)分区索引

本地索引:有前缀和无前缀。

全局索引:有前缀和无前缀。

6、索引的快速重建

锁表的情况

 alter index index_name rebuild; 

不锁表的情况

 alter index indx_name rebuild online;     

注:

B树索引:在B树的叶节点中存储索引字段的值与ROWID。

唯一索引和不唯一索引都只是针对B树索引而言

复合索引:Oracle最多允许包含32个字段的复合索引。

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐