[Oracle]索引

简介: [Oracle]索引

1、概述

下图红框处是数据表的rowid

其是每行记录的唯一标识

从宏观上说,索引是排序后rowid,其排序不是指升序或降序,而是将rowid按照一定的数据结构进行排列。

先说结论:

rowid本身无任何优化查询的功能,索引可以实现优化依赖于数据结构

1.1 创建

  1. 方式1:当添加主键唯一键时,会自动生成索引;
  2. 方式2:create index 索引名 on 表名(字段名)

1.2 查询

  1. user_indexes:查询用户所有索引;
  2. user_ind_columns:查询用户所有索引对应的字段。

1.3 适用场景

  1. 数据量大;
  2. 结果集所包含记录量占数据表记录量的2% ~ 4%左右;
  3. 经常用作条件多表连接的字段;
  4. 所查询字段的记录范围很广;
  5. 所查询字段包含大量 null,因为索引不包含 null

1.4 不适用场景

  1. 数据量小;
  2. 不常作为条件的字段;
  3. 频繁更新的字段;
  4. 索引字段是条件的一部分时。如:where emp.salary * 2 > 10000,此时索引无效;(原因后续说明)
  5. 条件中包含单行函数时。如:where round(salary) > 10000。(除“基于函数的索引”外,见下文)

1.5 补充说明

  1. 优点:优化查询速度。
  2. 不足:占用内存;索引数量多时难以维护;降低 DML 时性能。
  3. 适用场景:当数据量大、不经常进行 DML 且访问频率高时。(对上述阐述的总结)
  4. 一般情况下,先添加索引,后新增记录;(原因后续说明)
  5. 当条件中包含likeinnot in等范围查询时,索引失效。(下文说明)

2、关于索引构建过程(性能优化原理)

启发源自博文《Oracle索引结构》(转发)。

2.1 构建过程

注:

  1. 下文阐述中,id为数据表其中一个字段,非主键(上文阐述:“在创建主键或唯一键时,会自动创建索引”,这种索引称之为主键索引,即索引建立在主键字段上。实际上,可以在任何某个或某多个字段上建立索引,并且索引有多种,见下文);
  2. 上文阐述:“索引所实现的性能优化依赖于所使用的数据结构”。无论是oraclemysql,索引的数据结构都包括B树B+树这2种。下文以B树这种数据结构作为阐述示例,B树也是索引所使用的主要的数据结构之一。

借用那篇博文中的两张图,便于大家理解。

  1. rowidid取出,组成一个类似entry的数据结构(见第3项中的索引结构图),即一条记录对应一个“entry”。n个“entry”(n条记录)组成一块(叶节点);
    注:“entry”即索引,不过其不是entry,下文简述索引细节。这里说的entry,大家以Map.Entry理解就行。
  2. n个块由某个块(茎节点)管理,此节点记录着所管理的n个块的信息,即索引
  3. 以此类推。。。
  4. 由某个节点(根节点)管理所有的茎节点

2.2 关于 B+树

这是索引常使用的另一种数据结构,是由B树改进而成,相同的是,两者都属于平衡多路查找树(关于“平衡多路查找树”,后续补充,大家先注意“多路”两字即可,便于理解上述的索引构建过程);不同的是(改进措施):

  1. B树中,所有节点都存放索引数据,因此B树型索引的索引不会重复;而在B+树中,“非叶节点”存放的是所管理节点的信息(即下阶节点的索引),而所有的数据都存放在“叶节点”中,包括“索引列值”和rowid,即索引(见序言注释),因此所有“非叶节点”的索引值最终一定会全部出现在“叶节点”中(即“非叶节点”中的索引会重复1次);
  2. B+树中,会将所有“叶节点”都连接起来,形成“链表”,按索引升序排序,这样是为了便于范围查找,而B树不会。
    注:便于“范围查找”的原理:程序读取数据库的途径是IO流,对于已建立索引的数据表,一次IO,就是将B树/B+树的一个节点读入内存。若所查找的数据分布在多个节点中(范围查找即查找多个节点),则需要多次执行IO。因此,B+树的“链表”可以减少IO的次数。

