SQLServer 索引

索引是 SQL Server 性能优化的核心，合理的索引设计可以显著提高查询性能，减少 I/O 操作和 CPU 使用率。然而，不当的索引设计也会导致性能下降，增加维护成本。本文将详细介绍 SQL Server 索引的类型、设计原则、最佳实践和维护方法，帮助 DBA 设计和管理高效的索引策略。

索引基础概念

1. 索引的作用

索引是一种数据结构，用于快速定位表中的数据，主要作用包括：

提高查询性能：减少需要扫描的数据量，加速数据检索
加速 JOIN 操作：提高多表关联查询的性能
加速排序和分组：利用索引的有序性，避免额外的排序操作
强制数据唯一性：唯一索引可以确保数据的唯一性
支持外键约束：外键列的索引可以加速级联操作

2. 索引的工作原理

SQL Server 索引基于 B+ 树数据结构实现，主要包含以下组件：

根节点：B+ 树的顶层节点，指向中间节点或叶节点
中间节点：包含索引键值和指向下层节点的指针
叶节点：包含索引键值和指向数据行的指针

索引查询的工作流程：

从根节点开始，根据查询条件定位到对应的中间节点
沿着中间节点向下遍历，直到到达叶节点
在叶节点中查找匹配的索引键值
通过叶节点中的指针定位到实际的数据行

3. 索引的存储方式

SQL Server 索引有两种主要的存储方式：

聚集索引：数据行的物理存储顺序与索引键的逻辑顺序一致
非聚集索引：数据行的物理存储顺序与索引键的逻辑顺序无关，叶节点包含指向数据行的指针

索引类型

SQL Server 支持多种索引类型，以满足不同的查询需求：

1. 聚集索引 (Clustered Index)

特点：
- 每个表只能有一个聚集索引
- 数据行的物理存储顺序与索引键顺序一致
- 叶节点包含完整的数据行
- 适合范围查询和排序操作
- 主键约束默认创建聚集索引（除非指定非聚集）
最佳实践：
- 选择唯一、稳定、窄的列作为聚集索引键
- 考虑使用递增键，避免页分裂
- 适合频繁用于范围查询的列
- 避免使用 GUID 作为聚集索引键（可能导致索引碎片）

2. 非聚集索引 (Nonclustered Index)

特点：
- 每个表可以有多个非聚集索引（最多 999 个）
- 数据行的物理存储顺序与索引键顺序无关
- 叶节点包含索引键和指向数据行的指针（书签）
- 适合频繁用于过滤和连接的列
- 可以包含非键列，减少书签查找
最佳实践：
- 为频繁查询的列创建非聚集索引
- 考虑索引的选择性，选择区分度高的列
- 避免创建过多的非聚集索引，影响写性能
- 合理使用包含列，减少书签查找

3. 唯一索引 (Unique Index)

特点：
- 确保索引键值唯一
- 可以是聚集或非聚集索引
- 允许 NULL 值（最多一个）
- 唯一约束自动创建唯一索引
- 适合用于唯一标识符列
最佳实践：
- 为需要唯一性保证的列创建唯一索引
- 考虑使用唯一索引替代业务逻辑中的唯一性检查
- 唯一索引可以提高查询性能，因为查询优化器知道值是唯一的

4. 包含列索引 (Included Columns Index)

特点：
- 在非聚集索引中包含额外的非键列
- 叶节点包含索引键和包含列
- 减少书签查找，提高查询性能
- 包含列不影响索引键的排序
- 支持大数据类型（如 varchar(max)）
最佳实践：
- 为频繁在 SELECT 子句中出现的列创建包含列索引
- 避免包含过多的列，增加索引大小
- 考虑包含列的数据类型和大小
- 适合覆盖查询（Covering Query）

5. 过滤索引 (Filtered Index)

特点：
- 只包含满足特定条件的数据行
- 减少索引大小，提高查询性能
- 适合数据分布不均匀的列
- 可以与包含列结合使用
- SQL Server 2008 及以上版本支持
最佳实践：
- 为频繁查询的特定值范围创建过滤索引
- 适合包含大量 NULL 值的列
- 考虑过滤条件的选择性
- 定期更新统计信息，确保查询优化器选择过滤索引

6. 列存储索引 (Columnstore Index)

特点：
- 列式存储格式，适合数据仓库和分析工作负载
- 极高的数据压缩率（通常为 10:1 或更高）
- 支持批量更新和实时操作
- 可以与行存储索引并存
- 支持聚集和非聚集 Columnstore 索引
- SQL Server 2012 及以上版本支持
最佳实践：
- 为大型数据仓库表创建 Columnstore 索引
- 考虑使用增量更新，减少索引重建时间
- 适合批量导入和查询的场景
- 结合行存储索引，支持实时操作

7. 空间索引 (Spatial Index)

