關(guān)于創(chuàng)建外鍵，創(chuàng)建索引的問題

如下所示的三張表的創(chuàng)建過程， test1 和 test2 分別有一個(gè)字段，然后作為各自表的主鍵，

test3 中有兩個(gè)字段，分別作為外鍵引用 test1 和 test2 中的場景，

mysql> create table test1(field1 int, primary key(field1));
Query OK, 0 rows affected (0.04 sec)

mysql> create table test2(field2 int, primary key(field2));
Query OK, 0 rows affected (0.00 sec)

mysql> create table test3(field1 int ,field2 int,primary key(field1,field2),foreign key(field1) references test1(field1), foreign key(field2) references test2(field2));
Query OK, 0 rows affected (0.06 sec)

mysql> show index from test3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test3 |          0 | PRIMARY  |            1 | field1      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test3 |          0 | PRIMARY  |            2 | field2      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test3 |          1 | field2   |            1 | field2      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql>

這里在最后使用 show index from test3 時(shí)，會(huì)出現(xiàn)一個(gè) Key_name 為 field2 的索引，，這是什么原因？？

我使用 show create table test3 ，查看創(chuàng)建表的語句，MySQL 隱示的給我創(chuàng)建了該索引，感覺很奇怪

CREATE TABLE `test3` (
  `field1` int(11) NOT NULL DEFAULT '0',
  `field2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`field1`,`field2`),
  KEY `field2` (`field2`),
  CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test1` (`field1`),
  CONSTRAINT `test3_ibfk_2` FOREIGN KEY (`field2`) REFERENCES `test2` (`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我嘗試過很多創(chuàng)建表的操作，修改 primary key(field1, field2) 為 primary key(field2,field1) ，即簡單的調(diào)換順序后，這里的索引由 field2 變?yōu)?field1。

例如，我不定義主鍵，定義兩個(gè)外鍵，會(huì)有兩個(gè)索引被創(chuàng)建：

mysql> create table test3(field1 int ,field2 int,foreign key(field1) references test1(field1), foreign key(field2) references test2(field2));
Query OK, 0 rows affected (0.05 sec)

mysql> show index from test3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test3 |          1 | field1   |            1 | field1      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test3 |          1 | field2   |            1 | field2      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

然后，我使用其中的一列作為主鍵，并且兩列都為一個(gè)外鍵，會(huì)有一個(gè)索引，

mysql> create table test3(field1 int ,field2 int,primary key(field1),foreign key(field1) references test1(field1), foreign key(field2) references test2(field2));
Query OK, 0 rows affected (0.31 sec)

# 查看創(chuàng)建語句如下
CREATE TABLE `test3` (
  `field1` int(11) NOT NULL DEFAULT '0',
  `field2` int(11) DEFAULT NULL,
  PRIMARY KEY (`field1`),
  KEY `field2` (`field2`),
  CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test1` (`field1`),
  CONSTRAINT `test3_ibfk_2` FOREIGN KEY (`field2`) REFERENCES `test2` (`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

而在使用兩列一起作為主鍵時(shí)，為什么還是會(huì)有一個(gè)field2 索引呢，

在我的理解中，應(yīng)該是兩個(gè)才對(duì)，就算，因?yàn)橹麈I的原因，那也應(yīng)該是有既沒有 field1 ，也沒有 field2 ,只有一個(gè) PRIMARY 的索引才對(duì)。？？？

原始的場景不是這個(gè)樣子，不過跟上述的示例能夠表達(dá)我的疑惑了，