索引介绍
为什么使用索引
索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教科书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。Oracle 中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。
如上图所示，数据库在没有索引的情况下，数据分布在硬盘的不同位置上面，读取数据时，摆臂需要前后摆动查找，这样操作非常耗时。如果数据顺序摆放，那么也需要从 1 到 6 行按顺序读取，这样就相当于进行了六次 IO 操作，依旧非常耗时。如果我们不借助任何索引结构帮助我们快速定位数据的话，我们查找 Col2=89 这条数据，就要逐行去查找、去比较。从 Col2=34 开始，进行比较，发现不是，继续下一行。我们当前的表只有不到 10 行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次磁盘 I/O 才能找到。现在要查找 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/O 的次数，加快查询速率。
索引及其优缺点
索引概述 索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简单理解为'排好序的快速查找数据结构'，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。
索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少 16 个存储引擎，总索引长度至少为 256 字节。有些存储引擎支持更多的索引数和更大的索引长度。
优点
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本，这也是创建索引最主要的原因。
- 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
- 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
- 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了 CPU 的消耗。
缺点
- 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
- 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
因此，选择使用索引时，需要综合考虑索引的优点和缺点。
提示： 索引可以提高查询的速度，但是会影响插入记录的速度。在这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。
Oracle 的各种索引
优先使用 B-Tree 索引 + 组合索引，根据查询模式添加函数索引或位图索引。
| 索引类型 | 特点 | 适用场景 | 创建命令 | 备注 |
|---|---|---|---|---|
| B-Tree 索引 (默认索引类型) | - 平衡树结构 - 适合等值查询/范围查询 - 适合高基数列 - 默认索引类型 | 主键查询、单列查询、多列查询 | CREATE INDEX idx_name ON table_name (column);示例： CREATE INDEX idx_emp_last_name ON employees(last_name); | Oracle 默认索引类型（非唯一） |
| 唯一索引 | - 确保列值唯一 - 允许 NULL（但仅一个 NULL） - 创建主键时自动创建 | 保证列唯一性（如邮箱、身份证号） | CREATE UNIQUE INDEX idx_name ON table_name (column);示例： CREATE UNIQUE INDEX idx_emp_email ON employees(email); | B 树结构，B 树索引 + UNIQUE 约束 |
| 组合索引 (复合索引) | - 多列的 B 树索引 - 最左前缀原则（查询必须包含最左列） - 适合多条件查询 | WHERE dept_id = 10 AND job = 'Manager' 等多条件查询 | CREATE INDEX idx_name ON table_name (col1, col2, col3);示例： CREATE INDEX idx_emp_dept_job ON employees(department_id, job); | B 树结构，B 树索引 + 多列 |
| 位图索引 | - 用位图（bit array）存储数据 - 专为低基数列设计（如性别、状态） - 适合数据仓库（DWH） - 不适合高并发写入 | 低基数列查询（如 gender = 'M'）数据仓库场景 | CREATE BITMAP INDEX idx_name ON table_name (column);示例： CREATE BITMAP INDEX idx_emp_gender ON employees(gender); | |
| 函数索引 | - 在表达式/函数上创建索引 - 查询必须使用相同函数才能命中 - 适合 UPPER(), LOWER() 等函数 | WHERE UPPER(last_name) = 'SMITH' 等函数查询 | CREATE INDEX idx_name ON table_name (FUNCTION(column));示例： CREATE INDEX idx_upper_name ON employees(UPPER(last_name)); | |
| 索引组织表 (IOT) | - 表数据存储在索引中 - 消除回表操作 - 适合主键查询为主场景 - 必须定义主键 | 主键查询（如 WHERE employee_id = 100）高性能读取场景 | CREATE TABLE table_name (col1 PRIMARY KEY,col2,...) ORGANIZATION INDEX;示例： CREATE TABLE employees_iot ( employee_id NUMBER PRIMARY KEY, last_name VARCHAR2(25), department_id NUMBER) ORGANIZATION INDEX; | |
| 位图连接索引 | - 加速连接查询（如 table1.join_col = table2.join_col）- 专为星型模型设计 - 通常用于数据仓库 | 数据仓库星型模型连接查询 如 sales JOIN employees | 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'; | |
| 逆序索引 | - 反转索引键值（如 '123' → '321'） - 解决索引尾部热点问题 - 提升自增 ID 写入性能 | 自增 ID 插入（如 employee_id 递增）高并发写入场景 | CREATE INDEX idx_name ON table_name (column) REVERSE;示例： CREATE INDEX idx_emp_id_rev ON employees(employee_id) REVERSE; | |
| 本地分区索引 | - 索引分区 = 表分区 - 默认分区索引类型 - 适合分区表 | 分区表的查询（如 WHERE sale_date BETWEEN ...） | CREATE INDEX idx_name ON table_name (col) LOCAL;示例： CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL; | |
| 全局分区索引 | - 索引分区独立于表分区 - 适合范围查询 - 需维护分区 | 全局范围查询（如 WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'） | CREATE INDEX idx_name ON table_name (col)GLOBAL PARTITION BY RANGE (col) (PARTITION p1 VALUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (2000)); <br>示例：<br>CREATE INDEX idx_sales_amount ON sales(amount)<br>GLOBAL PARTITION BY RANGE (amount) (<br> PARTITION p1 VALUES LESS THAN (1000),<br> PARTITION p2 VALUES LESS THAN (2000)<br>); |
各种索引创建命令
-- B-Tree 索引
CREATE INDEX idx_name ON table_name (column);
-- 示例
CREATE INDEX idx_emp_last_name ON employees(last_name);
-- 唯一索引
CREATE UNIQUE INDEX idx_name ON table_name (column);
-- 示例
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
-- 组合索引 (复合索引)
CREATE INDEX idx_name ON table_name (col1, col2, col3);
-- 示例
CREATE INDEX 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);
-- 函数索引
CREATE INDEX idx_name ON table_name (FUNCTION(column));
-- 示例
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));
-- 索引组织表 (IOT)
CREATE TABLE table_name (
col1 PRIMARY KEY,
col2,
...
) ORGANIZATION INDEX;
-- 示例
CREATE TABLE employees_iot (
employee_id NUMBER PRIMARY KEY,
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';
-- 逆序索引
CREATE INDEX idx_name ON table_name (column) REVERSE;
-- 示例
CREATE INDEX idx_emp_id_rev ON employees(employee_id) REVERSE;
-- 本地分区索引
CREATE INDEX idx_name ON table_name (col) LOCAL;
-- 示例
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;
-- 全局分区索引
CREATE INDEX idx_name ON table_name (col)
GLOBAL PARTITION BY RANGE (col) (
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000)
);
-- 示例
CREATE INDEX idx_sales_amount ON sales(amount)
GLOBAL PARTITION BY RANGE (amount) (
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000)
);
索引详解
B 树索引
Oracle 数据库中最常见的索引类型是 b-tree 索引，也就是 B-树索引，以其同名的计算科学结构命名。Oracle 默认索引类型（未指定类型时自动创建）
特点：
- 适合与大量的增、删、改（OLTP）
- 不能用包含 OR 操作符的查询；
- 适合高基数的列（唯一值多）
- 典型的树状结构；
- 每个结点都是数据块；
- 大多都是物理上一层、两层或三层不定，逻辑上三层；
- 叶子块数据是排序的，从左向右递增；
- 在分支块和根块中放的是索引的范围；
技巧： 索引列的值都存储在索引中。因此，可以建立一个组合 (复合) 索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了 I/O 量。
唯一索引
B 树索引 + UNIQUE 约束（确保列值唯一） 允许一个 NULL（NULL 不参与唯一性比较） 创建主键时自动创建唯一索引
组合索引
多列的 B 树索引 最左前缀原则：查询条件必须包含最左列才能命中索引
位图索引
位图索引：专为低基数列设计的特殊索引（如性别、状态）
特点：
- 不适合高并发写入
- 非常适合 OR 操作符的查询；
- 基数比较少的时候才能建位图索引；
技巧： 对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女 (基数仅为 2)。位图对于低基数 (少量的不同值) 列来说非常快，这是因为索引的尺寸相对于 B 树索引来说小了很多。因为这些索引是低基数的 B 树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。 当大多数条目不会向位图添加新的值时，位图索引在批处理 (单用户) 操作中加载表 (插入操作) 方面通常要比 B 树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。 在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。
函数索引
在表达式/函数上创建的索引（非原始列） 查询条件必须使用相同函数才能命中索引
可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用 JOB 列上的索引，除非它是基于函数的索引：
select * from emp where UPPER(job)='MGR';
下面的查询使用 JOB 列上的索引，但是它将不会返回 JOB 列具有 Mgr 或 mgr 值的行：
select * from emp where job ='MGR';
可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式 UPPER(job) 创建索引，而不是直接在 JOB 列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));
尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：
- 能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗
- 是否有足够应付额外索引的存储空间？
- 在每列上增加的索引数量会对针对该表执行的 DML 语句的性能带来何种影响？
- 基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE 和 DELETE 语句的执行就会花费越多的时间。
索引组织表
表数据直接存储在主键索引的叶子节点中（而非单独存储数据块） 无需额外回表操作（查询主键时直接获取数据）
普通表结构： 索引 (B-Tree) → 数据块 (ROWID) → 数据行 IOT 结构： 索引 (B-Tree) → 数据行 (直接存储在叶子节点)
位图连接索引
位图索引的扩展，直接关联多表的连接条件 专为数据仓库的星型模型设计（如 fact_table JOIN dimension_table）
逆序索引
反转索引键值（如 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 → 插入位置分散在索引各处
本地分区索引
索引分区 = 表分区（表分区 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 数据)
全局分区索引
索引分区独立于表分区（索引分区 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 的其他数据)
