LLL的数据库培训-80-第五部分—Oracle11g-Oracle SQL对象管理—第7讲—索引之概念与分类

LLL的数据库培训-80-第五部分—Oracle11g-Oracle SQL对象管理—第7讲—索引之概念与分类

一、索引概念

2、索引的简单理解

索引是一种可选的数据库对象，主要用于提高查询速度。

数据库索引的作用与书籍中索引的作用类似。书籍的索引由目录和页码构成，当在书中查找信息时，先查看索引通常可以获得更快的速度。

获得了索引信息后，就可以在书中直接找到特定的书页，如果要找的内容在书中仅占了几页，需要查看的书页就会最少。

在这种方式中，索引的作用会与要找的内容在书中所占的页数成反比。

换言之，如果要找的内容在书中的每一页 上都有，创建索引就没有任何意义。在这种情况中，不论索引是否存在，只有阅读书中的每一页才能提高效率。

二、创建索引的时机（优化思路）

创建索引，在主观方面，可以分为积极方式创建索引和消极方式创建索引。

1、积极方式创建索引和消极方式创建索引

积极方式：初次开发应用程序时，DBA或开发人员根据情况推测应该为哪些表和列创建索引。

消极方式：当应用程序的性能下降，而且用户投诉性能问题时，DBA或开发人员尝试找出性能低下的SQL查询命令并提供索引解决方案。

2、以积极方式创建索引

在创建新的数据库应用时，处理过程中包含查找主键、唯一键和外键操作。与这些约束有关的列通常就是候选索引。

下面是一些指导原则。

--为每个表定义主键约束。这可以使Oracle在已设置的主键上自动创建索引。
--在主键列之外的、需要起唯一标识作用的列 (或列组合)上创建唯一键约束。 每个唯一键约束都会使Oracle在设置唯一键的列 上自动创建索引。
--在外键列上手动创建索引。这样做可以提高性能，避免特定的锁定问题。

3、以消极方式创建索引

在初次部署应用程序时，DBA或开发人员极少能够精确地创建索引组合。

在大型数据库系统中要做滴水不漏的预测是非常困难的。此外，完成应用程序后数据库中会出现更改( 新增表、新增列、新增约束、新增功能的数据库升级等)。

现实是你必须在数据库中处理无法预料的情况，通过添加索引提高性能。

（1）找出低性能SQL语句流程

--①找到低性能的SQL语句。(有用户投诉某个特定的语句，或者是DBA运行了自动数据库性能监控器（ADDM)或自动负载信息库，找到了耗费资源的SQL语句等）。
--②DBA通过检查表和索引的统计信息,确保过期统计信息不会导致优化器做出错误判断。
--③DBA或开发人员确定无法通过重新编写查询语句解决性能问题。
--④DBA或开发人员检查这些SQL语句，并通过SELECT、FROM和IWHERE子句确定它们访问了哪些表和列。
--⑤DBA或开发人员执行测试，并根据表和列(列组合)提出创建索引建议。

（2）创建索引的情况

--为经常被WHERE子句引用的列上创建索引。当WHERE子句引用了表中的多个列时，可使组合(多列)索引。
--在SELECT子句中创建覆盖索引( 即为所有列创建索引)。
--为在ORDER BY、GROUP BY、UNION和DISTINCT子句中使用的列创建索引。
--还可以在ORDER BY、GROUP BY、UNION和DISTINCT子句使用的列上创建索引。这可以为频繁使用这些SQL语句的查询操作大幅度提高效率。

（3）注意事项

在表上创建的索引越多，DML语句的运行速度就会越慢(因为当列值发生改变时，Oracle需要维护更多的索引)。

在找到正确的索引列前，不要随添加索引。更确切地说，在产品环境中创建索引前，应先验证性能。

三、索引的规划

（1）索引规划

在创建索引前应考虑下列几点，如果选择了错误的索引类型或功能，可能会造成严重的性能隐患。

--索引的类型;
--初始创建索引时和索引数据增长所需的空间;
--在创建索引(大型索引)时对临时表空间的使用;
--表空间的位置;
--命名惯例;
--包含哪些列;
--是使用单列还是使用列组合;
--特殊功能，如PARALLEL子句、NOLOGGING子句、压缩和隐藏索引;
--唯一性;
--对SELECT语句性能产生的影响( 提高 );
--对DML语句性能的影响（降低）

（2）索引创建原则

（3）创建索引时，会使用临时表空间

Oracle 中，创建索引时会使用默认临时表空间。

临时表空间是用于执行排序、连接和其他临时操作的存储空间。

当创建一个索引时，数据库引擎需要执行排序操作以构建索引数据结构。这些排序操作可能需要使用临时表空间来存储中间结果。

当创建索引时，数据库引擎会根据需要在临时表空间中创建临时段（Temporary Segment），用于执行排序操作。临时段在排序操作完成后会被释放。

临时表空间的大小和性能对索引创建操作的影响很大。如果临时表空间的大小不足，可能会导致排序操作失败或性能下降。因此，在创建索引之前，应该确保临时表空间有足够的可用空间，并根据需要进行调整

