Oracle索引

一、索引介绍

1.为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。Oracle中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

如上图所示，数据库在没有索引的情况下，数据分布在硬盘的不同位置上面，读取数据时，摆臂需要前后摆动查找，这样操作非常耗时。如果数据顺序摆放，那么也需要从1到6行按顺序读取，这样就相当于进行了六次IO操作，依旧非常耗时。如果我们不借助任何索引结构帮助我们快速定位数据的话，我们查找Col2=89这条数据，就要逐行去查找、去比较。从Col2=34开始，进行比较，发现不是，继续下一行。我们当前的表只有不到10行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次磁盘I/0才能找到。现在要查找Col2=89这条记录。CPU必须先去磁盘查找这条记录，找到之后加载到内存，再对数据进行处理。这个过程最耗时间的就是磁盘I/O（涉及到磁盘的旋转时间（速度较快）、磁头的寻道时间（速度慢、费时））

假如给数据使用 二叉树 这样的数据结构进行存储，如下图所示：

对字段Col2添加了索引，就相当于在硬盘上为Col2维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个结点存储的是（K，V）结构，key是Col 2，value是该key所在行的文件指针（地址）。比如：该二叉搜索树的根节点就是：（34，0x07）。现在对Col2添加了索引，这时再去查找Col2=89这条记录的时候会先去查找该二叉搜索树（二叉树的遍历查找）。读34到内存，89>34；继续右侧数据，读89到内存，89==89；找到数据返回。找到之后就根据当前结点的value快速定位到要查找的记录对应的地址。我们可以发现，只需要查找两次就可以定位到记录的地址，查询速度就提高了。

这就是我们为什么要建索引，目的就是为了减少磁盘I/0的次数，加快查询速率。

2.索引及其优缺点

索引概述
索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。 这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法 。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个存储引擎，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

优点
1.类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本 ，这也是创建索引最主要的原因。
2.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
3.在实现数据的 参考完整性方面，可以加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
4.在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点
1.创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
2.索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
3.虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

提示：
索引可以提高查询的速度，但是会影响插入记录的速度。在这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

3.Oracle的各种索引

优先使用 B-Tree 索引 + 组合索引，根据查询模式添加函数索引或位图索引。

4.各种索引创建命令

--B-Tree 索引CREATEINDEX idx_name ON table_name (column);--创建命令CREATEINDEX idx_emp_last_name ON employees(last_name);--示例--唯一索引CREATEUNIQUEINDEX idx_name ON table_name (column);--创建命令CREATEUNIQUEINDEX idx_emp_email ON employees(email);--示例--组合索引(复合索引)CREATEINDEX idx_name ON table_name (col1, col2, col3);--创建命令CREATEINDEX idx_emp_dept_job ON employees(department_id, job);--示例--位图索引CREATE BITMAP INDEX idx_name ON table_name (column);--创建命令CREATE BITMAP INDEX idx_emp_gender ON employees(gender);--示例--函数索引CREATEINDEX idx_name ON table_name (FUNCTION(column));--创建命令CREATEINDEX idx_upper_name ON employees(UPPER(last_name));--示例--索引组织表(IOT)CREATETABLE table_name ( col1 PRIMARYKEY, col2,...) ORGANIZATION INDEX;--创建命令CREATETABLE employees_iot ( employee_id NUMBER PRIMARYKEY, last_name VARCHAR2(25), department_id NUMBER) ORGANIZATION INDEX;--示例--位图连接索引CREATE BITMAP INDEX idx_name ON table1(col1)TABLE table2 (join_col)WHERE table2.status='ACTIVE';--创建命令CREATE BITMAP INDEX idx_sales_emp ON sales(sale_id)TABLE employees (employee_id)WHERE employees.status='ACTIVE';--示例--逆序索引CREATEINDEX idx_name ON table_name (column) REVERSE;--创建命令CREATEINDEX idx_emp_id_rev ON employees(employee_id) REVERSE;--示例--本地分区索引CREATEINDEX idx_name ON table_name (col)LOCAL;--创建命令CREATEINDEX idx_sales_date ON sales(sale_date)LOCAL;--示例--全局分区索引CREATEINDEX idx_name ON table_name (col)GLOBALPARTITIONBY RANGE (col)(PARTITION p1 VALUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (2000));--创建命令CREATEINDEX idx_sales_amount ON sales(amount)GLOBALPARTITIONBY RANGE (amount)(PARTITION p1 VALUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (2000));--示例

