CREATE TABLE test
(
user_id int unsigned not null,
post_id int unsigned not null,
primary key (user_id, post_id),
foreign key test_user_id (user_id) references user (id),
foreign key test_post_id (post_id) references post (id)
);
show index from test;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | |
| test | 0 | PRIMARY | 2 | post_id | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | test_post_id | 1 | post_id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+添加 id 列作为主键产生：
show index from test;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | test_user_id | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | test_post_id | 1 | post_id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+它显示索引 test_user_id 使用由外键约束中使用的列组成的复合主键时，不会创建。应该是这样吗？有没有办法克服这个问题(mysql（5.7版）
1条答案
按热度按时间toe950271#
创建外键约束时，必须有它的索引。如果需要的话，创建外键会自动创建一个新索引（至少是因为mysql的一些旧版本，比如4.0或其他版本，但是有一段时间，甚至旧版本也不会自动创建fk索引iirc）。
但是如果已经存在合适的索引，mysql就不需要创建新的索引。innodb至少有那么聪明。
任何包含fk列作为索引最左侧子集的索引都将满足要求。主键索引（即表的聚集索引）工作正常。