SQL Server多列索引

SQL Server多列索引

一、在SQL Server中创建索引的语法如下(当然也可以在SQL Server 工具中设置)

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]
]
[ ON filegroup ]

		

说明

CREATE INDEX：命令创建索引各参数说明如下：

UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。

CLUSTERED：用于指定创建的索引为聚集索引。

NONCLUSTERED：用于指定创建的索引为非聚集索引。

index_name：用于指定所创建的索引的名称。

table：用于指定创建索引的表的名称。

view：用于指定创建索引的视图的名称。

ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。

Column：用于指定被索引的列。

PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。

FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。

IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。

DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。

STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。

SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。

ON filegroup：用于指定存放索引的文件组。

二、单列索引和多列索引的比较

在SQL Server中，索引分为两种，一种是单列索引，一种是多列索引。多列索引创建方法类似单列索引，只是拥有多个列

如图

三、SQL Server多列索引实例

1、假设有这样一个people表

CREATE TABLE People( 
   PeopleId INT NOT NULL, 
   FirstName NVARCHAR(50) NOT NULL, 
   LastName NVARCHAR(50) NOT NULL, 
   Age INT NOT NULL, 
   PRIMARY KEY (PeopleId) 
 );

		

2、查找姓名为MikeSullivan、年龄17岁用户的peopleid

SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;

3、分析

(1)、我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引，数据库将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，数据库就返回最终的搜索结果。

(2)、由于建立了firstname列的索引，与执行表的完全扫描相比，SQL Server的效率提高了很多，但我们要求SQL Server扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。

(3)、为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，数据库只需一次检索就能够找出正确的结果！

四、多列索引特性

例如我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。

1、当搜索条件是以下各种列的组合时，数据库将使用fname_lname_age索引

firstname，lastname，age
firstname，lastname
firstname

SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; 
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; 
SELECT peopleid FROM people WHERE firstname='Mike'; 

		

2、下面这些查询不能够使用这个fname_lname_age索引

SELECT peopleid FROM people Where lastname='Sullivan'; 
SELECT peopleid FROM people Where age='17'; 
SELECT peopleid FROM people Where lastname='Sullivan' AND age='17';