Oracle 索引优化策略

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

1. 概述

索引是查询性能优化的关键[1]：

目标：

减少全表扫描
提升 WHERE/JOIN/ORDER BY 性能
平衡查询与 DML 性能

2. 索引选择

2.1 决策矩阵

场景	推荐索引
高基数等值	B-Tree
低基数	Bitmap
序列生成	Reverse Key
函数查询	Function-Based
主键查询	IOT
范围查询	B-Tree
OLAP	Bitmap
OLTP	B-Tree

2.2 候选列

WHERE 条件列
JOIN 连接列
ORDER BY 列
GROUP BY 列
高选择性列

3. 复合索引

3.1 列顺序原则

等值条件在前
范围条件在后
高选择性在前
排序列考虑

3.2 示例

-- 查询：WHERE dept_id = 10 AND salary > 5000
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- 查询：WHERE name = 'Smith' ORDER BY create_date
CREATE INDEX idx_emp_name_date ON employees(name, create_date);

3.3 前缀扫描

-- 索引 (a, b, c)
SELECT * FROM t WHERE a = 1;              -- 使用
SELECT * FROM t WHERE a = 1 AND b = 2;    -- 使用
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;  -- 使用
SELECT * FROM t WHERE b = 2;              -- 不使用
SELECT * FROM t WHERE a = 1 AND c = 3;    -- 部分使用

4. Function-Based 索引

4.1 适用

WHERE 使用函数
隐式转换
大小写查询

4.2 创建

-- 大小写
CREATE INDEX idx_emp_upper ON employees(UPPER(last_name));

-- 表达式
CREATE INDEX idx_emp_annual ON employees(salary * 12);

-- 复杂
CREATE INDEX idx_emp_comp ON employees(dept_id || '_' || job_id);

-- 使用
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
SELECT * FROM employees WHERE salary * 12 > 60000;

5. Bitmap 索引

5.1 适用

低基数列（< 1%）
OLAP/DSS
多列 AND/OR

5.2 创建

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);
CREATE BITMAP INDEX idx_emp_married ON employees(married);

5.3 限制

不适合 OLTP
DML 开销大
锁全索引

6. 索引维护

6.1 重建

ALTER INDEX idx_emp_name REBUILD;
ALTER INDEX idx_emp_name REBUILD ONLINE;
ALTER INDEX idx_emp_name REBUILD TABLESPACE indx;

6.2 合并

ALTER INDEX idx_emp_name COALESCE;

6.3 统计信息

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

6.4 监控使用

ALTER INDEX idx_emp_name MONITORING USAGE;
SELECT * FROM v$object_usage;
ALTER INDEX idx_emp_name NOMONITORING USAGE;

7. 索引失效场景

7.1 函数阻止

-- 索引 name
WHERE UPPER(name) = 'X'  -- 不用索引

-- 解决：函数索引
CREATE INDEX idx_upper ON t(UPPER(name));

7.2 隐式转换

-- 列类型 NUMBER，查询字符
WHERE id = '100'  -- 不用索引

-- 解决
WHERE id = 100

7.3 NULL 查询

WHERE name IS NULL  -- 不用索引

-- 解决：默认值 或 Bitmap 索引

7.4 LIKE

WHERE name LIKE '%ith'  -- 不用索引

-- 解决
WHERE name LIKE 'Smit%'  -- 使用

7.5 负向条件

WHERE id != 10  -- 不用索引

-- 解决
WHERE id < 10 OR id > 10

8. 索引分析

8.1 查看索引

SELECT 
  index_name,
  index_type,
  uniqueness,
  status,
  blevel,
  leaf_blocks,
  distinct_keys,
  clustering_factor
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

8.2 聚簇因子

-- CF 接近块数：索引有序
-- CF 接近行数：索引无序
SELECT index_name, clustering_factor, num_rows, leaf_blocks
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

8.3 索引高度

ANALYZE INDEX idx_emp_name VALIDATE STRUCTURE;
SELECT height, name, lf_rows, del_lf_rows FROM index_stats;
-- height > 4 重建
-- del_lf_rows / lf_rows > 20% 重建

9. 索引压缩

9.1 前缀压缩

CREATE INDEX idx_emp ON employees(dept_id, dept_name) COMPRESS 1;
-- 节省空间，适合重复值多

9.2 重建压缩

ALTER INDEX idx_emp REBUILD COMPRESS 1;

10. Invisible 索引

10.1 创建

CREATE INDEX idx_test ON employees(test_col) INVISIBLE;

10.2 测试

-- 优化器忽略
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
-- 测试后决定保留或删除

11. 索引监控

11.1 v$object_usage

SELECT 
  index_name,
  table_name,
  monitoring,
  used,
  start_monitoring,
  end_monitoring
FROM v$object_usage;

11.2 AWR

SELECT * FROM dba_hist_sql_plan
WHERE operation = 'INDEX'
  AND object_name = 'IDX_EMP_NAME';

12. 常见坑与排错

12.1 索引不使用

-- 1. 检查统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

-- 2. 检查 WHERE 条件
-- 3. 检查 HINT
-- 4. 检查参数

12.2 索引碎片

-- 重建
ALTER INDEX idx_name REBUILD ONLINE;

12.3 索引失效

-- 状态 UNUSABLE
ALTER INDEX idx_name REBUILD;

12.4 DML 慢

-- 索引过多
-- 1. 监控使用
-- 2. 删除无用索引

13. 最佳实践

主键自动索引：默认
外键加索引：避免锁
WHERE/JOIN/ORDER 列建索引：性能
复合索引列顺序：等值在前
函数列用 FBI：避免全表扫描
低基数用 Bitmap：OLAP
定期重建：减少碎片
监控使用：删除无用
Invisible 测试：安全
避免过度索引：影响 DML

14. 参考资料

[1] Oracle Database Performance Tuning Guide 19c, “Indexing” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/indexing.html