数据库使用-oracle索引的创建和分类

数据库使用-oracle索引的创建和分类

索引是数据库中一种可选的数据结构，她通常与表或簇相关。用户可以在表的一列或数列上建立索引，以提高在此表上执行 SQL 语句的性能。就像本文档的索引可以帮助读者快速定位所需信息一样，Oracle 的索引提供了更为迅速地访问表数据的方式。正确地使用索引能够显著的减少磁盘 I/O。

Oracle 提供了多种类型的索引，可以互为补充地提升查询性能：

·        平衡树索引（B-tree index）

·        平衡树簇索引（B-tree cluster index）

·        哈希簇索引（hash cluster index）

·        反向键索引（reverse key indexes）

·        位图索引（bitmap index）

·        位图连接索引（bitmap join index）

·        函数索引（function-based index）

 无论索引是否存在都无需对已有的 SQL 语句进行修改。索引只是提供了一种快速访问数据的路径，因此她只会影响查询的执行速度，但是数据在编辑的过程中，会不断去创建或者更新索引，会让整个系统的磁盘开销非常大，进一步会影响整个系统的运行

因此，当我们要创建索引的时候，一定要问自己，这个索引真的有必要创建么？

索引在逻辑上和物理上都与其基表（base table）是相互独立的。用户可以随时创建（create）或移除（drop）一个索引，而不会影响其基表或基表上的其他索引。当用户移除一个索引时，所有的应用程序仍然能够继续工作，但是数据访问速度有可能会降低。作为一种独立的数据结构，索引需要占用存储空间。

        当索引被创建后，对其的维护与使用都是 Oracle 自动完成的。当索引所依赖的数据发生插入，更新，删除等操作时，Oracle 会自动地将这些数据变化反映到相关的索引中，无需用户的额外操作。

        即便索引的基表中插入新的数据，对被索引数据的查询性能基本上能够保持稳定不变。但是，如果在一个表上建立了过多的索引，将降低其插入，更新，及删除的性能。因为 Oracle 必须同时修改与此表相关的索引信息。

        优化器可以使用已有的索引来建立（build）新的索引。这将加快新索引的建立速度。

唯一索引和非唯一索引

索引（index）可以是唯一（unique）的或非唯一（nonunique）的。在一个表上建立唯一索引（unique index）能够保证此表的索引列（一列或多列）不存在重复值。而非唯一索引（nonunique index）并不对索引列值进行这样的限制。
    Oracle 建议使用 CREATE UNIQUEINDEX 语句显式地创建唯一索引（unique index）。通过主键（primary key）或唯一约束（unique constraint）来创建唯一索引不能保证创建新的索引，而且用这些方式创建的索引不能保证为唯一索引。

复合索引

复合索引（composite index）（也被称为连结索引（concatenated index））是指创建在一个表的多列上的索引。复合索引内的列可以任意排列，她们在数据表中也无需相邻。

        如果一个 SELECT 语句的 WHERE 子句中引用了复合索引（composite index）的全部列（all of the column）或自首列开始且连续的部分列（leading portion of thecolumn），将有助于提高此查询的性能。因此，索引定义中列的顺序是很重要的。大体上说，经常访问的列（most commonly accessed）或选择性较大的列（most selective）应该放在前面。

        一个常规的（regular）复合索引（composite index）不能超过 32 列，而位图索引（bitmap index）不能超过 30 列。索引中一个键值（key value）的总长度大致上不应超过一个数据块（data block）总可用空间的一半。

索引和键

        索引（index）与键（key）是不同的概念，但是这两个术语经常被混用。索引是在数据库中实际存储的数据结构，用户可以使用 SQL 语句对其进行创建（create），修改（alter），或移除（drop）。索引提供了一种快速访问表数据的途径。而键只是一个逻辑概念。键的概念主要在 Oracle 的完整性约束（integrity constraint）功能中使用，完整性约束用于保证数据库中的业务规则（business rule）。

        因为 Oracle 也会使用索引（index）来实现某些完整性约束（integrity constraint），因此索引与键（key）这两个术语经常被混用。注意不要将二者混淆。

索引和空值

对于一个数据表的两行或多行，如果其索引列（key column）中全部非空（non-NULL）的值完全相同（identical），那么在索引中这些行将被认为是相同的；反之，在索引中这些行将被认为是不同的。因此使用 UNIQUE 索引可以避免将包含 NULL 的行视为相同的。以上讨论并不包括索引列的列值（column value）全部为 NULL 的情况。

        Oracle 不会将索引列（key column）全部为 NULL 的数据行加入到索引中。不过位图索引（bitmap index）是个例外，簇键（cluster key）的列值（column value）全部为 NULL 时也是例外。

