SQL Server索引概要(3)-聚集索引和非聚集索引的区别

索引用于加速 SQL Server 中的查询过程，从而提高性能。它们类似于教科书索引。在教科书中，如果您需要转到特定章节，请转到索引，找到该章节的页码并直接转到该页面。如果没有索引，查找所需章节的过程会非常缓慢。

这同样适用于数据库中的索引。如果没有索引，DBMS 必须遍历表中的所有记录才能检索所需的结果。这个过程被称为表扫描并且非常缓慢。另一方面，如果您创建索引，数据库会先访问该索引，然后直接检索相应的表记录。

SQL Server 中有两种类型的索引：

1. 聚集索引
2. 非聚集索引

聚集索引(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)**之间的以下差异。

1. 每个表只能有一个聚集索引。但是，您可以在单个表上创建多个非聚集索引。
2. 聚集索引只对表进行排序。因此，它们不会消耗额外的存储空间。非聚集索引存储在与实际表不同的地方，要求更多的存储空间。
3. 聚集索引比非聚集索引更快，因为它们不涉及任何额外的查找步骤。