印刻数据库

外观

Oracle索引设计 ​

索引是Oracle数据库中提高查询性能的重要手段，合理的索引设计可以显著提高查询速度，减少I/O操作。本文将详细介绍Oracle索引的类型、设计原则、创建方法和维护等内容。

索引概述 ​

什么是索引？ ​

索引是一种数据库对象，用于加速数据检索操作。它类似于书籍的目录，可以帮助数据库快速定位到所需的数据，而无需扫描整个表。

索引的工作原理 ​

  1. 索引创建时，Oracle会构建一个排序的数据结构（通常是B树）
  2. 每个索引条目包含索引列的值和对应的行地址（ROWID）
  3. 查询时，Oracle使用索引快速定位到匹配的索引条目
  4. 通过ROWID直接访问表中的数据行

索引的优缺点 ​

优点 ​

  • 提高查询性能，特别是对于大表
  • 加速ORDER BY和GROUP BY操作
  • 加速表连接操作
  • 强制数据唯一性（唯一索引）

缺点 ​

  • 增加存储空间
  • 降低INSERT、UPDATE和DELETE操作的性能
  • 需要维护索引结构
  • 过多的索引会导致查询优化器选择错误的索引

索引类型 ​

Oracle支持多种索引类型，每种类型适用于不同的场景。

B树索引（B-Tree Index） ​

B树索引是Oracle最常用的索引类型，适用于大多数查询场景。

结构 ​

  • 根节点：包含指向子节点的指针
  • 分支节点：包含索引值范围和指向子节点的指针
  • 叶节点：包含索引值和对应的ROWID

特点 ​

  • 适用于等值查询、范围查询和排序操作
  • 支持单列和多列索引
  • 自动维护，插入、更新和删除操作会自动更新索引

示例 ​

sql
-- 创建单列B树索引
CREATE INDEX idx_employees_last_name ON employees(last_name);

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

位图索引（Bitmap Index） ​

位图索引适用于低基数列（即列值重复率高的列），如性别、状态等。

结构 ​

  • 为每个不同的列值创建一个位图
  • 位图中的每一位表示对应行是否包含该值

特点 ​

  • 适用于低基数列
  • 高效支持AND、OR和NOT查询
  • 不适合高并发写操作，因为会导致位图锁定
  • 适合数据仓库和报表系统

示例 ​

sql
-- 创建位图索引
CREATE BITMAP INDEX idx_employees_gender ON employees(gender);
CREATE BITMAP INDEX idx_employees_status ON employees(employment_status);

函数索引（Function-Based Index） ​

函数索引基于列的函数值创建，适用于经常使用函数或表达式查询的场景。

特点 ​

  • 适用于经常使用函数或表达式的查询
  • 支持自定义函数
  • 需要启用查询重写

示例 ​

sql
-- 创建基于UPPER函数的索引
CREATE INDEX idx_employees_upper_last_name ON employees(UPPER(last_name));

-- 创建基于表达式的索引
CREATE INDEX idx_employees_salary_commission ON employees(salary + NVL(commission_pct, 0));

分区索引（Partitioned Index） ​

分区索引是与分区表对应的索引，索引被分为多个分区，每个分区对应表的一个分区。

类型 ​

  • 本地分区索引（Local Partitioned Index）：索引分区与表分区一一对应
  • 全局分区索引（Global Partitioned Index）：索引分区与表分区不一一对应，由用户指定

特点 ​

  • 提高查询性能，特别是对于分区表
  • 便于管理和维护，支持分区级别的操作
  • 减少索引重建时间

示例 ​

sql
-- 创建本地分区索引
CREATE INDEX idx_orders_order_date ON orders(order_date) LOCAL;

-- 创建全局分区索引
CREATE INDEX idx_orders_customer_id ON orders(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)
);

反向键索引（Reverse Key Index） ​

反向键索引将索引值的字节顺序反转后存储，适用于高并发插入场景，如序列生成的主键。

特点 ​

  • 减少索引热点，提高并发插入性能
  • 不适合范围查询
  • 适用于序列生成的主键

示例 ​

sql
-- 创建反向键索引
CREATE INDEX idx_employees_emp_id ON employees(employee_id) REVERSE;

降序索引（Descending Index） ​

降序索引按照列值的降序存储，适用于经常使用降序排序的查询。

特点 ​

  • 加速降序排序操作
  • 从Oracle 8i开始支持
  • 可以与升序列组合创建复合索引

示例 ​

sql
-- 创建降序索引
CREATE INDEX idx_employees_salary_desc ON employees(salary DESC);

-- 创建混合排序的复合索引
CREATE INDEX idx_employees_dept_salary ON employees(department_id ASC, salary DESC);

全文索引（Text Index） ​

全文索引用于文本数据的全文搜索，如文档、文章等。

特点 ​

  • 支持文本搜索，如关键词、短语、模糊搜索等
  • 需要Oracle Text组件
  • 适用于包含大量文本数据的列

示例 ​

