SQLServer 索引维护

索引维护概述

索引维护是 SQL Server 数据库管理的核心任务之一，直接关系到数据库的查询性能和系统稳定性。随着数据的频繁增删改操作，索引会逐渐产生碎片化，导致查询响应时间延长、I/O 开销增加、存储空间浪费，甚至影响整个数据库实例的性能。

对于生产环境中的 DBA 来说，建立一套科学合理的索引维护策略至关重要。不同版本的 SQL Server 在索引维护功能上存在差异，需要根据实际环境选择合适的维护方法。

索引碎片化

碎片化的类型

SQL Server 索引碎片化主要分为两种类型：

内部碎片化：

索引页填充率低于预期值，导致每页存储的数据量不足
主要原因：删除操作、过低的填充因子设置、页分裂后的空间浪费
影响：增加 I/O 开销，因为查询需要读取更多的页数

外部碎片化：

索引页在物理磁盘上的存储顺序与逻辑顺序不一致
主要原因：插入/更新操作导致的页分裂，特别是在索引中间插入数据
影响：增加磁盘寻道时间，降低 I/O 效率

碎片化的影响

在生产环境中，严重的索引碎片化会导致：

核心业务查询响应时间延长，影响用户体验
数据库服务器 CPU 和 I/O 使用率异常升高
备份和恢复时间增加，影响灾难恢复能力
存储成本上升，因为碎片化索引占用更多空间

碎片化的测量

使用 sys.dm_db_index_physical_stats DMV 是生产环境中最常用的碎片化测量方法：

sql

-- 生产环境推荐使用 LIMITED 模式，性能更好
SELECT 
    DB_NAME(database_id) AS 数据库名,
    SCHEMA_NAME(o.schema_id) AS 架构名,
    OBJECT_NAME(object_id) AS 表名,
    i.name AS 索引名,
    index_type_desc AS 索引类型,
    avg_fragmentation_in_percent AS 外部碎片率,
    avg_page_space_used_in_percent AS 页填充率,
    page_count AS 索引页数
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN 
    sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
JOIN
    sys.objects o ON i.object_id = o.object_id
WHERE 
    ips.index_id > 0 -- 排除堆表
    AND o.type = 'U' -- 只检查用户表
    AND ips.page_count >= 1000 -- 只检查大型索引
    AND avg_fragmentation_in_percent > 5 -- 只显示有明显碎片的索引
ORDER BY 
    avg_fragmentation_in_percent DESC;

生产环境注意事项：

避免使用 DETAILED 模式，尤其是在大型数据库上，会产生严重的性能开销
建议在业务低峰期执行碎片化分析
记录分析结果，用于评估维护效果

索引维护操作

索引重组 (REORGANIZE)

核心特点：

在线操作，不会阻塞查询和数据修改
仅重新组织索引页，不重建索引结构
主要减少外部碎片化，对内部碎片化改善有限
保留现有索引统计信息
适用于低到中度碎片化（5%-30%）

生产环境示例：

sql

-- 重组单个索引，同时压缩 LOB 数据
ALTER INDEX IX_Order_CustomerID ON dbo.Orders REORGANIZE WITH (LOB_COMPACTION = ON);

-- 重组表上所有索引，生产环境建议逐个索引处理
ALTER INDEX ALL ON dbo.Orders REORGANIZE WITH (LOB_COMPACTION = ON);

版本差异：

SQL Server 2012-2014：仅支持基本的 REORGANIZE 操作
SQL Server 2016+：支持在线 LOB 压缩，优化了重组算法

索引重建 (REBUILD)

核心特点：

可选择在线或离线执行
完全重建索引结构，消除内外碎片化
自动更新索引统计信息
适用于高度碎片化（>30%）
可以配置填充因子、压缩等高级选项

生产环境示例：

sql