四、索引的类型

五、各类索引详解

1、B*Tree索引及变体:

（1）B*Tree索引:

这种索引称为“传统”索引。到目前为止，这是 Oracle和大多数其他数据库中最常用的索引。

B*Tree 的构造类似于二叉树，我们通过键值就能快速地访问一行数据，或通过键值的某个范围内来定位多行数据；通过这种索引访问数据一般只需几个IO 就能完成。

不过，这里我们需要重点注意，B*Tree 中的 B 不代表二叉(binary)，而代表平衡(balanced)。B*Tree索引并不是一棵二叉树。

除了普通的 B*Tree 索引之外，以下几种类型也算是 B*Tree索引。

（2）索引组织表(index organized table):

这是一种表，但它的存储也是 B*Tree结构。

堆表( heap table)中的数据行是以一种无组织的方式存储的(哪儿有可用的空间，数据就放在哪儿)，而IOT与之不同，IOT中的数据要按主键来存储和排序。

对应用来说，IOT表现得与“常规”表并无二致,用来访问索引组织表的SQL跟常规表是一样的。

IOT对信息获取空间系统和OLAP应用最为有用。

（3）B*Tree 聚簇索引(B*Tree cluster index)：

这是传统B*Tree索引的一个近似变体。

B*Tree聚簇索引是对聚簇键建立的索引，在传统 B*Tree 索引中键会指向行这一级别;

而B*Tree聚簇不同，一个聚簇键会指向一个块，这个块中包含与这个聚簇键相关的数据。此处赞不讲。

（4）降序索引(descendingindex):

在降序索引中，数据是按“从大到小”的顺序(降序)来排列的，而不是按“从小到大”(升序)。

（5）反向键索引(reverse key index):

这也是B*Tree索引，只不过键中的字节会“反转”。

如果向一个索引中不断插人递增的数据，那么这些数据通过反向键索引会得到更均匀的分布。

例如，如果用一个序列来生成主键，这个序列将生成类似于987 500、987 501、987 502等的值，这是个单调递增序列。

倘若我们使用一个传统的 B*Tree 索引，这些值就可能放在同一个右手块上，这就加剧了对这个数据块的竞争。

如果我们用了反向键索引，Oracle 就会对205789、105789、005789等建立索引。

Oracle将数据放人索引之前，先把待存储数据的字节反转，这样原本可能在索引中相邻放置的数据，在字节反转之后就会相距很远。

通过反转字节，我们就能将索引上的插人分布到多个块上。

2、压缩建索引

可以通过创建压缩键索引来减小索引的存储空间占用。压缩键索引是一种索引类型，它使用一种特定的压缩算法来减小索引的大小。

通过使用压缩键索引，可以在存储数据索引时减小索引的存储空间要求，并提高查询性能。

3、位图索引(bitmap index):

在一颗B*Tree 中，通常索引条目和行之间存在一对一的关系：一个索引条目指向一行数据。

而对于位图索引，一个索引条目会通过一个位图同时指向多行数据。

位图索引适用于高度重复的数据(高度重复是指相对于表中的总行数，数据只有很少的几个不同值)，而且数据通常是只读的。

如果你有一个 100万行的表，它的某个列只有 3个值:Y、N 和NULL，而且你需要经常统计多少行有值 Y，那就很适合为这列建立位图索引。

不过这并不是说如果同一表中的某列有 1000 个不同的值，就不能建立位图索引；你当然能，但是适合不适合就需要仔细考量了。

我们在OLTP 数据库中不应当使用位图索引，这是因为位图索引在OLTP中会有一些并发性的问题。

4、位图联结索引 (bitmap jin index ):

数据的规范化通常是通过表来实现的，而这种索引也能对数据进行逆规范化。

举个例子，EMP和 DEPT表。

有人可能会问这样一个问题:“有多少人在位于波士顿的部门工作?”

EMP 有一个指向 DEPT 的外键，而列 LOC 在表 DEPT 中，如果要统计 LOC 值为Boston 的部门中的员工人数。

我们必须将这两个表联结起来，将 LOC列联结至EMP 记录“才能回答这个问题。

如果使用位图联结索引,我们就能在 EMP 表上直接对LOC列建立索引。

类似于常规位图索引，位图联结索引在 OLTP 系统中也有并发性的问题。

5、基于函数的索引 (fuction-based idex):

这些索引本身就是 B*Tree索引或位图索引，但是储的是某列或某几列的一个计算结果，而不是原始的列数据。

你可以将这种类型的索引看是虚拟列(或派生列)上的索引，换句话说，这个列并不存储在表中。

基于函数的索引可加快形如SELECT*FROM T WHERE FUNCTION(DATABASE_COLUMN) = SOME_VALUE的查询为值FUNCTION(DATABASELCOLUMN)已经提前计算并存储在索引中。

6、应用域索引(application domainindex)

应用域索引是你自己构建和存储的索引，它可能存在 Oracle中，也可能在 Oracle之外。

7、虚拟列索引