二、各种索引详解

1.B树索引

Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。Oracle 默认索引类型（未指定类型时自动创建）

特点：
适合与大量的增、删、改（OLTP）
不能用包含OR操作符的查询；
适合高基数的列（唯一值多）
典型的树状结构；
每个结点都是数据块；
大多都是物理上一层、两层或三层不定，逻辑上三层；
叶子块数据是排序的，从左向右递增；
在分支块和根块中放的是索引的范围；

技巧：
索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。

2.唯一索引

B树索引 + UNIQUE 约束（确保列值唯一）
允许一个 NULL（NULL 不参与唯一性比较）
创建主键时自动创建唯一索引

3.组合索引

多列的B树索引
最左前缀原则：查询条件必须包含最左列才能命中索引

4.位图索引

位图索引：专为低基数列设计的特殊索引（如性别、状态）

特点：
不适合高并发写入
非常适合OR操作符的查询；
基数比较少的时候才能建位图索引；

技巧：
对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。
当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。
在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

5.函数索引

在表达式/函数上创建的索引（非原始列）
查询条件必须使用相同函数才能命中索引

可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：

select*from emp where UPPER(job)='MGR';

下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：

select*from emp where job ='MGR';

可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：

createindex EMP$UPPER_JOB on emp(UPPER(job));

尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：
能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗
是否有足够应付额外索引的存储空间？
在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？
基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

6.索引组织表

表数据直接存储在主键索引的叶子节点中（而非单独存储数据块）
无需额外回表操作（查询主键时直接获取数据）

普通表结构：
索引 (B-Tree) → 数据块 (ROWID) → 数据行
IOT 结构：
索引 (B-Tree) → 数据行 (直接存储在叶子节点)

7.位图连接索引

位图索引的扩展，直接关联多表的连接条件
专为数据仓库的星型模型设计（如 fact_table JOIN dimension_table）

8.逆序索引

反转索引键值（如 123 → 321，ABC → CBA）
解决自增ID插入时的索引尾部热点问题（避免频繁修改索引最后一个叶子块）

普通 B-Tree 索引（自增ID）：
1, 2, 3, 4, 5, 6, 7, 8, 9, 10 → 每次插入都修改最后叶子块
逆序索引：
1 → 1, 2 → 2, 3 → 3, 4 → 4, 5 → 5, 6 → 6, 7 → 7, 8 → 8, 9 → 9, 10 → 01 →
插入位置分散在索引各处

9.本地分区索引

索引分区 = 表分区（表分区1 → 索引分区1，表分区2 → 索引分区2）
自动维护：表分区操作（如 ADD PARTITION）会自动同步到索引

表分区：
sales_2023Q1 (2023-01-01 ~ 2023-03-31)
sales_2023Q2 (2023-04-01 ~ 2023-06-30)
本地分区索引：
idx_sales_date_2023Q1 (仅包含 2023Q1 数据)
idx_sales_date_2023Q2 (仅包含 2023Q2 数据)

10.全局分区索引

索引分区独立于表分区（索引分区1可能包含多个表分区的数据）
需手动维护：表分区操作后需重建索引

表分区：
sales_2023Q1 (2023-01-01 ~ 2023-03-31)
sales_2023Q2 (2023-04-01 ~ 2023-06-30)
全局分区索引：
idx_sales_date_p1 (包含 sales_2023Q1 + sales_2023Q2 的部分数据)
idx_sales_date_p2 (包含 sales_2023Q1 + sales_2023Q2 的其他数据)