DB / Oracle / SQL 开发

Oracle 索引优化策略详解

2026.09.06

Oracle 索引优化策略详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

索引优化策略是性能关键[1]：

详细见：Oracle 索引类型与应用、Oracle 索引优化策略。

2. 索引类型

2.1 B-Tree

CREATE INDEX idx_emp_name ON employees(name);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
  • 默认
  • OLTP

2.2 位图

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);
  • 低基数
  • 仓库
  • 不适合 OLTP

2.3 函数

CREATE INDEX idx_emp_upper ON employees(UPPER(name));
CREATE INDEX idx_emp_year ON employees(EXTRACT(YEAR FROM hire_date));

2.4 反向键

CREATE INDEX idx_emp_id_rev ON employees(id) REVERSE;
  • 减少热点
  • 等值查询
  • 范围查询不可

2.5 复合

CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);
  • 多列
  • 最左前缀

3. 设计原则

3.1 选择性

SELECT COUNT(DISTINCT dept_id) / COUNT(*) AS selectivity 
FROM employees;
-- > 0.1 适合

3.2 覆盖索引

-- 查询列都在索引
CREATE INDEX idx_emp_cover ON employees(dept_id, name, salary);

SELECT name, salary FROM employees WHERE dept_id = 10;
-- 索引覆盖，避免表访问

3.3 前缀列

- 高选择性
- 查询常用
- 排序

4. 复合索引

4.1 顺序

-- 查询 1
SELECT * FROM employees WHERE dept_id = 10 AND salary > 5000;
-- 索引 (dept_id, salary) 好

-- 查询 2
SELECT * FROM employees WHERE salary > 5000;
-- 索引 (salary) 好

-- 综合
-- (dept_id, salary) 不能满足查询 2

4.2 列数

- 2-4 列
- 太多影响 DML
- 选择性平衡

5. 索引使用

5.1 触发条件

-- 索引使用
WHERE col = ?
WHERE col IN (...)
WHERE col BETWEEN ? AND ?
WHERE col LIKE 'prefix%'

-- 索引不使用
WHERE UPPER(col) = ?  -- 函数（除非函数索引）
WHERE col + 1 = ?     -- 计算
WHERE col != ?        -- 否定
WHERE col IS NULL     -- 一般（除位图）

5.2 验证

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));
-- INDEX RANGE SCAN
-- INDEX UNIQUE SCAN
-- TABLE ACCESS FULL（差）

详细见：Oracle 执行计划详解。

6. 监控

6.1 使用统计

ALTER INDEX idx_emp_name MONITORING USAGE;

-- 一段时间后
SELECT index_name, table_name, monitoring, used 
FROM v$object_usage 
WHERE index_name = 'IDX_EMP_NAME';

ALTER INDEX idx_emp_name NOMONITORING USAGE;

6.2 未使用索引

SELECT index_name, table_name, used 
FROM v$object_usage 
WHERE used = 'NO';
-- 删除未使用

6.3 索引统计

SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

7. 索引重建

7.1 重建

ALTER INDEX idx_emp_name REBUILD;
ALTER INDEX idx_emp_name REBUILD ONLINE;
ALTER INDEX idx_emp_name REBUILD TABLESPACE users;

7.2 COALESCE

ALTER INDEX idx_emp_name COALESCE;
-- 合并碎片

7.3 时机

- BLEVEL > 3
- 删除大量
- 碎片

8. 统计

8.1 收集

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT', 'IDX_EMP_NAME');

-- 表统计（含索引）
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', cascade => TRUE);

8.2 直方图

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES',
  method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254');

详细见：Oracle 直方图与统计信息。

9. 集群因子

9.1 CLUSTERING_FACTOR

SELECT index_name, clustering_factor 
FROM user_indexes 
WHERE table_name = 'EMPLOYEES';

9.2 含义

- 接近块数：有序，好
- 接近行数：无序，差
- 影响 Index Range Scan 性能

9.3 改进

- 按索引列排序插入
- 重建表
- 物化视图

10. 不可见索引

10.1 创建

CREATE INDEX idx_test ON employees(salary) INVISIBLE;

10.2 测试

-- 优化器不使用（默认）
SELECT * FROM employees WHERE salary > 5000;

-- 临时使用
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

10.3 切换

ALTER INDEX idx_test VISIBLE;
ALTER INDEX idx_test INVISIBLE;

11. 部分索引

11.1 12c+

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  status VARCHAR2(10),
  INDEXING OFF
)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 ... INDEXING OFF,
  PARTITION p2025 ... INDEXING ON
);

CREATE INDEX idx_sales_status ON sales(status) LOCAL INDEXING PARTIAL;

12. 应用场景

12.1 OLTP

- B-Tree
- 主键 / 外键
- 高选择性列
- 覆盖索引

12.2 仓库

- 位图
- 复合
- 物化视图
- 分区

12.3 模糊查询

-- 前缀
CREATE INDEX idx_emp_name ON employees(name);
SELECT * FROM employees WHERE name LIKE 'Smi%';

-- 全文
CREATE INDEX idx_emp_text ON employees(name) INDEXTYPE IS CTXSYS.CONTEXT;
SELECT * FROM employees WHERE CONTAINS(name, 'Smith') > 0;

详细见：Oracle 全文检索详解。

13. 性能

13.1 查询

- 索引减少 I/O
- 覆盖避免表访问
- 排序优化

13.2 DML

- 索引增加开销
- 插入/更新慢
- 平衡

13.3 内存

- 索引缓存
- Buffer Cache

14. 常见坑与排错

14.1 索引不使用

- 统计旧
- 函数
- 隐式转换
- 选择性低
- CBO 选择

14.2 索引失效

- MOVE 表
- 重建
- ONLINE

14.3 过多索引

- DML 慢
- 空间
- 删除未使用

15. 最佳实践

  1. B-Tree OLTP：通用
  2. 位图仓库：低基数
  3. 复合索引：覆盖
  4. 函数索引：函数
  5. 监控使用：删除未用
  6. 统计信息：更新
  7. 重建定期：碎片
  8. 不可见测试：评估
  9. 部分索引：12c+
  10. 平衡 DML：谨慎

16. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “Indexes” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/