主键和外键的索引 [英] Indexes on primary and foreign keys

问题描述

我有一个使用GUI工具创建的数据库，并且我注意到KEY(又名INDEX)定义的用法不一致:

I have a database created with a GUI tool and I've noticed what appears to be an inconsistent use of KEY (aka INDEX) definitions:

CREATE TABLE `foo_bar` (
  `foo_id` int(10) unsigned NOT NULL,
  `bar_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`foo_id`, `bar_id`),
  KEY `foo_bar_fk2` (`bar_id`), -- <== ???
  CONSTRAINT `foo_bar_fk1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `foo_bar_fk2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`bar_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Links between Foo and Bar';

关于索引，我有以下问题:

1. 是否有必要为主键和外键明确定义索引?
2. 如果不是，您实际上获得了两个索引(并且性能降低了)吗?
3. InnoDB和MyISAM(外键分开)是否有区别?

1. Is it necessary to explicitly define indexes for primary and foreign keys?
2. If it's not, do you actually get two indexes (and less performance)?
3. Is it different in InnoDB and MyISAM (foreign keys apart)?

推荐答案

我一直在对您告诉我的内容进行一些实验，我认为我会将其作为答案.

I've been doing some experiments on what you told me and I'd thought I'd share it as answer.

首先，我创建一些测试表:

First I create some test tables:

CREATE TABLE foo (
    foo_id int(10) unsigned NOT NULL,
    PRIMARY KEY (foo_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
CREATE TABLE bar (
    bar_id int(10) unsigned NOT NULL,
    PRIMARY KEY (bar_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;


CREATE TABLE foo_bar (
    foo_id int(10) unsigned NOT NULL,
    bar_id int(10) unsigned NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

到目前为止，不存在索引:

So far, no indexes exists:

mysql> SHOW INDEXES FROM foo_bar;
Empty set (0.00 sec)

添加主键会生成索引:

mysql> ALTER TABLE foo_bar
    -> ADD PRIMARY KEY (`foo_id`, `bar_id`);
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM foo_bar;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foo_bar |          0 | PRIMARY  |            1 | foo_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          0 | PRIMARY  |            2 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)

如果我在foo_id上添加外键，则它会重复使用主键索引，因为该列是索引中的第一列:

If I add a foreign key on foo_id it reuses the primary key index since that column is the first one in the index:

mysql> ALTER TABLE foo_bar
    -> ADD CONSTRAINT `foo_bar_fk1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM foo_bar;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foo_bar |          0 | PRIMARY  |            1 | foo_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          0 | PRIMARY  |            2 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

如果在bar_id上添加外键，则会创建索引，因为无法重用现有索引:

If I add a foreign key on bar_id, it creates an index because no existing index can be reused:

mysql> ALTER TABLE foo_bar
    -> ADD CONSTRAINT `foo_bar_fk2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`bar_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM foo_bar;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foo_bar |          0 | PRIMARY     |            1 | foo_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          0 | PRIMARY     |            2 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          1 | foo_bar_fk2 |            1 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.02 sec)

我们的一个外键正在使用主键索引.这意味着我们无法删除该索引！

One of our foreign keys is using the primary key index. That means that we cannot remove such index!

mysql> ALTER TABLE foo_bar
    -> DROP PRIMARY KEY;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-568_c7d' to '.\test\foo_bar' (errno: 150)

除非我们为外键创建索引，否则我们将其自身放下:

Unless we create an index for the foreign key or we drop the key itself:

mysql> ALTER TABLE foo_bar
    -> DROP FOREIGN KEY `foo_bar_fk1`;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE foo_bar
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

---

结论是，当功能需要使用MySQL时(但仅在绝对必要时)，MySQL会自动创建索引.

---

The conclusion is that MySQL creates indexes automatically when they're required for a functionality (but only if they are strictly necessary).

这篇关于主键和外键的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！