数据库索引重组与重建

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

时间发生在凌晨，组长说应该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;
```"