印刻数据库

外观

Oracle 索引类型选择指南 ​

索引类型选择概述 ​

选择合适的索引类型是提高 Oracle 数据库性能的关键。不同的索引类型适用于不同的业务场景，选择不当会导致性能下降、资源浪费和维护成本增加。

索引类型选择需要考虑以下因素：

  • 业务查询模式
  • 数据分布特征
  • 列的选择性
  • DML 操作频率
  • 表的大小和增长趋势
  • 系统资源状况

索引类型详细介绍 ​

B树索引 ​

B树索引是 Oracle 中最常用的索引类型，采用平衡树结构，适用于大多数查询场景。

特点 ​

  • 适用于等值查询和范围查询
  • 支持唯一索引和非唯一索引
  • 支持复合索引
  • 适用于高选择性列
  • 支持排序和分组操作

适用场景 ​

  • 主键和唯一键约束
  • 频繁用于 WHERE 子句的高选择性列
  • 需要进行范围查询的列
  • 需要排序或分组的列

示例 ​

sql
-- 创建 B 树索引
CREATE INDEX idx_emp_dept ON EMP(department_id);

-- 创建唯一 B 树索引
CREATE UNIQUE INDEX idx_emp_empno ON EMP(empno);

-- 创建复合 B 树索引
CREATE INDEX idx_emp_dept_sal ON EMP(department_id, salary);

位图索引 ​

位图索引使用位图数组存储索引键值和行 ID 的映射关系，适用于低基数列。

特点 ​

  • 适用于低基数列（不同值数量少于 10%）
  • 占用空间小
  • 适合 AND、OR、IN 查询
  • 不适合频繁更新的列
  • 适用于数据仓库环境

适用场景 ​

  • 性别、状态、类型等低基数列
  • 只读或很少更新的表
  • 数据仓库中的维度表
  • 频繁用于复杂条件查询的列

示例 ​

sql
-- 创建位图索引
CREATE BITMAP INDEX idx_emp_gender ON EMP(gender);

-- 创建复合位图索引
CREATE BITMAP INDEX idx_emp_dept_job ON EMP(department_id, job);

函数索引 ​

函数索引是基于函数或表达式的索引，适用于频繁使用函数查询的场景。

特点 ​

  • 支持基于函数或表达式的查询
  • 可以包含内置函数或用户自定义函数
  • 维护成本较高
  • 适用于频繁使用函数过滤的场景

适用场景 ​

  • 频繁使用 UPPER()、LOWER() 等函数的查询
  • 频繁使用日期函数的查询
  • 频繁使用自定义函数的查询
  • 需要对计算列进行索引的场景

示例 ​

sql
-- 创建基于 UPPER 函数的索引
CREATE INDEX idx_emp_ename_upper ON EMP(UPPER(ename));

-- 创建基于日期函数的索引
CREATE INDEX idx_emp_hire_year ON EMP(EXTRACT(YEAR FROM hire_date));

-- 创建基于表达式的索引
CREATE INDEX idx_emp_sal_comm ON EMP(sal + NVL(comm, 0));

分区索引 ​

分区索引是与分区表关联的索引，适用于大型表。

特点 ​

  • 与分区表的分区策略关联
  • 支持本地分区索引和全局分区索引
  • 提高查询性能，减少 I/O 操作
  • 便于管理和维护
  • 支持在线维护

适用场景 ​

  • 大型表（行数超过 1000 万）
  • 数据按时间或范围分区的表
  • 需要频繁进行分区维护的表
  • 需要提高查询性能的大型表

类型 ​

  1. 本地分区索引（Local Partitioned Index）：

    • 索引与表的分区策略完全一致
    • 每个分区有对应的索引分区
    • 支持分区独立性
    • 适合分区表的查询

  2. 全局分区索引（Global Partitioned Index）：

    • 索引的分区策略与表无关
    • 支持自定义分区键
    • 适合跨分区查询
    • 维护成本较高

  3. 全局非分区索引（Global Non-Partitioned Index）：

    • 索引不分区
    • 适合全局查询
    • 维护成本较高

示例 ​

sql
-- 创建本地分区索引
CREATE INDEX idx_sales_date_local ON SALES(sale_date) LOCAL;

-- 创建全局分区索引
CREATE INDEX idx_sales_customer_global ON SALES(customer_id)
GLOBAL PARTITION BY RANGE (customer_id) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

-- 创建全局非分区索引
CREATE INDEX idx_sales_product_global ON SALES(product_id) GLOBAL;

反向索引 ​

反向索引将索引键值反转存储，适用于序列生成的主键。

特点 ​

  • 将索引键值反转存储
  • 分散索引插入热点
  • 适用于序列生成的主键
  • 不适合范围查询

适用场景 ​

  • 序列生成的主键列
  • 频繁进行插入操作的表
  • 需要分散索引插入热点的场景

示例 ​

sql
-- 创建反向索引
CREATE INDEX idx_emp_empno_reverse ON EMP(empno) REVERSE;

位图连接索引 ​