2.3 补充说明

  1. B树高度较低。如:在一个有几百亿条记录的数据表上建立索引,“树”高度不过 20 余层;
  2. 数据库最小单位是,最小分配单位是存放一个段至少需要一个区;(最后这点我目前也不明其意)
  3. 索引查找不一定比全表扫描(无索引)效率高的原因:
    全表扫描一次可读多个块,而索引查找一次只能读一个。而索引查找的记录可能分散分布于多个块,即索引查找的读取次数可能较多于全局扫描。上文中第1.5项的第5点就是这种情况,因此致使索引失效。
    注:以上阐述基于B树,而B+树的其中一个作用就是应对这种情况。

2.4 索引结构细节

大家看另1张图。

可见,索引值由Index entry headerKey column lengthKey column value(列值,即上文中的“索引列值”)、ROWID组成。

3、关于索引分类

3.1 唯一索引

主键或唯一键创建时自动生成;

手动创建:

create unique index 索引名 on 表名(字段名)

3.2 组合索引

当创建索引时,指定多列,就是组合索引。其中,根据上文【构建过程】,可知组合索引的每一个索引列值都包含组合列的各个数据。

注意: 只有当条件(where)中包含此组合所有或大部分字段时,索引才能生效(后续详述)。

3.3 反向键索引

反转索引列值的每个字节,从而实现索引值的均匀分配避免B+树不平衡。(注:“不平衡”是指某个/某些分支较其他同高度分支较长,致使分支“倾斜”的情况)

常用宇连续增长、且索引列值前段相同的字段。如:索引列值为100110021003,经反向键索引转化后变为100120013001

手动创建:

create index 索引名 on 表名(字段名) reverse

3.4 位图索引

适合创建于“低基数”的字段,如:性别国家编号。(注:“低基数”指取值固定取值范围很小、且不经常更新

上文阐述:“索引值由索引列值(记录/数据)和rowid组成”,而位图索引不是。

位图索引不直接存储rowid,而是存储字节位rowid映射,目的是减少空间占用

因此,位图索引适用于数据仓库,不适用于OLTP(关于OLTP,后续说明)。

手动创建:create bitmap index 索引名 on 表名(字段名)

3.5 基于函数的索引

指基于1/n个字段上的函数或表达式创建的索引。简言之,在创建此类索引时,使用了表达式或函数。

限制:表达式中不能使用聚合函数(如:count、max、sum),比较常用的是单行函数;字段类型不能是lob(clob、blob);且当前用户必须拥有query rewrite权限。

示例:

需求:查询用户名,用“基于函数的索引”进行优化。
语句:`... where upper(user_name) = ‘xx’ ...`。
手动创建:`create index 索引名 on 表名(upper(字段名))`。

4、最后

本文是“纯阐述”,可谓是0示例,这样难免缺乏可信性可行性。因此,本文的目的是为了让大家对oracle索引所实现的性能优化原理、以及分类有一个大致的了解。

本文完结。

相关文章
|
8天前
|
SQL Oracle 关系型数据库
Oracle-index索引解读
Oracle-index索引解读
79 0
|
8天前
|
存储 Oracle 关系型数据库
Oracle索引知识看这一篇就足够
Oracle索引知识看这一篇就足够
|
8天前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引:数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率,如同多维导航仪。在同一表上创建针对不同列的多个索引,加速检索过程。虽然过多索引会增加存储和维护成本,但合理选择和使用索引策略,结合位图、函数索引等高级特性,能优化查询,应对复杂场景。数据管理员应善用这些工具,根据需求进行索引管理,支持企业数据分析。
|
6月前
|
索引
Oracle-序列、索引和同义词
Oracle-序列、索引和同义词
28 0
|
6月前
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
211 0
|
7月前
|
存储 Oracle 关系型数据库
9-6 Oracle 管理索引
9-6 Oracle 管理索引
|
SQL Oracle 关系型数据库
Oracle-表分析和索引分析解读
Oracle-表分析和索引分析解读
146 0
|
存储 SQL Oracle
Oracle索引
Oracle索引
83 0