index-strategies

索引策略

SQL Server 索引设计与优化综合指南。索引建议必须结合工作负载和约束条件：适用于某个查询的最佳索引，可能会损害写入性能、增加存储和维护成本、影响分区切换，或妨碍其他关键查询。

必要信息收集

在建议索引 DDL 之前，请收集以下信息，或注明其未知：

• SQL Server 版本、版本类型、兼容性级别和 Azure SQL 层级。
• 查询文本、有代表性的参数、实际执行计划、行数和谓词选择性。
• 现有的聚集、非聚集、筛选、列存储、唯一、已禁用、重复及重叠索引。
• 表 DDL、约束、数据类型、计算列、压缩、分区和统计信息。
• 写入工作负载：插入／更新／删除频率、批量加载、维护时段和存储预算。
• 变更约束：是否可以新增索引、是否可以修改超大型表的索引、是否允许联机／可恢复索引创建、是否需要分区切换、是否允许使用暂存表，以及由谁审批写入开销。

请使用 ../_shared/optimization-intake.md 和 ../_shared/assumption-tracker.md。未经现有索引和工作负载审查，不要将缺失索引 DMV 输出作为最终设计方案。

快速参考

类型	最适用场景	注意事项
聚集	主表排序、范围访问、窄且稳定的键	修改成本高；聚集键会包含在非聚集索引中。
非聚集	针对查询的查找、联接和排序	会增加写入和存储开销。
覆盖	避免重复键查找	过多的 INCLUDE 列会损害缓存和写入性能。
筛选	稳定且有选择性的子集	查询谓词必须蕴含筛选条件；参数可能会阻止使用索引。
列存储	分析、扫描、聚合和压缩	少量更新和单行查找可能会受影响。
唯一	强制执行业务规则并为优化器提供证明	必须符合实际语义。

索引设计工作流

1. 从查询形态入手

按作用对查询列进行分类：

作用	对索引设计的影响
等值谓词	通常放在前导键列，并根据选择性和工作负载复用情况排序。
联接键	可用作查找键并支持联接顺序。
范围谓词	通常放在等值键之后；只有一个范围条件可以实现深度查找。
ORDER BY / GROUP BY	考虑键的顺序，以避免排序或使用流聚合。
所选列	只有在查找成本足以抵消存储／写入成本时才使用 INCLUDE。

示例：

CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders(CustomerID, OrderDate)
INCLUDE (Status, TotalAmount);

2. 验证数据类型和 SARGability

如果谓词不可 SARG，或数据类型不匹配，索引就无法充分发挥作用。新增索引之前，请确认参数、临时表和源列的数据类型。尽可能先修复联接／筛选列上的 CONVERT_IMPLICIT。

3. 比较现有索引

新增索引之前：

• 检查能否安全扩展现有索引。
• 合并相互重叠的缺失索引请求。
• 找出前导键相同的重复索引。
• 新增 INCLUDE 列之前，评估查找次数和所选列。
• 考虑筛选索引或更窄的索引能否以更低的写入成本解决热点路径问题。

4. 考虑变更约束

对建议进行分类：

约束	更安全的应对方式
无法新增索引	重写查询、更新统计信息、使用提示、临时暂存或 Query Store 提示。
无法修改超大型表的索引	新增窄筛选索引、建立带索引的暂存表，或先减少行数，再访问该表。
不允许联机创建索引	规划维护时段并评估阻塞风险；在支持的情况下考虑可恢复创建。
需要分区切换	优先使用对齐索引；除非明确接受，否则避免非对齐索引。
写入工作负载繁重	尽量缩短键宽度并精简 INCLUDE 列；证明读取收益。
存储受限	合并重复索引，避免未经验证的覆盖索引。

聚集索引指南

理想的聚集键应当窄、唯一、静态，并且对于 OLTP 插入模式，通常应不断递增。

CREATE CLUSTERED INDEX CIX_Orders ON dbo.Orders(OrderID);

