Mysql如何创建聚集索引？

21 浏览
0 Comments

Mysql如何创建聚集索引？

我正在研究有关聚集索引的工作原理，并且认为它们对我的应用程序会有好处。我知道主键会自动成为聚集索引，但如果要为非主键列添加聚集索引，应该怎么做呢？例如，一个用于存储用户帖子的数据存储。每个帖子都有一个ID，但也有一个用户ID，但由于用户可以多次发帖，所以用户ID不是主键。如何为用户ID添加聚集索引，这样做是否明智？

0

3 答案

0 Comments

MySQL中如何创建聚簇索引？

当你为一个InnoDB表定义主键时，MySQL会使用主键作为聚簇索引。

如果你没有为一个表定义主键，MySQL会寻找第一个所有关键列都不为NULL的UNIQUE索引，并将其作为聚簇索引。

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

如果存在多个唯一索引，那么从左边开始第一个设置了唯一键的列将成为聚簇索引。

0
0 Comments

问题的出现原因：

InnoDB选择哪个列或列组合将作为聚集索引（主键，如果没有主键则选择第一个唯一索引，如果两者都没有，则选择隐藏列）。如果想要将非唯一列作为聚集索引，可以将post_id定义为唯一键，并将user_id和post_id的组合定义为主键，该主键将被选择为聚集索引。

解决方法：

可以通过以下SQL语句创建带有聚集索引的表：

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 ;

这种方法将会使查询具有"WHERE user_id = ?"条件或范围条件"WHERE user_id BETWEEN ? AND ?"或"GROUP BY user_id"的效率更高，因为所需的数据将在聚集索引中以所需的顺序找到。

但是，这种方法可能会导致聚集索引的碎片化，并且插入操作可能变慢。因此，建议在具体情况下进行测试选择最佳的方法。

另外，MySQL还有一种变体TokuDB，允许一个表中有多个聚集索引。详情请参考他们的文章："Introducing Multiple Clustering Indexes"。

0
0 Comments

Mysql如何创建聚集索引？

根据《Clustered and Secondary Indexes》，每个表只能有一个聚集索引。

除了聚集索引之外的所有索引都被称为辅助索引。

如果一个表没有主索引，但有另一个唯一索引，那么这个唯一索引将被用作聚集索引。

所以，我可以得出结论，你不需要自己添加聚集索引，而是MySQL选择表中的主索引或第一个唯一索引作为聚集索引。

如果你没有定义主键或唯一索引，MySQL会自动创建一个索引。

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

那么如果主键和唯一索引都不存在怎么办？

查看《primary key》：InnoDB要求每个表都有这样的索引。

如果你创建了一个具有主键和unique key not null列的表，主键将成为聚集索引。

0