如何识别数据仓库中需保留及可删除的索引？

一、找出未被使用的索引 #

报表需求变更后，很多索引可能已无人问津，优先排查这类闲置索引：

• 利用数据库系统视图统计使用情况
  主流数据库都自带跟踪索引使用的系统视图，能直接获取索引的查询调用次数（seeks/scans/lookups）。以SQL Server为例，执行以下查询可找出长期未被使用的索引：

  SELECT 
      OBJECT_NAME(s.object_id) AS table_name,
      i.name AS index_name,
      ISNULL(user_seeks + user_scans + user_lookups, 0) AS total_usage
  FROM 
      sys.indexes i
  LEFT JOIN 
      sys.dm_db_index_usage_stats s 
      ON i.object_id = s.object_id AND i.index_id = s.index_id
  WHERE 
      OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
      AND i.type_desc <> 'HEAP' -- 排除堆表
      AND i.is_primary_key = 0 -- 先跳过主键索引
      AND i.is_unique_constraint = 0 -- 跳过唯一约束索引
  ORDER BY 
      total_usage ASC;
  

  注意：统计数据要覆盖至少一个完整业务周期（比如一周），包含所有报表的运行时段，避免误删仅在特定时段使用的索引。

• 分析当前查询执行计划
  抓取所有现有报表的执行计划，检查哪些索引被实际调用。可以通过数据库的计划缓存（如SQL Server的sys.dm_exec_query_plan）或查询存储（Query Store）跟踪。如果某个索引从未出现在任何报表的执行计划里，基本可判定为闲置索引。

二、识别冗余索引 #

有些索引功能完全被其他索引覆盖，属于冗余范畴：

• 重复索引
  指两个索引的键列完全相同（顺序一致），或者一个索引是另一个的前缀（比如索引A是(col1, col2)，索引B是(col1)）。可通过对比索引列排查：

  WITH index_columns AS (
      SELECT 
          object_id,
          index_id,
          name AS index_name,
          STRING_AGG(COL_NAME(object_id, column_id), ', ') WITHIN GROUP (ORDER BY key_ordinal) AS key_columns
      FROM 
          sys.indexes i
      JOIN 
          sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
      WHERE 
          OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
          AND i.is_primary_key = 0
          AND i.is_unique_constraint = 0
      GROUP BY 
          object_id, index_id, name
  )
  SELECT 
      OBJECT_NAME(ic1.object_id) AS table_name,
      ic1.index_name AS index1,
      ic2.index_name AS index2,
      ic1.key_columns
  FROM 
      index_columns ic1
  JOIN 
      index_columns ic2 
      ON ic1.object_id = ic2.object_id 
      AND ic1.index_id < ic2.index_id 
      AND ic1.key_columns = ic2.key_columns;
  

  前缀索引需结合查询场景判断：如果所有用到col1的查询都能通过(col1, col2)索引满足，那单独的(col1)索引就是冗余的。

• 覆盖索引冗余
  如果索引A包含了索引B的所有键列和INCLUDE列，且所有依赖B的查询都能被A覆盖，那么B就是冗余的。比如索引A是(col1) INCLUDE (col2, col3)，索引B是(col1) INCLUDE (col2)，此时B的功能完全被A覆盖，除非有大量仅需col1和col2的轻量查询，但数据仓库中这种场景较少。

三、删除前的关键注意事项 #

• 先标记再观察：不要直接删除候选索引，先标记为待删除，持续观察1-2个业务周期，确认没有查询依赖后再动手。
• 考虑维护成本：数据仓库每日更新两次，索引会增加写入操作的耗时和资源占用。如果某个索引偶尔被使用，但维护成本远大于查询收益，也建议删除。
• 排查ETL依赖：部分索引可能是为ETL的增量更新、数据校验设计的，即便报表不用，也要确认ETL流程是否还依赖它们。
• 保留核心约束索引：主键索引、唯一约束索引除非业务规则明确变更，否则绝对不能删除，它们是保证数据一致性的基础。

内容的提问来源于stack exchange，提问作者theking2