位图连接索引是基于多表连接的位图索引，适用于数据仓库环境。

特点 ​

  • 基于多表连接的位图索引
  • 减少连接操作的成本
  • 适用于数据仓库环境
  • 不适合频繁更新的表

适用场景 ​

  • 数据仓库中的星型模型
  • 频繁进行多表连接查询的场景
  • 只读或很少更新的数据仓库表

示例 ​

sql
-- 创建位图连接索引
CREATE BITMAP INDEX idx_sales_customer_name ON SALES(s.customer_id, c.customer_name)
FROM SALES s JOIN CUSTOMERS c ON s.customer_id = c.customer_id;

全文索引 ​

全文索引用于文本搜索，适用于包含大量文本数据的列。

特点 ​

  • 支持文本搜索
  • 支持模糊查询和语义搜索
  • 维护成本较高
  • 适用于包含大量文本数据的列

适用场景 ​

  • 包含大量文本数据的列
  • 需要进行文本搜索的应用
  • 文档管理系统
  • 内容管理系统

示例 ​

sql
-- 创建全文索引
CREATE INDEX idx_emp_resume ON EMP(resume) INDEXTYPE IS CTXSYS.CONTEXT;

-- 使用全文索引查询
SELECT * FROM EMP WHERE CONTAINS(resume, 'Oracle AND SQL') > 0;

空间索引 ​

空间索引用于地理空间数据，适用于包含空间数据类型的列。

特点 ​

  • 支持地理空间数据查询
  • 适用于 SDO_GEOMETRY 数据类型
  • 维护成本较高
  • 适用于地理信息系统

适用场景 ​

  • 包含地理空间数据的列
  • 地理信息系统（GIS）
  • 位置服务应用
  • 地图应用

示例 ​

sql
-- 创建空间索引
CREATE INDEX idx_loc_geometry ON LOCATIONS(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- 使用空间索引查询
SELECT * FROM LOCATIONS WHERE SDO_WITHIN_DISTANCE(geometry, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(116.4074, 39.9042, NULL), NULL, NULL), 'distance=10 unit=km') = 'TRUE';

索引类型选择指南 ​

基于列选择性的选择 ​

选择性推荐索引类型不推荐索引类型
高（> 90%）B树索引位图索引
中（10%-90%）B树索引位图索引（除非是数据仓库）
低（< 10%）位图索引（只读表）、B树索引（频繁更新表）-

基于查询类型的选择 ​

查询类型推荐索引类型不推荐索引类型
等值查询B树索引、位图索引-
范围查询B树索引反向索引
AND/OR/IN 查询位图索引、B树索引-
函数查询函数索引普通索引
文本搜索全文索引普通索引
空间查询空间索引普通索引

基于DML操作频率的选择 ​

DML操作频率推荐索引类型不推荐索引类型
高（频繁更新）B树索引位图索引、位图连接索引
中（偶尔更新）B树索引位图索引（除非是低基数列）
低（只读或很少更新）位图索引、B树索引、位图连接索引-

基于表大小的选择 ​

表大小推荐索引类型不推荐索引类型
小（< 1000行）不建议创建索引-
中（1000-100万行）B树索引分区索引
大（> 100万行）分区索引、B树索引普通索引（非分区）

基于系统类型的选择 ​

系统类型推荐索引类型不推荐索引类型
OLTPB树索引位图索引、位图连接索引
数据仓库位图索引、位图连接索引、分区索引-
混合系统分区索引、B树索引位图索引（频繁更新表）

不同场景下的索引选择 ​

1. 在线事务处理 (OLTP) 系统 ​

OLTP 系统的特点是频繁的 DML 操作和简单的查询，索引选择应遵循以下原则：

  • 优先选择 B 树索引
  • 为高选择性列创建索引
  • 为外键创建索引
  • 避免使用位图索引
  • 考虑使用反向索引（对于序列生成的主键）

2. 数据仓库 (DW) 系统 ​

数据仓库系统的特点是大量的数据加载和复杂的查询，索引选择应遵循以下原则：

  • 优先选择位图索引和位图连接索引
  • 为维度表的主键创建 B 树索引
  • 为事实表的外键创建位图索引
  • 考虑使用分区索引
  • 为频繁用于过滤和分组的列创建索引

3. 客户关系管理 (CRM) 系统 ​

CRM 系统的特点是大量的客户数据和复杂的查询，索引选择应遵循以下原则：

  • 为客户 ID、姓名等核心字段创建 B 树索引
  • 为状态、类型等低基数列创建位图索引
  • 为频繁用于搜索的文本字段创建全文索引
  • 考虑使用分区索引（对于大型 CRM 系统）

4. 电子商务系统 ​

电子商务系统的特点是高并发访问和复杂的查询，索引选择应遵循以下原则：

  • 为产品 ID、订单 ID 等核心字段创建 B 树索引
  • 为产品分类、状态等低基数列创建位图索引
  • 为产品名称、描述等文本字段创建全文索引
  • 考虑使用分区索引（对于大型电子商务系统）

