SQLServer中的索引碎片是指索引页的逻辑顺序和物理顺序不一致，或者索引页中存在大量空闲空间的情况，长期的数据增删改操作都会导致索引碎片不断累积，最终影响查询的执行效率。
索引碎片的检测方式
我们可以通过系统动态管理视图sys.dm_db_index_physical_stats来查询指定表或索引的碎片率，该视图会返回索引的页密度、碎片比例等关键信息。以下是查询某个数据库所有索引碎片率的示例代码：
-- 查询当前数据库所有用户表的索引碎片信息
SELECT
OBJECT_NAME(ips.object_id) AS 表名,
i.name AS 索引名,
ips.index_type_desc AS 索引类型,
ips.avg_fragmentation_in_percent AS 平均碎片率,
ips.page_count AS 索引页数
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.page_count > 10 -- 过滤掉页数过少的索引，这类索引碎片影响可忽略
ORDER BY ips.avg_fragmentation_in_percent DESC;
碎片优化的两种方案选择
根据碎片率的不同，我们需要选择不同的优化方式，具体判断标准如下：
| 平均碎片率范围 | 推荐优化方式 | 操作说明 |
|---|---|---|
| 5% - 30% | 索引重组 | 对索引页进行逻辑重新排序，操作是联机的，不会长时间阻塞业务 |
| 大于30% | 索引重建 | 删除旧索引并创建新索引，清理效果更好，但需要选择合适的执行时机 |
| 小于5% | 无需操作 | 碎片率处于合理范围，不会对性能产生明显影响 |
重建索引的正确步骤
步骤1：确认执行时机
索引重建会占用大量CPU和IO资源，同时如果是非企业版SQLServer，默认重建操作会锁定索引对应的表，因此建议选择在业务低峰期执行，避免影响线上业务。
步骤2：单索引重建操作
如果只需要重建某个特定的索引，可以使用ALTER INDEX语句完成，示例代码如下：
-- 重建指定表的指定索引，开启联机操作（企业版支持）
ALTER INDEX [索引名称]
ON [schema].[表名]
REBUILD
WITH (
ONLINE = ON, -- 联机重建，业务不中断，仅企业版生效
FILLFACTOR = 80, -- 填充因子，预留20%空间减少后续碎片产生
SORT_IN_TEMPDB = ON -- 排序操作在tempdb执行，减少用户数据库IO占用
);
步骤3：批量重建索引
如果需要重建某个数据库下所有碎片率超标的索引，可以生成动态SQL批量执行，示例代码如下：
-- 生成批量重建索引的SQL语句
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql +
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']
REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);' + CHAR(10)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
WHERE ips.avg_fragmentation_in_percent > 30
AND ips.page_count > 10
AND o.type = 'U' -- 仅处理用户表
AND i.name IS NOT NULL; -- 排除堆表
-- 执行生成的重建语句
EXEC sp_executesql @sql;
步骤4：操作后验证
重建完成后，再次执行碎片检测语句，确认目标索引的碎片率已经降到5%以下，同时可以观察对应查询的执行时间是否有明显下降。
重建索引的注意事项
- 重建索引前建议备份数据库，避免操作异常导致数据问题。
- 填充因子需要根据业务场景设置，频繁更新的表可以设置较低的填充因子，只读表可以设置100。
- 如果数据库启用了镜像或者AlwaysOn，重建索引会增加日志量，需要确保日志文件有足够空间。
- 避免在业务高峰期执行大规模的索引重建操作，防止拖慢整个数据库性能。
索引优化不是一次性操作，建议定期检测索引碎片率，根据业务情况制定周期性的索引维护计划，才能长期保持数据库的良好性能。