SQL Server索引

一、SQL Server存储机制

1.数据库：数据库是最高级别的存储机制。

2.区段：区段是用来为表和索引分配空间的基本存储单元。它由8个连续的8k数据页组成。对于新记录，内存空间的每次分配的是一个区段。区段是建立在数据库的基础上的。

3.页：页是特定区段中的分配单元，每个区段由8个页组成。

4.行：行最大为8kb。每行最多包含1024个标准列（非稀疏列）。

5.稀疏列：

二、索引

       索引是建立在表和视图之上的，因此必须将所有和它们相关联。在SQL Server中有两种类型的索引：群集索引和非群集索引。其中群集索引可分为堆上的非群集索引和群集表上的非群集索引。 对于两种类型的索引来说，任何已读取的页都会保存在缓存中，一增加访问速度。

1.群集索引

        对于一个给定的表来说，最多只能有一个群集索引。群集索引的叶级就是实际的数据，并且索引的顺序就是其物理排序。对于一个新的记录插入到索引结构中，如果这个新纪录是插入到一个满页的末尾，那么这个新纪录将被插入到一个新页中，满页不会发生拆分；如果是插入到一个满页的中间，这个满页会放生对半拆分。注意，叶级的数据就是实际要获取的数据。

2.堆上的非群集索引

       堆上的非群集索引和群集索引的区别在于叶级不是数据，而是指向数据的指针。这个指针以RID形式出现，其中RID由索引指向的区段、页和行偏移量构成。因此，从内存的角度看，数据没有任何的排序，其内存顺序和索引顺序没有直接联系。

3.群集表上的非群集索引

       这种类型的索引和堆上的非群集索引在非叶级上的工作方式几乎一样，当到了叶级后，其会再通过群集索引的方式来找到群集索引叶级上的数据。

三、创建、修改和删除索引

1.创建索引

创建索引有两种方式：第一种，使用CREATE INDEX语句创建；第二种，在创建约束时作为隐含对象。

其中CREATE INDEX语句的语法结构如下：

从语法上可以看出，索引必须与表或者视图相关联，必须声明列（ON）所在的表或者视图。

1）ASC/DESC

        在默认情况下,为ASC，它表示升序。要将其设为降序，要显示的使用DESC。

2）INCLUDE

        在SQL Server中，使用INCLUDE将某一行添加到索引的叶级上。因为，对于SQL Server来说，如果它获取了需要的数据，就会停止工作。可以看出，INCLUDE实际上适用于非群集索引中，因为群集索引的叶级已经代表实际的数据了。

3）WITH

        其用于告诉SQL Server将要提供一个或多个后面的选项。

4）PAD_INDEX

        用于设置第一次创建索引时，非叶级页有多满的开关。其会在后面的选项FILLFACTOR选项设置百分比。如果需要设置，FILLFACTOR选项，需要将该选项设为ON。如果不设置FILLFACTOR选项，这个选项的设置任意。

5）FILLFACTOR

       该选项用于第一次创建索引时，页的满度，其可以为1~100的任意值。但，在进行页拆分时，仍然是对半分。在默认情况下，会尽量填满每页，但会留下两行的空间，并且保证每页有一行数据。

6）IGNORE_DUP_KEY

        通常，唯一约束或唯一索引，不允许任何种类的重复。该选项用于设置是否允许任何种类的重复，如果设为允许，将出现警告错误，而不是关键错误，并且这个重复行也没有被插入进去；如果设置不允许，那么将出现关键错误。

7）DROP_EXISTING

       设置该选项，那么具有所讨论的任何现有索引将在构造新索引之前被删除。

8）STATISTICS_NORECOMPUTE

      默认情况下，SQL Server会试图自动化在表或索引上更新统计信息的过程。而设置了该选项，表示将手动更新信息。不建议使用，‘

9）SORT_IN_TEMPDB

        如果没有设置该选项，那么构建索引的中间页将被写入到存储数据库的相同物理文件中，这样会造成竞争。如果使用了该选项，那么构建索引的中间页就会被写入temphb中。并且这个选项，只对temphb和数据库分别存储在不同的物理驱动器中才有效。否则，还是会发生竞争现象。

10）ONLINE

       如果设置为ON，那么强制对表对于一般的访问保持有效，并且不创建任何阻住用户使用索引或表的锁。在默认情况下，全索引操作会获取索引锁，以便对表进行完全和有效的访问，但这也会阻止用户。

11）ALLOW ROW/PAGE LOCKS

       用于设置是否支持行锁或页锁。

12）MAXDOP

       用于为构建覆盖关于最大并行度的系统设置。

13）ON

       将数据和索引单独存放。

2.隐含索引

      当向表添加以下两种约束时，就会创建隐含索引：

      1）主键约束

      2）唯一约束（也成为替换键）

       如果在创建主键是不需要索引，要在后面添加NONCLUSTERED关键字。

3.修改索引

         ALTER INDEX命令和其他命令有所不同，该命令不和索引的结构相关。如果需要修改索引的组成，只能使用DROP索引再CREATE索引，或者用DROP_EXISTING=ON选CREATE索引并使用索引。

        ALTER INDEX命令与维护有关，其语法如下：

1）REBUILD

        该选项用于修复索引的方法。设置了该选项，将会删除旧索引，生成修改优化后的索引。

2）DISABLE

       该选项用于禁止索引。在激活之前，必须重建索引。该选项用于群集索引，在重建或激活前，表会被禁止访问，数据仍然再。

3）REORGANIZE

       该选项用于重新组织索引，但可以联机工作（用户仍然可以使用索引）。但这个优化只对叶级其作用。

4.删除索引

其语法如下：

四、维护索引

索引的维护主要有两个问题：1）页拆分；2）碎片。

可以运用sys.dm_db_index_physical_stats函数获取数据库中页和区段有多满。