-- 在线重建单个索引，生产环境推荐使用 ONLINE=ON
ALTER INDEX IX_Order_CustomerID ON dbo.Orders REBUILD WITH (
    ONLINE = ON, -- 在线执行，减少阻塞
    FILLFACTOR = 80, -- 根据数据修改频率设置
    SORT_IN_TEMPDB = ON, -- 利用 tempdb 排序，加速重建
    DATA_COMPRESSION = PAGE, -- 启用页压缩，节省存储空间
    MAXDOP = 4 -- 限制并行度，避免资源竞争
);

-- 重建表上所有索引
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (
    ONLINE = ON,
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON
);

版本差异：

SQL Server 2012：在线重建不支持包含 LOB 列的索引
SQL Server 2014：支持包含 LOB 列的在线重建，但性能较差
SQL Server 2016+：大幅优化在线重建性能，支持更多索引类型
SQL Server 2019+：引入加速数据库恢复 (ADR)，进一步减少在线重建的影响

统计信息更新

索引维护不仅包括碎片化处理，还需要确保统计信息的准确性。生产环境中，过时的统计信息可能导致查询优化器选择错误的执行计划，造成性能问题。

生产环境示例：

sql

-- 更新单个索引的统计信息，使用采样
UPDATE STATISTICS dbo.Orders IX_Order_CustomerID WITH SAMPLE 20 PERCENT;

-- 更新表上所有统计信息，生产环境建议使用增量更新（SQL Server 2014+）
UPDATE STATISTICS dbo.Orders WITH RESAMPLE, INCREMENTAL = ON;

-- 完全扫描更新统计信息，仅在必要时使用
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

版本差异：

SQL Server 2012：支持基本的统计信息更新
SQL Server 2014+：支持增量统计信息更新，减少大型表的统计更新开销
SQL Server 2016+：自动统计信息更新更智能，支持动态采样

索引维护策略

根据碎片化程度选择维护操作

碎片化程度	索引大小	推荐操作
< 5%	所有大小	不需要维护
5% - 30%	> 1000 页	索引重组 (REORGANIZE)
> 30%	> 1000 页	索引重建 (REBUILD)
任何程度	< 1000 页	不需要维护（维护成本可能高于收益）

生产环境维护时间选择

核心业务系统：选择凌晨 2-4 点等绝对低峰期
报表/分析系统：选择非业务分析时段
维护窗口规划：根据维护操作的预计时长设置合理的窗口
并发控制：使用 Resource Governor 限制维护操作的资源使用

自动化索引维护

生产环境中，手动执行索引维护效率低下且容易出错。建议使用 SQL Server Agent 作业自动化维护任务。

生产级自动化脚本：

sql

