Automatic Indexing

Automatic Indexing（自动索引） 是数据库自治优化的核心能力，能自动分析负载、创建 / 重建 / 删除索引，大幅降低 DBA 运维成本并持续提升查询性能。以下从核心概念、工作流程、创建 / 删除机制、主流实现与配置、优缺点展开详细介绍。

一、核心概念

Automatic Indexing 是数据库内置的智能索引管理系统，通过后台进程周期性分析 SQL 负载、表统计信息，自动完成索引的创建、验证、启用、监控、重建与清理，无需人工干预。

• 核心目标：消除索引冗余、补全缺失索引、减少锁表与维护开销，让数据库 “自我调优”。
• 典型场景：OLTP/OLAP 混合负载、业务 SQL 频繁变化、DBA 人力不足的云 / 自治数据库环境。

二、完整工作流程（以 Oracle 19c 为例）

自动索引进程（Auto Index Process）默认每 15 分钟在后台执行一次，流程如下：

1. 捕获与分析（Capture）
   • 收集 SQL 历史、执行计划、绑定变量、表统计信息，识别高频 / 慢查询与过滤列。
   • 仅对统计信息最新的表生成候选索引。
2. 候选索引生成（Identify Candidates）
   • 基于查询模式推荐单列 / 组合索引，命名以SYS_AI_开头。
   • 支持分区表、虚拟列、JSON 表达式等复杂场景。
3. 静默创建（Invisible Creation）
   • 先创建不可见（Invisible）索引，优化器默认不使用，不影响现有业务。
4. 性能验证（Verify）
   • 用不可见索引测试 SQL 性能，对比原执行计划的耗时、IO、CPU。
   • 性能显著提升 → 转为可见（Visible）；无提升 → 标记为不可用（Unusable）。
5. 监控与维护（Monitor & Rebuild）
   • 持续跟踪索引使用频率，重建因表维护（如ALTER TABLE MOVE）失效的索引。
6. 自动清理（Drop）
   • 定期扫描未使用索引，达到保留期后自动删除，释放存储。

三、自动创建机制

1. 触发条件

• 高频查询缺少有效索引、全表扫描占比过高、响应时间超时。
• 表统计信息更新后，负载模式发生显著变化。

2. 安全策略（避免风险）

• 先不可见、后验证：确保新索引不破坏现有 SQL 稳定性。
• Online DDL：创建 / 重建不锁表（如 MySQL / 阿里云 DAS）。
• 可回滚：性能退化时自动禁用并生成 SQL 计划基线。

3. 索引类型

• 主流：B-Tree（通用）、Bitmap（低基数列）、函数 / 表达式索引。
• 特殊：向量索引（HNSW）、地理索引（BKD）、全文倒排索引（如 CrateDB）。

四、自动删除机制

1. 删除对象

• 自动索引（Auto Indexes）：系统创建、长期未使用的索引。
• 手动索引（Manual Indexes）：可配置是否自动清理（默认不删）。

2. 保留期（Retention）

• Oracle 默认：自动索引373 天、手动索引不自动删。
• 可通过参数调整：

  sql

  -- 自动索引保留60天
  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '60');
  -- 手动索引保留90天
  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '90');
  

  
  

  
  

3. 删除逻辑

• 索引连续 N 天未被优化器使用 → 标记为待删除。
• 保留期到期 → 后台自动DROP，不可手动删除（Oracle）。
• 重建不可用索引，而非直接删除。

五、主流数据库实现与配置

1. Oracle 19c+（自治数据库原生支持）

• 模式：
  • OFF：关闭。
  • REPORT ONLY：仅生成报告、创建不可见索引、不启用。
  • IMPLEMENT：自动创建并启用可见索引。
• 开启命令：

  sql

  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
  

  
  

  
  

2. 阿里云 DAS（MySQL/PostgreSQL）

• 开启路径：自治中心 → 自治功能 → 勾选自动索引创建 / 删除。
• 执行窗口：仅在可维护时间窗执行，避免业务高峰。

3. PostgreSQL（插件）

• hypopg：模拟索引、生成推荐，不自动创建。
• pg_auto_index：第三方扩展，实现自动创建 / 删除。

4. CrateDB（全文 / 向量数据库）

• 插入时自动为所有字段建索引（倒排 / BKD/HNSW），查询透明使用。

六、优缺点对比

表格

七、适用场景与最佳实践

1. 推荐使用

• 云数据库 / 自治数据库（如 Oracle ATP、阿里云 RDS）。
• 业务迭代快、SQL 变化频繁的 OLTP 系统。
• 缺乏专职 DBA 的中小团队。

2. 最佳实践

• 先在REPORT ONLY模式观察 1–2 周，确认推荐合理再启用IMPLEMENT。
• 合理设置保留期：自动索引 30–90 天、手动索引不自动删。
• 定期查看自动索引报告，监控性能变化。
• 关键业务 SQL 仍建议人工审核与固定执行计划。