SqlServer运维——重建索引
目录
0. 什么是重建索引
对基础数据执行插入，更新或删除操作，SQLServer数据库引擎会自动维护索引。但是时间长，这些修改可能会导致索引中的信息分散在数据库中。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
如果表中记录频繁地被删除或插入，尽管表中的记录总量保持不变，索引空间的使用量会不断增加。
虽然记录从索引中被删除，但是该记录索引项的使用空间不能被重新使用。
因此，如果表变化不定，索引空间量会不断增加，不论表中记录数量是否增加，这是因为索引中无效空间会增加。
要回收那些曾被删除记录使用的空间，则需要重建索引。
- 关于索引碎片的理解，可以参考：博客园：Index Fragmentation
1. 什么时候需要重建索引
1.1 重建索引的一般准则
- 碎片比例大于30% ，推荐重建索引
- 碎片小于30%，大于5%，推荐重新组织索引
- 碎片小于5% ，不必处理
1.2 查看索引碎片的方式
-
【法0】：总MSSM中的，选中表-->索引-->右键：全部重新组织-->每个索引的：碎片总计
-
【法1】：在SQL Server中，可以通过查询系统视图和动态管理视图来判断是否需要重建索引。以下是一个基本的SQL脚本，用于检查各个索引的健康状况，并据此推荐是否需要重建索引：
-
这个查询会返回所有平均碎片率大于1%的非主键索引，并根据碎片率的级别给出重建索引的建议。如果碎片率严重（大于20%），并且索引不是主键索引，则推荐重建该索引。
-
请注意，这个查询使用了
sys.dm_db_index_physical_stats函数，它提供了索引碎片的实时信息。注意，下面这个脚本查询还排除了主键索引，因为主键索引的碎片一般不会导致性能问题，除非表结构发生了显著变化。根据实际情况，可能需要调整阈值和排除条件。
-
SELECT OBJECT_NAME(i.object_id) AS 'Table Name',
i.name AS 'Index Name',
i.type_desc AS 'Index Type',
avg_fragmentation_in_percent,
CASE
WHEN avg_fragmentation_in_percent > 10.0 THEN
'HEAVILY_FRAGMENTED'
WHEN avg_fragmentation_in_percent > 2.0 THEN
'MODERATELY_FRAGMENTED'
WHEN avg_fragmentation_in_percent > 1.0 THEN
'LIGHTLY_FRAGMENTED'
ELSE
'NOT_FRAGMENTED'
END AS 'Fragmentation Level',
CASE
WHEN avg_fragmentation_in_percent > 20.0
AND i.name NOT LIKE 'PK_%' THEN
'REBUILD'
ELSE
'NONE'
END AS 'Recommended Action'
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
AND t.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 1.0
AND i.name NOT LIKE 'PK_%';
- 【法2】：最简单的查看某个表的索引碎片，使用
DBCC SHOWCONTIG(表名)
简单示例：查看物料表（T_BD_MATERIAL）的索引碎片
DBCC SHOWCONTIG(T_BD_MATERIAL);
结果：
DBCC SHOWCONTIG 正在扫描 'T_BD_MATERIAL' 表...
表: 'T_BD_MATERIAL' (2012586258)；索引 ID: 1，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 789
- 扫描区数..............................: 102
- 区切换次数..............................: 101
- 每个区的平均页数........................: 7.7
- 扫描密度 [最佳计数:实际计数].......: 97.06% [99:102]
- 逻辑扫描碎片 ..................: 2.41%
- 区扫描碎片 ..................: 40.20%
- 每页的平均可用字节数.....................: 737.4
- 平均页密度(满).....................: 90.89%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
完成时间: 2024-12-10T18:49:44.9562813+08:00
-
基于查询的上述信息，索引的碎片级别可通过以下方式确定：
-
- “逻辑扫描碎片” 和“区扫描碎片” （对于较小的盘区）的值是表的碎片级别的最好指标。 这两个值应尽可能接近零，但 0% 到 10% 之间的值都是可接受的。
-
- “区切换次数” 的值应尽可能接近“扫描区数” 的值。 此比率将作为“扫描密度” 值计算。 此值应尽可能的大，可通过减少索引碎片得到改善。
-
-
主要的参数含义
- 逻辑扫描碎片：就是聚集索引的碎片，注意只是聚集索引的的碎片大小
- 平均页密度(满)：也称页面填充度，百分比越大越好。
- 数据库中的每个页面包含的行数可以变化。 如果行占用了页面上的所有空间，则页面密度为 100%。 如果页面是空白的，则页面密度为 0%。 如果密度为 100% 的页面拆分为两个页面以容纳新行，则这两个新页面的密度约为 50%。
- 页面密度较低时，存储相同数量的数据需要更多的页面。 这意味着，读写此数据需要更多的 I/O，缓存此数据需要更多的内存。 内存有限时，所缓存的查询所需页面较少，从而导致磁盘 I/O 增加。 因此，页面密度较低会降低性能。
- 重建索引是提高平均页密度的有效方式。
- 在许多工作负载中，提高页面密度会比减少碎片更能提升性能。为避免在不必要的情况下降低页面密度，Microsoft 不建议将填充因子设置为 100 或 0 以外的值，除非索引遇到大量页面拆分，例如，包含非顺序 GUID 值的前导列并且频繁修改的索引。
-
【法3】：关于金蝶云星空，可以直接在服务器中的金蝶云星空管理中心--管理员看板
- 对索引碎片指标超过30%表的数量超过100个的情况,需要处理,对索引进行优化。若是超过300个，则说明重新生成索引很有必要
- 管理员看板的索引碎片指标什么情况下需要考虑索引优化
-
其他说明
-
关于dbcc命令，简洁有效，支持老版本的SqlServer2000,但是官方明确以后会删除该功能
-
SqlServer2005 中出现
sys.dm_db_index_physical_stats函数用来替代DBCC SHOWCONFIG -
DBCC SHOWCONTIG是将共享锁 (S) 放置在包含索引的表上，而sys.dm_db_index_physical_stats仅放置一个意图共享锁 (IS)，从而在函数执行期间极大地减少了表的阻塞。
-
2. 重建索引和重新组织
首先，减少索引碎片并增加页面密度，可以通过使用以下方法：重新组织索引、重新生成索引
- 重新生成索引（即：重建索引、Rebuild）
重新生成索引将会删除并重新创建索引。 重新生成操作可以脱机或联机执行，具体取决于索引类型和数据库引擎版本。 脱机索引重新生成耗费的时间通常比联机重新生成少，但它会在重新生成操作持续期间保留对象级锁，阻止查询访问表或视图。
- 重新组织索引
重新生成索引相比，重新组织索引消耗的资源更少。
因此，应首选此索引维护方法，除非出于特定原因需要使用索引重新生成。
重新组织始终属于联机操作。 也就是说，在执行 ALTER INDEX ... REORGANIZE 操作期间不保留长期对象级锁，且对基础表的查询或更新可以继续进行。
ALTER INDEX ALL ON YoutTableName REORGANIZE--重新组织指定表上的所有的索引
ALTER INDEX Index_Name ON YourTableName REORGANIZE--重新组织指定的索引
3. 如何重建索引：重建索引和删除原索引后再创建新索引
-
重建索引有两种方法：一种是最简单的，删除原索引，然后重建；第二种是使用ALTER INDEX … REBUILD命令对索引进行重建。
-
ALTER INDEX REBUILD和Drop Index……；Create Index……区别：-
ALTER INDEX REBUILD：这种方法不需要删除原有的索引，而是直接在原有索引的基础上进行重建。重建过程中，系统会使用原索引的叶子节点作为新索引的数据来源，这样可以减少I/O操作和排序工作
-
删除索引之后再创建：在删除原有索引后，需要重新分配存储空间来创建新的索引。这可能需要更多的磁盘空间，尤其是在磁盘空间有限的情况下
-
删除索引之后再创建，在创建索引的时候需要排序，而重建索引（ALTER INDEX REBUILD）不需要再重新排序
-
重建索引（ALTER INDEX REBUILD） 在重建的过程中的，并不影响查询语句使用现有的索引
-
-
删除索引之后再创建的使用场景：
- 在需要往表中插入大量数据时，可以先删除表现有的索引，之后在创建索引。这样做的好处：
- 提高数据插入的速度
- 更有效地使用索引空间
- 在需要往表中插入大量数据时，可以先删除表现有的索引，之后在创建索引。这样做的好处：
-
关于
DBCC DBREINDEX重建索引- 此方法一般在SqlServer2000中使用，重建索引(单个或多个)。（官方文档：后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。 改用 ALTER INDEX。）
4. 如何重建索引
-
首要说明：数据库优化(重建索引)执行期间耗费系统资源高、执行时间一般较长,建议在空闲时间操作。
-
其他说明：如果重建时有其他用户在对这个表操作，建议使用带ONLINE=ON参数以减少加锁问题
- ONLINE默认的参数是OFF。因为脱机索引相对联机索引耗费时间较少，所以默认重建索引就是脱机的。
- 如果你想要在不影响其他用户对表的访问的情况下重建索引，可以使用ONLINE=ON参数
- ONLINE = ON选项会让重建索引的操作在线进行，即不会阻塞对表的查询
- 最简示例
--对某个表进行重建索引
ALTER INDEX ALL ON YourTableName REBUILD
ALTER INDEX ALL ON dbo.YourTableName REBUILD WITH(ONLINE=ON)
--对某个表的某个索引重建
ALTER INDEX Index_Name ON YourTableNam REBUILD
ALTER INDEX Index_Name ON YourTableNam REBUILD WITH(ONLINE=ON)
- 查询数据库中的所有的用户创建的表对象
SELECT SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName
FROM sys.objects
WHERE type='U' --表
AND is_ms_shipped=0; --表示用户自行创建的，非Microsoft创建的
--说明：官方文档中的 'U'就是代表用户定义的表
说明：
is_ms_shipped是 SQL Server 中的一个系统元数据属性，用来表示数据库对象（如表、视图、存储过程等）是否由 Microsoft 提供。
如果值为 1，那么这个对象是由 Microsoft 提供的；
如果值为 0，那么这个对象是用户创建的。
- 基于所有的表对象创建重建索引的动态SQL语句
DECLARE @SQLSTR NVARCHAR(MAX) = N'';
SELECT @SQLSTR = @SQLSTR + +N'ALTER INDEX ALL ON ' + name + N' REBUILD;'
FROM sys.tables
WHERE type = 'U'
AND is_ms_shipped = 0;
PRINT @SQLSTR;
EXEC @SQLSTR; --执行动态生成的更新索引的SQL语句
- 关于金蝶云星空的中创建所有表索引的动态SQL语句
- 基于上述的创建动态SQL的脚本，这里添加两个筛选条件，筛选出
T_开头的表，并排除TMP_开头的
DECLARE @SQLSTR NVARCHAR(MAX) = N'';
SELECT @SQLSTR = @SQLSTR + +N'ALTER INDEX ALL ON ' + name + N' REBUILD;'
FROM sys.tables
WHERE type = 'U'
AND is_ms_shipped = 0
AND (name LIKE 'T_%')
AND (name NOT LIKE 'TMP%');
PRINT @SQLSTR;
EXEC @SQLSTR;
- 更新统计信息
使用UPDATE STATISTICS命令来更新某个表的统计信息。这有助于查询优化器在执行查询时生成更准确的查询计划。
在索引进行了重大变更的情况下执行更新统计信息过程
- 对某个表更新统计信息
UPDATE STATISTICS YourTableName;
- 生成所有的表的更新统计信息的动态SQL
DECLARE @SQLSTR1 NVARCHAR(MAX);
SET @SQLSTR1 = N'';
SELECT @SQLSTR1 = @SQLSTR1 + +N'UPDATE STATISTICS ' + name + N';'
FROM sys.objects
WHERE type = 'U'
AND is_ms_shipped = 0
PRINT @SQLSTR1;
EXEC @SQLSTR1
- 关于金蝶云星空生成所有表的更新统计信息的动态SQL
- 筛选出T_开头的表，并排除TMP_开头的
DECLARE @SQLSTR1 NVARCHAR(MAX);
SET @SQLSTR1 = N'';
SELECT @SQLSTR1 = @SQLSTR1 + +N'UPDATE STATISTICS ' + name + N';'
FROM sys.objects
WHERE type = 'U'
AND is_ms_shipped = 0
AND (name LIKE 'T_%')
AND (name NOT LIKE 'TMP%');
PRINT @SQLSTR1;
EXEC @SQLSTR1
-
说明：金蝶云星空可以直接在服务器端使用器 ：金蝶云星空管理中心 执行对数据库的优化和索引优化
5. 创建重建索引的定时作业
6. 参考
7. 附录
7.1. 查询数据库中所有表的索引，以及创建与删除该索引的语句
- 此脚本可以查看数据库中的所有的索引的信息，索引类型，索引列,索引删除SQL，索引创建语句 等信息
--生成表索引的创建删除语句
WITH TB
AS ( SELECT TB.object_id ,
Schema_name = Sch.name ,
table_name = TB.name
FROM sys.tables TB
INNER JOIN sys.schemas Sch ON TB.schema_id = Sch.schema_id
WHERE TB.is_ms_shipped = 0
),
IXC
AS ( SELECT IXC.object_id ,
IXC.index_id ,
IXC.index_column_id ,
IXC.is_descending_key ,
IXC.is_included_column ,
column_name = C.name
FROM SYS.index_columns IXC
INNER JOIN SYS.columns C ON IXC.object_id = C.object_id
AND IXC.column_id = C.column_id
),
IX
AS ( SELECT IX.object_id ,
index_name = IX.name ,
index_type_desc = IX.type_desc ,
IX.is_unique ,
IX.is_primary_key ,
IX.is_unique_constraint ,
IX.is_disabled ,
index_columns_TEMP = STUFF(IXC_COL.index_columns, 1, 1,
N'') ,
index_columns = CASE WHEN IXC_COL_INCLUDE.index_columns_includes IS NOT NULL
THEN STUFF(LEFT(IXC_COL.index_columns,
DATALENGTH(IXC_COL.index_columns)
- DATALENGTH(IXC_COL_INCLUDE.index_columns_includes)),
1, 1, N'')
ELSE STUFF(IXC_COL.index_columns,
1, 1, N'')
END ,
index_columns_includes = STUFF(IXC_COL_INCLUDE.index_columns_includes,
1, 1, N'')
FROM sys.indexes IX
CROSS APPLY ( SELECT index_columns = ( SELECT
N','
+ QUOTENAME(column_name)
FROM
IXC
WHERE
object_id = IX.object_id
AND index_id = IX.index_id
ORDER BY index_column_id
FOR XML PATH('') ,
ROOT('r') ,
TYPE
).value('/r[1]', 'nvarchar(max)')
) IXC_COL
OUTER APPLY ( SELECT index_columns_includes = ( SELECT
N','
+ QUOTENAME(column_name)
FROM
IXC
WHERE
object_id = IX.object_id
AND index_id = IX.index_id
AND is_included_column = 1
ORDER BY index_column_id
FOR XML PATH('') ,
ROOT('r') ,
TYPE
).value('/r[1]', 'nvarchar(max)')
) IXC_COL_INCLUDE
WHERE index_id > 0
)
SELECT DB_NAME() AS N'数据库名' ,
TB.Schema_name AS N'架构' ,
TB.table_name AS N'表名' ,
IX.index_name AS N'索引名' ,
IX.index_type_desc AS N'索引类型' ,
IX.is_unique AS N'是否唯一索引' ,
IX.is_primary_key AS N'是否主键' ,
IX.is_unique_constraint AS N'是否唯一约束' ,
IX.is_disabled AS N'是否禁用索引' ,
IX.index_columns AS N'索引列' ,
IX.index_columns_includes AS N'索引包含列' ,
N'CREATE INDEX ' + N'[' + IX.index_name + N']' + N' ON ' + N'['
+ QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
+ QUOTENAME(TB.table_name) + N'(' + IX.index_columns + N')'
+ CASE WHEN IX.index_columns_includes IS NOT NULL
THEN CHAR(13) + N'INCLUDE (' + IX.index_columns_includes
+ N')'
ELSE N''
END AS N'创建索引' ,
N'DROP INDEX ' + QUOTENAME(IX.index_name) + N' ON '
+ QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
+ QUOTENAME(TB.table_name) AS N'删除索引'
FROM TB
INNER JOIN IX ON TB.object_id = IX.object_id
--and IX.index_type_desc<>'CLUSTERED'
ORDER BY Schema_name ,
table_name ,
IX.index_name
结果形如：
| 数据库名 | 架构 | 表名 | 索引名 | 索引类型 | 是否唯一索引 | 是否主键 | 是否唯一约束 | 是否禁用索引 | 索引列 | 索引包含列 | 创建索引 | 删除索引 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AIS20240716171712 | dbo | AMyTest | PK_AMyTest | CLUSTERED | 1 | 1 | 0 | 0 | [Id] | NULL | CREATE INDEX [PK_AMyTest] ON [[AIS20240716171712].[dbo].AMyTest | DROP INDEX [PK_AMyTest] ON [AIS20240716171712].[dbo].[AMyTest] |
7.2. 金蝶云星空官方提供的重建索引的脚本
-
其使用的是游标 和 DBCC DBREINDEX 重建数据库索引
-
完整脚本：
DECLARE @sql NVARCHAR(1000) =N'DBCC DBREINDEX(@TABLE_NAME) WITH NO_INFOMSGS';
DECLARE @tbl NVARCHAR(1000) =N'';
DECLARE tblcur CURSOR FOR
SELECT [name]
FROM sys.tables
WHERE [name] NOT LIKE 'TMP%' AND [name] NOT LIKE 'Z[_]%'
ORDER BY 1
FOR READ ONLY;
OPEN tblcur;
FETCH NEXT FROM tblcur
INTO @tbl;
WHILE @@FETCH_STATUS=0 BEGIN
EXEC sp_executesql @sql, N'@table_name NVARCHAR(1000)', @tbl;
PRINT '表重建索引成功：'+@tbl;
FETCH NEXT FROM tblcur
INTO @tbl;
END;
CLOSE tblcur;
DEALLOCATE tblcur;
PRINT '全部执行完成！';
简单手工运行版本：
DECLARE @sql AS VARCHAR(MAX) ='';
SELECT @sql=@sql+'dbcc dbreindex(['+name+']);'+CHAR(13)+CHAR(10)
FROM sys.tables
WHERE name NOT LIKE 'tmp%' AND name NOT LIKE 'z%';
EXEC(@sql);
7.3. 重建金蝶数据库索引的完整脚本2
-
目前我们的正式环境中的重建索引的定时任务使用的即此脚本
-
除了其使用的sysobjects对象，和上文中的一致
SET QUOTED_IDENTIFIER ON;
--重建索引
DECLARE @SQLSTR NVARCHAR(MAX)
SET @SQLSTR=''
SELECT @SQLSTR=@SQLSTR++'ALTER INDEX ALL ON '+NAME+' REBUILD;'FROM SYSOBJECTS WHERE XTYPE='U'AND(NAME LIKE'T_%')AND(NAME NOT LIKE'TMP%')
EXEC (@SQLSTR)
--更新统计信息
DECLARE @SQLSTR1 NVARCHAR(MAX)
SET @SQLSTR1=''
SELECT @SQLSTR1=@SQLSTR1++'UPDATE STATISTICS '+NAME+';'FROM SYSOBJECTS WHERE XTYPE='U'AND(NAME LIKE'T_%')AND(NAME NOT LIKE'TMP%')
EXEC (@SQLSTR1)
7.4. 日常针对某个表的索引碎片查看及快速重建索引
- 还是可以使用DBCC命令的，相对简洁易记
DBCC SHOWCONTIG(YourTableName);--查看碎片大小：逻辑扫描碎片
DBCC DBREINDEX(YourTableName);--重建索引
7.5 查询金蝶星空云中所有表索引碎片大于10%的表
- 金蝶总部研发提供的脚本
- 排除了临时表和z表（种子表）
SELECT T.name,
page_count,
A.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS A
JOIN
(
SELECT object_id,
name
FROM sys.tables
WHERE name NOT LIKE 'TMP%'
AND name NOT LIKE 'Z[_]%'
) T
ON A.object_id = T.object_id
WHERE A.alloc_unit_type_desc = 'IN_ROW_DATA'
AND page_count > 20
AND A.index_id = 1
AND A.avg_fragmentation_in_percent > 10
ORDER BY A.avg_fragmentation_in_percent DESC;
结果形如：
结果形如：
| name | page_count | avg_fragmentation_in_percent |
|---|---|---|
| T_APM_TRACE | 125 | 98.4 |
| V_CB_CMPTCOSTQTYAMT_VH | 164 | 98.17073171 |
