SQL Server索引的介绍及应用

向右看齐 2022-06-14 04:28 209阅读 0赞

**1、什么是索引**

拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4K 。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），我们可以选择按拼音或笔画查找方式，快速查找到需要的字（词）。

同理，SQL Server允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度。

**2、索引类型**

1）唯一索引（UNIQUE）：唯一索引不允许两行具有相同的索引值；

2）主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空；

3）聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表最多只能有一个；

4）非聚集索引(NonClustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个。

**唯一索引（UNIQUE）：**

唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。例如，如果在stuInfo表中的学员员身份证号(stuID) 列上创建了唯一索引，则所有学员的身份证号不能重复。

提示：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束或唯一约束。

**主键索引：**

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

**聚集索引（clustered index）：**

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

**非聚集索引(NonClustered)：**

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

**3、何时使用聚集索引或非聚集索引 **  
下面的表总结了何时使用聚集索引或非聚集索引（很重要）：

<table style="color:rgb(75,75,75)"> 
 <tbody> 
 <tr> 
 <td> 动作描述 </td> 
 <td> 使用聚集索引 </td> 
 <td> 使用非聚集索引 </td> 
 </tr> 
 <tr> 
 <td> 列经常被分组排序 </td> 
 <td> 应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 返回某范围内的数据 </td> 
 <td> 应 </td> 
 <td> 不应 </td> 
 </tr> 
 <tr> 
 <td> 一个或极少不同值 </td> 
 <td> 不应 </td> 
 <td> 不应 </td> 
 </tr> 
 <tr> 
 <td> 小数目的不同值 </td> 
 <td> 应 </td> 
 <td> 不应 </td> 
 </tr> 
 <tr> 
 <td> 大数目的不同值 </td> 
 <td> 不应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 频繁更新的列 </td> 
 <td> 不应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 外键列 </td> 
 <td> 应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 主键列 </td> 
 <td> 应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 频繁修改索引列 </td> 
 <td> 不应 </td> 
 <td> 应 </td> 
 </tr> 
 </tbody> 
</table>

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

**4、如何创建和删除索引**

创建索引有多种方法，这些方法包括直接创建索引的方法和间接创建索引的方法。

第一，   直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导。

第二，   间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。

虽然，这两种方法都可以创建索引，但是，它们创建索引的具体内容是有区别的。

使用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。在使用这种方式创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。

通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。

当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

**以直接方法为例：**

**1） 使用T-SQL语句创建索引的语法：**

**a、创建单一索引：**

\-- 判断索引是否存在,如果不存在,则创建。注意:表的索引信息是存在系统表 sysindexes 中的

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = ' index\_name ')

CREATE \[UNIQUE\] \[CLUSTERED|NONCLUSTERED\]

INDEX   index\_name

ON table\_name (column\_name…)

\[WITH FILLFACTOR=x\]

注： UNIQUE表示唯一索引，可选；

CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选；

FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比，设置FILLFACTOR值时，应考虑如下因素：

l   填充因子的值是从 0 到 100 之间的百分比数值，用来指定在创建索引后对数据页的填充比例。

l   值为 100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。

l   值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。

l   使用sp\_configure系统存储过程可以在服务器级别设置默认的填充因子。

l   填充因子只在创建索引时执行；索引创建后，当表中进行数据的添加、删除或更新时，不会保持填充因子。

**b、创建符合索引:**

创建索引

create index idx1 on table1(col1,col2,col3)

**2） 使用T-SQL语句删除索引的语法：**

**以单一索引为例：**

\-- 判断索引是否存在,如果存在,则删除。注意:表的索引信息是存在系统表 sysindexes 中的

IF EXISTS (SELECT name FROM sysindexes WHERE name = ' index\_name ')

DROP INDEX  table\_name. index\_name;

**5、索引更名**

其语法格式如下：

sp\_rename \[ @objname = \] 'object\_name' ,

\[ @newname = \] 'new\_name'

\[ , \[ @objtype = \] 'object\_type' \]

例：将STUDENT数据库中“课程”表的pk\_kc索引名称更改为pk\_kecheng。其程序清单如下：

USE  STUDENT

EXEC  sp\_rename  'pk\_kc‘ , 'pk\_kecheng'

**6、索引的特征**

索引有两个特征，即唯一性索引和复合索引。

唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQL Server自动创建一个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。

复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。

**7、索引的优缺点**

**1）优点：**

加快访问速度

加强行的唯一性

**2）缺点：**

