MySQL创建索引语义:集群还是非聚集？

Question

我刚接触过数据库，特别是MySQL，如果这是一个简单的问题，请原谅我。

当我调用MySQL时，我试图弄清楚在CREATE INDEX上发生了什么。我知道主键是唯一的，并且对应于聚集索引，但是如果我有一个没有定义主键并创建索引的表，会发生什么情况。我知道默认情况下，数据结构是B树，但不清楚这是否与典型意义上的聚集索引或非聚集索引相对应。

具体来说，我有一个表，它有四个列作为整数：

销售(item_id，时间，价格，location_id)

唯一具有唯一值的列是时间。如果我创建这个表，然后运行CREATE INDEX dx ON sales(item_id);，会发生什么？

在这里中，我相信当我创建没有主键的表时，时间是唯一的，也就是聚集索引，所以我在item_id上创建了一个非聚集索引。

我的问题是:这样做对吗？如果是这样，我如何在MySQL中的非唯一列上创建聚集索引？

Answer 1

(这假设您使用的是`ENGINE=InnoDB。)

您的表必须有一个主键:您有三个选择：

1. (首选)您显式地提供了这样的信息。
2. 有一个非空列的UNIQUE索引(S)。(马虎，就让它成为PK吧)
3. (不是很好的选择)一个隐藏的PK将被提供。

PK将是集群和独特的。这是MySQL中pk的唯一选择。

不能对其他索引进行聚类。再说一次，这是定义上的。

索引的结构有三种选择：

• (最常见) B+Tree。(这个答案中的其他内容都是B+Tree)
• FULLTEXT --用于搜索文本中的单词
• SPATIAL --用于二维搜索，如地理搜索

销售(item_id，时间，价格，location_id)

也许你需要PRIMARY KEY(item_id, time)。但是，这假设您永远不会在完全相同的time中为同一项进行两次销售。

因此，第5栏可能更安全：

id INT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY(id)

让我们看看SELECTs；从它们我们可以决定您需要什么次要索引(Es)。或者自己动手吧，这里。

我的问题是:这样做对吗？如果是这样，我如何在MySQL中的非唯一列上创建聚集索引？

这是一个很少有人发现的诡计。一旦您有了一些独特的东西(例如上面的第5列)，就可以这样做，而不是上面的PK：

PRIMARY KEY(time, id),
INDEX(id)

现在，即使是它，time也不是唯一的，PK是集群的，并且是唯一的，因为id加进去了。AUTO_INCREMENT不需要“成为某些索引中的第一列”。