函数索引

        如果一个函数（function）或表达式（expression）使用了一个表的一列或多列，则用户可以依据这些函数或表达式为表建立索引，这样的索引被称为函数索引（Function-Based Index）。函数索引能够计算出函数或表达式的值，并将其保存在索引中。用户创建的函数索引既可以是平衡树类型（B-tree index）的，也可以是位图类型（bitmap index）的。

        用于创建索引的函数可以是一个数学表达式（arithmetic expression），也可以是使用了 PL/SQL 函数（PL/SQL function），包函数（package function），C 外部调用（C callout），或 SQL 函数（SQL function）的表达式。用于创建索引的函数不能包含任何聚合函数（ggregate function），如果为用户自定义函数，则在声明中必须使用 DETERMINISTIC关键字。如果在一个使用对象类型（object type）的列上建立函数索引，则可以使用此对象的方法（method）作为函数，例如此对象的 map 方法。用户不能在数据类型为 LOB，REF，或嵌套表（nested table）的列上建立函数索引，也不能在包含 LOB，REF，或嵌套表等数据类型的对象类型列上建立函数索引。

使用函数索引

如果一个 SQL 语句的 WHERE 子句中使用了函数，那么建立相应的函数索引（function-based index）是提高数据访问性能的有效机制。表达式（expression）的结果经过计算后将被存储在索引中。但是当执行 INSERT 和 UPDATE 语句时，Oracle 需要进行函数运算以便维护索引。

        例如，如果用户创建了以下函数索引：

        CREATE INDEX idx ON table_1 (a + b * (c – 1),a, b);

        当 Oracle 处理如下查询时就可以使用之前建立的索引：

        SELECT a FROM table_1 WHERE a + b * (c – 1)< 100;

        使用 UPPER(column_name) 或 LOWER(column_name) 函数建立函数索引（function-based index）有助于与大小写无关（case-insensitive）的查询。例如创建以下函数索引：

        CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

        有助于提高以下查询的性能：

        SELECT * FROM employees WHEREUPPER(first_name) = ‘RICHARD’;

函数索引的优化

        用户必须为优化器（optimizer）收集关于函数索引（unction-based index）的统计信息（statistic）。否则处理 SQL 语句时将不会使用此索引。

        当一个查询的 WHERE 子句中含有表达式（expression）时，优化器可以对函数索引（function-based index）进行索引区间扫描（index range scan）。例如以下查询：

        SELECT * FROM t WHERE a + b < 10;

        如果使用表达式（expression） a+b 建立的索引，优化器（optimizer）就能够进行索引区间扫描（index range scan）。如果谓词（predicate，即 WHERE 子句）产生的选择性（selectivity）较低，则对区间扫描极为有利。此外，如果表达式的结果物化在函数索引内（function-based index），优化器将能更准确地估计使用此表达式的谓词的选择性。

        优化器（optimizer）能够将 SQL 语句及函数索引（function-based index）中的表达式解析为表达式树（expression tree）并进行比较，从而实现表达式匹配。这个比较过程是大小写无关的（case-insensitive），并将忽略所有空格（blank space）。

逆序键索引

        用户可以创建逆序键索引（reverse key index），此处的逆序指索引列值（index key value）得各个字节（byte）按倒序排列，而非索引列（index key）逆序排列。在 RAC 环境中，使用这样的排列方式可以避免由于对索引的修改集中在一小部分叶块（leaf block）上而造成的性能下降。通过使索引的键值逆序排列，可以使插入操作分布在索引的全部叶块中。

        使用逆序键索引（reverse key index）后将无法对此索引进行索引区间扫描（index range scanning），因为在逆序键索引中，词汇上（lexically）相邻的索引键（index key）在存储上未必相邻。因此在逆序键索引上只能进行确定键扫描（fetch-by-key scan）或全索引扫描（full-index scan）。

        有些情况下，使用逆序键索引（reverse key index）可以令 RAC 环境下的 OLTP 应用效率更高。例如，为一个 e-mail 应用中的所有邮件进行索引：由于用户可能保存旧的邮件，因此索引必须做到既能快速访问最新邮件，也能快速访问旧邮件。

        用户使用 REVERSE 就可以轻易地创建逆序键索引（reverse key index）。在 CREATE INDEX语句中使用 REVERSE 关键字作为创建索引的选项：

        CREATE INDEX i ON t (a,b,c) REVERSE;

        用户也可以在 REBUILD 子句后添加 NOREVERSE 关键字将一个逆序键索引（reverse key index）转换为常规的索引：

        ALTER INDEX i REBUILD NOREVERSE;

        如果 REBUILD 子句后没有使用 NOREVERSE 关键字，那么逆序键索引（reverse key index）被重建后仍将保持逆序。

