是否可以在唯一索引(而不是主键)上创建MySQL集群？

Question

MySQL文档指出：

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

但这并不是唯一的可能性，您可以通过一个唯一的索引进行聚类：

如果不为表定义主键，MySQL将定位第一个唯一索引，其中所有键列都不是NULL，InnoDB使用它作为聚集索引。

似乎是这样的，如果我首先在表上创建一个唯一的索引，它将被标记为聚集；然后我可以创建一个主键，并且它将是非聚集的：

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

然而，当我检查结果表时，我的希望破灭了：

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

关于DBA.SE的另一个问题意味着创建索引作为CREATE TABLE脚本的一部分，而不是之后，作为单独的CREATE INDEX语句，可能会产生不同的效果。然而，我也得到了同样的结果。

是否有一种方法可以强制MySQL在我选择的唯一索引上群集，而不是主键，还是这个“回退选项”只适用于没有主键的表？

Answer 1

PRIMARY KEY

• InnoDB在每个表上正好有一个PRIMARY KEY。
• PRIMARY KEY始终是群集的；不能有任何其他“聚集”索引。
• 该PK要么是显式声明的PK (这是首选的)，要么是第一个UNIQUE键(有一定的限制)，或者是隐藏的序列号(与`AUTO_INCREMENT不完全相同；6个字节)。
• PK是UNIQUE和is和索引。
• PK (和其他索引)可能包含多个列(“复合”索引)。
• 表的结构是一个BTree (实际上是一个B+Tree)，所有列都有，BTree是根据PK排序的。

辅助键

• 所有其他索引都被称为“二级索引”。(“键”“==”索引)
• 该结构是一个B+Tree，按键排序。
• 此BTree的“行”包含PRIMARY KEY的列(S)的副本(即‘聚集索引’)。这就是辅助键如何到达行的方式。
• 请注意，通过辅助键查找一行需要向下钻取两个BTrees。(除非是“覆盖”指数)。
• UNIQUE KEY是两样东西:一个次要键(除非升级到PK)和一个“唯一性约束”。

其他笔记

• 与其他一些供应商不同，InnoDB没有"ROWNUM“或”行定位器“的概念。PK充当行定位器。
• MySQL在InnoDB上没有“堆”或“位”索引。
• InnoDB只有BTree、空间和全文；在这个答案中，我不讨论后两者。
• 在大多数情况下，您是在CREATE TABLE语句中创建索引还是稍后执行CREATE INDEX并不重要。同样用于定义FOREIGN KEYs。
• 从结构上讲，PK+data是一个B+Tree；每个次要索引都是一个B+Tree。这些B+Trees的外观、感觉、气味等都是一样的。唯一的区别是你在“行”中发现了什么。

AUTO_INCREMENT

• 有许多警告。这不一定是连续的数字，有很多种可能出现差距的方式。只有相信他们会是“独一无二的”。
• AUTO_INCREMENT不必声明为PRIMARY KEY，甚至不必声明为UNIQUE。

一个诡计。可以做以下工作。这就是你钓鱼的目的吗？？

CREATE TABLE ... (
  id INT AUTO_INCREMENT,
  foo ...,
  ...
  PRIMARY KEY (foo, id),  -- to get clustering on `foo` and uniqueness (via `id`)
  INDEX(id)               -- sufficient to keep AUTO_INCREMENT happy.
) ENGINE=InnoDB;

Answer 2

主键是唯一的键和索引。因此，将PK创建为要在其上进行集群的唯一索引。

创建之后添加一个索引，作为Create的一部分，结果是相同的。