印刻数据库

外观

Oracle 索引设计规范 ​

索引类型 ​

B树索引 ​

  • 适用场景：等值查询、范围查询、排序操作
  • 特点：默认索引类型，平衡树结构，支持快速查找
  • 创建语法：
    sql
    CREATE INDEX idx_table_column ON table_name(column_name);

位图索引 ​

  • 适用场景：低基数列（如性别、状态），OLAP 系统
  • 特点：存储空间小，适合位图AND/OR操作
  • 创建语法：
    sql
    CREATE BITMAP INDEX idx_table_status ON table_name(status);

函数索引 ​

  • 适用场景：需要基于函数或表达式的查询
  • 特点：支持表达式索引，提高查询性能
  • 创建语法：
    sql
    CREATE INDEX idx_table_upper_name ON table_name(UPPER(name));

分区索引 ​

  • 适用场景：分区表，提高查询和维护性能
  • 类型：本地分区索引、全局分区索引
  • 创建语法：
    sql
    CREATE INDEX idx_partitioned_table ON partitioned_table(column_name) LOCAL;

反向键索引 ​

  • 适用场景：高并发插入，避免索引热点块
  • 特点：键值反转存储，分散插入热点
  • 创建语法：
    sql
    CREATE INDEX idx_table_id ON table_name(id) REVERSE;

复合索引 ​

  • 适用场景：多列查询条件
  • 特点：支持最左前缀原则，提高多列查询性能
  • 创建语法：
    sql
    CREATE INDEX idx_table_col1_col2 ON table_name(column1, column2);

索引设计原则 ​

必要性原则 ​

  • 只在需要的列上创建索引
  • 考虑查询频率和数据分布
  • 避免在频繁更新的列上创建索引

选择性原则 ​

  • 高选择性列（唯一值比例高）适合创建索引
  • 低选择性列（如性别）适合位图索引
  • 选择性计算公式：SELECTIVITY = 唯一值数量 / 总行数

最左前缀原则 ​

  • 复合索引的列顺序应遵循最左前缀原则
  • 优先放置选择性高的列
  • 考虑查询条件的使用频率

数据类型原则 ​

  • 索引列的数据类型应尽可能小
  • 避免在大对象（LOB）列上创建索引
  • 考虑字符集对索引大小的影响

维护成本原则 ​

  • 索引会增加存储和维护成本
  • 权衡查询性能提升与维护成本
  • 定期评估和清理无用索引

索引创建最佳实践 ​

基础创建 ​

sql
-- 标准B树索引
CREATE INDEX idx_emp_dept_id ON employees(department_id);

-- 复合索引
CREATE INDEX idx_emp_dept_job ON employees(department_id, job_id);

-- 唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

高级创建选项 ​

sql
-- 并行创建索引
CREATE INDEX idx_large_table ON large_table(column_name) PARALLEL 4;

-- 在线创建索引
CREATE INDEX idx_emp_salary ON employees(salary) ONLINE;

-- 压缩索引
CREATE INDEX idx_emp_name ON employees(last_name, first_name) COMPRESS 2;

-- 包含列索引（Oracle 11g+）
CREATE INDEX idx_emp_id ON employees(employee_id) INCLUDE(first_name, last_name);

分区表索引 ​

sql
-- 本地分区索引
CREATE INDEX idx_part_table ON partitioned_table(column_name) LOCAL;

-- 全局分区索引
CREATE INDEX idx_part_table_global ON partitioned_table(column_name) 
  GLOBAL PARTITION BY RANGE(column_name) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
  );

索引维护策略 ​

索引监控 ​

  • 使用监控功能：

    sql
    ALTER INDEX idx_table_column MONITORING USAGE;
SELECT * FROM v$object_usage WHERE index_name = 'IDX_TABLE_COLUMN';

  • 监控索引大小：

    sql
    SELECT index_name, table_name, bytes/1024/1024 MB 
FROM dba_segments 
WHERE segment_type = 'INDEX' 
AND owner = 'HR' 
ORDER BY bytes DESC;

索引重建 ​

  • 何时重建：索引碎片率高、查询性能下降、索引统计信息过时
  • 重建方法：
    sql
    -- 离线重建
ALTER INDEX idx_table_column REBUILD;

-- 在线重建
ALTER INDEX idx_table_column REBUILD ONLINE;

-- 并行重建
ALTER INDEX idx_table_column REBUILD PARALLEL 4;

索引分析 ​

  • 收集统计信息：

    sql
    EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_DEPT_ID');
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);

  • 查看执行计划：

    sql
    EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 50;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

索引清理 ​

  • 识别无用索引：

    sql
    SELECT owner, index_name, table_name
