目录
0. 什么是重建索引
如果表中记录频繁地被删除或插入，尽管表中的记录总量保持不变，索引空间的使用量会不断增加。
虽然记录从索引中被删除，但是该记录索引项的使用空间不能被重新使用。
因此，如果表变化不定，索引空间量会不断增加，不论表中记录数量是否增加，这是因为索引中无效空间会增加。
要回收那些曾被删除记录使用的空间，则需要重建索引。
1. 什么时候需要重建索引
-
在SQL Server中，可以通过查询系统视图和动态管理视图来判断是否需要重建索引。以下是一个基本的SQL脚本，用于检查各个索引的健康状况，并据此推荐是否需要重建索引：
-
这个查询会返回所有平均碎片率大于1%的非主键索引，并根据碎片率的级别给出重建索引的建议。如果碎片率严重（大于20%），并且索引不是主键索引，则推荐重建该索引。
请注意，这个查询使用了sys.dm_db_index_physical_stats函数，它提供了索引碎片的实时信息。该查询还排除了主键索引，因为主键索引的碎片一般不会导致性能问题，除非表结构发生了显著变化。根据实际情况，可能需要调整阈值和排除条件。 -
注：可以参考这篇文章研究关于索引优化的相关知识，：mssql 优化之索引部分
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_%';
-
在金蝶云星空，可以直接在服务器中的金蝶云星空管理中心--管理员看板
- 对索引碎片指标超过30%表的数量超过100个的情况,需要处理,对索引进行优化。若是超过300个，则说明重新生成索引很有必要
- 管理员看板的索引碎片指标什么情况下需要考虑索引优化
2. 如何重建索引：重建索引和删除原索引后再创建新索引
-
重建索引有两种方法：一种是最简单的，删除原索引，然后重建；第二种是使用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。）
3. 如何重建索引
- 首要说明：数据库优化(重建索引)执行期间耗费系统资源高、执行时间一般较长,建议在空闲时间操作。
- 对某个表进行重建索引
ALTER INDEX ALL ON YourTableName REBUILD
- 说明：如果重建时有其他用户在对这个表操作，建议使用带ONLINE参数以减少加锁问题
ALTER INDEX index_name REBUILD ONLINE;
- 查询数据库中的所有的用户创建的表对象
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
-
说明：金蝶云星空可以直接在服务器端使用器 ：金蝶云星空管理中心 执行对数据库的优化和索引优化
4. 创建重建索引的定时作业
5. 附录
- 查询数据库中所有表的索引，以及创建与删除该索引的语句
--生成表索引的创建删除语句
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
- 金蝶云星空官方提供的重建索引的脚本
-
其使用的是游标 和 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);
- 实施方提供的更新金蝶数据库索引的完整脚本
- 除了其使用的sysobjects对象，和上文中的一致
--重建索引
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)