CREATE PROCEDURE dbo.usp_IndexMaintenance
    @DatabaseName SYSNAME = NULL,
    @FragmentationLow FLOAT = 5.0,
    @FragmentationHigh FLOAT = 30.0,
    @PageCountThreshold INT = 1000,
    @LogResults BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 记录维护日志的表（需提前创建）
    CREATE TABLE IF NOT EXISTS dbo.IndexMaintenanceLog (
        LogID INT IDENTITY(1,1) PRIMARY KEY,
        DatabaseName SYSNAME,
        SchemaName SYSNAME,
        TableName SYSNAME,
        IndexName SYSNAME,
        IndexTypeDesc NVARCHAR(60),
        FragmentationBefore FLOAT,
        FragmentationAfter FLOAT,
        MaintenanceOperation NVARCHAR(50),
        StartTime DATETIME2(3),
        EndTime DATETIME2(3),
        DurationSeconds INT,
        Status NVARCHAR(20)
    );
    
    DECLARE @sql NVARCHAR(MAX), @startTime DATETIME2(3), @endTime DATETIME2(3);
    DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME, @IndexTypeDesc NVARCHAR(60);
    DECLARE @FragmentationBefore FLOAT, @FragmentationAfter FLOAT;
    DECLARE @Operation NVARCHAR(50), @Status NVARCHAR(20);
    
    -- 临时表存储需要维护的索引
    CREATE TABLE #IndexesToMaintain (
        DatabaseName SYSNAME,
        SchemaName SYSNAME,
        TableName SYSNAME,
        IndexName SYSNAME,
        IndexTypeDesc NVARCHAR(60),
        Fragmentation FLOAT,
        PageCount INT
    );
    
    -- 收集需要维护的索引信息
    INSERT INTO #IndexesToMaintain
    EXEC sp_MSforeachdb 'USE [?];
    IF DB_ID(?) = DB_ID(@DatabaseName) OR @DatabaseName IS NULL
    BEGIN
        SELECT 
            DB_NAME() AS DatabaseName,
            SCHEMA_NAME(o.schema_id) AS SchemaName,
            OBJECT_NAME(ips.object_id) AS TableName,
            i.name AS IndexName,
            ips.index_type_desc AS IndexTypeDesc,
            ips.avg_fragmentation_in_percent AS Fragmentation,
            ips.page_count AS PageCount
        FROM 
            sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') ips
        JOIN 
            sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
        JOIN 
            sys.objects o ON i.object_id = o.object_id
        WHERE 
            ips.index_id > 0
            AND o.type = ''U''
            AND ips.avg_fragmentation_in_percent > @FragmentationLow
            AND ips.page_count >= @PageCountThreshold
    END', @DatabaseName, @FragmentationLow, @PageCountThreshold;
    
    -- 游标遍历处理每个索引
    DECLARE index_cursor CURSOR FAST_FORWARD FOR
    SELECT DatabaseName, SchemaName, TableName, IndexName, IndexTypeDesc, Fragmentation
    FROM #IndexesToMaintain
    ORDER BY DatabaseName, PageCount DESC; -- 先处理大型索引
    
    OPEN index_cursor;
    FETCH NEXT FROM index_cursor INTO @DatabaseName, @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @FragmentationBefore;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @startTime = SYSUTCDATETIME();
        SET @Status = 'Success';
        
        BEGIN TRY
            -- 根据碎片化程度选择维护操作
            IF @FragmentationBefore < @FragmentationHigh
            BEGIN
                SET @Operation = 'REORGANIZE';
                SET @sql = 'USE [' + @DatabaseName + ']; ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE WITH (LOB_COMPACTION = ON);';
            END
            ELSE
            BEGIN
                SET @Operation = 'REBUILD';
                SET @sql = 'USE [' + @DatabaseName + ']; ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (ONLINE = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON);';
            END
            
            -- 执行维护操作
            EXEC sp_executesql @sql;
            
            -- 获取维护后的碎片化程度
            SET @sql = 'USE [' + @DatabaseName + ']; 
            SELECT @FragmentationAfter = avg_fragmentation_in_percent 
            FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(''' + @SchemaName + '.' + @TableName + '''), INDEXPROPERTY(OBJECT_ID(''' + @SchemaName + '.' + @TableName + '''), ''' + @IndexName + ''', ''IndexID''), NULL, ''LIMITED'');';
            EXEC sp_executesql @sql, N'@FragmentationAfter FLOAT OUTPUT', @FragmentationAfter OUTPUT;
            
        END TRY
        BEGIN CATCH
            SET @Status = 'Failed';
            SET @FragmentationAfter = -1;
            -- 记录错误信息（可扩展）
        END CATCH
        
        SET @endTime = SYSUTCDATETIME();
        
        -- 记录维护结果
        IF @LogResults = 1
        BEGIN
            INSERT INTO dbo.IndexMaintenanceLog (
                DatabaseName, SchemaName, TableName, IndexName, IndexTypeDesc,
                FragmentationBefore, FragmentationAfter, MaintenanceOperation,
                StartTime, EndTime, DurationSeconds, Status
            )
            VALUES (
                @DatabaseName, @SchemaName, @TableName, @IndexName, @IndexTypeDesc,
                @FragmentationBefore, @FragmentationAfter, @Operation,
                @startTime, @endTime, DATEDIFF(SECOND, @startTime, @endTime), @Status
            );
        END
        
        FETCH NEXT FROM index_cursor INTO @DatabaseName, @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @FragmentationBefore;
    END
    
    CLOSE index_cursor;
    DEALLOCATE index_cursor;
    
    -- 清理临时表
    DROP TABLE #IndexesToMaintain;
