第十章——维护索引（3）——通过重建索引提高性能

前言：

重建一个索引只是在内部删除并重建索引，使得碎片消失、统计信息更新、物理顺序重新排列组织。它会压缩数据页，按照填充因子填充适当的数据。如果有需要，也会添加新的数据页。这些操作有利于提高数据查找的速度，但是这个工作如果发生在大表上面，将是非常耗时耗资源的。

 

准备工作：

首先先要决定是否达到了重建索引的临界值。否则，重组索引会更好。当碎片超过30%，那么重建索引会比较好。

重建索引有两种方式，在重建之前应该考虑使用哪种会更好：

1、 脱机：脱机重建索引是默认选项。它会锁住整个表，知道重建结束，没有人可以访问这个表。如果表非常大，这将持续几个小时甚至更久。但是它运行得更快，并且占用Tempdb的资源更少。

2、 联机：如果环境不允许脱机，那么可以使用联机重建，但是会占用非常多的资源，如果表数据类型为varchar(max)，nvarchar(max)和text类型，将不会在联机模式下工作。

注意：是否联机重建索引只有开发版和企业版可用。其他版本只有脱机重建。

 

步骤：

1、 创建测试表：

USE [AdventureWorks]
GO
 
IF EXISTS( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[ordDemo]')
                    AND type IN( N'U' ) )
    DROP TABLE [dbo].[ordDemo]
GO
 
CREATE TABLE [dbo].[ordDemo]
    (
      [OrderID] [int] IDENTITY(1, 1)
                      NOT NULL ,
      [OrderDate] [datetime] NULL ,
      [Amount] [money] NULL ,
      [Refno] [int] NULL
    )
ON  [PRIMARY]
 
GO

再创建一个聚集索引idx_refno：

 USE [AdventureWorks]
GO

IF EXISTS ( SELECT  *
            FROM    sys.indexes
            WHERE   object_id = OBJECT_ID(N'[dbo].[ordDemo]')
                    AND name = N'idx_refno' ) 
    DROP INDEX [idx_refno] ON [dbo].[ordDemo] WITH ( ONLINE = OFF )
GO

现在使用下面的脚本重建索引：

 --使用联机方式重建索引idx_refno 
ALTER INDEX [idx_refno] ON [ordDemo] REBUILD WITH (FILLFACTOR=80,ONLINE =ON)
GO

--使用脱机方式重建索引idx_refno 
ALTER INDEX [idx_refno] ON [ordDemo] REBUILD WITH (FILLFACTOR=80,ONLINE =OFF)
GO

--使用脱机方式重建表上所有索引：
ALTER INDEX ALL ON [ordDemo] REBUILD WITH (FILLFACTOR=80,ONLINE =OFF )
GO

--使用DROP_EXISTING 来重建索引：
CREATE CLUSTERED INDEX [idx_refno] ON [ordDemo](Refno)
WITH (DROP_EXISTING=ON ,FILLFACTOR=70,ONLINE=ON )
GO

--使用DBCC DBREINDEX重建标上所有索引：
DBCC DBREINDEX('ordDemo')
GO

--重建带有填充因子的索引：
DBCC DBREINDEX('ordDemo','idx_refno',90)
GO

2、 注意，DBCC DBREINDEX命令将在未来版本删除，所以不建议使用，并尽快改成其他方式实现。 

 

分析：

在重建索引时，会锁住资源，直到进程完毕为止。重建会移除空白或者未使用的页，创建新的页，如果达到填充因子的限定，会分页，并以B-TREE方式存放这些数据页。

 

扩充信息：

基于个人经验，建议重建大表的索引时，把恢复模式改成大容量日志或者简单模式，避免日志文件的过度增长。但是更改恢复模式应该慎重，并在更改后立即做日志备份。

因为重建大表索引会非常耗时，所以不要不耐烦并停止重建操作，这样会引起一些危险的后果，并可能使得数据库进入恢复模式。

重建索引需要有sysadmin、db_onwer或者db_ddladmin角色。