mysql创建复合主键索引语法_为什么MySQL为复合键创建一个自动的“只有一个”索引？...

根据外键约束的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.

因此,在主键多列索引的第一(最左侧)列上指定一个索引将是多余的.