sql
-- 创建全文索引
CREATE INDEX idx_documents_content ON documents(content) INDEXTYPE IS CTXSYS.CONTEXT;

空间索引（Spatial Index） ​

空间索引用于地理信息系统（GIS）数据，如地图、位置信息等。

特点 ​

  • 支持空间数据类型，如点、线、面等
  • 需要Oracle Spatial组件
  • 适用于地理信息系统应用

示例 ​

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

索引设计原则 ​

选择合适的索引类型 ​

查询类型推荐索引类型
等值查询B树索引
范围查询B树索引
排序操作B树索引、降序索引
低基数列位图索引
高基数列B树索引
函数/表达式查询函数索引
分区表分区索引
并发插入反向键索引
文本搜索全文索引
空间数据空间索引

确定需要索引的列 ​

  1. 经常用于WHERE子句的列
  2. 经常用于JOIN条件的列
  3. 经常用于ORDER BY和GROUP BY的列
  4. 作为外键的列
  5. 需要唯一性约束的列

复合索引设计 ​

  1. 列顺序很重要：将选择性高的列放在前面
  2. 考虑查询模式：包含最常用查询的所有列
  3. 避免过多的列：复合索引的列数一般不超过4列
  4. 考虑索引覆盖：如果索引包含查询所需的所有列，Oracle可以直接从索引获取数据，无需访问表

索引设计最佳实践 ​

  1. 避免过度索引：

    • 过多的索引会降低DML操作性能
    • 增加存储空间
    • 导致查询优化器选择错误的索引

  2. 使用索引覆盖：

    • 包含查询所需的所有列
    • 减少I/O操作，提高查询性能

  3. 考虑数据分布：

    • 高选择性列适合B树索引
    • 低选择性列适合位图索引

  4. 避免在索引列上使用函数：

    • 会导致索引失效
    • 如有需要，创建函数索引

  5. 定期分析索引：

    • 收集索引统计信息
    • 帮助查询优化器选择正确的索引

  6. 考虑索引的存储位置：

    • 将索引存储在与表不同的表空间
    • 减少I/O竞争

索引创建与管理 ​

创建索引 ​

sql
-- 基本语法
CREATE [UNIQUE] [BITMAP] INDEX index_name
ON table_name(column1 [ASC|DESC], column2 [ASC|DESC], ...)
[TABLESPACE tablespace_name]
[STORAGE (storage_clause)]
[PCTFREE integer]
[INITRANS integer]
[MAXTRANS integer]
[COMPUTE STATISTICS];

-- 示例：创建唯一索引
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

-- 示例：指定表空间和存储参数
CREATE INDEX idx_employees_department_id ON employees(department_id)
TABLESPACE users
STORAGE (
    INITIAL 10M
    NEXT 5M
    PCTINCREASE 0
);

-- 示例：创建索引并收集统计信息
CREATE INDEX idx_employees_hire_date ON employees(hire_date)
COMPUTE STATISTICS;

修改索引 ​

sql
-- 重命名索引
ALTER INDEX idx_employees_last_name RENAME TO idx_emp_last_name;

-- 重建索引
ALTER INDEX idx_employees_last_name REBUILD;

-- 重建索引并指定表空间
ALTER INDEX idx_employees_last_name REBUILD TABLESPACE users;

-- 整理索引碎片
ALTER INDEX idx_employees_last_name COALESCE;

-- 禁用索引
ALTER INDEX idx_employees_last_name UNUSABLE;

-- 启用索引
ALTER INDEX idx_employees_last_name REBUILD;

-- 修改索引存储参数
ALTER INDEX idx_employees_last_name STORAGE (NEXT 10M);

删除索引 ​

sql
-- 删除索引
DROP INDEX idx_employees_last_name;

-- 删除多个索引
DROP INDEX idx_employees_last_name, idx_employees_email;

查看索引信息 ​

sql
-- 查看用户所有索引
SELECT index_name, table_name, uniqueness, status
FROM user_indexes;

-- 查看索引列信息
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES';

-- 查看索引统计信息
SELECT index_name, blevel, leaf_blocks, num_rows, last_analyzed
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 查看索引使用情况
SELECT index_name, table_name, monitoring, used
FROM v$object_usage
WHERE table_name = 'EMPLOYEES';

索引维护 ​

收集统计信息 ​

定期收集索引统计信息，帮助查询优化器选择正确的索引。

sql
-- 收集表和索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade => TRUE
);

-- 仅收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'HR',
    indname => 'IDX_EMPLOYEES_LAST_NAME'
);

重建索引 ​

当索引出现碎片或性能下降时，需要重建索引。

sql
-- 重建单个索引
ALTER INDEX idx_employees_last_name REBUILD;

-- 并行重建索引
ALTER INDEX idx_employees_last_name REBUILD PARALLEL 4;

-- 重建后关闭并行
ALTER INDEX idx_employees_last_name NOPARALLEL;

监控索引使用情况 ​

监控索引的使用情况，删除未使用的索引。

sql
-- 启用索引监控
ALTER INDEX idx_employees_last_name MONITORING USAGE;

