SQL Server 索引重建（2005、2008R2、2012、2014）

最近一个客户在夜间重建索引时业务大面积停滞，因此询问索引重建的相关事宜，借此机会对SQL Server的索引重建进行一次较为细致的总结，文中摘抄了不少前人的总结也有不少来自官网，错误之处万望指正。

一、为什么要重建索引？

SQL Server绝大多数表都是IOT表，当我们在对数据库做DML操作时，数据库引擎会自动维护索引结构，本质上来说就是对B+树的再平衡，但是这种自动维护随着DML次数的增多会导致索引的填充度变小，影响查询性能，因此需要定期重建索引。

同Oracle索引的不同之处在于，SQL Server（可能还有MySQL的Innodb存储引擎）的聚集索引叶子节点实质上就是数据页，非聚集索引页的BookMark也是聚集索引，这与Oracle的BookMark为ROWID不同，这可能也是Oracle从来不建议重建索引的原因，至少我从未遇到过Oracle需要重建索引的情况。

二、用什么方式重建索引？

1.Rebuild方式：

　　1）DBCC DBREINDEX,具体语法参照DBCC HELP(DBREINDEX)的查询结果。

这种方式在SQL Server2005之前较为常见，只支持offline重建，MSDN已经说明在未来版本中可能不会对此语句提供支持，因此最好不要再使用此语句重建索引。

　　2）Alter index [index_name]|[all] on [table_name] rebuild [with (...)];

　　--all可以重建表上所有索引,具体语法参照：https://msdn.microsoft.com/en-us/library/ms188388.aspx

　　这种方式是SQL SERVER 2005后微软官方推荐的索引重建方式，与DBCC DBREINDEX不同的地方在于增加了很多选项，例如online选项以及sort_in_tempdb的选项。

　　例：

　　Alter index [index_name]|[all] rebuild on [table_name] [with (online=on)];

　　Alter index [index_name]|[all] rebuild on [table_name] [with (fillfactor = 90 ,sort_in_tempDB = on)];

 

　　那么新增的Online方式与传统的Offline方式的区别在哪里呢？对业务有何影响呢？

• Offline时，会在表上获取Sch-M锁来阻止所有用户的访问，然后将旧索引的数据复制到新索引中,新索引完成重建后删掉旧索引才会释放表锁。
• Online时，同样是复制旧索引数据到新索引中，但同时旧索引是可以读写的。重建过程中旧索引的修改操作同时会被应用到新索引中，因此有一个中间数据结构实现新旧索引数据的映射和修改冲突。在重建完成后，会使用Sch-M锁定表非常短的时间，然后使用新索引替代旧索引，并释放Sch-M。

　　毫无疑问Online的方式消耗更多的系统资源，如果磁盘空间充足，那么可以考虑Online的方式重建索引，但online并不表示完全不会阻塞表，在一次合作客户的微软技术支持组人员培训中他们也表示Online的方式现在也是存在很多问题的，但我想夜间索引重建还是能Hold住的。

　　鉴于Offline的索引重建方式无论如何都必定会造成读和写的阻塞，导致业务停滞，因此如业务需要可以考虑Online的重建方式。

 

　　此外fillfactor（填充因子）的合适值设置为多少合适呢？这取决于表的读/写比率：  

　　低更新表（读/写比：100比1）：100% fill factor  ，高更新表（写超过读）：50%-70% fill factor  ，居中：80%-90% fill factor     

　　过低的fillfactor会增加索引页的数量，也会导致更多的页需要被移至缓存，缓存中有用的数据减少。默认的fill factor为0（即100% fill factor），通常这不是个好的选择，特别是对于聚集索引。

　　另外对于索引重建的老大难问题--日志暴涨，无论是简单模式还是完整模式都无法避免，因此建议提前做好测试，准备足够的磁盘空间，经验值为磁盘空间剩余量保持在2/3最好，在索引重建之前也可以做完整备份，截断日志。

 

2.Reorganize方式：

Reorganize的方式都不会造成阻塞，但是效果却不理想。

　　1）DBCC INDEXDEFRAG; --依然是不推荐使用的语法，原因同DBCC DBREINDEX。

　　2）Alter index [index_name]|[all] on [table_name] reorganize;

　　索引重组使用的系统资源最少，它在叶级层从左至右，重新排列叶级页使之于索引的逻辑顺序一致，同时也会对页按填充因子进行压缩，但是会跳过正在使用的索引页，而且这里的使用资源较少并不意味着重建时间较短，相反的时间可能会更长。

　　因此如果维护窗口期访问量不大，可以考虑重组的索引碎片整理方式。

3.Drop原索引，create index创建新索引。

　　这种方式是最原始的方式，有一个巨大的缺陷是：重建聚集索引会导致非聚集索引重建两次，即便微软已经提供了Create Index ... DROP_EXISTING子句来避免这种情况，但有更正规的alter语句，为什么还要用这个呢？

　　因此最后一种个人建议直接忽略。

 

最后提供一个索引重建的脚本：

--查看索引碎片忽略小表--USE <dbname>SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,page_count --into #t_indexFROM sys.dm_db_index_physical_stats(DB_id(),null, null, null, null) AS DINNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_idINNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_idWHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30and page_count>=1000order by D.avg_fragmentation_in_percent desc--生成重建索引代码--USE dbnameSELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,page_count into #t_indexFROM sys.dm_db_index_physical_stats(DB_id(),null, null, null, null) AS DINNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_idINNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_idWHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30and page_count>=1000order by D.avg_fragmentation_in_percent descSELECT 'ALTER INDEX ' + index_name + ' ON ' + schema_name + '.'+ table_name + ' REBUILD WITH (ONLINE = ON)' FROM #t_index--ALTER INDEX ix_userid ON dbo.user_service REBUILD WITH (ONLINE = ON) drop table #t_index