为什么要进行重新组织索引和重新生成索引?
索引是数据库引擎中针对表(包括索引视图)建立的特别数据结构,主要用来帮助查找数据。索引的重要性体现在能够使数据库引擎快速返回查询结果。索引是数据库优化的重要手段。当对索引所在的基础数据表进行修改时(包括插入、删除和更新等操作),当索引的逻辑排序和基础表(或索引视图)的物理排序不匹配时, 就会产生索引碎片。随着索引碎片的不断增多,查询响应时间就会变慢,查询性能也会下降。在SQL Server中,修复索引碎片的方法有两种:重新组织索引和重新生成索引。
重新组织索引
重新组织索引会对最外层数据页里的数据进行重新排序,并压缩索引页。重新组织的过程中 不会添加任何额外的数据,所以索引可能还残留着一定程度的碎片。重新组织索引操作不会占用很多系统资源,在运行过程中外部进程也能够对该索引所在的数据表 进行查询,所以能够说是联机(online)执行。
重新组织索引的SQL语句:
- ALTER INDEX 索引名 on 表名 REBUILD
重新生成索引
重新生成索引操作是删除掉目标索引并创建一个新索引。旧索引中的任何碎片都会随之被删除,新索引的逻辑排序将和对象的物理排序相匹配。由于整个过程需要删除索引并重新创建,所以外部进程无法访问数据表(虽然也有联机重新生成索引的方法,但是访问性能也会大受影响)。在重新生成索引的过程中,其他进程并不能完全锁定数据表。这是重新生成索引的一大障碍。
重新生成索引的SQL语句:
- ALTER INDEX 索引名 on 表名 reorganize
什么时候进行索引的重新组织or重新生成?
根据MSDN网站的推荐,当索引碎片比例在5%以下时,不需要考虑进行整理。当索引碎片比例在5%至30%之间时,进行索引重新组织。超过30%时,需要进行索引重新生成。
下面是MSDN网站给出的索引碎片整理的存储过程,大家可以在程序或数据库维护计划中定期执行,以便优化数据库。
- CREATE proc [dbo].[reBuildIndex]
- as
- DECLARE @objectid int;
- DECLARE @indexid int;
- DECLARE @partitioncount bigint;
- DECLARE @schemaname sysname;
- DECLARE @objectname sysname;
- DECLARE @indexname sysname;
- DECLARE @partitionnum bigint;
- DECLARE @partitions bigint;
- DECLARE @frag float;
- DECLARE @command varchar(8000);
- IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
- DROP TABLE work_to_do;
- SELECT
- object_id AS objectid,
- index_id AS indexid,
- partition_number AS partitionnum,
- avg_fragmentation_in_percent AS frag
- INTO work_to_do
- FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
- WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
- DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
- OPEN partitions;
- FETCH NEXT
- FROM partitions
- INTO @objectid, @indexid, @partitionnum, @frag;
- WHILE @@FETCH_STATUS = 0
- BEGIN;
- SELECT @objectname = o.name, @schemaname = s.name
- FROM sys.objects AS o
- JOIN sys.schemas as s ON s.schema_id = o.schema_id
- WHERE o.object_id = @objectid;
- SELECT @indexname = name
- FROM sys.indexes
- WHERE object_id = @objectid AND index_id = @indexid;
- SELECT @partitioncount = count (*)
- FROM sys.partitions
- WHERE object_id = @objectid AND index_id = @indexid;
- IF @frag < 30.0
- BEGIN;
- SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
- IF @partitioncount > 1
- SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
- EXEC (@command);
- END;
- IF @frag >= 30.0
- BEGIN;
- SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
- IF @partitioncount > 1
- SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
- EXEC (@command);
- END;
- FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
- END;
- -- Close and deallocate the cursor.
- CLOSE partitions;
- DEALLOCATE partitions;
- IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
- DROP TABLE work_to_do;
- 工资小程序
- 员工工资查询小程序
- 我的微信公众号
- 我的微信公众号扫一扫
评论