如何在MySQL中创建聚集索引？

在数据库性能优化中，索引是一个重要的工具。在MySQL中，聚集索引（Clustered Index）是一种特殊的索引，它将表中的记录按照索引顺序存储。了解如何创建聚集索引以及它的特点，对提高查询效率和优化表结构至关重要。

这篇文章我们将详述MySQL中聚集索引的基础理论、规则以及相关操作方法。

什么是聚集索引？

聚集索引并不是一种单独的索引类型，而是表中的记录按照索引的顺序存储。在MySQL的InnoDB存储引擎中，聚集索引与表的物理存储顺序紧密相关。

一个表只能有一个聚集索引，其他所有的索引被称为辅助索引（Secondary Index）。当通过辅助索引检索数据时，数据库会先通过辅助索引找到对应的记录指针，然后再访问聚集索引以取回完整的行数据。

MySQL如何选择聚集索引？

在MySQL的InnoDB存储引擎中，以下规则决定了表的聚集索引：

PRIMARY KEY作为默认聚集索引：如果表定义了一个主键（PRIMARY KEY），那么主键自动成为聚集索引。
首个非空的唯一索引（UNIQUE Index）：如果表没有定义主键，但有一个唯一索引（并且所有索引列设置为NOT NULL），那么此唯一索引会被用作聚集索引。
隐式生成的内部索引：如果表既没有主键也没有合适的唯一索引，InnoDB会创建一个隐藏的聚集索引。它是一个名为GEN_CLUST_INDEX的索引，基于自动生成的6字节行ID来存储行数据。这些行ID在插入记录时单调递增。

以下是使用这些规则的一个示例：

CREATE TABLE ExampleTable (
    column1 INT NOT NULL,
    column2 INT NOT NULL UNIQUE,
    column3 VARCHAR(100)
) ENGINE=InnoDB;

在这个表中，由于column2是唯一索引且非空，当表没有定义主键时，它会被InnoDB选择为聚集索引。

如果既没有主键也没有唯一索引，则InnoDB会为表生成自动的GEN_CLUST_INDEX。

如何定制聚集索引？

虽然MySQL不能直接在一个非主键列创建聚集索引，但通过修改主键定义，可以达到调整聚集索引的目的。例如，希望一个组合键成为聚集索引。

以下是一个典型的案例，表Post中有两个相关的字段：user_id和post_id。默认情况下，post_id可能是主键，并因此成为聚集索引。但通过重设主键为组合键(user_id, post_id)，可以改变聚集索引的行为。

示例代码：

CREATE TABLE Post (
    post_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    content TEXT,
    PRIMARY KEY (user_id, post_id),   -- 设置组合键为聚集索引
    UNIQUE (post_id)                 -- 确保post_id唯一性
) ENGINE=InnoDB;

在上述示例中，user_id和post_id的联合主键成为表的聚集索引。这种调整可以优化按user_id查询或分组时的性能。