19c 和 21c 索引类型新特性 ​

Oracle 19c 新特性 ​

  1. 自动索引：Oracle 19c 引入了自动索引功能，能够自动识别和创建合适的索引类型
  2. 索引优化建议：通过 SQL Tuning Advisor 提供更准确的索引类型建议
  3. 分区索引增强：改进了分区索引的维护功能，支持在线分区索引维护
  4. 函数索引增强：支持更多类型的函数和表达式

Oracle 21c 新特性 ​

  1. 智能索引类型选择：引入了机器学习算法，能够根据查询模式自动选择最优索引类型
  2. JSON 索引：新增了 JSON 数据类型的索引支持
  3. 区块链表索引：新增了区块链表的索引支持
  4. 索引压缩增强：改进了索引压缩算法，支持更多索引类型
  5. 实时索引监控：提供实时的索引使用监控，能够及时识别不合适的索引类型

生产环境最佳实践 ​

1. 索引类型评估 ​

  • 定期评估现有索引类型的有效性
  • 根据查询模式和数据分布调整索引类型
  • 使用 EXPLAIN PLAN 分析索引类型对查询性能的影响
  • 进行性能测试，验证索引类型的选择

2. 索引类型迁移 ​

  • 当数据分布发生变化时，考虑调整索引类型
  • 当查询模式发生变化时，考虑调整索引类型
  • 对于大型表，使用在线操作迁移索引类型
  • 迁移索引类型前，确保有有效的备份

3. 索引类型监控 ​

  • 监控不同索引类型的使用情况
  • 监控索引类型对系统性能的影响
  • 监控索引类型的维护成本
  • 设置索引类型相关的告警

4. 索引类型组合 ​

  • 根据业务需求，合理组合使用不同类型的索引
  • 对于复杂查询，考虑使用多种索引类型
  • 避免过度使用某种索引类型
  • 定期审查索引类型组合的有效性

常见问题 (FAQ) ​

如何选择合适的索引类型？ ​

选择合适的索引类型需要考虑以下因素：

  • 业务查询模式
  • 数据分布特征
  • 列的选择性
  • DML 操作频率
  • 表的大小和增长趋势
  • 系统资源状况

可以通过分析查询执行计划、监控查询性能、评估数据分布等方法来选择合适的索引类型。

B树索引和位图索引有什么区别？ ​

  • B树索引：适用于高选择性列，支持等值查询和范围查询，适合频繁更新的表
  • 位图索引：适用于低选择性列，支持 AND、OR、IN 查询，不适合频繁更新的表

什么时候使用函数索引？ ​

函数索引适用于以下情况：

  • 频繁使用函数或表达式的查询
  • 需要对计算列进行索引的场景
  • 频繁使用 UPPER()、LOWER() 等函数的查询
  • 频繁使用日期函数的查询

什么时候使用分区索引？ ​

分区索引适用于以下情况：

  • 大型表（行数超过 1000 万）
  • 数据按时间或范围分区的表
  • 需要频繁进行分区维护的表
  • 需要提高查询性能的大型表

如何评估现有索引类型的有效性？ ​

评估现有索引类型有效性的方法包括：

  • 分析查询执行计划，查看索引类型的使用情况
  • 监控索引类型对查询性能的影响
  • 评估索引类型的维护成本
  • 分析数据分布，判断索引类型是否适合

19c 和 21c 在索引类型方面有什么主要区别？ ​

Oracle 21c 在索引类型方面相比 19c 有以下主要增强：

  • 引入了智能索引类型选择功能
  • 新增了 JSON 索引和区块链表索引
  • 改进了索引压缩算法
  • 提供了实时索引监控功能
  • 增强了函数索引的支持

如何迁移索引类型？ ​

迁移索引类型的方法包括：

  • 创建新类型的索引
  • 修改应用程序，使用新索引
  • 监控新索引的性能
  • 删除旧类型的索引（如果不再需要）

如何监控不同索引类型的使用情况？ ​

监控不同索引类型使用情况的方法包括：

  • 查询 V$SQL_PLAN 视图，查看索引类型的使用情况
  • 查询 DBA_HIST_SQL_PLAN 视图，查看历史索引类型使用情况
  • 使用 Oracle Enterprise Manager 监控索引类型使用率
  • 启用索引监控功能，查看索引类型的使用情况

总结 ​

选择合适的索引类型是提高 Oracle 数据库性能的关键。DBA 应该根据业务查询模式、数据分布特征、列的选择性、DML 操作频率等因素，选择合适的索引类型。

不同的索引类型适用于不同的场景，B树索引适用于大多数查询场景，位图索引适用于低基数列和数据仓库环境，函数索引适用于频繁使用函数查询的场景，分区索引适用于大型表，全文索引适用于文本搜索，空间索引适用于地理空间数据。

随着 Oracle 版本的升级，索引类型的功能不断增强，DBA 应充分利用这些新特性，提高索引类型选择的准确性和效率。通过合理的索引类型选择和维护，可以有效提高数据库系统的性能和可靠性，满足业务需求。