Oracle的虚拟列索引是一种特殊的索引类型，它与传统的物理列索引不同。虚拟列索引是基于一个或多个虚拟列而创建的索引，而不是直接在表的物理列上创建索引。

虚拟列是通过一个表达式来定义的，它在查询时动态计算而不是在表中存储。虚拟列可以使用SQL表达式中的函数、运算符和其他列来计算其值。

创建虚拟列索引的主要目的是提高查询性能。通过在虚拟列上创建索引，可以加快查询中对虚拟列的过滤、排序和连接操作。

使用虚拟列索引的一个常见场景是在大型表中使用函数索引。当需要根据函数计算的结果进行查询时，可以使用虚拟列索引来提高查询性能。

虚拟列索引的创建和使用方式与传统索引类似，可以使用CREATE INDEX语句创建虚拟列索引，并在查询中使用该索引来加速查询操作。

需要注意的是，虚拟列索引只能用于查询操作，无法用于对表进行数据修改的操作（例如INSERT、UPDATE）。虚拟列索引也需要根据实际情况进行设计和使用，以确保其对查询性能的提升。

8、虚拟索引

虚拟索引就是在创建时，不分配数据段，不占用segment空间，目的是为了测试sql的执行计划是否走此索引而已。

9、隐藏索引

在 Oracle 数据库中，隐藏索引是一种特殊类型的索引，它被设计为不会被正常的查询优化器使用，只有在特定情况下才会被使用。

隐藏索引可以用来支持特定的数据库管理任务，例如维护、诊断或修复工作。它们可以用于执行如数据完整性检查、数据恢复、导入/导出操作等特定的后台任务。

隐藏索引的创建方式与普通的索引一样，使用 CREATE INDEX 语句进行创建，不同之处在于使用了隐藏索引的特殊选项。例如，在创建索引时使用 NOVISIBLE 关键字可以将其标记为隐藏索引。

隐藏索引的主要特点如下：

1. 不会被正常的查询优化器使用，除非显式指定使用该索引。
2. 对于正常的查询操作，隐藏索引不会被考虑在查询计划中，也不会对查询性能产生影响。
3. 可以用于执行特定的管理任务，例如数据完整性检查、数据恢复等。

10、全局分区索引（分区表阶段讲）

Oracle 的全局分区索引（Global Partitioned Index）是一种索引类型，用于在分区表上进行索引操作。与常规索引不同的是，全局分区索引覆盖整个分区表，而不仅仅是单个分区。

全局分区索引与分区表一起使用，可以提供更好的查询性能和数据管理。它可以在查询时避免全表扫描，只检索必要的分区，并按照定义的索引键进行快速索引访问。

（1）优点

1. 跨多个分区：全局分区索引覆盖整个分区表，可以在多个分区间快速定位数据。
2. 查询性能：使用全局分区索引可以避免全表扫描，提高查询速度。
3. 管理简便：全局分区索引会随着分区表的增减自动管理，索引和分区的关联维护由数据库自动处理。

在创建全局分区索引时，需要确保索引的键与分区表的分区键保持一致。索引的存储结构将与分区表的分区结构相对应，以实现分区级别的索引访问。

可以使用类似于创建常规索引的方式来创建全局分区索引，但需要指定关键字 GLOBAL。例如：

CREATE INDEX idx_name ON partitioned_table (column1, column2) GLOBAL;

总之，Oracle 的全局分区索引是一种用于在分区表上进行索引操作的特殊索引类型，可以提供更好的查询性能和数据管理。它覆盖整个分区表，在查询中避免全表扫描，并与分区表的分区结构相对应进行索引访问。

11、本地分区索引（分区表阶段讲）

Oracle 的本地分区索引（Local Partitioned Index）是一种索引类型，用于在分区表上进行索引操作。与全局分区索引相对，本地分区索引仅覆盖单个分区。

本地分区索引在每个分区内独立存在，以提高查询性能和数据管理。它可以在特定分区中快速定位数据，避免了对不相关的分区进行检索，从而减少了查询的开销。

（1）优点

--分区级索引：本地分区索引仅在单个分区内创建，每个分区都有自己的索引结构。
--查询性能：使用本地分区索引可以充分利用分区表的数据定位，提高查询速度。
--管理灵活：每个分区的索引是独立的，可以针对不同分区进行添加、删除、重建等操作，方便维护和管理。
--在创建本地分区索引时，需要确保索引的键与分区表的分区键保持一致。

可以使用类似于创建常规索引的方式来创建本地分区索引，并在创建语句中指定关键字 LOCAL。例如：

CREATE INDEX idx_name ON partitioned_table (column1, column2) LOCAL;

（2）缺点

--本地分区索引需要在分区表分区后创建，并且在每个分区中都有一个独立的索引。
--当对分区表进行维护操作时，需要考虑索引与分区的一致性和同步更新。

总之，Oracle 的本地分区索引是一种用于在分区表上进行索引操作的特殊索引类型，仅覆盖单个分区。

它在每个分区内独立存在，能够提供更好的查询性能和数据管理，可以在特定分区中快速定位数据。创建时需要与分区表的分区键保持一致，并使用关键字 LOCAL。