END;

自动化作业配置：

创建 SQL Server Agent 作业
添加 T-SQL 步骤，执行上述存储过程
配置调度：例如每周日凌晨 3 点执行
配置通知：作业失败时发送邮件/短信告警
添加日志清理步骤：定期清理维护日志表

生产环境最佳实践

1. 合理设置填充因子

频繁修改的表：设置较低的填充因子（60%-80%），减少页分裂
静态表：设置较高的填充因子（90%-100%），提高存储效率
服务器级别默认值：不建议修改，建议针对特定索引单独设置

生产示例：

sql

-- 针对频繁更新的订单表索引设置填充因子
ALTER INDEX IX_Order_Status ON dbo.Orders REBUILD WITH (FILLFACTOR = 70);

2. 优化 tempdb 配置

索引重建的 SORT_IN_TEMPDB 选项会使用 tempdb 进行排序，生产环境需优化 tempdb 配置：

为每个 CPU 核心创建一个 tempdb 数据文件，最多 8 个
所有 tempdb 数据文件大小相同，避免分配争用
将 tempdb 放置在高性能存储上（如 SSD）
设置合理的自动增长参数，避免频繁扩容

3. 监控维护操作

生产环境中，需密切监控索引维护操作，避免对业务造成影响：

sql

-- 监控正在执行的索引维护操作
SELECT 
    r.session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.start_time,
    DATEADD(ms, r.estimated_completion_time, GETDATE()) AS 预计完成时间,
    t.text AS SQL语句
FROM 
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
    r.command IN ('ALTER INDEX', 'UPDATE STATISTICS')
ORDER BY 
    r.start_time;

4. 利用企业版功能

如果使用 SQL Server 企业版，可利用高级功能优化索引维护：

分区索引：只维护碎片化严重的分区，减少维护时间
数据压缩：重建索引时启用压缩，节省存储空间
列存储索引：使用 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) 优化

5. 避免过度维护

生产环境中，过度的索引维护会浪费资源：

不要每天执行索引维护，根据数据变化频率调整
小型索引（< 1000 页）即使碎片化严重，维护收益也可能不明显
监控维护前后的性能变化，验证维护效果

常见问题

Q: 生产环境中，在线索引重建会影响业务吗？

A：

在线索引重建会占用更多系统资源（CPU、内存、I/O）
会生成大量事务日志，需确保日志备份频率足够
可能导致锁升级或阻塞，建议在低峰期执行
SQL Server 2016+ 版本对在线重建进行了大幅优化，影响明显降低

Q: 如何确定索引维护的频率？

A：

对于频繁修改的表（如订单表）：每周维护一次
对于中等修改频率的表：每两周维护一次
对于静态表（如字典表）：每月或每季度维护一次
建议先进行为期一个月的监控，根据碎片化增长速度调整频率

Q: 索引重建和重组，哪个对性能提升更明显？

A：

索引重建能完全消除碎片化，效果更明显
但索引重建的资源消耗和锁开销更大
对于高度碎片化的大型索引，重建的长期收益更高
对于中度碎片化的索引，重组是更平衡的选择

Q: 为什么维护后索引碎片化很快又增加了？

A：

可能是填充因子设置不合理，建议根据数据修改频率调整
可能是索引设计问题，如索引键选择不当导致频繁页分裂
可能是业务高峰期数据修改量过大，建议调整维护频率
考虑使用过滤索引或调整索引覆盖范围，减少索引大小

Q: 如何处理包含 LOB 列的索引维护？

A：