带索引的表在数据库中需要更多的存储空间

操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

**8、索引的维护**

为了维护系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。

使用DBCC SHOWCONTIG语句（SQL语句：“DBCC SHOWCONTIG（表名）”），可以显示表的数据和索引的碎块信息。当执行DBCC SHOWCONTIG语句时，SQL Server浏览叶级上的整个索引页，来确定表或者指定的索引是否严重碎块。DBCC SHOWCONTIG语句还能确定数据页和索引页是否已经满了。当对表进行大量的修改或者增加大量的数据之后，或者表的查询非常慢时，应该在这些表上执行DBCC SHOWCONTIG语句。当执行DBCC SHOWCONTIG语句时，应该考虑这些因素：当执行DBCC SHOWCONTIG语句时，SQL Server要求指定表的ID号或者索引的ID号，表的ID号或者索引的ID号可以从系统表sysindexes中得到；应该确定多长时间使用一次DBCC SHOWCONTIG语句，这个时间长度要根据表的活动情况来定，每天、每周或者每月都可以。

使用DBCC DBREINDEX语句重建表的一个或者多个索引。当希望重建索引和当表上有主键约束或者唯一性键约束时，执行DBCC DBREINDEX语句。除此之外，执行DBCC DBREINDEX语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。当使用执行DBCC DBREINDEX语句时，应该考虑这些因素：根据指定的填充度，系统重新填充每一个叶级页；使用DBCC DBREINDEX语句重建主键约束或者唯一性键约束的索引；使用SORTED\_DATA\_REORG选项可以更快地创建聚簇索引，如果没有排列关键值，那么不能使用DBCC DBREINDEX语句；DBCC DBREINDEX语句不支持系统表。另外，还可以使用数据库维护规划向导自动地进行重建索引的进程。

统计信息是存储在SQL Server中的列数据的样本。这些数据一般地用于索引列，但是还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息，并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。当表中数据发生变化时，SQL Server周期性地自动修改统计信息。索引统计被自动地修改，索引中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如，如果表中有10000行数据，1000行数据修改了，那么统计信息可能需要修改。然而，如果只有50行记录修改了，那么仍然保持当前的统计信息。除了系统自动修改之外，用户还可以通过执行UPDATE STATISTICS语句或者sp\_updatestats系统存储过程来手工修改统计信息。使用UPDATE STATISTICS语句既可以修改表中的全部索引，也可以修改指定的索引。

使用SHOWPLAN和STATISTICS IO语句可以分析索引和查询性能。使用这些语句可以更好地调整查询和索引。SHOWPLAN语句显示在连接表中使用的查询优化器的每一步以及表明使用哪一个索引访问数据。使用SHOWPLAN语句可以查看指定查询的查询规划。当使用SHOWPLAN语句时，应该考虑这些因素。SET SHOWPLAN\_ALL语句返回的输出结果比SET SHOWPLAN\_TEXT语句返回的输出结果详细。然而，应用程序必须能够处理SET SHOWPLAN\_ALL语句返回的输出结果。SHOWPLAN语句生成的信息只能针对一个会话。如果重新连接SQL Server，那么必须重新执行SHOWPLAN语句。STATISTICS IO语句表明输入输出的数量，这些输入输出用来返回结果集和显示指定查询的逻辑的和物理的I/O的信息。可以使用这些信息来确定是否应该重写查询语句或者重新设计索引。使用STATISTICS IO语句可以查看用来处理指定查询的I/O信息。

就象SHOWPLAN语句一样，优化器隐藏也用来调整查询性能。优化器隐藏可以对查询性能提供较小的改进，并且如果索引策略发生了改变，那么这种优化器隐藏就毫无用处了。因此，限制使用优化器隐藏，这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏时，考虑这些规则：指定索引名称、当index\_id为0时为使用表扫描、当index\_id为1时为使用聚簇索引；优化器隐藏覆盖查询优化器，如果数据或者环境发生了变化，那么必须修改优化器隐藏。

**9、其他书上没有的索引使用经验总结**

注意：这是别人的经验，是否适用还得看具体情况而定

1）、用聚合索引比用不是聚合索引的主键速度快；

2）、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下；

3）、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个；

4）、日期列不会因为有分秒的输入而减慢查询速度。

本文转载自博客园文章：[SQL Server索引的介绍及应用][SQL Server]

[SQL Server]: http://www.cnblogs.com/luxinhong2011/archive/2011/08/05/2128656.html