SQL Server 索引管理——索引备份

SQL Server 索引管理——索引备份

作为一个DBA，做任何操作（尤其是删除、更新等）之前，首先要想到事情是，操作如何还原，对于索引的操作也是如此。前文分别叙述了无用索引删除脚本的生成、以及禁用脚本的生成。对于禁用的索引，只需要将禁用脚本中的disable改为REBUILD即可重建恢复。而禁用脚本，在最终确认无用时，还是要删除掉的，所以对于这类要删除的索引，在删除之前，我们还是要备份其创建脚本的。下面我直接给出生成创建索引脚本的两个过程：

/*
	获取索引的键列、包含列、分区列信息
*/
CREATE PROCEDURE dba_getIndexColomnStr
	@objectId INT
	,@indexId INT
	,@isPartion BIT		--0表示索引未分区，1表示索引分区
	,@keyColumnStr VARCHAR(100) OUTPUT
	,@includeColumnStr VARCHAR(100) OUTPUT
	,@partitonColumnName VARCHAR(50) OUTPUT
AS
	
BEGIN
SET NOCOUNT ON;
SELECT c.[object_id],index_id,name column_name,key_ordinal,partition_ordinal,is_descending_key,is_included_column
INTO #temp
FROM sys.index_columns ic
LEFT JOIN sys.columns c ON ic.[object_id]=c.[object_id] AND ic.column_id=c.column_id
WHERE ic.[object_id]=@objectId AND index_id=@indexId;

 SELECT @keyColumnStr=STUFF((
                     SELECT column_name+CASE WHEN is_descending_key=0 THEN ' ASC ' ELSE ' DESC' END +N','
                     FROM #temp
                     WHERE is_included_column=0 AND key_ordinal>0
                     ORDER BY key_ordinal
                     FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
SELECT @includeColumnStr=STUFF((
                     SELECT column_name + N','
                     FROM #temp
                     WHERE is_included_column=1
                     ORDER BY key_ordinal
                     FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
SELECT @keyColumnStr=LEFT(@keyColumnStr,LEN(@keyColumnStr)-1),@includeColumnStr=LEFT(@includeColumnStr,LEN(@includeColumnStr)-1);
	IF @isPartion=1 
		SELECT 
			@partitonColumnName=column_name
		FROM #temp
		WHERE partition_ordinal>0;
	ELSE
		SET @partitonColumnName='';

	TRUNCATE TABLE #temp;
	DROP TABLE #temp;

END;
GO

此过程传入对象编号和索引编号，返回索引键列、包含列字符串，如果索引有分区，还会返回分区列。下面是生成索引创建脚本的过程：

/*
	生成索引的创建脚本
	测试
	exec dba_generateCreateCodeOfDisabledIndexes 0
*/
ALTER PROCEDURE dba_generateCreateCodeOfDisabledIndexes
	@isDisableIndex BIT=1	--0生成数据库下所有表的所有索引的创建脚本，1生成数据库中禁用索引的创建脚本，默认值为1
AS
BEGIN
DECLARE @createIndexCode VARCHAR(MAX)
	,@keyColumnStr VARCHAR(100)		--索引键列组（包括升降序）
	,@includeColumnStr VARCHAR(100)	--索引包含列组
	,@objectId INT
	,@indexId INT
	,@indexName VARCHAR(100)
	,@indexTypeDesc VARCHAR(50)	--索引类型
	,@isUnique VARCHAR(10)		--是否唯一索引
	,@ignoreDupKey BIT	--是否忽略重复键
	,@isPadded BIT	--是否使用空格对齐
	,@isPrimaryKey BIT	--是否为主键
	,@allowRowLock BIT	--是否允许行锁
	,@allowPageLock BIT	--是否允许页锁
	,@filterDefinition VARCHAR(50)	--过滤定义
	,@fillFactor INT	--填充因子
	,@partitionSchemesName VARCHAR(50)	--分区方案名
	,@partitionColumnName VARCHAR(50)	--分区列
	,@indexOptions VARCHAR(200)			--索引配置选项
	,@fileGroupName VARCHAR(50)			--索引所在的文件组
	,@schemaName VARCHAR(50)
	,@tableName VARCHAR(50)
	,@indexType INT
	,@compressionDelay INT
	,@sql VARCHAR(MAX);

--索引压缩、索引分区、内存表索引，聚集索引、非聚集索引、唯一索引，主键索引

--无分区的索引创建脚本生成
DECLARE cur CURSOR FOR(
SELECT t.[object_id],ix.index_id,ix.TYPE indexType,QUOTENAME(SCHEMA_NAME(t.SCHEMA_ID),'[') [schema_name], QUOTENAME(t.name,'['), QUOTENAME(ix.name,'['),
	 CASE WHEN ix.is_unique = 1 THEN 'UNIQUE ' ELSE '' END is_unique
	 ,ix.is_primary_key
	 ,ix.type_desc,
	 CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END
	 + CASE WHEN ix.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
	 + CASE WHEN ix.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
	 + CASE WHEN INDEXPROPERTY(t.OBJECT_ID, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
	 + CASE WHEN ix.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF' END
	 +CASE p.data_compression_desc WHEN 'page' THEN ', DATA_COMPRESSION = PAGE' 
			WHEN 'row' THEN ', DATA_COMPRESSION = ROW'
			ELSE '' END
	 + CASE WHEN is_primary_key=1 THEN '' ELSE ', SORT_IN_TEMPDB = OFF' END	--创建主键索引不能加此参数
	 +CASE WHEN ix.fill_factor>0 THEN ', FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(6)) ELSE '' END AS IndexOptions
	  ,QUOTENAME(FILEGROUP_NAME(ix.data_space_id),'[') FileGroupName--,p.data_compression_desc
 FROM sys.tables t 
 INNER JOIN sys.indexes ix ON t.OBJECT_ID=ix.OBJECT_ID 
 INNER JOIN sys.partitions p  ON p.[object_id]=ix.[object_id] AND p.index_id=ix.index_id
 WHERE ix.data_space_id>0	--=0是内存表的索引
	AND ix.data_space_id<65536	-->65536 为分区索引
	AND ix.TYPE IN(1,2,5,6)	--1聚集索引，2非聚集索引,5聚集列索引，6非聚集列索引
	AND (CASE WHEN @isDisableIndex=1 THEN ix.is_disabled ELSE 1 END)=1
 --order by schema_name(t.schema_id), t.name, ix.name
 );
 OPEN cur;
 FETCH NEXT FROM cur INTO @objectId,@indexId,@indexType,@schemaName,@tableName,@indexName,@isUnique,@isPrimaryKey,@indexTypeDesc,@indexOptions,@fileGroupName;
 DECLARE @desc VARCHAR(50);
 SET @desc='没有不分区的'+(CASE WHEN @isDisableIndex=1 THEN '禁用' ELSE ''  END) +'索引';
  IF @@fetch_status < 0
    BEGIN
		CLOSE cur;
        DEALLOCATE cur;
        RAISERROR(15472,-1,-1,@desc); -- 没有该类型的索引.
        --return (0)
		GOTO  partitionIndex;
    END;

 WHILE @@FETCH_STATUS=0
 BEGIN
	EXECUTE dba_getIndexColomnStr @objectId,@indexId,0,@keyColumnStr OUTPUT,@includeColumnStr OUTPUT,@partitionColumnName OUTPUT;
	IF @indexType IN(1,2)	--行索引
	BEGIN
		IF @isPrimaryKey=1	--主键索引
			SET @sql='ALTER TABLE ' +@schemaName+'.'+@tableName +' ADD CONSTRAINT '+@indexName+' PRIMARY KEY '+@indexTypeDesc
				+'('+@keyColumnStr+') '
				+'WITH('+@indexOptions+')'
				+' ON ' +@fileGroupName;
		ELSE	--非主键索引
			SET @sql='CREATE '+@isUnique +' '+ @indexTypeDesc +' INDEX '+@indexName+' ON '+@schemaName+'.'+@tableName
					+' (' +@keyColumnStr+') '
					+CASE WHEN @includeColumnStr IS NULL THEN ' ' ELSE ' INCLUDE( '+@includeColumnStr+') ' END
					+'WITH('+@indexOptions+') '
					+'ON ' +@fileGroupName;
	END;
	ELSE IF @indexType IN(5,6)	--列索引
	BEGIN
		SET @sql='CREATE '+@indexTypeDesc+' INDEX '+@indexName+ ' ON ' +@schemaName+'.'+@tableName 
			+'('+@includeColumnStr+') '
			+' ON ' +@fileGroupName;
	END;
	PRINT @sql;
	FETCH NEXT FROM cur INTO @objectId,@indexId,@indexType,@schemaName,@tableName,@indexName,@isUnique,@isPrimaryKey,@indexTypeDesc,@indexOptions,@fileGroupName;
 END;
 CLOSE cur;
 DEALLOCATE cur;

 --分区索引
 partitionIndex:;
DECLARE cur1 CURSOR FOR(
SELECT t.[object_id],ix.index_id,ix.TYPE indexType,QUOTENAME(SCHEMA_NAME(t.SCHEMA_ID),'[') [schemaName]
	, QUOTENAME(t.name,'[') tableName, QUOTENAME(ix.name,'[') indexName
	, CASE WHEN ix.is_unique = 1 THEN 'UNIQUE ' ELSE '' END is_unique
	 ,ix.is_primary_key
	 ,ix.type_desc,
	 CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END
	 + CASE WHEN ix.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
	 + CASE WHEN ix.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
	 + CASE WHEN INDEXPROPERTY(t.OBJECT_ID, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
	 + CASE WHEN ix.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END
	 + CASE WHEN is_primary_key=1 THEN '' ELSE ', SORT_IN_TEMPDB = OFF' END	--创建主键索引不能加此参数
	 +CASE WHEN ix.fill_factor>0 THEN ', FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(6)) ELSE '' END AS IndexOptions
	  ,ps.name partitonSchemesName--,fg.groupname FileGroupName
	  ,ix.compression_delay
 FROM sys.tables t 
 INNER JOIN sys.indexes ix ON t.OBJECT_ID=ix.OBJECT_ID
 INNER JOIN sys.partition_schemes ps ON ps.data_space_id=ix.data_space_id
 WHERE ix.TYPE IN(1,2,5,6)	--1聚集索引，2非聚集索引
	AND ix.data_space_id>65536	--分区索引
	AND (CASE WHEN @isDisableIndex=1 THEN ix.is_disabled ELSE 1 END)=1
);
OPEN cur1;
FETCH NEXT FROM cur1 INTO @objectId,@indexId,@indexType,@schemaName,@tableName,@indexName,@isUnique,@isPrimaryKey
	,@indexTypeDesc,@indexOptions,@partitionSchemesName,@compressionDelay;
SET @desc='不存在分区的'+(CASE WHEN @isDisableIndex=1 THEN '禁用' ELSE '' END) +'索引'; 
  IF @@fetch_status < 0
    BEGIN
		CLOSE cur1;
        DEALLOCATE cur1;
        RAISERROR(15472,-1,-1,@desc); -- 没有该类型的索引.
        --return (0)
		GOTO memoryOptimizedTableIndex;
    END;

	WHILE @@FETCH_STATUS=0
 BEGIN
	EXECUTE dba_getIndexColomnStr @objectId,@indexId,1,@keyColumnStr OUTPUT,@includeColumnStr OUTPUT,@partitionColumnName OUTPUT;
	IF @indexType IN(1,2)
	BEGIN
		IF @isPrimaryKey=1	--主键索引
			SET @sql='ALTER TABLE ' +@schemaName+'.'+@tableName +' ADD CONSTRAINT '+@indexName+' PRIMARY KEY '+@indexTypeDesc
				+'('+@keyColumnStr+') '
				+'WITH('+@indexOptions+')'
				+' ON ' +@fileGroupName;
		ELSE	--非主键索引
			SET @sql='CREATE '+@isUnique +' '+ @indexTypeDesc +' INDEX '+@indexName+' ON '+@schemaName+'.'+@tableName
					+' (' +@keyColumnStr+') '
					+CASE WHEN @includeColumnStr IS NULL THEN ' ' ELSE ' INCLUDE( '+@includeColumnStr+') ' END
					+'WITH('+@indexOptions+') '
					+'ON ' +@partitionSchemesName+'('+@partitionColumnName+')';
		PRINT @sql;
		--生成分区的压缩脚本
		SELECT @sql=STUFF((
						SELECT 
							'ALTER INDEX '+@indexName+' ON '+@schemaName+'.'+@tableName+' REBUILD PARTITION = '+CONVERT(VARCHAR(5),partition_number)+
								' WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF, DATA_COMPRESSION = '+data_compression_desc+ ' ) '+CHAR(10)
						FROM sys.partitions
						WHERE [object_id]=@objectId AND index_id=@indexId AND data_compression>0
						FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
		PRINT @sql;
	END;
	ELSE IF @indexType IN(5,6)	--列索引
	BEGIN
		SET @sql='CREATE '+@indexTypeDesc+' INDEX '+@indexName+ ' ON ' +@schemaName+'.'+@tableName 
			+'('+@includeColumnStr+') '
			+' ON ' +@partitionSchemesName+'('+@partitionColumnName+')';
		PRINT @sql;
		--列索引被禁用后，其在sys.partitions 中的信息消失
		--生成列索引的压缩脚本
		--SELECT @sql=STUFF((
		--				SELECT 
		--					'ALTER INDEX '+@indexName+' ON '+@schemaName+'.'+@tableName+' REBUILD PARTITION = '+CONVERT(VARCHAR(5),partition_number)+
		--						' WITH (DATA_COMPRESSION = '+data_compression_desc+ ' ) '+CHAR(10)
		--				FROM sys.partitions
		--				WHERE [object_id]=@objectId AND index_id=@indexId AND data_compression>0
		--				FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
		--PRINT @sql;
	END;
	FETCH NEXT FROM cur1 INTO @objectId,@indexId,@indexType,@schemaName,@tableName,@indexName,@isUnique,@isPrimaryKey
	,@indexTypeDesc,@indexOptions,@partitionSchemesName,@compressionDelay;
 END;
 CLOSE cur1;
 DEALLOCATE cur1;

 columnIndex:;


 memoryOptimizedTableIndex:;

  --内存表索引创建脚本生成
  --内存表索引不能禁用，这里给出的是备份索引的脚本
  DECLARE cur2 CURSOR FOR(
SELECT t.[object_id],ix.index_id
	,QUOTENAME(SCHEMA_NAME(t.SCHEMA_ID),'[') [schemaName]
	, QUOTENAME(t.name,'[') tableName
	, QUOTENAME(ix.name,'[') indexName
	,ix.type_desc
 FROM sys.tables t 
 INNER JOIN sys.indexes ix ON t.OBJECT_ID=ix.OBJECT_ID 
 INNER JOIN sys.partitions p  ON p.[object_id]=ix.[object_id] AND p.index_id=ix.index_id
 WHERE ix.data_space_id=0	--=0是内存表的索引
	AND ix.is_primary_key=0
	AND (CASE WHEN @isDisableIndex=1 THEN ix.is_disabled ELSE 1 END)=1
 --order by schema_name(t.schema_id), t.name, ix.name
 );
 OPEN cur2;
 FETCH NEXT FROM cur2 INTO @objectId,@indexId,@schemaName,@tableName,@indexName,@indexTypeDesc;
 --DECLARE @desc VARCHAR(50);
 SET @desc='没有不分区的'+(CASE WHEN @isDisableIndex=1 THEN '禁用' ELSE ''  END) +'索引';
  IF @@fetch_status < 0
    BEGIN
		CLOSE cur2;
        DEALLOCATE cur2;
        RAISERROR(15472,-1,-1,@desc); -- 没有该类型的索引.
        --return (0)
		GOTO  pEnd;
    END;

	 WHILE @@FETCH_STATUS=0
	 BEGIN
		EXECUTE dba_getIndexColomnStr @objectId,@indexId,0,@keyColumnStr OUTPUT,@includeColumnStr OUTPUT,@partitionColumnName OUTPUT;
	
				SET @sql='ALTER TABLE ' +@schemaName+'.'+@tableName +' ADD INDEX '+@indexName+' '+@indexTypeDesc
					+'('+@keyColumnStr+') ';	

		PRINT @sql;
		 FETCH NEXT FROM cur2 INTO @objectId,@indexId,@schemaName,@tableName,@indexName,@indexTypeDesc;
	 END;
	 CLOSE cur2;
	 DEALLOCATE cur2;
	 pEnd:;
 END;

注意该过程可以生成行索引、列索引以及内存优化表索引创建脚本的生成，对于xml索引、空间索引、全文索引暂未包括，请注意使用。

如果喜欢，可以搜索关注 MSSQLServer 公众号，将有更多精彩内容分享：