索引的重新组织和重新生成

王世权 技术分享评论5,648 views2阅读模式

重新组合索引与重新生成索引

为什么要进行重新组织索引和重新生成索引?

索引是数据库引擎中针对表(包括索引视图)建立的特别数据结构,主要用来帮助查找数据。索引的重要性体现在能够使数据库引擎快速返回查询结果。索引是数据库优化的重要手段。当对索引所在的基础数据表进行修改时(包括插入、删除和更新等操作),当索引的逻辑排序和基础表(或索引视图)的物理排序不匹配时, 就会产生索引碎片。随着索引碎片的不断增多,查询响应时间就会变慢,查询性能也会下降。在SQL Server中,修复索引碎片的方法有两种:重新组织索引和重新生成索引。

重新组织索引

重新组织索引会对最外层数据页里的数据进行重新排序,并压缩索引页。重新组织的过程中 不会添加任何额外的数据,所以索引可能还残留着一定程度的碎片。重新组织索引操作不会占用很多系统资源,在运行过程中外部进程也能够对该索引所在的数据表 进行查询,所以能够说是联机(online)执行。

重新组织索引的SQL语句:

  1. ALTER INDEX 索引名 on 表名 REBUILD

重新生成索引

重新生成索引操作是删除掉目标索引并创建一个新索引。旧索引中的任何碎片都会随之被删除,新索引的逻辑排序将和对象的物理排序相匹配。由于整个过程需要删除索引并重新创建,所以外部进程无法访问数据表(虽然也有联机重新生成索引的方法,但是访问性能也会大受影响)。在重新生成索引的过程中,其他进程并不能完全锁定数据表。这是重新生成索引的一大障碍。

重新生成索引的SQL语句:

  1. ALTER INDEX 索引名 on 表名 reorganize

什么时候进行索引的重新组织or重新生成?

根据MSDN网站的推荐,当索引碎片比例在5%以下时,不需要考虑进行整理。当索引碎片比例在5%至30%之间时,进行索引重新组织。超过30%时,需要进行索引重新生成。

下面是MSDN网站给出的索引碎片整理的存储过程,大家可以在程序或数据库维护计划中定期执行,以便优化数据库。

  1. CREATE proc [dbo].[reBuildIndex]
  2. as
  3.  DECLARE @objectid int;
  4.  DECLARE @indexid int;
  5.  DECLARE @partitioncount bigint;
  6.  DECLARE @schemaname sysname;
  7.  DECLARE @objectname sysname;
  8.  DECLARE @indexname sysname;
  9.  DECLARE @partitionnum bigint;
  10.  DECLARE @partitions bigint;
  11.  DECLARE @frag float;
  12.  DECLARE @command varchar(8000);
  13.  IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
  14.      DROP TABLE work_to_do;
  15.   SELECT
  16.      object_id AS objectid,
  17.      index_id AS indexid,
  18.      partition_number AS partitionnum,
  19.      avg_fragmentation_in_percent AS frag
  20.  INTO work_to_do
  21.  FROM sys.dm_db_index_physical_stats (DB_ID(), NULLNULL , NULL, 'LIMITED')
  22.  WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
  23.  DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
  24.  OPEN partitions;
  25.  FETCH NEXT
  26.     FROM partitions
  27.     INTO @objectid, @indexid, @partitionnum, @frag;
  28. WHILE @@FETCH_STATUS = 0
  29.      BEGIN;
  30.          SELECT @objectname = o.name, @schemaname = s.name
  31.          FROM sys.objects AS o
  32.          JOIN sys.schemas as s ON s.schema_id = o.schema_id
  33.          WHERE o.object_id = @objectid;
  34.         SELECT @indexname = name
  35.          FROM sys.indexes
  36.          WHERE  object_id = @objectid AND index_id = @indexid;
  37.         SELECT @partitioncount = count (*)
  38.          FROM sys.partitions
  39.          WHERE object_id = @objectid AND index_id = @indexid;
  40.  IF @frag < 30.0
  41.      BEGIN;
  42.      SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
  43.      IF @partitioncount > 1
  44.          SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
  45.      EXEC (@command);
  46.      END;
  47. IF @frag >= 30.0
  48.      BEGIN;
  49.      SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
  50.      IF @partitioncount > 1
  51.          SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
  52.      EXEC (@command);
  53.      END;
  54. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
  55.  END;
  56.  -- Close and deallocate the cursor.
  57.  CLOSE partitions;
  58.  DEALLOCATE partitions;
  59.  IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
  60.      DROP TABLE work_to_do;

  • 工资小程序
  • 员工工资查询小程序
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
王世权
  • 本文由 发表于 2015年8月5日 22:55:08
  • 转载请务必保留本文链接:http://www.liaodestudio.com/?p=998