sql聚簇索引和非聚簇索引

The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A book index may have a list of topics discussed in a book in alphabetical order. Therefore, if you want to search for any specific topic, you simply go to the index, find the page number of the topic, and go to that specific page number. Database indexes are similar and come handy. Particularly, if you have a huge number of records in your database, indexes can speed up the query execution process. There are two major types of indexes in SQL Server: clustered indexes and non-clustered indexes.

SQL CREATE INDEX语句用于在SQL Server中创建群集索引和非群集索引。 数据库中的索引与书中的索引非常相似。 书索引可以具有按字母顺序在书中讨论的主题的列表。 因此，如果要搜索任何特定主题，只需转到索引，找到该主题的页码，然后转到该特定页码。 数据库索引很相似并且很方便。 特别是，如果数据库中有大量记录，则索引可以加快查询执行过程。 SQL Server中有两种主要的索引类型：聚集索引和非聚集索引。

In this article, you will see what the clustered and non-clustered indexes are, what are the differences between the two types and how they can be created via SQL CREATE INDEX statement. So let’s begin without any further ado.

在本文中，您将看到什么是聚集索引和非聚集索引，两种类型之间的区别以及如何通过SQL CREATE INDEX语句创建它们。 因此，让我们开始吧。

创建虚拟数据 (Creating dummy data)

The following script creates a dummy database named BookStore with one table i.e. Books. The Books table has four columns: id, name, category, and price:

以下脚本使用一个表（即Books）创建一个名为BookStore的虚拟数据库。 Books表包含四列： id ， name ， category和price ：

CREATE Database BookStore;GOUSE BookStore;CREATE TABLE Books(id INT PRIMARY KEY NOT NULL,name VARCHAR(50) NOT NULL,category VARCHAR(50) NOT NULL,price INT NOT NULL)

Let’s now add some dummy records in the Books table:

现在让我们在Books表中添加一些虚拟记录：

USE BookStore INSERT INTO Books    VALUES(1, 'Book1', 'Cat1', 1800),(2, 'Book2', 'Cat2', 1500),(3, 'Book3', 'Cat3', 2000),(4, 'Book4', 'Cat4', 1300),(5, 'Book5', 'Cat5', 1500),(6, 'Book6', 'Cat6', 5000),(7, 'Book7', 'Cat7', 8000),(8, 'Book8', 'Cat8', 5000),(9, 'Book9', 'Cat9', 5400),(10, 'Book10', 'Cat10', 3200)

The above script adds 10 dummy records in the Books table.

上面的脚本在Books表中添加了10条虚拟记录。

聚集索引 (Clustered indexes)

Clustered indexes define the way records are physically sorted in a database table. A clustered index is very similar to the table of contents of a book. In the table of contents, you can see how the book has been physically sorted. Either the topics are sorted chapter wise according to their relevance or they can be sorted alphabetically.

聚集索引定义了在数据库表中对记录进行物理排序的方式。 聚集索引与书的目录非常相似。 在目录中，您可以查看书籍的物理排序方式。 可以根据主题的相关性按章对主题进行排序，也可以按字母顺序对主题进行排序。

There can be only one way in which records can be physically sorted on a disk. For example, records can either be sorted by their ids or they can be sorted by the alphabetical order of some string column or any other criteria. However, you cannot have records physically sorted by ids as well as names. Hence, there can be only one clustered index for a database table. A database table has one clustered index by default on the primary key column. To see the default index, you can use the sp_helpindex stored procedure as shown below:

只能对磁盘上的记录进行物理排序的一种方法。 例如，记录可以按其ID排序，也可以按某些字符串列的字母顺序或任何其他条件排序。 但是，您不能具有按ID和名称进行物理排序的记录。 因此，一个数据库表只能有一个聚集索引。 数据库表默认在主键列上具有一个聚集索引。 若要查看默认索引，可以使用sp_helpindex存储过程，如下所示：

USE BookStore		EXECUTE sp_helpindex Books

Here is the output:

这是输出：

You can see the clustered index name and the column on which the clustered index has been created by default.

您可以看到聚集索引名称和默认情况下在其上创建聚集索引的列。

To see the records arranged by default clustered index, simply execute the SELECT statement to select all the records from the books table:

要查看默认聚集索引排列的记录，只需执行SELECT语句以从books表中选择所有记录：

SELECT * FROM Books

You can see that the records have been sorted by default clustered index for the primary key column i.e. id.

您可以看到，记录已按默认聚集索引针对主键列即ID进行排序。

To create a clustered index in SQL Server, you can modify SQL CREATE INDEX. Here is the syntax:

