MySQL - "many"关系的 "one to many"侧的聚集索引

Question

我相信这对你们中的许多人来说都是简单的事情，所以我希望你们能轻松地提供帮助。

如果我在“一对多”关系的“多”端有一个 MySQL 表——像这样:

Create Table MyTable(
ThisTableId int auto_increment not null,
ForeignKey int not null,
Information text
)

因为这个表总是通过使用外键的连接来使用，所以将外键做成聚集索引似乎很有用，这样对于相同的源记录，外键总是相邻排序。但是，ForeignKey 不是唯一的，所以我认为将其作为聚簇索引是不可能的还是不好的做法？如果我尝试使用 (ForeignKey, ThisTableId) 创建复合主键以实现有用的集群和唯一性，则会出现错误“只能有一个自动列，并且必须将其定义为键”。

我想也许我处理这个问题的方式不正确，在这种情况下，为上表编制索引以获得最大速度的最佳方法是什么？

Answer 1

InnoDB 要求如果你有一个自动递增的列，它必须是键中的第一个列。

因此您不能将主键定义为 (ForeignKey, ThisTableId) -- 如果 ThisTableId 是自动递增的。

如果 ThisTableId 只是一个常规列(不是自动递增)，您可以这样做，但是您将负责分配一个值，该值至少在 ForeignKey 中具有相同值的其他行中是唯一的。

我见过的一种方法是使列成为 BIGINT UNSIGNED，并使用 BEFORE INSERT 触发器从函数 UUID_SHORT() 中为该列分配一个值。

---

@ypercube 正确指出了另一个解决方案:InnoDB 规则是自增列应该是some 键的第一列，如果您创建一个普通的辅助键就足够了。这允许您创建如下表:

CREATE TABLE `MyTable` (
  `ForeignKey` int(11) NOT NULL,
  `ThisTableId` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ForeignKey`,`ThisTableId`),
  KEY (`ThisTableId`)
) ENGINE=InnoDB;

并且自动增量按预期工作:

mysql> INSERT INTO MyTable (ForeignKey) VALUES (123), (234), (345), (456);

mysql> select * from MyTable;
+------------+-------------+
| ForeignKey | ThisTableId |
+------------+-------------+
|        123 |           1 |
|        234 |           2 |
|        345 |           3 |
|        456 |           4 |
+------------+-------------+