SQL - 聚集索引

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

表中的数据以一种称为“Heap”的无序数据结构形式存储，其中行没有特定顺序地放置。因此，在从表中检索数据时，查询优化器必须扫描整个表以定位请求的行。这个过程可能很耗时，特别是当处理大表时。为了加速数据检索，SQL 提供了一种称为 index 的数据对象，它以特定方式存储和组织表数据，从而允许更快的数据访问。

SQL 聚集索引

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

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

• 建议每个表只设置一个聚集索引。如果在同一表上创建多个聚集索引，表将不得不以多种顺序存储相同数据，这是不可行的。
• 当我们尝试在表上创建主键约束时，会自动在表上创建一个唯一的聚集索引。但是，聚集索引与主键并不相同。主键是一种约束，它强制列或一组列的唯一性，而聚集索引决定了表中数据的物理顺序。

MySQL 数据库没有单独规定 Clustered 和 Non-Clustered 索引。当在表上定义 PRIMARY KEY 时，会自动创建 clustered indexes。如果没有定义 PRIMARY KEY，则第一个 UNIQUE NOT NULL 键将被视为 Clustered index。

Syntax

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

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

其中，

• index_name：指定要为正在创建的索引指定的名称。
• column_name：指定将按指定顺序进行索引的列。
• asc|desc：指定数据排序的顺序（asc - 升序，desc - 降序）。默认排序顺序为升序。

Example

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

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

现在，使用以下查询向 CUSTOMERS 表插入一些值：

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 数据库中成功创建。

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

CREATE CLUSTERED INDEX CLU_ID ON CUSTOMERS(ID ASC);

Output

执行上述查询后，输出显示如下：

Commands Completed Successfully.

Verification

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

SELECT * FROM CUSTOMERS;

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

在多列上创建聚集索引

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

不创建新表，而是考虑之前创建的 CUSTOMERS 表，并使用以下查询在该表的多个列（如 AGE 和 SALARY）上定义聚集索引：

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

输出

执行上述查询时，将得到如下输出：

Commands Completed Successfully.

验证

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

SELECT * FROM CUSTOMERS;

如下面的表中所示，记录仅根据 AGE 列的值排序，而未根据 SALARY 列的值排序。因此，建议一个表上只有一个聚集索引。