SQL Server基础概念--索引

rel="File-List" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"> rel="themeData" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"> rel="colorSchemeMapping" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml">

数据库的索引类似于书的目录，在数据库中，索引就是表中数据和相应物理存储位置的列表，其可以大大提高系统的性能，主要表现在以下几个方面：

（1）    通过创建唯一性索引，可以保证每一行数据的唯一性

（2）    可以大大加快数据的检索速度

（3）    可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

（4）    在使用ORDER BY和GROUP BY子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

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

虽然索引能加速查询速度，但是为数据库中的每张表都设置大量的索引并不是一个明智的做法，因为增加索引也有其不利的一面：

（1）每个索引都将占用一定的存储空间，如果建立聚簇索引（会改变数据物理存储位置的一种索引），那么占用需要的空间就会更大：

（2）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的更新速度。

 

创建索引的语法格式如下：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON { table | view} ( column [ ASC | DESC ] [ ,…n ] )

[ WITH [ PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY

| DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB ] ]

参数说明：

（1）    UNIQUE表示创建唯一性的索引，在索引列中不能有相同的两个列值存在

（2）    CLUSTERED | NONCLUSTERED是聚簇索引和非聚簇索引

（3）    ON表示可以在表或视图上创建索引，这里指定表或视图的名称和相应的列名称

（4）    PAD_INDEX为非叶级索引页的填充度

（5）    IGNORE_DUP_KEY指定出现冗余数据的系统行为

（6）    DROP_EXISTING删除指定的索引，然后重建该索引

（7）    STATISTICS_NORECOMPUTE指定不自动计算过期的索引统计信息

（8）    SORT_IN_TEMPDB指定创建索引时产生的中间结果，在tempdb数据库中进行排序

 

何时需要创建索引，要考虑如下经验性指导原则：

（1）    在经常需要搜索的列上创建索引

（2）    在主键上创建索引

（3）    在经常用于连接的列上创建索引，也就是说在外键上创建索引

（4）    在经常需要根据范围进行搜索的列上创建索引

（5）    在经常需要排序的列上创建索引

（6）    在经常用在WHERE子句中的列上创建索引

（7）    此外，SQL Server为某些类型的约束（如PRIMARY KEY和UNIQUE约束）自动创建索引。

对于某些列则不该建立索引，一般性指导原则如下：

（1）    对于那些在查询中很少使用和参考的列不应该创建索引。

（2）    对于那些只有很少值的列不应该建立索引，如“性别”。

（3）    属性值分布严重不均匀的属性。要保证搜索某个范围值时，不会出现过多内容的情况。

（4）    过长的属性。如超过30个字节。因为在过长的属性上建立索引，索引所占的存储空间较大，而索引的级数也随之增加，有诸多不利之处。如必须建立索引，必须采取索引属性压缩的措施。

（5）    经常更新的属性或表。

根据索引的顺序与数据表的物理顺序是否相同，可以把索引分为聚簇索引和非聚簇索引。聚簇索引的顺序和数据表的物理顺序相同；非聚簇索引的顺序和数据表的物理顺序不同。

聚簇索引中，页的顺序总是升序。聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。在表中经常搜索的列或按照顺序访问的列，应该创建聚簇索引。当创建聚簇索引时，应注意以下因素：

（1）    每一个表，只能有一个聚簇索引

（2）    表中行的物理顺序和索引中行的物理顺序是相同的。在创建任何非聚簇索引之前创建聚簇索引，因为聚簇索引改变了表中行的物理顺序。数据行按照一定的顺序排列，并且自动维护这个顺序。

（3）    关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护。这些唯一标识符是系统自己使用的，用户不能访问。

（4）    聚簇索引的平均大小大约是数据表的5%，但是实际的聚簇索引的大小常常根据索引列的大小不同而变化。

非聚簇索引表示行的逻辑顺序。SQL server 2000在搜索数据值时，先对非聚簇索引进行行搜索，找到数据值在表中的位置，然后从该位置直接检索数据，这使非聚簇索引成为精确匹配查询的最佳方法。当需要以多种方式检索数据时，非聚簇索引就非常有用了。创建非聚簇索引时，要考虑以下情况：

（1）    在默认情况下，创建的索引是非聚簇索引

（2）    在每一个表上面，可以创建不多于249个非聚簇索引

（3）    索引页的叶级只包含索引的关键字，不包含实际的数据

 

在考虑是否为一列创建索引时，应考虑被索引的列，是否以及如何用于查询中。索引对下列查询很有帮助：

（1）    搜索符合特定搜索关键字的行（精确匹配查询）。精确匹配比较是指查询使用WHERE语句指定具有给定值的列条目。

（2）    搜索其搜索关键字值为范围值的行（范围查询）。范围查询是指查询指定其值介于两个值之间的任何条目。

（3）    在表T1中搜索根据连接谓词与表T2中的某个行匹配的行（索引嵌套循环联接）。

（4）    在不进行显示排序操作的情况下，按一种有序的顺序对行进行扫描，以允许基于顺序的操作，如合并联接和流聚合。

（5）    以优于表扫描的性能对表中所有的行进行扫描，性能提高是由于减少了要扫描的列集和数据总量（该查询有覆盖索引可供使用）

（6）    搜索插入和更新操作中重复的新搜索关键字值，以实施PRIMARY KEY和UNIQUE约束。

（7）    搜索已定义了FOREIGN KEY约束的两个表之间的匹配的行。

（8）    使用LIKE比较进行查询时，如果模式以特定字符串如“abc%”开头，使用索引则会提高效率；如果模式以通配符如“%xyz”开头，则索引不起作用。

在很多查询中，索引可以带来多方面的好处。例如，索引除了提供对单一行的快速访问外，还使得可以进行范围查询。SQL Server可以在同一个查询中为一个表使用多个索引，并可以合并多个索引（使用联接算法），以便搜索关键字共同覆盖一个查询。另外，SQL Server会自动确定利用那些索引进行查询，并且能够在表被改动时确保该表的所有索引都得到维护。另外，索引设计的时候还要考虑的其他准则，主要包括以下几点：

（1）    一个表，如果建有大量索引会影响INSERT,UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有索引都必须进行适当的调整。另一方面，对于不需要修改数据的查询（SELECT语句），大量索引有助于提高性能，因为SQL Server有更多的索引可供选择，以便确定以最快速度访问数据的最佳方法。

（2）    覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列，都包含在同一个索引中。创建覆盖一个查询的索引可以提高性能，因为该查询的所有数据都包含在索引自身当中；检索数据时只需引用表的索引页，不必引用数据页，因而减少了I/O总量。尽管给索引添加列以覆盖查询可以提高性能，但在索引中额外维护更多的列，会产生更新和存储成本。

（3）    对小型表进行索引可能不会产生优化效果，因为SQL Server在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。

（4）    应使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。

（5）    可以在视图上指定索引

（6）    可以在计算列上指定索引。