SQL Server 2012：在线重建不支持包含 LOB 列的索引，需离线执行
SQL Server 2014+：支持在线重建包含 LOB 列的索引
重组操作始终支持包含 LOB 列的索引，并可通过 LOB_COMPACTION = ON 压缩 LOB 数据
对于大型 LOB 列，建议单独存储或使用 FILESTREAM

Q: 统计信息更新和索引维护的关系是什么？

A：

索引重建会自动更新统计信息，无需额外操作
索引重组不会更新统计信息，需要单独执行
生产环境中，建议将统计信息更新纳入维护计划
SQL Server 2016+ 支持自动统计信息更新，可减少手动维护需求

Q: 如何评估索引维护的效果？

A：

比较维护前后的索引碎片化程度
监控核心查询的响应时间变化
跟踪数据库服务器的 I/O 使用率变化
分析执行计划的变化，确保没有劣化
记录维护操作的时长和资源消耗

Q: 云环境中的索引维护有什么特殊考虑？

A：

Azure SQL Database：利用自动索引优化功能，由 Azure 自动管理
AWS RDS for SQL Server：使用 Maintenance Windows 执行维护
云环境中需注意存储 I/O 吞吐量限制，避免触发额外费用
考虑使用弹性伸缩功能，在维护期间临时增加资源

总结

索引维护是 SQL Server DBA 的核心职责之一，直接影响数据库的性能和稳定性。建立科学合理的索引维护策略，需要考虑碎片化程度、索引大小、业务特点和 SQL Server 版本特性。

生产环境中，建议采用自动化方式执行索引维护，并密切监控维护过程和效果。同时，不断优化索引设计和填充因子设置，从源头上减少碎片化的产生。

随着 SQL Server 版本的演进，索引维护功能不断优化，DBA 应根据实际环境选择合适的维护方法，平衡性能提升和资源消耗，确保数据库系统的高效稳定运行。

SQLServer 索引维护 ​

索引维护概述 ​

索引碎片化 ​

碎片化的类型 ​

碎片化的影响 ​

碎片化的测量 ​

索引维护操作 ​

索引重组 (REORGANIZE) ​

索引重建 (REBUILD) ​

统计信息更新 ​

索引维护策略 ​

根据碎片化程度选择维护操作 ​

生产环境维护时间选择 ​

自动化索引维护 ​

生产环境最佳实践 ​

1. 合理设置填充因子 ​

2. 优化 tempdb 配置 ​

3. 监控维护操作 ​

4. 利用企业版功能 ​

5. 避免过度维护 ​

常见问题 ​

Q: 生产环境中，在线索引重建会影响业务吗？ ​

Q: 如何确定索引维护的频率？ ​

Q: 索引重建和重组，哪个对性能提升更明显？ ​

Q: 为什么维护后索引碎片化很快又增加了？ ​

Q: 如何处理包含 LOB 列的索引维护？ ​

Q: 统计信息更新和索引维护的关系是什么？ ​

Q: 如何评估索引维护的效果？ ​

Q: 云环境中的索引维护有什么特殊考虑？ ​

总结 ​

SQLServer 索引维护

索引维护概述

索引碎片化

碎片化的类型

碎片化的影响

碎片化的测量

索引维护操作

索引重组 (REORGANIZE)

索引重建 (REBUILD)

统计信息更新

索引维护策略

根据碎片化程度选择维护操作

生产环境维护时间选择

自动化索引维护

生产环境最佳实践

1. 合理设置填充因子

2. 优化 tempdb 配置

3. 监控维护操作

4. 利用企业版功能

5. 避免过度维护

常见问题

Q: 生产环境中，在线索引重建会影响业务吗？

Q: 如何确定索引维护的频率？

Q: 索引重建和重组，哪个对性能提升更明显？

Q: 为什么维护后索引碎片化很快又增加了？

Q: 如何处理包含 LOB 列的索引维护？

Q: 统计信息更新和索引维护的关系是什么？

Q: 如何评估索引维护的效果？

Q: 云环境中的索引维护有什么特殊考虑？

总结