sql-clustered-index

理解 SQL 索引：聚集索引

数据库中的索引是一种特殊的数据结构，它允许从表中更快地检索数据，类似于书的末尾的索引。您无需阅读整本书（即“全表扫描”），而是可以在索引中查找主题并直接跳转到正确的页面。聚集索引是一种特殊类型的索引，它决定了表中数据的物理顺序。

什么是聚集索引？

想象一本字典或电话簿。单词或姓名不仅按字母顺序索引——整本书都是按该顺序物理排序的。这正是聚集索引的工作方式。它根据其键列中的值对表的行进行排序。

由于数据本身只能以一种物理顺序排序，因此一个表只能有一个聚集索引。

聚集索引和主键

在许多流行的数据库系统中，主键和聚集索引之间存在密切关联：

• SQL Server 和 MySQL (InnoDB)：当您在一个表上定义 PRIMARY KEY 时，数据库引擎默认会自动在该键上创建一个唯一的聚集索引。
• 在 MySQL 的 InnoDB 引擎中，如果您没有定义 PRIMARY KEY，它将使用第一个 UNIQUE NOT NULL 索引作为聚集索引。如果不存在这样的索引，InnoDB 会在内部创建一个隐藏的聚集索引。

创建聚集索引 (SQL Server 语法)

虽然在 MySQL 中聚集索引通常通过主键定义，但在 SQL Server 中您可以显式创建它。让我们首先创建一个没有主键的表来说明。

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    StartDate DATE
);

-- 数据以随机顺序插入
INSERT INTO Employees (EmployeeID, FirstName, LastName, StartDate) VALUES
(103, 'Charlie', 'Brown', '2022-08-01'),
(101, 'Alice', 'Smith', '2021-06-15'),
(102, 'Bob', 'Johnson', '2020-03-10');

-- 现在，在 EmployeeID 上创建聚集索引
CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees(EmployeeID ASC);

创建聚集索引后，Employees 表中的物理数据将按 EmployeeID 重新排序。现在，SELECT * FROM Employees; 将自然地按 101、102、103 的顺序返回行，这对于 WHERE EmployeeID BETWEEN 100 AND 105 这样的范围查询来说要快得多。

多列上的聚集索引

聚集索引也可以在多个列上创建。排序是分层的，就像按一列排序，然后按另一列排序电子表格一样。数据首先按索引定义中的第一列排序，然后对于第一列中具有相同值的行，再按第二列排序，依此类推。

CREATE TABLE OrderItems (
    OrderID INT,
    OrderItemID INT,
    ProductName VARCHAR(100),
    PRIMARY KEY (OrderID, OrderItemID) -- 在 SQL Server/MySQL 中，这会创建一个聚集索引
);

在此示例中，数据将首先按 OrderID 物理存储和排序，然后，在每个 OrderID 内部，它将按 OrderItemID 排序。这对于检索特定订单的所有项目（WHERE OrderID = ?）来说效率极高。

聚集索引 vs. 非聚集索引

这里有一个快速比较：

属性	聚集索引	非聚集索引
每个表的数量	只有一个	多个
数据存储	排序并存储表的物理数据	与数据行分离的结构
叶节点	包含实际数据页	包含指向数据行（行定位器）的指针
类比	一本字典（数据按字母顺序排序）	书后的索引（指向特定页面）

最佳实践和性能影响

• 明智选择：聚集索引键的选择是一个关键的设计决策。它通常被称为表上“最重要的索引”。
• 理想的键是窄的、静态的和唯一的：AUTO_INCREMENT 或 IDENTITY 整型列是极佳的候选。它们小巧、永不改变且总是唯一的。
• 避免使用宽或易变键：使用宽键（例如，长字符串）意味着同一表上的所有非聚集索引也会变得更大，因为它们需要存储聚集键值。使用频繁更改的键（例如，人名）可能会导致显著的性能开销，因为数据库可能需要物理移动行以保持排序顺序。
• 范围扫描：聚集索引为选择值范围的查询（例如 WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31'）提供了巨大的性能提升。