mysql碎片率如何计算 sql碎片整理

 

SQL SERVER整理索引碎片测试

SQL SERVER整理索引的方法也就这么几种，而且老是自作聪明的加入智能判断很不爽，还是比DBMS_ADVISOR差远了：

1

SQL SERVER 2000/2005

查询

2000 use DB_Name declare @table_id int set @table_id=object_id('Table_Name') dbcc showcontig(@table_id) 2005 SELECT index_id,index_type_desc,avg_fragmentation_in_percent,page_count FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('Table_Name'), NULL, NULL , 'LIMITED');

整理

2000 DBCC INDEXDEFRAG (DB_Name, 'dbo.Table_Name', Index_Name) DBCC DBREINDEX ('DB_Name.dbo.Table_Name','',100) 2005 ALTER INDEX Index_Name ON dbo.Table_Name REBUILD

以下是一个网上找的不错的例子，链接是： 新建一个表：

create    
 table 
  t3
(
i   int 
   
 primary 
   
 key 
 ,
xx   varchar 
 ( 
 200  )  
 not 
   
 null 
 
)

加入数据：

declare     @x 
   
 int 
 
  set    
 @x 
   
 = 
   
 0  
  while    
 @x 
   
 < 
 1000  
  begin                         
  insert    
 into 
  t3  
 values 
  ( 
 @x 
 , 
 ' 
 qweasdqweasdqweasdqweqweasdqwe 
 ' 
 )
  set    
 @x 
   
 = 
   
 @x 
 + 
 1  
  end

执行动态管理视图：

SELECT   index_id,index_type_desc,avg_fragmentation_in_percent,page_count   
 FROM   sys.dm_db_index_physical_stats
 (  db_id  (),   OBJECT_ID 
 ( 
 ' 
 t3 
 ' 
 ),  
 NULL 
 ,  
 NULL 
  ,  
 ' 
 LIMITED 
 ' 
 );

可以看到：

index_id为0表示这个是堆，平均的碎片有33%

现在执行几个可以减少碎片的方法都不管用，不能减少碎片。

包括：

DBCC   INDEXDEFRAG (test,   '  dbo.t3  '  , PK__t3__0EA330E9)
  alter     index   PK__t3__0EA330E9   on 
  t3
rebuild
  dbcc   dbreindex (  '  t3  ' 
 )

这几个方法还有删除重建索引，都不能减少碎片数量。 后来我觉得是因为数据太少了，导致页也很少，数据库可能存在某种智能，判断是否值得去做重建索引的工作，所以加大的数据量：

declare     @x     int  
  set     @x     =    
 1000  
  while     @x     <  10000  
  begin                         
  insert     into   t3   values   ( 
 @x 
 , 
 ' 
 qweasdqweasdqweasdqweqweasdqwe 
 ' 
 )
  set     @x     =    
 @x 
 + 
 1  
  end

再执行语句：

SELECT   index_id,index_type_desc,avg_fragmentation_in_percent,page_count   
 FROM   sys.dm_db_index_physical_stats
 (  db_id  (),   OBJECT_ID  (  '  t3  ' 
 ),  
 NULL 
 ,  
 NULL 
  ,  
 ' 
 LIMITED 
 ' 
 );
  alter     index   t3index   on   t3 rebuild

显示出来了！

结论：

SQL Server在执行相关的操作的时候都会智能去判断是否值得去做，比如在页面数太小的情况下可以不去重建索引，rebuild reindex 。类似的，在SQL Server 2005 里面也多了许多智能的判断来保证一个完整庞大而又不失智能的设计，

比如：

生成查询计划的阀值

缓存机制，缓存的筛选，LRU算法

预读机制

checkpoint减少回滚距离

智能join判断

重编译

了解SQL Server这种类似的软件产品能够为我们在设计产品的时候提供更多的思路想法，即使你了解上面的东西对你的SQL开发也不会有太多帮助。

另外附上几种方式的区别：

reindex是比较好的选择，速度快，但是他不能在线操作

INDEXDEFRAG 比较慢，但是可以在线操作

rebuild建议在碎片较少时采用。

附上微软的重建索引脚本，从里面也可以看出微软根据碎片大小推荐的方式，不过这个要随每个不同的数据库而定。

--   ensure a USE <databasename> statement has been executed first.  
  SET   NOCOUNT   ON  ;
  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  );
  --   ensure the temporary table does not exist  
  IF     EXISTS   (  SELECT   name   FROM   sys.objects   WHERE 
  name  
 = 
   
 ' 
 work_to_do 
 ' 
 )
      DROP     TABLE   work_to_do;
  --   conditionally select from the function, converting object and index IDs to names.  
  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 the cursor for the list of partitions to be processed.  
  DECLARE   partitions   CURSOR     FOR     SELECT     * 
   
 FROM 
  work_to_do;
  --   Open the cursor.  
  OPEN   partitions;
  --   Loop through the 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 
 ;
  --   30 is an arbitrary decision point at which to switch between reorganizing and rebuilding  
  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  ;
  PRINT     '  Executed   '     +     @command 
 ;
  FETCH     NEXT     FROM   partitions   INTO     @objectid 
 ,  
 @indexid 
 ,  
 @partitionnum 
 ,  
 @frag 
 ;
  END  ;
  --   Close and deallocate the cursor.  
  CLOSE   partitions;
  DEALLOCATE   partitions;
  --   drop the temporary table  
  IF     EXISTS   (  SELECT   name   FROM   sys.objects   WHERE 
  name  
 = 
   
 ' 
 work_to_do 
 ' 
 )
      DROP     TABLE   work_to_do;
  GO

BOL的推荐：

avg_fragmentation_in_percent 值	修复语句
> 5% 且 < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

小于5没必要重建，所以上面的SQL语句还是有得商量的地方。

/************************************** 作用:在线整理索引碎片 时间:2008-5-15 说明： 根据微软的示例改编 适用于无法停机重建索引的情况， 在线整理索引碎片，并更新表的统计信息以增强效果。 另外可以自定义需要整理的索引的逻辑碎片程度，扫描密度，页数 必须要在数据库访问较少的时候执行 ***************************************/ CREATE PROC [dbo].[SYS_IndexDrag] AS BEGIN SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL--逻辑碎片 DECLARE @MaxScanDensity DECIMAL--扫描密度 DECLARE @Page INT --8k页数,设定一个限度对一定大的索引进行整理 DECLARE @TmpName VARCHAR(150) -- Decide on the maximum fragmentation to allow SELECT @maxfrag = 30.0 SELECT @MaxScanDensity=70.0 SELECT @Page=400 -- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Create the table CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablename END -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables -- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE (LogicalFrag >= @maxfrag OR [ScanDensity]<=@MaxScanDensity) AND [CountPages]>=@page AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 -- Open the cursor OPEN indexes -- loop through the indexes FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag WHILE @@FETCH_STATUS = 0 BEGIN --在线整理碎片 PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ', ' + RTRIM(@indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%' SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')' EXEC (@execstr) --更新统计信息 IF @TmpName<>@tablename BEGIN SET @tmpName=@tableName EXEC('UPDATE STATISTICS '+@TableName) END FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag END -- Close and deallocate the cursor CLOSE indexes DEALLOCATE indexes -- Delete the temporary table DROP TABLE #fraglist END GO