如何将索引碎片数量降至最低

梦里梦外; 2021-12-23 02:05 246阅读 0赞

索引碎片能增大索引树的大小，增加不必要的IO，所以每隔一段时间对索引碎片进行检查时很有必要的。  
下面一个示例一起来分析如何将索引降至最低。  
  
新建一个表：

![Image 1][] create  table  t3  
![Image 1][](  
![Image 1][]i  int  primary  key ,  
![Image 1][]xx  varchar ( **200** )  not  null   
![Image 1][])

加入数据：

![Image 1][] declare  @x  int   
![Image 1][] set  @x  =  **0**   
![Image 1][] while  @x  < **1000**   
![Image 1][] begin   
![Image 1][] insert  into  t3  values  ( @x , ' qweasdqweasdqweasdqweqweasdqwe ' )  
![Image 1][] set  @x  =  @x \+ **1**   
![Image 1][] end

执行动态管理视图：

![Image 1][] 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 ' );  
![Image 1][]

可以看到：  
![indexview.jpg.jpg][]  
  
index\_id为0表示这个是堆，平均的碎片有33%  
  
现在执行几个可以减少碎片的方法都不管用，不能减少碎片。  
包括：

![Image 1][] DBCC  INDEXDEFRAG (test,  ' dbo.t3 ' , PK\_\_t3\_\_0EA330E9)  
![Image 1][]  
![Image 1][] alter  index  PK\_\_t3\_\_0EA330E9  on  t3  
![Image 1][]rebuild  
![Image 1][]  
![Image 1][] dbcc  dbreindex ( ' t3 ' )  
![Image 1][]

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

![Image 1][] declare  @x  int   
![Image 1][] set  @x  =  **1000**   
![Image 1][] while  @x  < **10000**   
![Image 1][] begin   
![Image 1][] insert  into  t3  values  ( @x , ' qweasdqweasdqweasdqweqweasdqwe ' )  
![Image 1][] set  @x  =  @x \+ **1**   
![Image 1][] end

再执行语句：

显示出来了！  
![indexview2.jpg.jpg][]  
  
结论：  
SQL Server在执行相关的操作的时候都会智能去判断是否值得去做，比如在页面数太小的情况下可以不去重建索引，rebuild reindex 。类似的，在SQL Server 2005 里面也多了许多智能的判断来保证一个完整庞大而又不失智能的设计，  
比如：  
生成查询计划的阀值  
缓存机制，缓存的筛选，LRU算法  
预读机制  
checkpoint减少回滚距离  
智能join判断  
重编译  
  
了解SQL Server这种类似的软件产品能够为我们在设计产品的时候提供更多的思路想法，即使你了解上面的东西对你的SQL开发也不会有太多帮助。  
  
另外附上几种方式的区别：  
reindex是比较好的选择，速度快，但是他不能在线操作  
INDEXDEFRAG 比较慢，但是可以在线操作  
rebuild建议在碎片较少时采用。  
  
附上微软的重建索引脚本，从里面也可以看出微软根据碎片大小推荐的方式，不过这个要随每个不同的数据库而定。

