为什么MySQL为复合键创建一个自动的“只有一个”索引？

Question

在此代码中：

CREATE TABLE institution (
    iid INT(6) AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    PRIMARY KEY (iid))
ENGINE = INNODB;

CREATE TABLE plan (
    pid INT(2) AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    PRIMARY KEY (pid))
ENGINE = INNODB;

CREATE TABLE subscription (
    iid INT(6),
    pid INT(2),
    PRIMARY KEY (iid, pid),
    CONSTRAINT iid_FOREIGN_KEY FOREIGN KEY (iid) REFERENCES institution (iid),
    CONSTRAINT pid_FOREIGN_KEY FOREIGN KEY (pid) REFERENCES plan (pid))
ENGINE = INNODB;

• 为什么MySQL只为"pid_FOREIGN_KEY“约束创建自动索引？为什么不也用"iid_FOREIGN_KEY“呢？
• 如果我将“主键(iid，pid)”更改为“主键(pid，iid)”，它是否会为"iid_FOREIGN_KEY“创建自动索引，而不会为"pid_FOREIGN_KEY”创建另一个索引？

“键”约束

Answer 1

根据文档对外键的约束。

index_name index_name表示一个外键ID。如果子表上已经有一个可以支持外键的显式定义索引，则忽略值。否则，MySQL将隐式创建外键索引.

MySQL确定主键可以支持列上的外键引用。如果删除主键，则两个索引都是隐式创建的。

在没有外键约束的表上使用ALTER时也会发生相同的行为。

ALTER TABLE `subscription`
    ADD CONSTRAINT `iid_FOREIGN_KEY` FOREIGN KEY (`iid`) REFERENCES `institution` (`iid`),
    ADD CONSTRAINT `pid_FOREIGN_KEY` FOREIGN KEY (`pid`) REFERENCES `plan` (`pid`);

您也可以在CREATE TABLE语句中显式定义索引。

CREATE TABLE `subscription` (
    `iid` INT(6),
    `pid` INT(2),
    PRIMARY KEY (`iid`, `pid`),
    INDEX `iid_FOREIGN_KEY` (`iid`),
    INDEX `pid_FOREIGN_KEY` (`pid`),
    CONSTRAINT FOREIGN KEY (`iid`) REFERENCES `institution` (`iid`),
    CONSTRAINT FOREIGN KEY (`pid`) REFERENCES `plan` (`pid`)
)
ENGINE = INNODB;

列顺序影响具有多列索引的值的索引，因此MySQL确定主键中的第一个(最左边)列可以支持外键约束索引。见：多列索引。

MySQL可以将多列索引用于测试索引中所有列的查询，也可以使用查询，只测试第一列、前两列、前三列等等。如果在索引定义中按正确的顺序指定列，那么单个复合索引可以加快同一表上的几种查询。

为了进一步澄清，主键确实有一个关联的索引。。

因此，在主键多列索引的第一个(最左边)列上指定一个索引是多余的。