SQL Server 2012学习笔记 (五) ------ SQL Server 索引

1、索引的概念

  假设数据库中现在有2万条记录，现在要执行这样一个查询：SELECT * FROM table where num=10000。如果没有索引，必须遍历整个表，直到num等于10000的这一行被找到为止；如果在num列上创建索引，SQL Server不需要任何扫描，直接在索引里面找10000，就可以得知这一行的位置，所以索引的建立可以加快数据库的查询速度。

  与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。

2、索引的优缺点

  1）索引的优点：

  创建惟一性索引，保证数据库表中每一行数据的惟一性。

  大大加快数据的检索速度，这也是创建索引的最主要原因。

  加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

  在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。

  通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

  2）索引的缺点：

  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

  索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间就会更大。

  当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，降低了数据的维护速度。

3、索引的分类

  在SQL Server 中提供的索引类型主要有以下几类：聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引、空间索引、筛选索引、和XML索引。

  按照存储结构的不同，可以将索引分为两类：聚集索引和非聚集索引，聚集索引和非聚集索引的区别是在物理数据的存储方式上。

  1.聚集索引： 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

  聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。

  2.非聚集索引： 具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

  非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。

4、索引的操作

  1.索引的创建

  2.查看索引信息

   3.索引的修改

  4.索引的删除

  5.设置索引的选项

5、索引的设计原则

  （1）索引并非越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。

  （2）避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

  （3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

  （4）在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。

  （5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引能够确保定义的列的数据完整性，提高查询速度。

  （6）在频繁进行排序或分组（即进行GROUP BY或ORDER BY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

6、索引的分析与维护

6.1 索引的分析

   1．使用SHOWPLAN语句

  显示查询语句的执行信息，包含查询过程中连接表时所采取的每个步骤以及选择哪个索引。语句如下：

SET SHOWPLAN_ALL { ON | OFF }
SET SHOWPLAN_TEXT { ON | OFF }

   2．使用STATISTICS IO语句

  STATISTICS IO语句表示使 SQL Server 显示有关由 Transact-SQL 语句生成的磁盘活动量的信息。语法如下：

SET STATISTICS IO { ON | OFF }

6.2 索引的维护

  1．使用DBCC SHOWCONTIG语句，显示指定表的数据和索引的碎片信息。当对表进行大量的修改或添加数据后，应该执行此语句来查看有无碎片。

  2．使用DBCC DBREINDEX语句对指定数据库中的表重新生成一个或多个索引。

  3．使用DBCC INDEXDEFRAG语句，指定表或视图的索引碎片整理。

7、全文索引

  全文索引是一种特殊类型的基于标记的功能性索引，它是由 Microsoft SQL Server 全文引擎生成和维护的。生成全文索引的过程不同于生成其他类型的索引。全文引擎并非基于特定行中存储的值来构造 B 树结构，而是基于要编制索引的文本中的各个标记来生成倒排、堆积且压缩的索引结构。

8、数据完整性

  数据完整性是SQL Server用于保证数据库中数据一致性的一种机制，防止非法数据存入数据库。具体地数据完整性主要体现在以下几点。

  （1）数据类型准确无误。

  （2）数据取值符合规定的范围。

  （3）多个数据表之间的数据不存在冲突。

  下面介绍SQL Server提供的4种数据完整性机制：

  1.域完整性：域是指数据表中的列（字段），域完整性就是指列的完整性。它要求数据表中指定列的数据具有正确的数据类型、格式和有效的数据范围。

  2.实体完整性：是指所有的记录都应该有一个惟一的标识，以确保数据表中数据的惟一性。

  3.引用完整性：引用完整性又称参照完整性，是用来维护相关数据表中数据一致的手段。

  4.用户定义完整性：用户定义完整性使您可以定义不属于其他任何完整性类别的特定业务规则。