特点：
- 用于地理空间数据类型（geometry 和 geography）
- 加速空间查询，如距离计算和空间关系查询
- 支持多种空间索引类型
- SQL Server 2008 及以上版本支持
最佳实践：
- 为频繁进行空间查询的列创建空间索引
- 考虑空间数据的分布和查询模式
- 定期重建空间索引，优化性能

8. 全文索引 (Full-Text Index)

特点：
- 用于文本数据的全文搜索
- 支持关键词搜索、短语搜索和模糊搜索
- 可以索引 char、varchar、text、nchar、nvarchar、ntext 和 xml 列
- SQL Server 2005 及以上版本支持
最佳实践：
- 为需要全文搜索的文本列创建全文索引
- 考虑使用停用词表，提高搜索效率
- 定期更新全文索引，确保搜索结果准确

9. XML 索引

特点：
- 用于 XML 数据类型
- 加速 XML 数据的查询和修改
- 支持主 XML 索引和辅助 XML 索引
- SQL Server 2005 及以上版本支持
最佳实践：
- 为频繁查询的 XML 列创建 XML 索引
- 考虑 XML 数据的结构和查询模式
- 先创建主 XML 索引，再创建辅助 XML 索引

索引设计原则

1. 索引设计考虑因素

查询模式：分析常用查询，为 WHERE、JOIN 和 ORDER BY 子句中的列创建索引
数据分布：考虑列的数据分布和选择性，选择区分度高的列
表大小：对于小表，索引可能不会带来性能提升
更新频率：考虑索引对写操作的影响，避免为频繁更新的列创建过多索引
索引大小：尽量使用窄索引，减少存储空间和 I/O 操作
复合索引顺序：将选择性高的列放在复合索引的前面

2. 复合索引设计

复合索引是包含多个列的索引，设计时需要考虑：

列顺序：将选择性高的列放在前面，提高索引选择性
查询覆盖：尽量覆盖常用查询，减少书签查找
前缀匹配：复合索引支持前缀匹配，如索引 (a, b, c) 可以用于查询 a、a+b、a+b+c
避免冗余：如果已有索引 (a, b)，则不需要再创建索引 (a)
考虑 ORDER BY：如果查询中使用 ORDER BY，可以将排序列包含在索引中

3. 索引选择性

索引选择性是指索引列中唯一值的比例，选择性越高，索引效果越好。

计算公式：选择性 = 唯一值数量 / 总行数
高选择性：选择性接近 1，如主键列
低选择性：选择性接近 0，如性别列
最佳实践：为选择性高的列创建索引，选择性低于 5% 的列通常不适合创建索引

4. 避免过度索引

写操作开销：每个索引都会增加 INSERT、UPDATE 和 DELETE 操作的开销
存储空间：索引需要占用存储空间，过多的索引会增加存储成本
维护成本：索引需要定期维护，过多的索引会增加维护成本
计划缓存膨胀：过多的索引会导致计划缓存膨胀，影响查询优化器性能

索引创建与管理

1. 创建索引

使用 CREATE INDEX 语句

sql

-- 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_Employee_LastName
ON HumanResources.Employee (LastName);

-- 创建包含列索引
CREATE NONCLUSTERED INDEX IX_Employee_LastName_FirstName
ON HumanResources.Employee (LastName)
INCLUDE (FirstName, EmailAddress);

-- 创建唯一索引
CREATE UNIQUE NONCLUSTERED INDEX IX_Employee_EmployeeID
ON HumanResources.Employee (EmployeeID);

-- 创建过滤索引
CREATE NONCLUSTERED INDEX IX_Employee_Active
ON HumanResources.Employee (HireDate)
WHERE IsActive = 1;

使用 SSMS 创建索引

打开 SQL Server Management Studio
连接到 SQL Server 实例
展开数据库和表
右键点击 "索引"，选择 "新建索引" → "非聚集索引"
在 "新建索引" 对话框中，选择要包含的列和设置
点击 "确定" 创建索引

2. 修改索引

重命名索引

sql

EXEC sp_rename N'HumanResources.Employee.IX_Employee_LastName', N'IX_Employee_LastName_New', N'INDEX';

添加包含列

sql

CREATE NONCLUSTERED INDEX IX_Employee_LastName_FirstName
ON HumanResources.Employee (LastName)
INCLUDE (FirstName)
WITH (DROP_EXISTING = ON);

3. 删除索引

sql

DROP INDEX IX_Employee_LastName ON HumanResources.Employee;

索引维护

1. 索引碎片

索引碎片是指索引页中的逻辑顺序与物理顺序不一致，或索引页中的可用空间过多。

碎片类型

外部碎片：索引页的物理顺序与逻辑顺序不一致
内部碎片：索引页中的可用空间过多

碎片影响

