Sql 简明教程

SQL - Clustered Index

数据库中的 index 是一个数据结构，有助于提高从表和视图中检索特定数据的速度。

An index in a database is a data structure that helps to improve the speed of retrieving specific data from tables and views.

表中的数据以名为“堆”的无序数据结构形式存储，其中行以没有任何特定顺序的方式放置。因此，从表中检索数据时，查询优化器必须扫描整个表以查找所请求的行。此过程可能十分耗时，尤其是在我们处理大型表时。为了加速数据检索，SQL 提供了一个称为索引的数据对象，该对象以特定方式存储和组织表数据，从而允许更快速地访问数据。

Data in a table is stored in the form of an unordered data structure called a "Heap", where rows are placed without any specific order. Thus, when retrieving data from a table, the query optimizer must scan the entire table to locate the requested rows. This process can be time-consuming, especially when we are dealing with large tables. To speed up the data retrieval, SQL provides a data object called index that stores and organizes table data in a specific way, allowing faster data access.

SQL Clustered Indexes

SQL 中的 clustered index 是一种索引，它决定数据值在表中存储的物理顺序。

A clustered index in SQL is a type of index that determines the physical order in which the data values will be stored in a table.

在指定列上定义聚集索引时，在创建新表期间，将按排序顺序将数据插入该列中。这有助于更快地检索数据，因为数据以特定顺序存储。

When a clustered index is defined on a specific column, during the creation of a new table, the data is inserted into that column in a sorted order. This helps in faster retrieval of data since it is stored in a specific order.

It is recommended to have only one clustered index on a table. If we create multiple clustered indexes on the same table, the table will have to store the same data in multiple orders which is not possible.
When we try to create a primary key constraint on a table, a unique clustered index is automatically created on the table. However, the clustered index is not the same as a primary key. A primary key is a constraint that imposes uniqueness on a column or set of columns, while a clustered index determines the physical order of the data in the table.

Syntax

以下是在 SQL Server 中创建聚集索引的语法 −

Following is the syntax to create a clustered index with SQL Server −

CREATE INDEX index_name ON table_name(column_name [asc|desc])

其中，

Where,

index_name: specifies the name you want to give to the index being created.
column_name: specifies the column(s) that will be indexed in the order specified.
asc|desc: specifies the order (asc - ascending, desc - descending) in which the data should be sorted. The default sorting order is ascending order.

Example

在此示例中，让我们在 SQL Server 中的表上创建一个聚集索引。为此，我们需要首先使用以下查询创建一个名为 CUSTOMERS 的表 −

In this example, let us create a clustered index on a table in SQL Server. For that, we need to first create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE  INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2)
);

现在，使用以下查询将一些值插入 CUSTOMERS 表中 −

Now, insert some values into the CUSTOMERS table using the following query −

INSERT INTO CUSTOMERS VALUES
(7, 'Muffy', '24', 'Indore', 5500),
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(6, 'Komal', '22', 'Hyderabad', 9000),
(2, 'Khilan', '25', 'Delhi', 1500),
(4, 'Chaitali', '25', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(3, 'Kaushik', '23', 'Kota', 2000);

该表已在 SQL Server 数据库中成功创建。

The table is successfully created in the SQL Server database.

ID	NAME	AGE	ADDRESS	SALARY
7	Muffy	24	Indore	5500.00
1	Ramesh	32	Ahmedabad	2000.00
6	Komal	22	Hyderabad	9000.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
3	Kaushik	23	Kota	2500.00

现在，让我们使用以下查询在名为 ID 的列上创建一个聚集索引 −

Now, let us create a clustered index on the column named ID using the following query −

CREATE CLUSTERED INDEX CLU_ID ON CUSTOMERS(ID ASC);

Output

执行以上查询后，输出显示如下 −

On executing the above query, the output is displayed as follows −

Commands Completed Successfully.

Verification

要验证是否在 ID 列上定义了聚集索引，请检查 CUSTOMERS 表的记录是否按使用以下查询检索时对它们排序 −

To verify if the clustered index is defined on ID column, check whether the records of CUSTOMERS table are sorted by retrieving them using the following query −

SELECT * FROM CUSTOMERS;

表的记录按名为 ID 的列中的值按升序排序。

The records of the table are sorted in ascending order based on values in the column named ID.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	9000.00
7	Muffy	24	Indore	5500.00

Creating Clustered Index on Multiple Columns

通过以下示例，让我们了解在表的多个列上创建聚集索引时它如何工作。

With the following example, let us understand how clustered index works when it is created on multiple columns of a table.

考虑以前创建的 CUSTOMERS 表而非创建一个新表，并使用以下查询在此表的多个列（例如 AGE 和 SALARY）上定义一个聚集索引 −

Instead of creating a new table, consider the previously created CUSTOMERS table and define a clustered index on multiple columns of this table, such as AGE and SALARY, using the following query −

CREATE CLUSTERED INDEX MUL_CLUS_ID
ON CUSTOMERS (AGE, SALARY ASC);

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Commands Completed Successfully.

Verification

现在，让我们验证列 AGE 和 SALARY 中的值是否已排序 −

Now, let us verify whether the values in the columns AGE and SALARY is sorted or not −

SELECT * FROM CUSTOMERS;

正如我们在下面的表格中看到的，记录仅根据 AGE 列中的值进行排序，而不是 SALARY 列中的值。因此，建议在表上只有一簇索引。

As we can observe in the table below, the records are sorted only based on the values in AGE column and not with the values in SALARY column. So, it is recommended to have only one clustered index on a table.

ID	NAME	AGE	ADDRESS	SALARY
6	Komal	22	Hyderabad	9000.00
3	Kaushik	23	Kota	2500.00
7	Muffy	24	Indore	5500.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
1	Ramesh	32	Ahmedabad	2000.00