Mysql如何创建聚集索引？

Question

我正在阅读关于聚集索引如何工作的所有内容，并且认为它们会对我的应用程序有益。我知道主键是自动聚集的索引，但是如何将聚集索引添加到非主键列？

即用于用户帖子的数据存储。每个帖子都有一个ID，但也有一个user-id，但由于用户可以多次发表，所以user-id不是主键。如何将聚集索引添加到user-id，这是一个好主意吗？

Answer 1

根据Clustered and Secondary Indexes的说法，每个表只能有一个聚集索引。

聚集索引以外的所有索引都称为辅助索引。

如果表没有主索引，但有另一个唯一索引，则此索引用作聚集索引。

如果没有为表定义主键，MySQL将查找所有键列都不为NULL的第一个唯一索引，InnoDB将其用作聚集索引。

因此，我的结论是，您不需要自己添加聚集索引，但是MySQL会选择表的主索引或第一个惟一索引作为聚集索引。

如果您尚未定义主索引或唯一索引，MySQL会自行创建一个索引

如果表没有主键或合适的唯一索引，InnoDB将在包含行ID值的合成列上在内部生成一个名为GEN_CLUST_INDEX的隐藏聚集索引。这些行是按照InnoDB分配给这种表中的行的ID排序的。行ID是一个6字节的字段，随着新行的插入而单调递增。因此，按行ID排序的行实际上是按插入顺序排列的。

Answer 2

正如@Olaf所描述的，InnoDB选择哪个列或列组合将作为聚集索引(主键，如果没有主键，则选择第一个唯一索引；如果没有主键，则选择隐藏列)。

如果希望使用非唯一列作为聚集索引，可以将post_id定义为唯一键，并将user_id和post_id的组合作为主键，该主键将被选为聚集索引：

CREATE TABLE Post
( post_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
--- other columns
, CONSTRAINT Post_PK
    PRIMARY KEY (user_id, post_id)     -- your clustered index
, CONSTRAINT post_id_UQ
    UNIQUE (post_id)             -- you still want uniqueness for the `post_id`
) ENGINE = InnoDB ;

这是否是一个好主意取决于您的应用程序、数据量和您拥有的查询。一般来说，聚集键的最佳属性是唯一的、窄的、静态的和不断增加的。这就是为什么自动增量列是最好的。请阅读金伯利·L·特里普的博客文章：Ever-increasing clustering key - the Clustered Index Debate..........again!和The Clustered Index Debate Continues... (不要因为它们是针对SQL-Server而停下来的，99%的问题也适用于InnoDB的集群索引)

像(user_id, post_id)这样的聚集键具有前3个属性，但它不会一直增加。这将导致CI的碎片，并可能减慢插入表的速度。

但是，它将产生更高效的查询，这些查询具有WHERE user_id = ?条件或范围条件、WHERE user_id BETWEEN ? AND ?或GROUP BY user_id分组，因为所需的数据将以所需的顺序在聚集索引中的一个位置找到。

我建议您进行测试，以选择最适合您的情况。

还有一个MySQL变体TokuDB，它允许在一个表中有多个聚集索引。详细信息请参阅他们的文章：

Answer 3

为表定义InnoDB主键时，MySQL使用主键作为聚集索引。

如果没有表的主键，MySQL将搜索所有键列都不为NULL的第一个唯一索引，并将该唯一索引用作聚集索引。

如果InnoDB表没有主键或合适的惟一索引，MySQL将在包含行ID值的合成列上生成一个名为GEN_CLUST_INDEX的隐藏聚集索引。