查询性能下降：需要更多的 I/O 操作来读取数据
存储效率降低：占用更多的存储空间
维护成本增加：索引维护需要更多的资源

碎片检测

使用 sys.dm_db_index_physical_stats DMV 检测索引碎片：

sql

SELECT
    object_name(object_id) AS TableName,
    name AS IndexName,
    index_id AS IndexID,
    index_type_desc AS IndexType,
    avg_fragmentation_in_percent AS FragmentationPercent,
    page_count AS PageCount
FROM sys.dm_db_index_physical_stats(
    DB_ID('AdventureWorks2019'),
    OBJECT_ID('HumanResources.Employee'),
    NULL,
    NULL,
    'LIMITED'
) AS ips
JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id;

碎片处理

重组索引 (REORGANIZE)：
- 适合碎片率在 5% 到 30% 之间的索引
- 在线操作，不会锁定表
- 重新组织索引页，减少外部碎片
- 不改变索引的物理结构
sql
```
ALTER INDEX IX_Employee_LastName ON HumanResources.Employee REORGANIZE;
```

重建索引 (REBUILD)：

适合碎片率超过 30% 的索引
可以在线或离线执行
重新创建索引，消除碎片
可以重新排序索引键
可以修改索引选项

sql

-- 离线重建
ALTER INDEX IX_Employee_LastName ON HumanResources.Employee REBUILD;

-- 在线重建
ALTER INDEX IX_Employee_LastName ON HumanResources.Employee REBUILD WITH (ONLINE = ON);

2. 更新统计信息

统计信息是查询优化器生成执行计划的重要依据，过期的统计信息会导致查询优化器生成低效的执行计划。

统计信息更新方式

自动更新：SQL Server 会自动更新统计信息，基于数据修改量
手动更新：使用 UPDATE STATISTICS 语句手动更新

手动更新统计信息

sql

-- 更新表的所有统计信息
UPDATE STATISTICS HumanResources.Employee;

-- 更新特定索引的统计信息
UPDATE STATISTICS HumanResources.Employee IX_Employee_LastName;

-- 完整扫描更新统计信息
UPDATE STATISTICS HumanResources.Employee WITH FULLSCAN;

-- 使用采样更新统计信息
UPDATE STATISTICS HumanResources.Employee WITH SAMPLE 20 PERCENT;

3. 索引监控

使用 DMV 监控索引使用情况：

sql