![Image 1][] \--  ensure a USE <databasename> statement has been executed first.   
![Image 1][] SET  NOCOUNT  ON ;  
![Image 1][] DECLARE  @objectid  int ;  
![Image 1][] DECLARE  @indexid  int ;  
![Image 1][] DECLARE  @partitioncount  bigint ;  
![Image 1][] DECLARE  @schemaname  sysname;  
![Image 1][] DECLARE  @objectname  sysname;  
![Image 1][] DECLARE  @indexname  sysname;  
![Image 1][] DECLARE  @partitionnum  bigint ;  
![Image 1][] DECLARE  @partitions  bigint ;  
![Image 1][] DECLARE  @frag  float ;  
![Image 1][] DECLARE  @command  varchar ( **8000** );  
![Image 1][] \--  ensure the temporary table does not exist   
![Image 1][] IF  EXISTS  ( SELECT  name  FROM  sys.objects  WHERE  name  =  ' work\_to\_do ' )  
![Image 1][] DROP  TABLE  work\_to\_do;  
![Image 1][] \--  conditionally select from the function, converting object and index IDs to names.   
![Image 1][] SELECT   
![Image 1][] object\_id  AS  objectid,  
![Image 1][] index\_id  AS  indexid,  
![Image 1][] partition\_number  AS  partitionnum,  
![Image 1][] avg\_fragmentation\_in\_percent  AS  frag  
![Image 1][] INTO  work\_to\_do  
![Image 1][] FROM  sys.dm\_db\_index\_physical\_stats ( DB\_ID (),  NULL ,  NULL  ,  NULL ,  ' LIMITED ' )  
![Image 1][] WHERE  avg\_fragmentation\_in\_percent  >  **10.0**  AND  index\_id  >  **0** ;  
![Image 1][] \--  Declare the cursor for the list of partitions to be processed.   
![Image 1][] DECLARE  partitions  CURSOR  FOR  SELECT  \*  FROM  work\_to\_do;  
![Image 1][]  
![Image 1][] \--  Open the cursor.   
![Image 1][] OPEN  partitions;  
![Image 1][]  
![Image 1][] \--  Loop through the partitions.   
![Image 1][] FETCH  NEXT   
![Image 1][] FROM  partitions  
![Image 1][] INTO  @objectid ,  @indexid ,  @partitionnum ,  @frag ;  
![Image 1][]  
![Image 1][] WHILE  **@@FETCH\_STATUS**  =  **0**   
![Image 1][] BEGIN ;  
![Image 1][] SELECT  @objectname  =  o.name,  @schemaname  =  s.name  
![Image 1][] FROM  sys.objects  AS  o  
![Image 1][] JOIN  sys.schemas  as  s  ON  s.schema\_id  =  o.schema\_id  
![Image 1][] WHERE  o. object\_id  =  @objectid ;  
![Image 1][]  
![Image 1][] SELECT  @indexname  =  name  
![Image 1][] FROM  sys.indexes  
![Image 1][] WHERE  object\_id  =  @objectid  AND  index\_id  =  @indexid ;  
![Image 1][]  
![Image 1][] SELECT  @partitioncount  =  count  ( \* )  
![Image 1][] FROM  sys.partitions  
![Image 1][] WHERE  object\_id  =  @objectid  AND  index\_id  =  @indexid ;  
![Image 1][]  
![Image 1][] \--  30 is an arbitrary decision point at which to switch between reorganizing and rebuilding   
![Image 1][] IF  @frag  <  **30.0**   
![Image 1][] BEGIN ;  
![Image 1][] SELECT  @command  =  ' ALTER INDEX  '  \+  @indexname  \+  '  ON  '  \+  @schemaname  \+  ' . '  \+  @objectname  \+  '  REORGANIZE ' ;  
![Image 1][] IF  @partitioncount  >  **1**   
![Image 1][] SELECT  @command  =  @command  \+  '  PARTITION= '  \+  CONVERT  ( CHAR ,  @partitionnum );  
![Image 1][] EXEC  ( @command );  
![Image 1][] END ;  
![Image 1][]  
![Image 1][] IF  @frag  >=  **30.0**   
![Image 1][] BEGIN ;  
![Image 1][] SELECT  @command  =  ' ALTER INDEX  '  \+  @indexname  \+ '  ON  '  \+  @schemaname  \+  ' . '  \+  @objectname  \+  '  REBUILD ' ;  
![Image 1][] IF  @partitioncount  >  **1**   
![Image 1][] SELECT  @command  =  @command  \+  '  PARTITION= '  \+  CONVERT  ( CHAR ,  @partitionnum );  
![Image 1][] EXEC  ( @command );  
![Image 1][] END ;  
![Image 1][] PRINT  ' Executed  '  \+  @command ;  
![Image 1][]  
![Image 1][] FETCH  NEXT  FROM  partitions  INTO  @objectid ,  @indexid ,  @partitionnum ,  @frag ;  
![Image 1][] END ;  
![Image 1][] \--  Close and deallocate the cursor.   
![Image 1][] CLOSE  partitions;  
![Image 1][] DEALLOCATE  partitions;  
![Image 1][]  
![Image 1][] \--  drop the temporary table   
![Image 1][] IF  EXISTS  ( SELECT  name  FROM  sys.objects  WHERE  name  =  ' work\_to\_do ' )  
![Image 1][] DROP  TABLE  work\_to\_do;  
![Image 1][] GO

BOL的推荐：

<table> 
 <tbody> 
  <tr> 
   <th><strong>avg_fragmentation_in_percent</strong> 值 </th> 
   <th>修复语句 </th> 
  </tr> 
  <tr> 
   <td> <p>&gt; 5% 且 &lt; = 30% </p> </td> 
   <td> <p>ALTER INDEX REORGANIZE</p> </td> 
  </tr> 
  <tr> 
   <td> <p>&gt; 30%</p> </td> 
   <td> <p>ALTER INDEX REBUILD WITH (ONLINE = ON)*</p> </td> 
  </tr> 
 </tbody> 
</table>

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

转载于:https://www.cnblogs.com/perfectdesign/archive/2008/02/20/sqlserverreindexrebuild.html

[Image 1]: 
[indexview.jpg.jpg]: /images/20211220/447278dfb70342c6ab846f56fa469f2a.png
[indexview2.jpg.jpg]: /images/20211220/5a5c94e6b5d944318eec0927e576b625.png