mysql - 为什么 MySQL 会为组合键创建一个自动 "only one"索引？

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”呢？
• 如果我将“PRIMARY KEY (iid, pid)”更改为“PRIMARY KEY(pid, iid)”是否会为“iid_FOREIGN_KEY”创建一个自动索引而不是另一个“pid_FOREIGN_KEY”？

MySQL creates an auto index for "pid_FOREIGN_KEY" CONSTRAINT only

Answer 1

根据documentation关于外键约束。

index_name represents a foreign key ID. The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key. Otherwise, MySQL implicitly creates a foreign key index...

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

在没有 FOREIGN KEY 约束的表上使用 ALTER TABLE 时会发生相同的行为。

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确定主键中的第一个(最左边)列可以支持外键约束索引。参见:Multiple Column Indexes .

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

为了进一步阐明，primary key does have an associated index .

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