SELECT
    object_name(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalAccesses,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID('AdventureWorks2019')
ORDER BY TotalAccesses DESC;

识别未使用的索引：

sql

SELECT
    object_name(i.object_id) AS TableName,
    i.name AS IndexName,
    i.index_id
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE i.object_id = OBJECT_ID('HumanResources.Employee')
AND s.object_id IS NULL;

索引性能优化

1. 索引提示

在某些情况下，可以使用索引提示强制查询优化器使用特定索引：

sql

SELECT * FROM HumanResources.Employee WITH (INDEX(IX_Employee_LastName)) WHERE LastName = 'Smith';

注意：索引提示应谨慎使用，因为查询优化器通常比人工更能选择合适的索引。

2. 计划指南

计划指南用于指导查询优化器生成特定的执行计划，而不修改应用程序代码：

sql

-- 创建计划指南
EXEC sp_create_plan_guide
    @name = N'PG_Employee_LastName',
    @stmt = N'SELECT * FROM HumanResources.Employee WHERE LastName = @LastName',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@LastName NVARCHAR(50)',
    @hints = N'OPTION (TABLE HINT(Employee, INDEX(IX_Employee_LastName)))';

3. 查询优化器统计信息

确保查询优化器有准确的统计信息：

启用自动创建和更新统计信息
定期手动更新统计信息，特别是对于大型表
考虑使用 FULLSCAN 更新统计信息，提高准确性
为列创建统计信息，即使没有索引

4. 避免索引失效

避免在索引列上使用函数：WHERE DATEPART(YEAR, OrderDate) = 2023 会导致索引失效
避免使用不等运算符：<>, !=, NOT IN 可能导致索引失效
避免使用 NULL 比较：IS NULL 或 IS NOT NULL 可能导致索引失效
避免使用 LIKE 通配符开头：LIKE '%Smith' 会导致索引失效，而 LIKE 'Smith%' 可以使用索引
避免类型转换：不同数据类型之间的比较会导致索引失效

版本差异

1. SQL Server 2012 及之前

基本的索引类型支持
有限的 Columnstore 索引支持（只读）
没有内存优化表的哈希索引和范围索引

2. SQL Server 2014

增强的 Columnstore 索引（支持更新）
引入内存优化表的哈希索引和范围索引
改进的索引重建功能

3. SQL Server 2016

增强的 Columnstore 索引（实时操作分析）
引入自适应查询处理，优化索引使用
改进的统计信息更新机制

4. SQL Server 2017

跨平台支持，在 Linux 上的索引实现
增强的自动索引管理
改进的索引性能

5. SQL Server 2019

增强的 Columnstore 索引性能
引入持久化内存支持，优化索引访问
改进的索引碎片管理

6. SQL Server 2022

增强的智能查询处理，优化索引使用
改进的索引性能
增强的自动索引建议

常见问题 (FAQ)

Q: 如何选择聚集索引键？

A: 选择聚集索引键时应考虑：

唯一性：聚集索引键必须唯一
稳定性：避免频繁更新的列
窄宽度：使用窄列，减少索引大小
递增性：考虑使用递增键，避免页分裂
查询模式：考虑范围查询和排序需求

Q: 何时应该使用包含列索引？

A: 使用包含列索引的场景：

查询中需要返回多个列，而这些列不在索引键中
希望减少书签查找，提高查询性能
包含列可以是大数据类型，而索引键不能

Q: 如何识别未使用的索引？

A: 识别未使用的索引的方法：

使用 sys.dm_db_index_usage_stats DMV 监控索引使用情况
查找 user_seeks、user_scans 和 user_lookups 为 0 的索引
考虑删除长期未使用的索引，减少维护成本

Q: 如何优化索引碎片？

A: 优化索引碎片的方法：

定期检测索引碎片，使用 sys.dm_db_index_physical_stats DMV
碎片率在 5% 到 30% 之间，使用 REORGANIZE
碎片率超过 30%，使用 REBUILD
考虑使用填充因子，减少页分裂

Q: 为什么查询没有使用预期的索引？

A: 查询没有使用预期索引的可能原因：

索引统计信息过期，查询优化器没有准确的统计信息
索引选择性低，查询优化器认为全表扫描更高效
查询中使用了函数或类型转换，导致索引失效
索引列在查询中被修改，导致索引失效
查询优化器认为其他索引或执行计划更高效

Q: 如何平衡索引的读写性能？

A: 平衡索引读写性能的方法：

只为频繁查询的列创建索引
考虑索引的选择性，选择区分度高的列
避免创建过多的非聚集索引
考虑使用 Columnstore 索引，适合读写混合工作负载
定期维护索引，减少碎片

总结

索引是 SQL Server 性能优化的核心，合理的索引设计可以显著提高查询性能。DBA 应该根据查询模式、数据分布和业务需求，设计和管理高效的索引策略。同时，需要定期监控和维护索引，确保索引的有效性和性能。

通过本文的介绍，相信读者已经对 SQL Server 索引有了深入的了解，包括索引类型、设计原则、创建方法、优化技巧和维护最佳实践。在实际生产环境中，DBA 应该结合具体的业务场景，灵活运用这些知识，不断优化索引策略，提高系统性能和可靠性。

SQLServer 索引 ​

索引基础概念 ​

1. 索引的作用 ​

2. 索引的工作原理 ​

3. 索引的存储方式 ​

索引类型 ​

1. 聚集索引 (Clustered Index) ​

2. 非聚集索引 (Nonclustered Index) ​

3. 唯一索引 (Unique Index) ​

4. 包含列索引 (Included Columns Index) ​

5. 过滤索引 (Filtered Index) ​

6. 列存储索引 (Columnstore Index) ​

7. 空间索引 (Spatial Index) ​

8. 全文索引 (Full-Text Index) ​

9. XML 索引 ​

索引设计原则 ​

1. 索引设计考虑因素 ​

2. 复合索引设计 ​

3. 索引选择性 ​

4. 避免过度索引 ​

索引创建与管理 ​

1. 创建索引 ​

使用 CREATE INDEX 语句 ​

使用 SSMS 创建索引 ​

2. 修改索引 ​

重命名索引 ​

添加包含列 ​

3. 删除索引 ​

索引维护 ​

1. 索引碎片 ​

碎片类型 ​

碎片影响 ​

碎片检测 ​

碎片处理 ​

2. 更新统计信息 ​

统计信息更新方式 ​

手动更新统计信息 ​

3. 索引监控 ​

索引性能优化 ​

1. 索引提示 ​

2. 计划指南 ​

3. 查询优化器统计信息 ​

4. 避免索引失效 ​

版本差异 ​

1. SQL Server 2012 及之前 ​

2. SQL Server 2014 ​

3. SQL Server 2016 ​

4. SQL Server 2017 ​

5. SQL Server 2019 ​

6. SQL Server 2022 ​

常见问题 (FAQ) ​

Q: 如何选择聚集索引键？ ​

Q: 何时应该使用包含列索引？ ​

Q: 如何识别未使用的索引？ ​

Q: 如何优化索引碎片？ ​

Q: 为什么查询没有使用预期的索引？ ​

Q: 如何平衡索引的读写性能？ ​

总结 ​