MySQL忘记自动为外键创建索引了吗？

Question

在运行以下SQL语句之后，您将看到，在运行了MySQL first ALTER TABLE语句之后，已经在tag_id列上自动创建了非唯一索引ALTER TABLE。

但是在第二个 ALTER TABLE语句运行之后，我认为MySQL还应该为我在question_id列上自动创建另一个非唯一的索引question_tag_question_id_question_id。

但是，从SHOW INDEXES语句输出中可以看到，它并不存在。

为什么MySQL会忘记第二次 ALTER TABLE语句？

顺便说一句，由于我已经创建了一个唯一的索引question_id_tag_id_idx，使用了 question_id和tag_id列。是否为每个索引创建一个单独的索引是多余的？

mysql> DROP DATABASE mydatabase;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE mydatabase;
Query OK, 1 row affected (0.00 sec)

mysql> USE mydatabase;
Database changed
mysql> CREATE TABLE question (id BIGINT AUTO_INCREMENT, html TEXT, PRIMARY KEY(id)) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE tag (id BIGINT AUTO_INCREMENT, name VARCHAR(10) NOT NULL, UNIQUE INDEX name_idx (name), PRIMARY KEY(id)) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE question_tag (question_id BIGINT, tag_id BIGINT, UNIQUE INDEX question_id_tag_id_idx (question_id, tag_id), PRIMARY KEY(question_id, tag_id)) ENGINE = INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE question_tag ADD CONSTRAINT question_tag_tag_id_tag_id FOREIGN KEY (tag_id) REFERENCES tag(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE question_tag ADD CONSTRAINT question_tag_question_id_question_id FOREIGN KEY (question_id) REFERENCES question(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM question_tag;
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| question_tag |          0 | PRIMARY                    |            1 | question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          0 | PRIMARY                    |            2 | tag_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          0 | question_id_tag_id_idx     |            1 | question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          0 | question_id_tag_id_idx     |            2 | tag_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          1 | question_tag_tag_id_tag_id |            1 | tag_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.01 sec)

mysql>

Answer 1

来自文档

必须有一个索引，其中将外键列按相同顺序列为第一列。如果引用表不存在，则自动在该表上创建这样的索引。

question_id是主键和question_id_tag_id_idx索引的第一列。因此，外键不需要进一步的索引。question_id_tag_id_idx索引实际上是不必要的，因为主键按相同的顺序具有相同的列。主键是唯一的聚类索引。

对于question_tag_tag_id_tag_id外键，创建索引是因为没有以tag_id开头的现有索引。

question_id_tag_id_idx索引(或主键)可用于question_id上的外键，而不能用于tag_id上的外键的原因，由文档中关于MySQL如何使用索引的部分解释：

如果表有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果您有一个三列索引on (col1，col2，col3)，那么您已经在(col1)、(col1、col2)和(col1、col2、col3)上建立了索引搜索功能。

外键需要能够在引用表(question_tag)中快速查找值。question_id首先出现在question_id_tag_id_idx (是最左边的前缀)中，因此可以使用该索引。tag_id第二次出现，因此不能使用索引。

Answer 2

在多色索引中，我相信您可以免费获得第一列的索引，就好像只有该列有索引一样。

更新:正如其他回答者指出的那样，只有在特定索引上才会出现这种情况，这些索引是非空的，成为“聚集索引”。