SQL Server索引

索引分类

1.聚集索引和非聚集索引

聚集索引会对基本表进行物理排序，所以这种索引对查询非常有效，在每一张基本表中只能有一个聚集索引。当建立主键约束时，如果基本表中没有聚集索引，SQL Server会用主键列作聚集索引键。

非聚集索引不会对基本表进行物理排序。如果表中不存在聚集索引，则基本表中是不排序的。

2.唯一索引和非唯一索引

唯一索引确保在被索引的列中，所有数据是唯一的，不包含重复值。如果表具有PRIMARY KEY约束或UNIQUE约束，那么执行CREATE TABLE语句或ALTER TABLE语句时，SQL Server会自动创建唯一索引。

非唯一索引允许所保存的列中出现重复值，所以在数据操作时，非唯一索引会比唯一索引带来更大的开销。

3.简单索引和复合索引

只针对基本表的一列建立的索引，称为简单索引；

针对多个列(最多为16列)建立的索引成为复合索引或组合索引

创建索引

语句格式

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX <索引名>
ON <表名或视图名>(<列名>[ASC|DESC][, ... n])
[
WITH PAD_INDEX
[[,]FILLFACTOR = <填充因子>]
[,INGORE_DUP_KEY]
[[,DROP_EXISTING]]
... ]

UNIQUE——指定创建的索引为唯一索引，省略则为非唯一索引

CLUSTERED|NONCLUSTERED——用于指定创建的索引为聚集索引或非聚集索引。省略则默认非聚集索引

ASC|DESC——用于指定索引列升序或降序，默认ASC

PAD_INDEX——指定索引填充，取值为ON|OFF，默认OFF。PAD_INDEX选项只用于连接FILLFACTOR

FILLFACTOR——指定填充因子的大小。使用FILLFACTOR是读与写之间的一个平衡操作；一般的选择：对于数据基本不变化的，将填充因子设置到足够大；对于数据经常变化的，将填充因子设置为足够小

IGNORE_DUP_KEY——当向唯一聚集索引或非唯一索引中插入重复数据时，用于忽略重复值输入。此子句要与UNIQUE保留字同时使用

DROP_EXISTING——指定应删除并重新创建同名的先前存在的聚集索引或非聚集索引

例：为学生名字创建唯一索引

USE JXGL
CREATE UNIQUE INDEX 学生_姓名
ON 学生(姓名)

例：为选修课程表中创建复合索引

USE JXGL
CREATE INDEX I_学号_课程号
ON 选修(学号 ASC,课程号 DESC)

间接创建索引

在定义或修改表结构时，如果定义了主键约束(PRIMARY KEY)或唯一性约束(UNIQUE)，那么系统就同时创建了索引

以此方法创建的索引，完全由系统自动选择和维护

管理索引

1.查看索引

EXEC sp_helpindex <表名称>

2.修改索引

EXEP sp_rename <表名>.<旧名称>,<新名称>[,<对象类型>]

3.删除索引

DROP INDEX <表名>.<索引名>[, ... n]

4.维护索引

Ⅰ.检查整理索引碎片

• 检查相关表中有无索引的碎片信息

  DBCC SHOWCONTIG(<表名>)
  

• 使用DBCC INDEXDEFRAG整理索引碎片。它对索引的页级进行碎片管理，以便使页的物理顺序与叶结点从左到右的逻辑顺序相匹配，从而提高索引扫描性能

  DBCC INDEXDEFRAG(<表名>,[<索引名>[,<填充因子>]])
  

Ⅱ.重新组织索引

如果有关表或索引的统计信息已经过时或者不完整，则会导致优化器选择不是最佳的方案，并且会降低执行查询的速度，这样就需要重新组织索引。

ALTER INDEX <索引名>|ALL
ON <表>
REBUILD
[
WITH
(
[[,]PAD_INDEX = ON|OFF]
[[,]FILLFACTOR = <填充因子>]
[[,]SORT_IN_TEMPDB = ON|OFF]
)
]

SORT_IN_TEMPDB = ON|OFF指定是否在tempdb中存储排序结果。默认OFF