除非宽复合聚集键符合明确的访问模式和写入权衡，否则应避免使用。随机 GUID 聚集可能导致页拆分；适当时可考虑使用 NEWSEQUENTIALID()、代理键或填充因子策略。

覆盖索引

当查找成本较高时，覆盖索引可以避免查找操作。

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cover
ON dbo.Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);

对于仅用于输出的列，请使用 INCLUDE 列，不要将其用于筛选或排序。尽量缩短 INCLUDE 列列表；过多的 INCLUDE 列可能比偶尔进行查找更糟。

筛选索引

筛选索引适用于稳定的子集：

CREATE NONCLUSTERED INDEX IX_Orders_Open_ByCustomer
ON dbo.Orders(CustomerID, OrderDate)
WHERE Status = 'Open';

要求：

• 查询谓词必须蕴含筛选条件。
• 参数化查询可能需要重新编译或使用针对字面值的动态 SQL，才能可靠匹配。
• 如果筛选列对优化器而言不明显，可能需要将其包含在索引中。
• 验证参数嗅探风险和计划缓存行为。

列存储索引

对于分析扫描、聚合和压缩，请使用列存储。

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analysis
ON dbo.Orders(OrderDate, ProductID, Quantity, Amount)
WHERE Status = 'Completed';

最佳实践：

1. 尽可能以至少 102,400 行为批次加载数据。
2. 根据常用的消除列对数据排序，以便更好地消除段。
3. 使用 REORGANIZE 压缩增量行组并合并行组。
4. 纯列存储设计应避免高频的单行更新。
5. 当大型事实表需要滑动窗口时，可结合分区以提高可管理性。

分区对齐检查

对于分区表，索引设计必须考虑分区消除和维护。

请验证：

• 分区函数／方案、边界类型，以及 RANGE LEFT 与 RANGE RIGHT 的选择。
• 基表分区列与查询谓词列。
• 每个重要的非聚集索引是否已对齐，或是否有意设置为非对齐。
• 要求唯一索引包含分区列时，是否已将其包含在内。
• 执行计划中的实际分区消除情况。
• 不安全的谓词：对分区列使用函数、类型不匹配、筛选相关的非分区日期、OR 通配谓词或远程数据源。

除非日期筛选条件以兼容且 SARGable 的形式针对分区列，或可信约束能够证明等价性，否则不要声称实现了分区消除。请参阅 references/partition-alignment-analysis.md。

维护与运维

碎片整理规则取决于工作负载，以下是常见的起始参考值：

碎片率	典型操作
低于 5%	不采取操作。
5-30%	如果页数和工作负载情况允许，则重新组织。
高于 30%	如果维护时段、版本和阻塞情况允许，则重建。

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

当自动创建的统计信息或采样统计信息不足时，在进行重大索引更改后更新统计信息：

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

有用的诊断信息

自上次重启或数据库附加以来的索引使用情况：

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
  ON i.object_id = ius.object_id
 AND i.index_id = ius.index_id
 AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY COALESCE(ius.user_seeks, 0) + COALESCE(ius.user_scans, 0) DESC;

缺失索引候选项：

SELECT
    migs.avg_user_impact AS ImpactPercent,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups AS mig
JOIN sys.dm_db_missing_index_group_stats AS migs
  ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
  ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC;

建议格式

请提供：

1. 证据：计划运算符、读取次数、查找操作、估算值、缺失索引请求或工作负载指标。
2. DDL：拟议索引，包含架构限定名称和最精简的键／包含列。
3. 原因：访问路径、排序、覆盖或消除方面的收益。
4. 成本：存储、写入、阻塞、维护和分区方面的影响。
5. 验证：前后执行计划、逻辑读取次数、CPU、耗时和写入影响。
6. 回滚或替代方案：尤其适用于超大型表。

参考资料

• ../_shared/optimization-intake.md - 优化前的需求收集。
• ../_shared/assumption-tracker.md - 假设状态跟踪。
• references/partition-alignment-analysis.md - 分区方案和分区消除分析。