“串行主KEY`”在表上创建两个索引吗？

Question

自SERIAL是简写BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE以来，SERIAL PRIMARY KEY似乎在UNIQUE和PRIMARY KEY上翻了一番：

> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);

> SHOW CREATE TABLE foo \G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

PRIMARY KEY和UNIQUE KEY id在SHOW CREATE TABLE中同时出现的因素是肯定的，但这真的是我看到的两个物理索引吗？

人们应该避免将PRIMARY KEY与SERIAL一起使用，而应该选择编写bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY，还是使用没有主键的SERIAL？

Answer 1

我还在5.7.18 MySQL社区服务器中看到了这种行为。

根据SHOW INDEX FROM foo的输出，正在创建和维护一个重复的索引：

mysql> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

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

编辑:这也发生在普通表中：

mysql> CREATE TABLE foo (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'foo';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | my_db             | PRIMARY         | my_db        | foo        | PRIMARY KEY     |
| def                | my_db             | id              | my_db        | foo        | UNIQUE          |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)