索引用于加速 SQL Server 中的查询过程,从而提高性能。它们类似于教科书索引。在教科书中,如果您需要转到特定章节,请转到索引,找到该章节的页码并直接转到该页面。如果没有索引,查找所需章节的过程会非常缓慢。
这同样适用于数据库中的索引。如果没有索引,DBMS 必须遍历表中的所有记录才能检索所需的结果。这个过程被称为表扫描并且非常缓慢。另一方面,如果您创建索引,数据库会先访问该索引,然后直接检索相应的表记录。
SQL Server 中有两种类型的索引:
- 聚集索引
- 非聚集索引
聚集索引(Clustered Index)
聚集索引定义了数据在表中的物理存储顺序。表数据只能以一种方式排序,因此每个表只能有一个聚集索引。在 SQL Server 中,主键约束会自动在该特定列上创建聚集索引。
让我们来看看。首先,通过执行以下脚本在“schooldb”中创建一个“student”表:
CREATE DATABASE schooldb
CREATE TABLE student
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
DOB datetime NOT NULL,
total_score INT NOT NULL,
city VARCHAR(50) NOT NULL
)
请注意,在“student”表中,我们在“id”列上设置了主键约束。这会自动在“id”列上创建一个聚集索引。要查看特定表上的所有索引,请执行“sp_helpindex”存储过程。此存储过程接受表名作为参数并检索表的所有索引。以下查询检索在 student 表上创建的索引。
USE schooldb
EXECUTE sp_helpindex student
上面的查询将返回这个结果:
索引名称 | 索引描述 | 索引键 |
---|---|---|
PK__student__3213E83F7F60ED59 | clustered, unique, primary key located on PRIMARY | id |
在输出中,您可以看到唯一的一个索引。这是由于“id”列上的主键约束而自动创建的索引。
查看表索引的另一种方法是转到“对象资源管理器-> 数据库-> <数据库名>-> 表-> <表名> -> 索引。请查看以下屏幕截图以供参考。
这个聚集索引以“id”的升序存储学生表中的记录。因此,如果插入记录具有5的ID,该记录将会被插入第五行而不是第姨行。同样,如果第四条记录的 id 为 3,则它会被插入到第三行而不是第四行。这是因为聚集索引必须根据索引列(即 id)维护存储记录的物理顺序。要查看此排序的实际效果,请执行以下脚本:
USE schooldb
INSERT INTO student
VALUES
(6, 'Kate', 'Female', '03-JAN-1985', 500, 'Liverpool'),
(2, 'Jon', 'Male', '02-FEB-1974', 545, 'Manchester'),
(9, 'Wise', 'Male', '11-NOV-1987', 499, 'Manchester'),
(3, 'Sara', 'Female', '07-MAR-1988', 600, 'Leeds'),
(1, 'Jolly', 'Female', '12-JUN-1989', 500, 'London'),
(4, 'Laura', 'Female', '22-DEC-1981', 400, 'Liverpool'),
(7, 'Joseph', 'Male', '09-APR-1982', 643, 'London'),
(5, 'Alan', 'Male', '29-JUL-1993', 500, 'London'),
(8, 'Mice', 'Male', '16-AUG-1974', 543, 'Liverpool'),
(10, 'Elis', 'Female', '28-OCT-1990', 400, 'Leeds');
上述脚本在学生表中插入十条记录。请注意,这里的记录以“id”列中值的随机顺序插入。但是由于 id 列上的默认聚集索引,记录在物理上按照“id”列中值的升序存储。执行以下 SELECT 语句以从 student 表中检索记录。
USE schooldb
SELECT * FROM student
记录将按以下顺序检索:
id | name | gender | DOB | total_score | city |
---|---|---|---|---|---|
1 | Jolly | Female | 1989-06-12 00:00:00.000 | 500 | London |
2 | Jon | Male | 1974-02-02 00:00:00.000 | 545 | Manchester |
3 | Sara | Female | 1988-03-07 00:00:00.000 | 600 | Leeds |
4 | Laura | Female | 1981-12-22 00:00:00.000 | 400 | Liverpool |
5 | Alan | Male | 1993-07-29 00:00:00.000 | 500 | London |
6 | Kate | Female | 1985-01-03 00:00:00.000 | 500 | Liverpool |
7 | Joseph | Male | 1982-04-09 00:00:00.000 | 643 | London |
8 | Mice | Male | 1974-08-16 00:00:00.000 | 543 | Liverpool |
9 | Wise | Male | 1987-11-11 00:00:00.000 | 499 | Manchester |
10 | Elis | Female | 1990-10-28 00:00:00.000 | 400 | Leeds |
创建自定义聚集索引
您可以创建自己的自定义索引以及默认的聚集索引。要在表上创建新的聚集索引,您首先必须删除以前的索引。
要删除索引,请转到“对象资源管理器-> 数据库-> 数据库名称-> 表-> 表名称-> 索引”。右键单击要删除的索引并选择删除。请参阅下面的屏幕截图。
现在,要创建一个新的聚集索引,请执行以下脚本:
use schooldb
CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score
ON student(gender ASC, total_score DESC)
创建聚集索引的过程与普通索引类似,但有一个例外。对于聚集索引,您必须在“INDEX”之前使用关键字“CLUSTERED”。
上面的脚本在学生表上创建了一个名为“IX_tblStudent_Gender_Score”的聚集索引。该索引是在“gender”和“total_score”列上创建的。在多个列上创建的索引称为“复合索引”。
上述索引首先按性别升序对所有记录进行排序。如果两个或多个记录的性别相同,则记录按其“total_score”列中值的降序排序。您也可以在单个列上创建聚集索引。现在,如果您从 student 表中选择所有记录,它们将按以下顺序检索:
id | name | gender | DOB | total_score | city |
---|---|---|---|---|---|
3 | Sara | Female | 1988-03-07 00:00:00.000 | 600 | Leeds |
1 | Jolly | Female | 1989-06-12 00:00:00.000 | 500 | London |
6 | Kate | Female | 1985-01-03 00:00:00.000 | 500 | Liverpool |
4 | Laura | Female | 1981-12-22 00:00:00.000 | 400 | Liverpool |
10 | Elis | Female | 1990-10-28 00:00:00.000 | 400 | Leeds |
7 | Joseph | Male | 1982-04-09 00:00:00.000 | 643 | London |
2 | Jon | Male | 1974-02-02 00:00:00.000 | 545 | Manchester |
8 | Mice | Male | 1974-08-16 00:00:00.000 | 543 | Liverpool |
5 | Alan | Male | 1993-07-29 00:00:00.000 | 500 | London |
9 | Wise | Male | 1987-11-11 00:00:00.000 | 499 | Manchester |
非聚集索引(Non-Clustered Index)
非聚集索引不对表内的物理数据进行排序。实际上,非聚集索引存储在一个位置,而表数据存储在另一个位置。这类似于一本教科书,其中书籍内容位于一个地方而索引位于另一个地方。这允许每个表有多个非聚集索引。
重要的是要在这里提到,在表内部,数据将按聚集索引进行排序。但是,非聚集索引里面的数据是按照指定的顺序存储的。索引包含创建索引的列值以及该列值所属的记录的地址。
当针对创建索引的列发出查询时,数据库将首先转到索引并在表中查找相应行的地址。然后它将转到该行地址并获取其他列值。正是由于这个额外的步骤,非聚集索引比聚集索引慢。
创建非聚集索引(Non-Clustered Index)
创建非聚集索引的语法与聚集索引类似。但是,在非聚集索引的情况下,使用关键字“NONCLUSTERED”而不是“CLUSTERED”。看看下面的脚本。
use schooldb
CREATE NONCLUSTERED INDEX IX_tblStudent_Name
ON student(name ASC)
上面的脚本在学生表的“name”列上创建了一个非聚集索引。索引按名称升序排序。正如我们前面所说,表数据和索引将存储在不同的地方。如果有聚集索引,表记录将按聚集索引排序。索引将根据其定义进行排序,并将与表分开存储。
学生表数据:
id | name | gender | DOB | total_score | city |
---|---|---|---|---|---|
1 | Jolly | Female | 1989-06-12 00:00:00.000 | 500 | London |
2 | Jon | Male | 1974-02-02 00:00:00.000 | 545 | Manchester |
3 | Sara | Female | 1988-03-07 00:00:00.000 | 600 | Leeds |
4 | Laura | Female | 1981-12-22 00:00:00.000 | 400 | Liverpool |
5 | Alan | Male | 1993-07-29 00:00:00.000 | 500 | London |
6 | Kate | Female | 1985-01-03 00:00:00.000 | 500 | Liverpool |
7 | Joseph | Male | 1982-04-09 00:00:00.000 | 643 | London |
8 | Mi | Male | 1974-08-16 00:00:00.000 | 543 | Liverpool |
9 | Wise | Male | 1987-11-11 00:00:00.000 | 499 | Manchester |
10 | Elis | Female | 1990-10-28 00:00:00.000 | 400 | Leeds |
IX_tblStudent_Name 索引数据
Name | 行地址 |
---|---|
Alan | Row Address |
Elis | Row Address |
Jolly | Row Address |
Jon | Row Address |
Joseph | Row Address |
Kate | Row Address |
Laura | Row Address |
Mice | Row Address |
Sara | Row Address |
Wise | Row Address |
请注意,这里的索引中的每一行都有一列存储名称所属的行的地址。因此,如果发出查询以检索名为“Jon”的学生的性别和 DOB,数据库将首先在索引中搜索姓名“Jon”。然后它将读取“Jon”的行地址,并直接转到“student”表中的该行以获取 Jon 的性别和 DOB。
结论
从讨论中我们发现**聚集索引(Clustered Index)和非聚集索引(Non-Clustered Index)**之间的以下差异。
- 每个表只能有一个聚集索引。但是,您可以在单个表上创建多个非聚集索引。
- 聚集索引只对表进行排序。因此,它们不会消耗额外的存储空间。非聚集索引存储在与实际表不同的地方,要求更多的存储空间。
- 聚集索引比非聚集索引更快,因为它们不涉及任何额外的查找步骤。