mysql - INDEX() 在 MySQL 中创建聚集索引还是非聚集索引？

Question

我正在学习在 CREATE TABLE 语句中使用 INDEX() 的教程，但没有解释它是集群的还是非集群的。我的问题是:INDEX() 在 CREATE TABLE 语句中使用时会产生聚簇索引还是非聚簇索引？

例如:

CREATE TABLE test (a varchar(30), b varchar(30), index(a));

/* Is column A a clustered or non-clustered index? */

还想知道如何做相反的事情:如果这个例子产生了一个非聚集索引，那么你如何编写一个聚集索引，反之亦然？

Answer 1

TL;DR 主键 - 只有主键 - 是聚簇索引。如果你没有显式定义主键，第一个合适的 UNIQUE使用 key 。如果您没有主键或合适的 UNIQUE 键，MySQL 会生成一个隐藏的聚集索引。您不能使用 INDEX() 创建聚簇索引。

作为 explained in the docs(添加了重点):

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.

...

• When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

• If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

...

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

另请参阅 definition of clustered index in the glossary ，它将其定义为“主键索引的 InnoDB 术语”，以及一些其他详细信息。

因此，要回答您的问题，除了创建主键或在没有主键的表上创建合适的 UNIQUE 键(所有键列都不是 NULL)之外，没有其他方法可以创建聚集索引。 INDEX() 只是创建一个辅助(即非集群) key ，无论您用它做什么。

^{* 注意:正如评论中所指出的，其他一些数据库根本没有聚簇索引，而有些则允许在一张表上使用多个聚簇索引。我在回答中只针对 MySQL。}