-- 查看索引使用情况
SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE table_name = 'EMPLOYEES';

-- 禁用索引监控
ALTER INDEX idx_employees_last_name NOMONITORING USAGE;

识别无效索引 ​

定期检查并重建无效的索引。

sql
-- 查看无效索引
SELECT index_name, table_name, status
FROM user_indexes
WHERE status = 'UNUSABLE';

-- 重建无效索引
ALTER INDEX idx_employees_last_name REBUILD;

版本差异 ​

Oracle 11g及之前 ​

  • 支持基本的B树索引、位图索引和函数索引
  • 分区索引功能有限
  • 不支持JSON索引

Oracle 12c ​

  • 增强分区索引功能
  • 引入可插拔数据库（PDB）中的索引管理
  • 支持在线索引重建

Oracle 18c ​

  • 增强JSON支持，包括JSON索引
  • 引入自动索引（Auto Index）
  • 增强索引监控功能

Oracle 19c ​

  • 长期支持版本
  • 增强自动索引功能
  • 改进索引重建性能

Oracle 21c ​

  • 引入本机JSON类型和JSON索引
  • 增强索引压缩功能
  • 改进索引统计信息收集

索引设计常见误区 ​

  1. 为所有列创建索引

    • 过多的索引会降低DML性能
    • 增加存储空间和维护成本

  2. 不考虑查询模式

    • 索引应该基于实际的查询模式设计
    • 复合索引的列顺序应根据查询频率和选择性确定

  3. 忽视索引统计信息

    • 过时的统计信息会导致查询优化器选择错误的索引
    • 应定期收集索引统计信息

  4. 在低选择性列上创建B树索引

    • 低选择性列适合位图索引，不适合B树索引
    • B树索引在高选择性列上效果更好

  5. 不监控索引使用情况

    • 未使用的索引会浪费存储空间和维护成本
    • 应定期监控并删除未使用的索引

  6. 在频繁更新的列上创建索引

    • 频繁更新的列会导致索引频繁维护
    • 应谨慎在频繁更新的列上创建索引

常见问题（FAQ） ​

Q: 如何确定是否需要创建索引？ ​

A: 确定是否需要创建索引的主要因素：

  • 查询频率：经常被查询的列适合创建索引
  • 数据量：大表适合创建索引
  • 列选择性：高选择性列适合创建B树索引
  • 查询类型：等值查询、范围查询适合B树索引

Q: 如何选择复合索引的列顺序？ ​

A: 复合索引的列顺序应考虑：

  • 列的选择性：选择性高的列放在前面
  • 查询频率：经常单独使用的列放在前面
  • 查询模式：考虑最常用的查询条件

Q: 什么是索引覆盖？ ​

A: 索引覆盖是指查询所需的所有列都包含在索引中，Oracle可以直接从索引获取数据，无需访问表。索引覆盖可以显著提高查询性能。

Q: 如何识别无效索引？ ​

A: 识别无效索引的方法：

  • 查询USER_INDEXES视图，STATUS为'UNUSABLE'的索引
  • 执行计划中未使用的索引
  • 监控索引使用情况，长期未使用的索引

Q: 索引重建的最佳时机是什么？ ​

A: 索引重建的最佳时机：

  • 索引碎片严重（leaf_blocks与num_rows比例异常）
  • 索引统计信息过时
  • 索引状态为UNUSABLE
  • 表数据发生重大变化（如大量插入、更新或删除）

Q: 如何优化索引性能？ ​

A: 优化索引性能的方法：

  • 选择合适的索引类型
  • 合理设计复合索引
  • 定期收集统计信息
  • 监控索引使用情况
  • 重建碎片严重的索引
  • 删除未使用的索引

Q: 位图索引和B树索引的区别是什么？ ​

A: 位图索引和B树索引的主要区别：

  • 适用场景：位图索引适用于低基数列，B树索引适用于高基数列
  • 存储空间：位图索引存储空间较小，B树索引存储空间较大
  • 并发性能：位图索引不适合高并发写操作，B树索引适合
  • 查询性能：位图索引适合AND、OR查询，B树索引适合等值和范围查询

Q: 什么是自动索引？ ​

A: 自动索引是Oracle 18c引入的功能，它可以自动识别、创建、维护和删除索引。自动索引由Oracle数据库自动管理，无需人工干预，可以提高查询性能，同时减少索引管理工作量。

总结 ​

索引是Oracle数据库中提高查询性能的重要手段，合理的索引设计可以显著提高查询速度，减少I/O操作。在设计索引时，应根据查询模式、数据分布和业务需求选择合适的索引类型和设计方法。

索引设计是一个平衡的过程，需要考虑查询性能、DML性能、存储空间和维护成本等因素。应避免过度索引，定期监控和维护索引，删除未使用的索引，以确保索引的有效性和性能。

随着Oracle数据库的发展，新的索引类型和功能不断引入，如自动索引、JSON索引等，这些功能可以帮助我们更高效地设计和管理索引，提高数据库性能。