SQL Server索引进阶 第十二篇:创建,修改,删除

2016-3-3 141+ 分享:

本文是SQL Server索引进阶系列(Stairway to SQL Server Indexes)的一部分。

在第十级中我们看到了索引的内部结构,在第十一级中我们看到了平衡树结构潜在的负面影响:索引碎片。有了索引内部结构的知识,我们可以检查在执行数据定义语句和数据操作语句的时候,都发生了什么。在本级中我们介绍数据定义语言的三个动词:create,alter和drop。在下一级中,我们介绍数据操作语言的三个动词:insert,update,delete。

创建,修改,删除索引都是索引维护的范围。create,alter,drop作为维护索引的动词,只是因为SQL Server团队认为队友对象的维护应该使用相同的DDL动词。在文中你会看到,这几个动词做的工作要比表面的多,使得你可以创建,重建,重新组织,禁用,删除索引,还可以修改元数据。

在你创建或者修改索引的时候,你可以指定一个选项。这些选项是索引元数据的一部分,存储在系统表中,通过sys.indexes视图查看这些内容。在查询和更新数据的时候,还有维护索引的时候,会被SQL Server使用。在本文中的很多地方我们用到了这些选项,但是没有详细介绍。但是,我们在文章的最后面详细介绍这些选项。

你的表越大,在索引上执行DDL的影响越大。执行DDL的同时,会消耗服务器资源,以及减慢其他查询。在理解了你的索引在DDL和DML的过程中发生了什么,你可以:

  • 理解定期的索引维护的需要。
  • 最大化维护操作的性能。
  • 最小化维护操作给其他查询带来的影响。
  • 减少维护的次数。

创建索引

我们先看创建聚集索引,稍后再看创建非聚集索引。

如果在创建聚集索引的时候,可能会出现如下的信息。

The table already is a clustered index:

发生了错误。一张表不能创建两个聚集索引,将会导致一张表在同时有两种排序,这是不可能的。

The table is empty:

SQL Server更新了系统表中的聚集索引,还没有分配空间。

The table has rows.  The table does not have nonclustered indexes:

SQL Server更新了系统表中的聚集索引。

SQL Server用索引键的顺序对表进行排序。

The table has rows.  The table has nonclustered indexes:

SQL Server释放了非聚集索引占用的空间,但是保留了元数据。

SQL Server更新了系统表中的聚集索引。

SQL Server构建聚集索引。

根据元数据重建非聚集索引。没有其他选择,非聚集索引一定要重建。每个入口的标签,以前是RID,现在是聚集索引的键值。因此,每个入口的大小和原来的大小不一样。

因此,如果你将在表中创建多个索引,为了节省时间和降低影像,应该先创建聚集索引,然后创建非聚集索引。

创建非聚集索引

The table is empty:

SQL Server更新了系统表中的非聚集索引。这时候还没有分配空间。

The table has rows:

SQL Server更新了系统表中的非聚集索引。

SQL Server扫描表,包含需要列的其他非聚集索引,为每一行构建索引入口,以索引顺序排序入口。

修改索引

修改索引允许你做四件事:

  • 禁用索引。
  • 重建索引。
  • 重新组织索引。
  • 改变索引的选项。

记住:有一件事修改索引不允许你做,那就是改变组成索引键的列。如果需要的话,只能删除索引,然后新建索引,或者是在create index的时候使用drop_existing选项。

禁用索引

禁用索引,使用disable关键字。

 


  1. ALTER INDEX PK_FragTest_PKCol  
  2.          ON FragTest 
  3.          DISABLE; 
  4. GO 

禁用索引,不会从系统表中删除索引的定义。所有禁用的索引可以在以后重建或者删除。

禁用非聚集索引会释放索引占用的磁盘空间。禁用非聚集索引之后,所有的查询和没有索引一样。

禁用聚集索引会释放索引中非叶子层占用的空间。禁用聚集索引之后,表不能进行查询和更新操作。

因为禁用索引伴随着空间释放,处理过程会需要少量的处理器时间和日志文件的IO。

禁用索引的主要原因是在重建索引的时候节省一些磁盘空间。如果没有禁用索引,重建过程会维护旧版本的索引,直到新版本的索引创建完成,导致两个版本同时消费磁盘空间。首先删除索引,在重建的过程中就会节省整个索引的空间。

重建索引

重建索引使用下面的语句。

 


  1. ALTER INDEX PK_FragTest_PKCol  
  2.          ON FragTest 
  3.          REBUILD  
  4.       WITH ( FILLFACTOR = 75 
  5.            , SORT_IN_TEMPDB = ON 
  6.            , MAXDOP = 3 );