FROM dba_indexes
WHERE owner = 'HR'
AND NOT EXISTS (
  SELECT 1 FROM v$object_usage 
  WHERE index_name = dba_indexes.index_name
  AND table_name = dba_indexes.table_name
  AND used = 'YES'
);

  • 删除无用索引：

    sql
    DROP INDEX hr.idx_unused_index;

性能优化技巧 ​

索引使用优化 ​

  • 避免全表扫描：确保查询条件使用索引列
  • 使用绑定变量：提高共享池利用率和索引使用效率
  • 避免函数操作：在索引列上使用函数会导致索引失效

索引列顺序优化 ​

  • 高选择性列优先：将选择性高的列放在复合索引前面
  • 查询频率优先：将频繁使用的查询条件列放在前面
  • 范围查询列最后：将范围查询列放在复合索引的最后

索引压缩 ​

  • 适用场景：重复值较多的列、复合索引
  • 压缩级别：根据重复程度选择合适的压缩级别
  • 性能影响：减少存储，可能轻微影响查询性能

并行索引操作 ​

  • 适用场景：大型索引的创建和重建
  • 并行度选择：根据CPU核心数和系统负载调整
  • 注意事项：避免在生产高峰期使用高并行度

版本差异 ​

Oracle 11g 特性 ​

  • 索引包含列：INCLUDE 子句减少回表操作
  • 分区索引增强：更好的分区索引管理
  • 索引监控改进：更详细的索引使用统计

Oracle 12c 特性 ​

  • 在线索引重建增强：减少锁争用
  • 索引压缩改进：更高的压缩率
  • 多租户环境索引管理：PDB级别的索引管理

Oracle 19c 特性 ​

  • 索引维护自动化：自动索引优化
  • 混合分区索引：更灵活的分区策略
  • 索引健康检查：内置索引健康评估

Oracle 21c 特性 ​

  • JSON 索引增强：更好的 JSON 数据索引支持
  • 索引压缩进一步优化：智能压缩算法
  • 实时索引统计：更及时的索引统计更新

常见问题（FAQ） ​

Q1: 如何判断是否需要创建索引？ ​

A1: 考虑以下因素：

  • 查询频率：高频查询的列适合创建索引
  • 数据选择性：高选择性列（唯一值比例高）适合创建索引
  • 更新频率：频繁更新的列不适合创建索引
  • 查询条件：WHERE 子句中经常使用的列适合创建索引
  • 排序操作：ORDER BY 子句中的列适合创建索引

Q2: 复合索引的列顺序如何确定？ ​

A2: 遵循以下原则：

  • 最左前缀原则：考虑查询中最常使用的列顺序
  • 选择性原则：将选择性高的列放在前面
  • 范围查询原则：将范围查询列放在最后
  • 数据类型原则：将较小数据类型的列放在前面

Q3: 如何识别和清理无用索引？ ​

A3: 步骤如下：

  1. 启用索引监控：ALTER INDEX index_name MONITORING USAGE;
  2. 收集一段时间的使用情况
  3. 查询未使用的索引：SELECT * FROM v$object_usage WHERE used = 'NO';
  4. 分析索引的必要性
  5. 谨慎删除确认无用的索引

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

A4: 考虑以下情况：

  • 索引碎片率高（通常超过 30%）
  • 查询性能明显下降
  • 索引统计信息过时
  • 数据库升级后
  • 大量数据变更后

Q5: 如何优化位图索引的性能？ ​

A5: 最佳实践：

  • 只在低基数列（如性别、状态）上使用
  • 适合读多写少的 OLAP 环境
  • 避免在频繁更新的列上使用
  • 考虑使用分区位图索引提高维护性能

Q6: 函数索引的使用注意事项？ ​

A6: 注意事项：

  • 确保查询条件与函数索引完全匹配
  • 考虑函数的执行成本
  • 监控函数索引的使用情况
  • 定期更新函数索引的统计信息

Q7: 分区表的索引策略？ ​

A7: 建议：

  • 对于分区键列，使用本地分区索引
  • 对于非分区键列，根据查询模式选择本地或全局索引
  • 考虑索引的维护成本和查询性能
  • 定期检查分区索引的状态

Q8: 如何监控索引的使用情况？ ​

A8: 方法：

  • 使用 v$object_usage 视图监控索引使用
  • 启用 AWR 报告分析索引性能
  • 使用 DBMS_SQLTUNE 分析 SQL 执行计划
  • 监控索引相关的等待事件

Q9: 索引过多会有什么影响？ ​

A9: 负面影响：

  • 增加存储开销
  • 减慢 DML 操作速度
  • 增加维护成本
  • 可能导致优化器选择错误的索引
  • 增加备份和恢复时间

Q10: 如何处理索引热点问题？ ​

A10: 解决方案：

  • 使用反向键索引分散插入热点
  • 考虑分区表和分区索引
  • 调整应用程序的插入模式
  • 增加索引的并行度
  • 考虑使用序列缓存减少争用