位图索引

       位图索引也只一种非常重要的索引，在一些特定的环境中，有非常好的效果，但是非常容易被滥用，使用前后需要仔细验证看是否达到预期效果。

表分析

       有的时候，我们会发现虽然有索引，但是执行计划不走索引，全表扫描跑的哗哗的，这个时候要查看下这个表的最后的分析时间，如果DBA没有及时做表分析，那么索引可能用不到。

select * from user_tables t ;

找到对应的表的last_analyzed时间；如果时间太老或者没有时间，那么说明这张表需要做表分析了。

表分析一般要在业务性能不太繁忙的时候做，先了解一下什么是表分析：

简单的说，就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执行路径。通过对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以优化。

oracle的联机文档描述了analyze的做用：

Use the ANALYZE statement to collect non-optimizer statistics, forexample, to:

--使用analyze功能可以收集一些没有进行优化的统计信息，例如：

Collect or delete statistics about an index or index partition, table ortable partition,

--聚集或删除索引或索引分区，表或表分区

index-organized table, cluster, or scalar object attribute.

--索引表，串，或游标的目标属性等统计信息

Validate the structure of an index or index partition, table or tablepartition, index-organized table,

--增加下列结构的有效性：索引或索引分区，表或表分区，索引表

cluster, or object reference (REF).

--串，目标参照

Identify migrated and chained rows of a table or cluster.

--鉴别一个表或串中已经被移动的抑或被锁住的列。

表分析的语句参考：

analyze table tableName compute statistics;

平衡树结构的优势

平衡树数据结构（B-tree structure）具有以下优势：

·        平衡树（B-tree）内所有叶块（leaf block）的深度相同，因此获取索引内任何位置的数据所需的时间大致相同。

·        平衡树索引（B-tree index）能够自动保持平。

·        平衡树内的所有块容量平均在总容量的 3/4 左右。

·        在大区间（wide range）范围内进行查询时，无论匹配个别值（exact match）还是搜索一个区间（range search），平衡树都能提供较好的查询性能。

·        数据插入（insert），更新（update），及删除（delete）的效率较高，且易于维护键值的顺序（key order）

·        大型表，小型表利用平衡树进行搜索的效率都较好，且搜索效率不会因数据增长而降低。

索引唯一扫描

        索引唯一扫描（index unique scan）是效率最高的数据访问方式之一。从平衡树索引（B-tree index）中获取数据时将采用此种方式。当一个唯一索引（采用平衡树结构）的全部列都包含在查询条件中，且查询体条件表达式均为等号（equality）时，优化器将选择使用索引唯一扫描。

索引区间扫描

        当访问选择性较大的数据（selective data）时 Oracle 常进行索引区间扫描（index range scan）。扫描区间可以是封闭的（bounded）（两端均封闭），也可以是不封闭的（unbounded）（一端或两端均不封闭）。扫描所返回的数据按照索引列的升序进行排列，对于索引值相同的行将按 ROWID 的升序排列。

性能上及存储上的考虑

        键压缩（key compression）能够节约大量存储空间，因此用户可以在一个索引块（index block）内存储更多的索引键（index key），从而减少 I/O，提高性能。

        键压缩（key compression）能够减少索引所需的存储空间，但索引扫描时需要重构（reconstruct）键值（key value），因此增加了 CPU 的负担。此外键压缩也会带来一些存储开销，每个前缀（prefix entry）需要 4 字节（byte）的管理开销。

使用键压缩

        键压缩（key compression）在多种情况下都能够发挥作用，例如：

·        对于非唯一索引（nonunique index），Oracle 会在每个重复的索引键（index key）之后添加 rowid 以便区分。如果使用了键压缩，在一个索引块（index block）内，Oracle 只需将重复的索引键作为前缀（（prefix entry））存储一次，并用各行的 rowid 作为后缀（suffix entry）。

·        唯一索引（nonunique index）中也存在相同的情况。例如唯一索引（stock_ticker，transaction_time）的含义是（项目，时间戳），通常数千条记录中stock_ticker 的值是相同的，但她们对应的 transaction_time 值各不相同。使用了键压缩后，一个索引块中每个 stock_ticker 值作为前缀只需存储一次，而各个transaction_time 值则作为后缀存储，并引用一个共享的 stock_ticker 前缀。

·        在一个包含 VARRAY 或 NESTED TABLE 数据类型（datatype）的索引表（index-organized table）中，这些collection 类型中各个元素（element）的对象标识符（object identifier）是重复的。用户可以使用键压缩以避免重复存储这些对象标识符。

        有些情况无法使用键压缩（key compression）。例如，一个只有一个索引键（index key）的唯一索引（unique index）就无法使用键压缩，因为索引键中不存在可供共享的分组片段（grouping piece）。