代码之家 › 专栏 › 技术社区 › bobo

mysql忘记自动为外键创建索引了吗?

innodb foreign-keys indexing database-design mysql

bobo · 技术社区 · 15 年前

在运行以下sql语句之后,您将看到,mysql已经自动创建了非唯一索引 question_tag_tag_id_tag_id 上 tag_id 我的专栏之后第一 ALTER TABLE 语句已运行。

但是在第二 变更表 语句已运行,我认为mysql还应自动创建另一个非唯一索引 question_tag_question_id_question_id 上 question_id 我的专栏。

但是你可以从 SHOW INDEXES 语句输出,它不在那里。

为什么mysql忘记了第二 变更表 声明?

顺便说一下,因为我已经创建了一个唯一的索引 question_id_tag_id_idx 被使用 二者都 疑问句 和 塔吉德 柱。为每个索引创建单独的索引是多余的吗?

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>

2 回复 | 直到 15 年前

Phil Ross Matt Johnson-Pint 15 年前

从 documentation :

必须有一个索引外键列被列为按相同顺序排列的第一列。这样在引用上创建索引如果没有,则自动生成表存在。

question_id 是主键和 question_id_tag_id_idx 索引。因此,外键不需要进一步的索引。这个 问号 索引实际上是不必要的,因为主键具有相同顺序的相同列。主键是唯一的 clustered indexes .

对于 question_tag_tag_id_tag_id 外键,将创建索引,因为没有以 tag_id .

原因是 问号 索引(或主键)可用于上的外键 疑问句 ,但外键不能打开 塔吉德 是由 how MySQL uses indexes 在文档中:

如果表有多个列索引,任何最左边的前缀索引可由优化器用于查找行。例如,如果您有上的三列索引(col1,col2, col3),你已经索引了搜索在(col1),(col1,col2)上的功能, 和(col1,col2,col3)。

外键需要能够在引用表中快速查找值( question_tag ) 疑问句 出现在 问号 (是最左边的前缀),因此可以使用此索引。 塔吉德 出现在第二个,因此无法使用索引。

Zak 15 年前

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

更新:正如另一个回答者所指出的,这只是特定索引的情况,这些索引不为空,成为“聚集索引”。