SQL Server索引概述

1. 索引的概念

• 通过索引，可以减少返回查询结果集时读取的数据量，从而显著提高数据库查询的性能。索引还可以强制表中的行具有唯一性，从而确保表中数据的完整性。
• 假设有一个员工表（ Employees ），它有一个基于员工编码（ EmployeeID ）的索引，如图下图所示。该索引有序地存储每一个员工的 EmployeeID 值，并且与表中的行相对应。
  
  当 SQL Server 在 Employees 中查找某一个特定的 EmployeeID 值时，它自动找到EmployeeID 列索引表。在该索引表中，利用某种查找算法（如折半查找）可以快速地查找数据。当在索引表中找到某个特定的 EmployeeID 值后，提取其存储地址，然后在 Employees
  表中找到该存储地址所对应的员工的全部信息。如果 EmployeeID 列没有创建索引，那么 SQLServer 只能从表的第一行开始，逐行搜索特定的 EmployeeID 值，直到扫描完整个表为止。
  例如，在 mySales 数据库中执行“ SELECT * FROM Products WHERE ProductID=5 ”这条查询语句时，查询优化器自动评估可用于检索数据的每种方法，判断 ProductID 列上是否存在索引，然后选择最有效的方法，选择是扫描表还是从索引中搜索。
  扫描表时，查询优化器读取表中的所有行，并提取满足查询条件的行。扫描表会有许多磁盘 I ／ O 操作，并占用大量资源。但是，如果查询的结果集占表中的比例很大时，扫描表是一种最为有效的方法。
  查询优化器使用索引时，搜索索引键列，查找到查询所需行的存储位置，然后从该位置提取匹配行。通常，搜索索引比扫描表要快很多，因为索引与表不同，一般每行包含的列非常少，而且行遵循排序顺序。
  如果设计并创建了索引，查询优化器可以从多个有效的索引中选择。
• 在很多查询中，索引可以带来多方面的好处。然而索引所带来的好处是有代价的。一个建有索引的表在数据库中需要占用更多的存储空间。不仅如此，一个表如果建有大量索引会影响 INSERT 、 PDATE 和 DELETE 语句的性能，因为在更改表中的数据时，所有索引都必须进行适当的调整。除此之外，对小型表使用索引可能不会产生优化效果，因为 SQL Server在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。因此，在设计和创建索引时，应当确保执行的好处超过额外的存储空间和处理资源的代价。

2. 索引的类型

1. 聚集索引（ Clustered Index ）：表中存储的数据按照索引的顺序存储，即表中每一行的物理顺序与索引顺序相同。一个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储，但聚集索可以包含多个列（即组合索引）。聚集索引可用于经常使用的查询，其检索效率比普通索引高，但对数据新增、修改、删除的效率影响比较大。
2. 非聚集索引（ Nonclustered Index ）：索引不影响表中的数据存储顺序，一般包含索引键值和指向表数据存储位置的行定位器。其检索效率比聚集索引低，但对数据新增、修改、删除的效率影响很少。一张表最多可以创建 249 个非聚集索引。
3. 唯一索引（ Unique Index ）：保证索引键中不包含重复的值，从而使表中的每一行具有唯一性。创建 PRIMARY KEY或 UNIQUE 约束自动会为在指定的列上创建唯一索引。
   注：在实际应用中，当数据量很小时，用聚集索引作为排序列要比使用非聚集索引速度明显快得多；而当数据量比较大时（如 10 万条记录以上），则两者速度差别不很明显。

3.创建索引

• 创建索引的方法主要有两种：一种是在创建表时定义主键约束（ PRIMARY KEY ）或唯一约束（ UNIQUE ），这时系统会自动建立一个唯一索引；另一种是直接使用 CREATE INDEX语句。
• 注：使用 CREATE INDEX 语句创建索引时，如果没有指明 CLUSTERED 或 NONCLUSTERED ，则创建非聚集索引；在定义主键约束时，如果没有强制指定非聚集索引而且这个表之前又没有聚集索引，那么系统为主键列建立一个唯一的聚集索引。
• 创建索引时还可以对列关键字定义排序方向，索引列的默认排序方向是升序的，在CREATE INDEX 的列定义中可以显式使用 ASC 或 DESC 设置排序方向。调试方法大致如下：
  /* 创建一个唯一非聚集索引，注意：输出结果并没有按 ID 排序。/
  CREATE UNIQUE NONCLUSTERED INDEX 索引名称1 ON 表名 (索引列)
  / 删除非聚集索引，创建一个聚集索引，注意输出结果已按 ID 升序排列。/
  DROP INDEX 表名.索引名称1
  CREATE CLUSTERED INDEX 索引名称2 ON 表名 (索引列)
  / 删除原聚集索引，创建一个按 ID 降序排列的聚集索引，注意 SELECT 语句的输出结果。*/
  DROP INDEX 表名.索引名称2
  GO
  CREATE CLUSTERED INDEX 表名.索引名称3 ON 表名 (索引列 DESC)
  SELECT * FROM 表名
  /创建一个有多个列组成的组合索引。/
  CREATE CLUSTERED INDEX 表名.索引名称 ON 表名( 索引名称1 ASC,索引名称2 DESC )

4.删除索引

• 当不再需要一个索引时，可以将其从数据库中删除，以回收它使用的存储空间。一个表可以有多个索引，使用系统存储过程 sp_helpindex 可以查看一个表中所有索引的名称、类型以及定义它的键，也可以通过检索系统表 sysindexes 查看一个数据库中的所有索引对象。SQL Server使用DROP INDEX 语句从当前数据库中删除一个或多个索引。
• 对于在建表时通过 PRIMARY KEY 或 UNIQUE 约束创建的索引，不能使用 DROP INDEX进行删除，而应该使用 ALTER TABLE 和 DROP CONSTRAINT 子句进行删除。调试方法大致如下：
  /* 使用 DROP INDEX 删除 Suppliers 表中的一个索引。/
  IF EXISTS (SELECT name FROM sysindexes WHERE name = ’ 索引名称’)
  DROP INDEX 表名. 索引名称
  GO
  / 通过删除 PRIMARY KEY 约束来删除聚集索引。 */
  IF (OBJECT_ID(‘主键约束名’) IS NOT NULL)
  ALTER TABLE 表名
  DROP CONSTRAINT 主键约束名