若要在SQL Server中创建聚集索引，可以修改SQL CREATE INDEX。 语法如下：

CREATE CLUSTERED INDEX 
    
     ON 
     
      (
      
        ASC/DESC)
      
     
    

Let’s now create a custom clustered index that physically sorts the record in the Books table in the ascending order of the price. Since there can be only one clustered index, we first need to remove the default clustered index created via the primary key constraint. To remove the default clustered index, you simply have to remove the primary key constraint from the table that contains the default clustered index. Look at the following script:

现在，让我们创建一个自定义的聚集索引，该索引将按价格的升序对Books表中的记录进行物理排序。 由于只能有一个聚集索引，因此我们首先需要删除通过主键约束创建的默认聚集索引。 要删除默认的聚集索引，只需从包含默认聚集索引的表中删除主键约束。 看下面的脚本：

USE BookStoreALTER TABLE BooksDROP CONSTRAINT PK__Books__3213E83F7DFA309BGO

Now we can create a new clustered index via SQL CREATE INDEX statement as shown below:

现在，我们可以通过SQL CREATE INDEX语句创建一个新的聚集索引，如下所示：

use BookStoreCREATE CLUSTERED INDEX IX_tblBook_PriceON Books(price ASC)

In the script above, we create a clustered index named IX_tblBook_Price. This clustered index physically sorts all the records in the Books table by the ascending order of the price.

在上面的脚本中，我们创建一个名为IX_tblBook_Price的聚集索引。 该聚集索引以价格的升序对Book表中的所有记录进行物理排序。

Let’s now select all the records from the Books table to see if they have been sorted in the ascending order of their prices:

现在，从“书籍”表中选择所有记录，以查看它们是否已经按照价格的升序排序：

SELECT * FROM Books

Here is the output:

这是输出：

From the output, you can see that records have actually been sorted by the increasing amount of price.

从输出中，您可以看到记录实际上已经按照价格的增加进行了排序。

非聚集索引 (Non-clustered indexes)

A non-clustered index is an index that doesn’t physically sort the database records. Rather, a non-clustered index is stored at a separate location from the actual database table. It is the non-clustered index which is actually similar to an index of a book. A book index is stored at a separate location, while the actual content of the book is separately located.

非聚集索引是不对数据库记录进行物理排序的索引。 而是，非聚集索引存储在与实际数据库表不同的位置。 它实际上是与书籍索引相似的非聚集索引。 书籍索引存储在单独的位置，而书籍的实际内容则单独放置。

The SQL CREATE INDEX query can be modified as follows to create a non-clustered index:

可以如下修改SQL CREATE INDEX查询，以创建非聚集索引：

CREATE NONCLUSTERED INDEX 
    
     ON 
     
      (
      
        ASC/DESC)
      
     
    

Let’s create a simple non-clustered index that sorts the records in the Books table by name. You can modify the SQL CREATE INDEX query as follows:

让我们创建一个简单的非聚集索引，该索引按名称对Books表中的记录进行排序。 您可以按如下所示修改SQL CREATE INDEX查询：

use BookStoreCREATE NONCLUSTERED INDEX IX_tblBook_NameON Books(name ASC)

As I said earlier, the non-clustered index is stored at a location which is different from the location of the actual table, the non-clustered index that we created will look like this:

如前所述，非聚集索引存储在与实际表的位置不同的位置，我们创建的非聚集索引如下所示：

Name	Record Address
Book1	Record address
Book2	Record address
Book3	Record address
Book4	Record address
Book5	Record address
Book6	Record address
Book7	Record address
Book8	Record address
Book9	Record address
Book10	Record address

名称	记录地址
书1	记录地址
书2	记录地址
书3	记录地址
书4	记录地址
书5	记录地址
书6	记录地址
书7	记录地址
书8	记录地址
书9	记录地址
书10	记录地址

Now if a user searches for the name, id, and price of a specific book, the database will first search the book’s name in the non-clustered index. Once the book name is searched, the id and price of the book are searched from the actual table using the record address of the record in the actual table.

现在，如果用户搜索特定书籍的名称，编号和价格，则数据库将首先在非聚集索引中搜索书籍的名称。 一旦搜索了书名，就使用实际表中记录的记录地址从实际表中搜索书的ID和价格。

结论 (Conclusion)

The article covers how to use SQL CREATE INDEX statement to create a clustered as well as a non-clustered index. The article also shows the main differences between the two types of clustered indexes with the help of examples.

本文介绍了如何使用SQL CREATE INDEX语句创建聚簇索引和非聚簇索引。 本文还通过示例展示了两种类型的聚集索引之间的主要区别。

翻译自:

sql聚簇索引和非聚簇索引