数据库索引重组与重建

不久前，遇到了一个问题。一个普通的数据库查询出现了超时，表的数据量不多，不应该会出现超时的情况。

时间发生在凌晨，组长说应该DBA在执行索引重组与重建脚本。哦？这是什么我完全不知道，上百度！

什么是索引

索引大家应该都了解，索引的作用就像书的目录，可以让数据库在查询时更快定位数据，而不是整表扫描。

1. 提升查询效率
2. 加速排序和分组
3. 保证唯一性

什么是重组与重建索引呢？为什么要这么做？

索引不是一劳永逸的，它们会因为数据更新而“碎片化”：

1. 插入：可能在页中间插入新值，导致数据页分裂。
2. 删除：留下空洞。
3. 更新：可能把数据移到别的页。

这些操作时间长了会让索引的存储结构（B+树）效率下降。

所以DBA通常会定期重建索引或重组索引，来减少碎片、提高查询性能。

为什么多在凌晨执行

白天系统访问量大，重建索引会锁表/占用大量I/O和CPU，影响业务。

凌晨访问量小，适合做维护任务（索引重建、统计信息更新、备份）。

SQL脚本

知道了什么是重组与重建索引，就要理解如何实现该操作呢？

找Chatgpt写一个脚本，解析一下看看。

--关闭行计数返回
SET NOCOUNT ON;

DECLARE @SchemaName NVARCHAR(256);  --变量模式
DECLARE @TableName NVARCHAR(256);   --变量表名
DECLARE @IndexName NVARCHAR(256);   --变量索引名
DECLARE @AvgFrag DECIMAL(18,2);     --变量平均碎片率
DECLARE @sql NVARCHAR(MAX);         --变量要执行的SQL字符串

--判断是否已存在，先删后建
IF OBJECT_ID('tempdb..#FragList') IS NOT NULL
    DROP TABLE #FragList;

--创建临时表
CREATE TABLE #FragList
(
    ObjectId INT,
    IndexId INT,
    SchemaName NVARCHAR(256),
    TableName NVARCHAR(256),
    IndexName NVARCHAR(256),
    AvgFragmentation DECIMAL(18,2),
    PageCount BIGINT
);

--统计索引碎片率信息
--sys.dm_db_index_physical_stats 获取当前数据库的索引物理统计
INSERT INTO #FragList
SELECT
    ips.object_id,
    ips.index_id,
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
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.tables t ON t.object_id = ips.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ips.database_id = DB_ID()
  AND i.type_desc <> 'HEAP'
  AND ips.page_count > 100; -- 可选：只处理较大的索引

--游标批处理（等同于foreach），遍历#FragList
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT SchemaName, TableName, IndexName, AvgFragmentation
FROM #FragList;

OPEN cur;
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @AvgFrag;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = NULL;

    IF @AvgFrag BETWEEN 5 AND 30
        SET @sql = N'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE;';
    ELSE IF @AvgFrag > 30
        SET @sql = N'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (ONLINE = ON);';

    IF @sql IS NOT NULL
    BEGIN
        PRINT @sql;
        EXEC sp_executesql @sql;
    END

    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @AvgFrag;
END

CLOSE cur;
DEALLOCATE cur;
```"